skip to content

When a block is inserted into a ClickHouse table, what data can its materialized view's SELECT actually see?

level: middleimportance: must knowfreq 70%

answer

  1. scope is narrower than the table
  2. think about what one INSERT hands the view
  3. the whole table is not in scope
  4. the block being inserted, nothing else
  5. merges and mutations never fire it

basics

~20 s

Only the rows in the block just inserted. A ClickHouse materialized view runs its SELECT as if that single block were the whole source table, so it never sees existing rows, and merges, mutations and TTL deletions do not trigger it at all.

solid answer

~50 s

The view's `SELECT` is executed over the freshly inserted block and nothing else — the rest of the source table is invisible. Three consequences follow. First, any `GROUP BY` inside the view aggregates *within the block*, so the target table receives one partial row per group per block; the target must therefore be an aggregating engine, or you re-aggregate at read time. Second, `JOIN` behaves asymmetrically: only the leftmost table in the `FROM` is the trigger source, and the right-hand side is read fresh on every block. That makes joins expensive on the insert path and means new rows in the joined table never produce view output. Third, only inserts fire the view. `ALTER TABLE ... UPDATE`/`DELETE`, TTL expiry, `DROP PARTITION` and background merges — including `ReplacingMergeTree` collapsing duplicates — never reach it, so the target can drift from the source.

code

sql · 12 lines
sql
-- Two inserts, same day, one view
CREATE TABLE daily (day Date, c UInt64)
ENGINE = SummingMergeTree ORDER BY day;

CREATE MATERIALIZED VIEW mv TO daily AS
SELECT toDate(ts) AS day, count() AS c
FROM events GROUP BY day;

INSERT INTO events VALUES (now(), 1), (now(), 2);  -- writes daily: (today, 2)
INSERT INTO events VALUES (now(), 3);              -- writes daily: (today, 1)
-- daily now holds two rows until a merge collapses them
SELECT day, sum(c) FROM daily GROUP BY day;        -- always re-aggregate

go deeper

for a junior

Remember the boundary: the view sees only the rows of the insert that triggered it, so it cannot look at data already stored in the table.

for a middle

Explain the three consequences precisely — per-block partial aggregates needing an aggregating target, joins that trigger only on the left table and re-read the right one, and inserts as the only trigger.

for a senior

Diagnose divergence between a rollup and its source: look for mutations, TTL, DROP PARTITION, a collapsing source engine, or an overlapping backfill, and know that none of them reach the view.

for a principal

Decide when incremental views are the wrong tool at all — mutable or late-correcting sources need a recompute strategy, not a trigger, and that choice shapes the whole ingest architecture.

