Snowflake
Materialized Views for Precomputed Query Acceleration
Automatically maintain precomputed query results for faster repeated access.
A materialized view stores the precomputed result of a query physically, and — unlike a regular view — Snowflake automatically and incrementally keeps it up to date as the underlying base table changes, without requiring manual refresh scheduling. This makes materialized views ideal for expensive aggregations or transformations over data that changes relatively infrequently but is queried often.
A materialized view is like a running total kept updated in real time on a whiteboard, versus a regular view which is a formula you have to fully recalculate by hand every single time someone asks for the answer.
Key Concepts
1
Unlike regular views (which simply re-run their defining query every time they're referenced) or manually managed summary tables (which require your own refresh logic), materialized views are refreshed by a Snowflake-managed background service that performs incremental maintenance, only reprocessing changed micro-partitions rather than recomputing the entire result from scratch.
2
Materialized views come with notable restrictions: they support only a single table in the FROM clause (no joins), a limited set of aggregate functions, and cannot include ORDER BY, HAVING in some contexts, or reference other views/materialized views. These constraints exist because Snowflake must be able to compute incremental deltas efficiently.
FROMORDER BYHAVING
3
Because the background refresh process consumes serverless compute credits, materialized views are best reserved for cases with a clear, measured benefit: a query pattern that's run very frequently against a large, slowly-changing base table, where the aggregation/filtering cost is significant enough to justify the ongoing maintenance overhead versus just querying the base table (or using result caching) directly.