Skip to content
ROOT
STUB

Materialized Views

The pattern: precompute the result of an expensive query and store it as if it were a real table. Subsequent reads hit the materialized result, not the underlying join or aggregate. Refreshed on a schedule, on a trigger, or incrementally.

The trade-off: read speed vs. freshness vs. compute cost. Materialized views make reads fast at the cost of compute (refresh) and freshness (stale until refresh). Incremental materialization — only recompute affected partitions — reduces both costs but is harder to design correctly. dbt’s +materialized: incremental in models is the canonical lakehouse-shape implementation.

Deepens in Year 3 Phase 17 with dbt incremental models on commit-history rollups inside basecamp’s data tier.

  • snapshot-plus-delta — incremental refresh is “apply deltas to a prior snapshot.”
  • oltp-vs-olap — materialized views are how OLAP engines hide aggregate cost.
  • batch-processing — scheduled refresh is a batch job with a contract.
  • stream-processing — Materialize and Flink push the same idea to continuous incremental refresh.