## The block is the world ClickHouse ingests rows in *blocks*: a batch of columns produced by one `INSERT` (split by `max_insert_block_size`, or assembled by the async-insert buffer when that is enabled). A materialized view attached to a table is invoked once per block. Inside that invocation, the view's `SELECT` runs as though the block were the entire source table. This single fact explains almost every surprise people hit with ClickHouse materialized views. ## Consequence 1: aggregation is per block, not per table Write this view: ```sql CREATE MATERIALIZED VIEW mv TO daily AS SELECT toDate(ts) AS day, count() AS c FROM events GROUP BY day; ``` Each insert produces one row per distinct day *in that block*. Insert a hundred batches covering today and you get a hundred rows for today in `daily`, not one. Nothing is wrong — the view did exactly what it promised. It is your job to make the target table converge, which means either an aggregating engine (`SummingMergeTree`, `AggregatingMergeTree`) whose background merges collapse rows sharing the sort key, or a read-time `GROUP BY` over the target — in practice both, because merges are asynchronous and you can never assume they have run. The same reasoning kills any expression that needs global context: `row_number()`-style numbering across the table, a running total since the beginning of time, or a dedupe against rows already stored. None of that is available, because the earlier rows are not in scope. ## Consequence 2: joins are one-sided and re-read per block A view whose `SELECT` joins the source to a dimension table triggers only on the leftmost table in the `FROM`. Inserts into the right-hand table produce nothing. Worse, the right-hand side is evaluated for every block, so a join against a large table multiplies your insert cost by the size of that read. The idiomatic alternative is a `Dictionary` with `dictGet(...)`, which is an in-memory lookup with a controlled refresh, rather than a table join executed inside every insert. If you must join a table, keep the right side tiny and remember that late-arriving dimension rows will never be back-filled into already-written view output. ## Consequence 3: only inserts fire the view The trigger is on the insert path, so everything else in the source table's lifecycle bypasses it: - `ALTER TABLE ... UPDATE` and `ALTER TABLE ... DELETE` (mutations) rewrite parts in the source; the view never runs, and the target keeps the pre-mutation values. - TTL expiry and `DROP PARTITION` remove source rows silently; derived aggregates stay. - Background merges do not fire the view. This one bites hardest with `ReplacingMergeTree` or `CollapsingMergeTree` sources: the source will eventually deduplicate or cancel rows, but the view already consumed every version as it arrived, so the aggregate double-counts. The rule of thumb: attach materialized views to append-only source data. If the source semantically supports updates, either aggregate in a way that tolerates them (store the raw versions and resolve at read time), or apply the correction to both tables yourself. ## Consequence 4: block boundaries are visible in your output Because one block yields one write into the target, the shape of your ingest determines the shape of your target table. Ten-row inserts produce a target full of tiny parts and a huge number of pre-merge rows; large batched inserts produce far fewer. The standard advice to batch inserts is doubly important once views are attached, since the part-creation cost is multiplied across every attached view. ## How to demonstrate this in an interview A compact answer: *"The view's SELECT sees exactly the block being inserted, as if it were the whole table. So aggregates are partial and need an aggregating target, joins only trigger on the left table and re-read the right one per block, and nothing but an INSERT fires the view — no mutation, no TTL, no merge."* Then give the diagnosis that follows. When someone reports that a rollup table disagrees with `SELECT count() FROM source`, the causes are almost always in this list: a mutation or TTL on the source, a `ReplacingMergeTree` source whose duplicates were consumed before collapsing, a backfill that overlapped the live window, or a read query that forgot to re-aggregate rows the background merge has not yet combined.

  • Why does a GROUP BY inside a ClickHouse materialized view still leave many rows per group in the target table?
    Because the GROUP BY only aggregates within the inserted block, so every block contributes its own partial row for the same group. An aggregating engine such as `SummingMergeTree` or `AggregatingMergeTree` collapses rows with the same sort key during background merges, but merges are asynchronous and never guaranteed to have happened, so read queries must still re-aggregate with GROUP BY.
  • You need to enrich incoming rows with a country name from a lookup table. Why is a Dictionary usually better than a JOIN inside the view?
    A JOIN inside the view re-reads the right-hand table for every inserted block, putting that cost directly on the ingest path, and inserts into the lookup table never trigger the view. A `Dictionary` with `dictGet()` keeps the lookup in memory with an explicit refresh policy, so enrichment is a cheap per-row function call instead of a repeated table scan.
  • What happens to a materialized view when its source table is a ReplacingMergeTree that deduplicates on merge?
    The view consumes every version as it is inserted, because merges do not trigger it. The source will eventually keep one row per key, but the view's target has already counted all of them, so any sum or count drifts high. Either aggregate over a raw append-only source, or make the target itself resolvable at read time — for example by storing versions and applying argMax.

saying these in an interview costs you the question

  • Assuming the view's SELECT can read existing table rows
  • Expecting one output row per group per day, not per block
  • Thinking a JOIN in the view fires when the right table changes
  • Believing mutations or TTL deletions update the target table
  • Assuming ReplacingMergeTree dedup on the source reaches the view

context

resolving links…