Materialised Views — When and How to Use Them

What a materialised view is

A materialised view stores the result of a query physically, like a table. Unlike a regular view, the data is precomputed and reading it requires no re-execution of the underlying query. The tradeoff: data can be stale until refreshed.

PostgreSQL

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT DATE(created_at) AS day, SUM(amount) AS revenue
FROM orders
GROUP BY DATE(created_at);

-- Refresh (full recompute, brief lock)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

CONCURRENTLY rebuilds without locking reads but requires a unique index on the view.

Snowflake Dynamic Tables

Snowflake's Dynamic Tables support incremental refresh — only changed source rows are reprocessed. Set a target lag (TARGET_LAG = '1 minute') and Snowflake manages refresh automatically.

dbt materialisation

Set materialized: incremental in dbt and define the unique_key and incremental_strategy (merge/append/delete+insert). dbt handles incremental logic per warehouse type.