What is a Materialized View?

How it works

A regular view is a saved query: cheap to create, always current, but every read pays the full cost of the query. A table is stored data you load yourself. A materialized view sits in between: the engine stores the query’s result and knows how to recompute it.

How it stays fresh depends on the engine:

• PostgreSQL: only on REFRESH MATERIALIZED VIEW, which recomputes everything; CONCURRENTLY (with a unique index) keeps it readable during the refresh.
• Snowflake: maintained automatically in the background, with restrictions (one table, no joins) and serverless refresh costs.
• BigQuery and Redshift: incremental refresh where possible, and the optimizer can rewrite queries on the base table to use the view.
• Databricks: materialized views in Lakeflow Declarative Pipelines (formerly Delta Live Tables) refresh incrementally when they can.

Materialized views suit expensive aggregations that many queries repeat, such as daily revenue for a dashboard. They cost storage plus refresh compute, so they pay off when reads far outnumber changes. In dbt, the materialized_view materialization creates one; the table and incremental materializations are the usual alternatives.

Example

A new order arrives after the view is built. The view keeps returning the stored result until it is refreshed (PostgreSQL 16).

CREATE TABLE orders (order_date date, amount numeric);
INSERT INTO orders VALUES ('2026-10-20', 40), ('2026-10-20', 25), ('2026-10-21', 10);

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT order_date, sum(amount) AS revenue, count(*) AS orders
FROM orders GROUP BY order_date;

INSERT INTO orders VALUES ('2026-10-21', 90);
SELECT * FROM daily_revenue ORDER BY order_date;   -- stale: still the stored result

REFRESH MATERIALIZED VIEW daily_revenue;
SELECT * FROM daily_revenue ORDER BY order_date;   -- recomputed

 order_date | revenue | orders
------------+---------+--------
 2026-10-20 |      65 |      2
 2026-10-21 |      10 |      1

 order_date | revenue | orders
------------+---------+--------
 2026-10-20 |      65 |      2
 2026-10-21 |     100 |      2

Common mistakes

• Assuming a materialized view is always current; in many engines it is only as fresh as the last refresh.
• Fully refreshing a large view every few minutes, which can cost more than running the queries directly.
• Building chains of materialized views without a clear refresh order.
• Using one where a dbt table or incremental model with tests and lineage would be easier to manage.