In ClickHouse, how does a MATERIALIZED VIEW differ from an ordinary VIEW?
answer
- one stores a query, one stores rows
- think about when the work happens
- it fires on write, not on read
- trigger over each inserted block
- the view writes into a target table
basics
~20 sAn ordinary ClickHouse VIEW stores only a query and re-runs it at read time. A MATERIALIZED VIEW is an insert trigger: it transforms every newly inserted block and writes the result into a separate physical target table.
solid answer
~50 sA plain `VIEW` in ClickHouse is just a saved `SELECT`. It holds no data; reading it substitutes the query text, so it costs exactly what the underlying query costs. A `MATERIALIZED VIEW` is not a cached result set. It is a trigger on inserts into its source table: whenever a block of rows lands in that table, ClickHouse runs the view's `SELECT` over **that block only** and writes the output rows into a target table. Reading the view means reading that stored target table, which is why it is fast. You normally create it with `TO target_table` so the destination is an explicit table you control. Without `TO`, ClickHouse creates a hidden inner table for you. Historical rows already in the source are not included — the trigger only fires for future inserts, so you backfill them separately.
go deeper
Be ready to state the difference in one sentence: a VIEW is a stored query executed at read time, a MATERIALIZED VIEW is a trigger that writes transformed rows into a table at insert time.
Explain the mechanics: the SELECT runs over each inserted block, the output lands in a target table you define with TO, and history needs a separate backfill.
Show that you design around the trigger model — append-only pipelines, an explicit target table, and a documented backfill procedure, because deletes and mutations on the source never reach the view.
Own the tradeoff of how much of the read path is materialized at write time: every view adds insert latency, parts and storage, and creates a derived dataset someone must keep correct when the source is corrected.
## Two objects with similar names and nothing else in common ClickHouse has both `CREATE VIEW` and `CREATE MATERIALIZED VIEW`, and the second is not "the first, but cached". Candidates arriving from PostgreSQL, Oracle or Snowflake almost always carry the wrong model, and interviewers ask this question precisely to find that out. ## The ordinary VIEW `CREATE VIEW v AS SELECT ...` stores a query definition. It occupies no storage and does no work until someone selects from it, at which point ClickHouse inlines the definition into the outer query and executes the whole thing. A view over a billion-row table scans a billion rows every time. Views are for readability and for hiding a filter or a column expression, never for performance. ## The MATERIALIZED VIEW is an insert trigger `CREATE MATERIALIZED VIEW mv TO target AS SELECT ... FROM source` attaches a trigger to `source`. Rows arrive in ClickHouse in *blocks* — a batch of rows from one `INSERT`. For each block written into `source`, ClickHouse runs the view's `SELECT` treating that block as if it were the entire table, and inserts the resulting rows into `target`. That work happens synchronously, inside the original insert. So the flow is write-time, not read-time: ```sql INSERT INTO events VALUES (...); -- writes a part into events -- AND runs the MV SELECT over that block -- AND writes a part into the target table ``` Querying "the view" is really querying `target`, an ordinary MergeTree-family table with its own `ORDER BY`, partitioning, TTL and compression. ## TO table versus the implicit inner table Two spellings exist: - `CREATE MATERIALIZED VIEW mv TO target AS SELECT ...` — you create `target` yourself with the engine and sort order you want. This is the form to prefer: the destination is a normal table you can `ALTER`, backfill, `TRUNCATE`, or point a second view at. - `CREATE MATERIALIZED VIEW mv ENGINE = MergeTree ORDER BY ... AS SELECT ...` — ClickHouse creates a hidden inner table (named `.inner_id.<uuid>` under an Atomic database) and the view name reads from it. Convenient for a demo, awkward in production because dropping the view drops the data. Name the `SELECT` output columns so they line up with the target table's columns; a mismatch between what the view produces and what the target expects is the most common cause of a view that "silently writes nothing useful". ## History is not included A freshly created materialized view is empty for everything that arrived before it existed. Two ways to fill it: - `POPULATE` — supported only on the *inner-table* form, not together with `TO`. It copies existing data at creation time, but rows inserted while the population runs can be missed, so it is a poor fit for a live ingest table. - Manual backfill — create the view first (so new rows start flowing), then run `INSERT INTO target SELECT ...` over the historical range with the same expression list, using a timestamp watermark to avoid double-counting the overlap. This is the production pattern. ## What follows from the trigger model Because it is a trigger and not a refresh job: - The view sees only the rows in the block being inserted, never the rest of the table. - `ALTER TABLE ... UPDATE`/`DELETE` on the source (mutations), `TTL` deletions, `DROP PARTITION`, and background merges do **not** fire it. A materialized view is append-shaped; corrections to the source must be applied to the target yourself. - Insert latency and part creation multiply: one client insert now writes into the source table and into every attached view's target. - If the view's `SELECT` throws, the insert itself fails by default. ## Interview framing Say it in one line: *a ClickHouse materialized view is an insert trigger that writes a transformed copy into a real table, not a cached query result.* Then name the consequence that matters for the job you are interviewing for — usually that aggregation inside the view is computed per inserted block, which is why the target is nearly always an aggregating engine. One more distinction worth having ready: newer ClickHouse versions also offer *refreshable* materialized views, which do periodically re-run the whole query on a schedule. Those are the exception; the default, incremental, insert-triggered form is what "materialized view" means in ClickHouse unless someone says otherwise.
- How do you load the rows that existed before the materialized view was created?Create the view first so new inserts start flowing, then backfill with an explicit `INSERT INTO target SELECT ...` over the historical range, using a timestamp cutoff that matches the moment the view went live so the overlap is not counted twice. `POPULATE` exists but only on the inner-table form (not with `TO`), and rows inserted while it runs can be missed, so it is unsuitable for a live ingest table.
- Why is TO target_table usually preferred over letting ClickHouse create an inner table?With `TO`, the destination is an ordinary table you own: you can choose its engine and `ORDER BY`, `ALTER` it, backfill it, attach a second view to it, and drop or recreate the view without losing data. The inner-table form ties the data's lifetime to the view object and makes schema changes and backfills clumsy.
- If someone runs ALTER TABLE events DELETE WHERE ... on the source, what happens to the view's target table?Nothing. Mutations, TTL deletions and `DROP PARTITION` on the source do not trigger the view — only inserts do. The target keeps the rows derived from the deleted data, so any correction must be applied to the target explicitly, which is a strong argument for designing the pipeline as append-only.
saying these in an interview costs you the question
- Calling it a cached query result refreshed on read
- Assuming the view re-runs its SELECT over the whole table
- Expecting a new view to contain historical rows automatically
- Believing DELETE or UPDATE on the source propagates to the view
- Thinking a plain VIEW improves query performance