Materialized View
A materialized view is a pre-computed query result stored as a physical table, providing fast access to aggregated or joined data at the cost of manual refresh latency.
Definition
A materialized view is a saved SQL query whose results are stored as an actual table in the database. When you query a regular (virtual) view, the database runs the query each time—fast for fresh data but slow if the query is complex. A materialized view computes the query once and stores the results, making subsequent queries instant but introducing staleness—the results are only current until the next refresh. Materialized views are perfect for expensive aggregations (daily sales summary, customer lifetime value) that don't need real-time freshness. They're common in data warehouses where you pre-aggregate for dashboard speed.
How It Works
1. Define: CREATE MATERIALIZED VIEW monthly_sales AS SELECT month, SUM(amount) FROM sales GROUP BY month. 2. Compute: Database runs the query and stores results. 3. Query: SELECT * FROM monthly_sales returns instant results. 4. Stale: When new sales are added, the view is out of date. 5. Refresh: REFRESH MATERIALIZED VIEW monthly_sales recomputes it.
When to Use It
Use materialized views for expensive aggregations that don't require real-time freshness: monthly reports, dashboard summaries, customer metrics. Refresh on a schedule (nightly, hourly) or on-demand. Avoid for rapidly-changing data or when you need sub-minute freshness. Modern data warehouses support incrementally-refreshed views (dbt incremental models, materialized views in Snowflake).
Relevant Tools
Last updated: Jun 17, 2026