A materialized view stores the precomputed result of a query as a physical table, so reads hit the cached answer instead of recomputing it every time. For a warehouse where the same expensive aggregation runs behind a dashboard thousands of times a day, that's the difference between scanning a billion rows per request and reading a few thousand pre-aggregated ones. The trade is the usual one for any cache: faster reads in exchange for storage and the work of keeping the result current.
Full refresh vs. incremental
The hard part of a materialized view is staleness - when the base data changes, the stored result is wrong until refreshed. There are two strategies. A full refresh recomputes the whole view on a schedule or on demand: simple, but expensive and periodically stale. An incremental refresh applies only the changes since the last refresh, which is far cheaper but only works for queries the engine can update incrementally (typically aggregations over append-mostly data). Choosing between them depends on how fresh the result must be and how much the base data churns.
Two models: trigger-on-insert vs. refresh-on-demand
Engines implement materialized views differently, and ClickHouse is the clearest example of the split. A ClickHouse incremental materialized view is really an insert trigger: as rows land in the source table, the view's transformation runs on that block and writes to the target - keeping the aggregate continuously current but only seeing new inserts, not updates or back-dated changes. A refreshable materialized view instead re-runs the full query on a schedule, like the classic database materialized view. The two answer different needs: continuous freshness on streaming inserts versus periodic full correctness.
When to use one
Materialized views pay off for known, repeated, expensive query shapes - the rollups behind dashboards, daily KPIs, funnel and retention aggregates. They're the engine-managed equivalent of an aggregate fact table. Don't materialize ad hoc or rarely-run queries, and watch the write amplification: every materialized view over a table adds work to each load. Materialize the few views that earn their refresh cost in read savings.
Frequently asked questions
How is a materialized view different from a regular view? A regular view is a saved query that runs fresh every time; a materialized view stores the computed result physically, so reads are fast but the result must be refreshed when base data changes.
What's the difference between full and incremental refresh? Full refresh recomputes the entire view (simple, costly); incremental refresh applies only changes since the last run (cheap, but limited to queries the engine can update incrementally).
Why does ClickHouse have two kinds? Its incremental materialized view is an insert-time trigger that sees only new rows; its refreshable materialized view re-runs the full query on a schedule. One favors continuous freshness, the other full correctness.
What's the cost of a materialized view? Storage for the result plus write amplification on every load, since the view must be maintained. Use them only for repeated, expensive queries where read savings outweigh that cost.