skip to content

What does a ClickHouse refreshable materialized view (REFRESH EVERY) do differently from a standard one?

level: seniorimportance: nice to knowfreq 28%

answer

  1. one is a trigger, the other has a clock
  2. the whole query runs again
  3. readers never see a half-built result
  4. full recompute buys you joins
  5. atomic swap unless you say APPEND

basics

~20 s

A refreshable materialized view re-runs its whole SELECT on a schedule and atomically replaces the target table's contents, instead of triggering per inserted block. That makes full recomputation, joins and non-incremental logic possible, at the cost of recomputing everything each period.

solid answer

~50 s

The standard ClickHouse materialized view is incremental: it fires on each inserted block and appends derived rows. A refreshable one is scheduled — `REFRESH EVERY 1 HOUR` or `REFRESH AFTER 1 HOUR` — and executes the entire `SELECT` over the full source data each time, writing into a new table that is then swapped in atomically. Readers see the old contents until the swap, then the new ones; `APPEND` switches it to appending each run's output instead of replacing. That unlocks the cases incremental views cannot serve: joins across several tables, dimension snapshots, deduplication or `argMax` resolution over the whole table, anything needing global context. The cost is full recomputation every period plus roughly double storage while the new copy is built. `DEPENDS ON` orders refreshes in a chain, `RANDOMIZE FOR` spreads load, `SYSTEM REFRESH VIEW` forces one, and `system.view_refreshes` reports schedule, progress and last error. It was introduced as an experimental feature in ClickHouse 24.x behind `allow_experimental_refreshable_materialized_view`.

code

sql · 10 lines
sql
CREATE MATERIALIZED VIEW customer_360
REFRESH EVERY 1 HOUR OFFSET 10 MINUTE RANDOMIZE FOR 5 MINUTE
TO customer_360_tbl AS
SELECT c.id, c.segment, sum(o.amount) AS lifetime_value
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.segment;

SELECT view, status, last_refresh_time, next_refresh_time, exception
FROM system.view_refreshes;

go deeper

for a junior

Know that ClickHouse has two kinds: the default insert-triggered view, and a refreshable one that re-runs its query on a schedule.

for a middle

Explain the mechanics — full recomputation each period, atomic replacement of the target unless APPEND is used, and the scheduling modifiers EVERY, AFTER, OFFSET and RANDOMIZE FOR.

for a senior

Justify the choice on cost and freshness: full-scan compute per refresh, roughly doubled storage during a rebuild, staleness bounded by the interval, and a failed refresh leaving stale rather than wrong data.

for a principal

Own where scheduling belongs — an in-database refresh with atomic swap and observable state versus an external orchestrator — and set the staleness SLA each derived dataset is allowed to carry.

