In ClickHouse, how does chaining materialized views turn one insert into writes across several tables?

## How the cascade happens A ClickHouse materialized view is attached to a table and fires on inserts into it. Nothing in that rule says the insert must come from a client. When view `mv1` writes its output block into table `minute_rollup`, that is an insert into `minute_rollup`, and any view attached to `minute_rollup` fires in turn. That is the whole mechanism behind multi-level rollups: ``` events ──mv_minute──▶ minute_rollup ──mv_hour──▶ hour_rollup ``` When the second level aggregates data that is already stored as aggregate states, use the composed combinator: `uniqMergeState(uniq_users)` reads the incoming states, merges them, and emits a coarser state for the hourly table. Additive columns are simply summed again. ```sql CREATE MATERIALIZED VIEW mv_hour TO hour_rollup AS SELECT toStartOfHour(minute) AS hour, country, uniqMergeState(uniq_users) AS uniq_users, sum(events) AS events FROM minute_rollup GROUP BY hour, country; ``` ## Everything is synchronous and on the insert path The cascade is not a background job. It executes inside the original `INSERT` statement, on the thread doing the insert, before the client gets an acknowledgement. So a single insert of a block into `events`: 1. writes a part into `events`; 2. runs `mv_minute`'s `SELECT` over that block and writes a part into `minute_rollup`; 3. runs `mv_hour`'s `SELECT` over *that* block and writes a part into `hour_rollup`. Three tables, three new parts, one client insert. Add a fan-out of six views on the raw table and one insert becomes seven parts plus whatever their own children produce. ## The two costs that actually bite **Part explosion.** MergeTree's health depends on parts being large and few. Small frequent inserts are already the classic ClickHouse anti-pattern; with N attached views the damage is multiplied by N, and each target has its own merge backlog. The mitigations are the usual ones, applied harder: batch inserts into large blocks, or enable async inserts so the server assembles batches for you, and keep an eye on the parts count per target table. **Insert latency.** Every view's `SELECT` is real work on the insert path — a `GROUP BY`, possibly a dictionary lookup or a join that re-reads a table per block. Views attached to the same source are executed one after another by default; the setting `parallel_view_processing` runs them concurrently instead, trading CPU and memory for wall-clock latency. Measure before flipping it: parallel execution multiplies peak memory by the number of views. ## Failure semantics If a view's `SELECT` throws — a type overflow, a memory limit, a missing dictionary — the exception propagates and the client's `INSERT` fails. But views that already ran in this cascade have already written their rows. The result is a partially applied insert: the source may hold the block while a downstream rollup does not, or vice versa. There is no transaction wrapping the chain. The setting `materialized_views_ignore_errors` makes such failures logged and skipped instead of failing the insert. That keeps ingest flowing at the price of silently losing view output, so it is a deliberate choice for non-critical derived tables, never a default for the rollup a dashboard trusts. For observability, `system.query_views_log` records one row per view execution per query — the view name, rows read and written, duration, memory and any exception — which is the fastest way to find *which* view in a chain is costing your insert latency. It is populated when view logging is enabled for the query. ## Design guidance - Prefer a shallow ladder (raw → fine rollup → coarse rollup) over a wide fan-out of independent views over the raw table: each level reads far less data than the one below it, so total insert-path work drops. - Keep every view's `SELECT` cheap. Enrichment belongs in a `Dictionary` lookup, not a join. - Give every level an explicit `TO` table so you can backfill, `ALTER` or rebuild a single level without touching the rest of the chain. - Remember that a rebuild of level N must be replayed into level N+1 yourself; re-inserting into the intermediate table will cascade, so plan for the double counting that follows and use an append-safe design or a truncate-and-replay window. - Watch the depth. Every level adds latency to a synchronous path, and the deeper the chain the harder the partial-failure story becomes. ## The interview answer *"Views trigger on inserts into their source, and a view's own write is an insert — so chains cascade. All of it runs synchronously inside the client's INSERT, creating one part per table per block, sequentially unless `parallel_view_processing` is on. The costs are part explosion and insert latency, and a mid-chain failure leaves partial state because there is no transaction around it."*