Incremental Daily Aggregates with ClickHouse Materialized Views

Scanning billions of raw events for a daily user total is expensive even on MergeTree. A ClickHouse Materialized View lets you push a lightweight transformation into the insert path so the pre-aggregated result is written to a target table as new blocks arrive. The trade-off is that the view only reacts to INSERTs, it does not reprocess history, and correctness of the final aggregate depends on the target table engine.

How a Materialized View triggers on insert

A Materialized View is a table with the MaterializedView engine. It stores the view definition and a reference to the source table. On each INSERT into the source, ClickHouse runs the view's SELECT against that incoming block only, then inserts the result into the target table specified with TO.

The SELECT is evaluated per batch, not over the whole source. If the SELECT contains GROUP BY, each batch is aggregated independently. The target table must be able to merge partial results, for example SummingMergeTree, otherwise duplicate keys will accumulate.

Worked configuration for daily user sums

Run the following as a user with CREATE TABLE and INSERT privileges on the default database.

Source table

CREATE TABLE default.events (
    event_time DateTime,
    user_id UInt64,
    value Float64
) ENGINE = MergeTree()
ORDER BY (event_time, user_id);

Risk: ORDER BY choice affects insert performance and later merges. Changing the source table name later breaks the view definition.

Target table

CREATE TABLE default.daily_sum (
    day Date,
    user_id UInt64,
    total Float64
) ENGINE = SummingMergeTree()
ORDER BY (day, user_id);

SummingMergeTree merges rows with the same ORDER BY key by summing the Summing columns. The target engine is your responsibility.

Materialized View

CREATE MATERIALIZED VIEW default.mv_daily_sum
TO default.daily_sum
AS SELECT
    toDate(event_time) AS day,
    user_id,
    sum(value) AS total
FROM default.events
GROUP BY day, user_id;

The view references default.events by name. Renaming or dropping the source table invalidates the view. The view does not process rows already in default.events.

Limits you must design around

• Insert-only trigger. UPDATE and DELETE via mutations on the source do not propagate to the target.
• No retroactive processing. Creating the view does not backfill. A one-off INSERT SELECT into the target table is required for historical data.
• Batch-level aggregation. Global correctness relies on the target engine merging partial aggregates. Heavy functions or joins in the view SELECT increase insert latency.
• Replication behavior is version dependent. Historically a materialized view had to exist on each replica receiving inserts. Newer releases add support for replicated materialized views via ZooKeeper/ClickHouse Keeper. Verify the behavior for your deployment before assuming replication.

Common mistakes

Mistake	Why it hurts	Mitigation
Creating a target with a non-merging engine	Partial aggregates from each insert batch remain separate and queries return inflated or duplicated results.	Choose an engine that merges, e.g., SummingMergeTree, and align ORDER BY with grouping keys.
Heavy joins or dictionaries in the view	Insert path becomes slow and can time out.	Keep the view SELECT lightweight. Use separate views or application-side enrichment for complex pipelines.
Circular view dependencies	View A writes to table B which is source for view B writing back to A can cause loops or crashes.	Map data flow linearly: source → view → target, with no feedback edges.

Check the result

Insert test data into the source and inspect the target. Visibility may be delayed by background merges, so check again after a short wait.

INSERT INTO default.events VALUES ('2026-10-10 10:00:00', 123, 10.5);
SELECT * FROM default.daily_sum WHERE user_id = 123;

Check view health:

SELECT name, database, is_active FROM system.views WHERE name = 'mv_daily_sum';

An active view with no failed parts suggests the definition is accepted. If the target is empty after inserts, verify the view references the correct source name and that the target table exists before the view was created.

Rollback

Removing the configuration changes state. Drop the materialized view table first, then the target table if it is no longer needed.

DROP TABLE default.mv_daily_sum;
DROP TABLE default.daily_sum;

Source data in default.events is unaffected.