## Two different things wearing the same name ClickHouse's classic materialized view is an insert trigger. It cannot look at data outside the block being inserted, which rules out anything needing global context: joining two large tables, resolving the latest row per key across the whole history, recomputing a metric whose definition changed, or reflecting updates to a dimension table. Refreshable materialized views fill exactly that gap by working the way materialized views do in traditional warehouses: run the query on a schedule, store the result. ```sql CREATE MATERIALIZED VIEW customer_360 REFRESH EVERY 1 HOUR OFFSET 10 MINUTE RANDOMIZE FOR 5 MINUTE TO customer_360_tbl AS SELECT c.id, c.segment, sum(o.amount) AS lifetime_value FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.segment; ``` ## Refresh semantics - **`REFRESH EVERY <interval>`** anchors runs to wall-clock boundaries; **`REFRESH AFTER <interval>`** waits that long after the previous run finished. The first suits "top of every hour" reporting, the second avoids overlapping long jobs. - **`OFFSET`** shifts the wall-clock anchor, so an hourly refresh can start ten minutes past the hour when upstream data usually lands. - **`RANDOMIZE FOR`** jitters the start so that many views do not all fire at the same instant. - **Replace, by default.** Each run builds the result in a fresh table and swaps it into place atomically, so readers see either the previous full result or the new one — never a half-written table. - **`APPEND`** changes that: each run's output is appended to the target instead of replacing it, which is how you build a periodic snapshot history rather than a current-state table. - **`DEPENDS ON <view>`** sequences a chain, so a coarse view refreshes only after the finer one it reads has completed, instead of racing it. Operationally: `SYSTEM REFRESH VIEW v` forces a run now, `SYSTEM STOP VIEW v` / `SYSTEM START VIEW v` pause and resume the schedule, and `SYSTEM WAIT VIEW v` blocks until the current refresh finishes — useful in tests and backfills. `system.view_refreshes` shows each view's status, last refresh time, next scheduled time, progress and last exception. ## What it costs The economics are the opposite of the incremental view's: - **Compute** is proportional to the whole dataset, every period. An incremental view costs a little on each insert forever; a refreshable one costs a full scan every refresh regardless of how little changed. For a source that grows without bound, that cost grows with it. - **Storage** roughly doubles during a replace-mode refresh, because the new copy is materialized before the swap. - **Staleness** is bounded by the interval, and it is a real interval, not eventual consistency measured in seconds. A one-hour refresh means dashboards can be an hour behind. - **Failure** leaves the previous contents in place — which is a genuine advantage: a failed refresh degrades to stale data rather than to wrong or partial data. Check `system.view_refreshes` for the exception; nothing alerts you otherwise. ## Choosing between the two Use the **incremental** form when the transformation is per-row or an aggregate over an append-only stream with a key you can group by, when you need seconds-fresh results, and when the source is large enough that repeated full scans would be wasteful. That is the majority of ClickHouse rollups. Use the **refreshable** form when the query genuinely cannot be expressed incrementally — multi-table joins, latest-state resolution, window functions over the full history, a metric that must be recomputed after late-arriving corrections — and when the result is small relative to the source, so the full scan is affordable at the cadence you need. It is also the honest answer when you would otherwise be building an external scheduler that runs `INSERT INTO ... SELECT` on a cron: refreshable views put that loop inside the database with atomic swap and observable state. A hybrid is common: an incremental view keeps a fine-grained, always-fresh rollup, and a refreshable view periodically recomputes a small, join-heavy summary on top of it, using `DEPENDS ON` so the ordering is explicit. ## Version caution This feature arrived as experimental in ClickHouse 24.x and had to be enabled with `allow_experimental_refreshable_materialized_view = 1`; it has been stabilising since. Before proposing it, check what your specific version supports — the syntax and the guard setting are the two things that have moved. In an interview, saying "experimental in 24.x, check the version you are on" is a better answer than asserting a status you are not sure of.

  • When is a refreshable materialized view the right choice over an incremental one?
    When the query cannot be expressed per inserted block — joins across large tables, latest-state resolution over the whole history, window functions, or a metric that must be recomputed after late-arriving corrections — and when the result is small enough that a full scan at the required cadence is affordable. If the transformation is per-row or a groupable aggregate over append-only data and you need second-level freshness, stay incremental.
  • What does the APPEND modifier change about a refreshable view's behaviour?
    Without it, each refresh builds the full result and atomically replaces the target's contents, so the table always holds current state. With `APPEND`, each run's output is added to the existing rows instead, which is how you accumulate periodic snapshots — an hourly point-in-time record — rather than a single current-state table. You then filter by the snapshot timestamp at read time.
  • How do you keep a chain of refreshable views from refreshing out of order?
    Declare `DEPENDS ON` on the downstream view naming the view it reads. The dependent refresh then waits for its dependency's run to complete instead of racing it on a shared schedule, so a coarse summary is always computed from a completed finer result. `system.view_refreshes` shows the resulting ordering and each view's next scheduled run.

saying these in an interview costs you the question

  • Calling standard ClickHouse materialized views scheduled refreshes
  • Assuming a refresh updates only the rows that changed
  • Expecting readers to see partially rebuilt results mid-refresh
  • Ignoring that full recompute cost grows with the source table
  • Using a refreshable view for second-level freshness requirements

context

resolving links…