Lakehouse

ClickHouse materialized views are insert triggers

, 2 minute read

The name sets the wrong expectation. In Postgres a materialized view is a stored query result you refresh. In ClickHouse, an ordinary materialized view is an insert trigger: every block inserted into the source table runs through the view's SELECT, and the result is inserted into a target table.

Everything odd about them follows from that sentence.

They only see new inserts

Creating a view over a table that already holds a year of data does nothing to that year. POPULATE exists, but it races with concurrent inserts and can drop or duplicate rows. Backfill explicitly instead, in slices you can retry:

CREATE MATERIALIZED VIEW daily_mv TO daily AS
SELECT account_id, toDate(ts) AS day, countState() AS events
FROM events
GROUP BY account_id, day;

-- backfill one month at a time
INSERT INTO daily
SELECT account_id, toDate(ts) AS day, countState()
FROM events
WHERE ts >= '2026-01-01' AND ts < '2026-02-01'
GROUP BY account_id, day;

Create the view first, note the time, then backfill everything before that point. Avoiding overlap is your job, not the database's.

They aggregate per block

The GROUP BY inside the view runs over a single insert block. Two inserts for the same account and day produce two rows in the target. That is why the target should be an AggregatingMergeTree, and why reads need the -Merge combinator:

SELECT account_id, day, countMerge(events) AS events
FROM daily
GROUP BY account_id, day;

Forgetting the outer GROUP BY gives numbers that look right in testing and drift in production, depending on when background merges happened to run.

Joins see the right side as it is now

A join inside the view reads the right-hand table at insert time. Update the dimension later and old rows keep the old value. For slowly changing attributes, a dictionary with a lifetime is usually the better tool.

Deletes don't propagate

Lightweight deletes and mutations on the source table never reach the target. If you need to remove a customer's data, plan to delete from every target table as well.