Skip to content

CONCEPT Cited by 2 sources

Materialized view

Definition

A materialized view (MV) is a database object that persists the result of a query as a physical table. Unlike a regular view — which is a stored query definition that is re-evaluated on each read — a materialized view stores the query's output rows, so reads scan the stored result directly instead of re-executing the underlying computation.

CREATE MATERIALIZED VIEW order_report AS
SELECT region, SUM(orders)
FROM customer_and_order_table
GROUP BY region;

Reading order_report scans a small pre-aggregated table; the underlying GROUP BY over customer_and_order_table is not re-executed on each read.

The three axes of MV design

1. Refresh strategy

Strategy Mechanism Trade-off
Full refresh Re-execute the MV definition; replace the persisted result. Simple; expensive at scale; staleness between refreshes.
Incremental refresh (IVM) Compute only the delta induced by input changes. Continuous freshness; engine complexity; coverage limited by MV shape.
On-demand refresh Refresh only when a read requests it. Bounds cost to actual demand; introduces read-time latency.

2. Use case

Use case Shape Examples
Query acceleration Pre-aggregations powering dashboards. Star-schema rollups; daily/hourly aggregates; metric-view materializations.
ETL replacement Derived tables in a pipeline that would otherwise need hand-written incremental code. The SDP / Enzyme thesis: "if MVs can be efficiently and incrementally maintained, it will significantly simplify ETL workloads which otherwise require writing complex custom code." (Source: sources/2026-05-29-databricks-databricks-at-sigmod-2026)
Semantic-layer materialisation The semantic-layer / headless-BI metric definition is materialised behind the same query surface. concepts/metric-view-materialization / systems/databricks-metric-views.

3. Authoring language

Language Industrial coverage
SQL Universal across MV-supporting systems.
Python Rare in industrial IVM; explicitly claimed by Enzyme ("While most industry solutions just focus on SQL, Enzyme supports MVs specified in Python as well"). See concepts/multi-language-materialized-view.
DSL (e.g. metric-view DSL) Newer surface; see concepts/metric-view-materialization.

Why MVs are increasingly central in lakehouse architectures

Three trends elevate MVs from a query-acceleration sidecar to a first-class architectural primitive:

  1. IVM coverage has caught up to production MV shapes. Joins, windows, aggregations, and combinations are all incrementally maintainable in modern engines (the Enzyme claim). This unlocks ETL-replacement use cases beyond simple aggregations.

  2. Declarative pipelines need a unit of declaration. SDP's @dp.materialized_view decorator is a Python-level analogue of the SQL CREATE MATERIALIZED VIEW statement; the MV is the unit of declarative authorship.

  3. Semantic layers materialise their definitions. The headless-BI semantic layer hosts metric definitions; the metric-view materialisation capability lets the platform automatically maintain pre-aggregated results behind the metric surface.

What MVs replace

Replaced artifact What MVs supply instead
Hand-written aggregate tables Declarative pre-aggregation.
Hand-written incremental ETL (merge / upsert / backfill code) The IVM engine handles incremental maintenance.
BI-tool-side aggregate-table swapping Transparent query rewriting onto materialised metric views.
Refresh pipelines (Airflow DAGs that rebuild aggregates nightly) Continuous incremental refresh from the IVM engine.

Seen in

Last updated · 542 distilled / 1,571 read