When is a Snowflake materialized view worth its maintenance cost, and what can it not contain?

## What it is A materialized view stores the precomputed result of a query so that later reads scan a small persisted object instead of re-deriving it. In Snowflake it is a first-class object maintained **automatically** — there is no refresh statement you schedule and no staleness you have to reason about at read time. ```sql CREATE MATERIALIZED VIEW mv_daily_sales AS SELECT DATE_TRUNC('day', order_ts) AS day, country, SUM(amount) AS amount FROM sales GROUP BY 1, 2; ``` It is available on Enterprise Edition and above. ## The restrictions The restrictions are narrow enough that they decide most design conversations: - **One base table only.** No joins, no subqueries against other tables, no `UNION`. - **No window functions.** - **Cannot be defined on a view or another materialized view.** - **Only a supported subset of aggregate functions.** The practical reading: a Snowflake materialized view is for *filtering, projecting and aggregating one table*, not for modelling. A denormalized fact-plus-dimension result is out of scope by construction. ## Consistency Snowflake does not hand you stale rows. Maintenance runs in the background, and if the view has not caught up with recent DML, a query against it combines the materialized data with the newer base-table changes to produce a current answer. The cost of that is that reads against a badly lagging view get slower — the saving erodes as churn grows — but correctness is never the thing you trade away. ## Automatic rewrite The optimizer can redirect a query written against the base table to a materialized view that satisfies it. This is what makes materialized views useful for BI traffic you do not control: analysts and tools keep querying the fact table, and eligible queries silently get the cheap path. You do not have to migrate every dashboard's SQL to see the benefit. ## The cost model — the half candidates forget Two costs, both real: 1. **Storage** for the materialized result, billed like any table. 2. **Maintenance credits**, consumed by a serverless background service, roughly proportional to how much the base table changes. There is no warehouse to suspend and no way to make it free while the view exists. That gives a simple decision rule. Let *R* be how often and how expensively the result is read, and *C* be how much the base table churns. Materialize when *R* is large and *C* is small. A table under continuous ingestion with a materialized view over it can quietly become one of the largest lines on the bill — visible in `ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY` and in `METERING_DAILY_HISTORY` by service type. Check both before and after creating one. ## Alternatives, and when each wins - **Do nothing.** If the query result is reused by the query result cache — a stable predicate over a table that changes a few times a day — you already get near-zero cost with no maintenance at all. Try this before materializing. - **A scheduled aggregate table** built by a task on a warehouse you control. More work, but the cost is a warehouse you can size, schedule and suspend, and the transformation can join freely. - **Dynamic tables**, Snowflake's declarative pipeline object, which handle multi-table transformations with a declared target lag — the natural answer to "but I need a join". Assumes a current Snowflake release. - **Clustering the base table**, when the problem is that queries scan too much rather than that they recompute too much. ## Operational notes A materialized view can carry its own clustering key, which is often the real win: the base table is ordered for loading, the view is ordered for the dashboard's filter. Materialized views can also be defined over external tables, which is a common way to make a repeatedly-queried external dataset cheap to read. `SHOW MATERIALIZED VIEWS` reports each view's maintenance state, and a view can become invalidated by DDL on the base table — dropping a referenced column, for instance — so schema changes need to account for dependent views. ## The answer an interviewer wants Name the single-table restriction and the serverless maintenance cost in the same breath, then give the read-heavy/change-light heuristic and say what you would try first — result-cache-friendly query shapes, then a materialized view, then a scheduled table or dynamic table when a join is required.