Materialized Views
A materialized view stores the precomputed result of a query on a single table and is kept current automatically by a serverless background process. Enterprise edition. Querying one requires a running warehouse, because it's stored data being scanned like any table.
Use one when an expensive aggregation over a large, slowly-changing table is queried far more often than the base data changes. The optimizer will even rewrite queries against the base table to use a matching materialized view silently.
MV in one worked example
Alice's revenue tile runs the same aggregation a few hundred times a day, once per dashboard refresh, per user:
-- What every dashboard load executes: scans 40M rows, every timeSELECT order_date, SUM(amount) FROM sales GROUP BY order_date;-- Compute it once, keep it current automaticallyCREATE MATERIALIZED VIEW daily_revenue AS SELECT order_date, SUM(amount) AS revenue FROM sales GROUP BY order_date;
Nothing else changes, and that's the elegant part: the dashboards keep querying sales, and the optimizer transparently rewrites those queries to read daily_revenue instead: 365 precomputed rows instead of a 40M-row scan, 0.3 seconds instead of 18. When the nightly load lands new rows in sales, a serverless background process refreshes the view; queries in between are never stale, because Snowflake combines the stored results with the not-yet-materialized changes. The bill for that convenience is the refresh credits after every load plus the storage, visible in MATERIALIZED_: cheap when the base table changes nightly, brutal when it changes every minute, which is exactly the selection logic to apply.
| Regular view | Materialized view | |
|---|---|---|
| Stores results | No, query runs each time | Yes, physically stored |
| Costs | Compute at query time only | Storage + serverless refresh credits + query compute |
| Freshness | Always current | Maintained automatically; always consistent when queried |
| Joins / multiple tables | Anything SQL allows | Single table only, no joins, no self-joins |
| Other limits | Few | No non-deterministic functions, no UDFs, limited aggregates; can't query Time Travel on it |
| Edition | Standard | Enterprise |
"A materialized view can join two tables": always false. Also watch the cost triple: an MV is never free, it charges storage, serverless refresh, and normal compute when queried. And unlike the result cache, reading an MV does need a warehouse.
A concrete example
Alice's revenue dashboard runs SELECT region, SUM(amount) FROM sales_fact GROUP BY region about 400 times a day, 90 seconds per run on a Medium. That's 10 hours of warehouse time daily computing the same aggregate over a table that changes once a night.
CREATE MATERIALIZED VIEW mv_revenue_ by_ region AS SELECT region, SUM(amount) AS revenueFROM sales_factGROUP BY region;
Dashboard queries now read a few hundred pre-computed rows in under a second, and the optimizer even rewrites queries against sales_fact itself to use the MV when it can. The new costs: storing those rows (trivial here) and one serverless refresh after the nightly load. Ten warehouse-hours traded for one small refresh; that ratio, reads vastly outnumbering writes on a single-table aggregate, is the entire business case for MVs, and its absence is why an MV over a table rewritten hourly loses money.
QUERY TIME · READ PATH
QUERYDashboard tileSUM(amount) FROM salesGROUP BY order_date
query on SALES
CLOUD SERVICESOptimizerrewrites the query
MATERIALIZED VIEWDAILY_REVENUE365 stored rowsalways current
updates the MV
LOAD TIME · MAINTENANCE PATH
SERVERLESSMV maintenanceserverless credits
changes
BASE TABLESALES40M rows
new rows
LOADNightly loadINSERT new rows
Operating materialized views
There is no manual refresh command: maintenance is exclusively the serverless background process, and the only control you have is ALTER MATERIALIZED VIEW mv1 SUSPEND (queries against a suspended MV fail; the base table is unaffected) and RESUME. SHOW MATERIALIZED VIEWS exposes a behind_by column showing how far maintenance lags the base table, but the lag never leaks into results: a query that touches an MV mid-refresh transparently merges the stored results with the base table's fresh deltas, so answers are always current. MVs can also carry their own clustering key when the view's query pattern differs from the base table's.
- Single table, no joins, no self-joins, no non-deterministic functions or UDFs. Enterprise edition.
- Three costs every time: storage, serverless refresh, and warehouse compute when queried. The optimizer may use an MV silently for base-table queries.
- No manual refresh exists; results are never stale even mid-maintenance; SUSPEND/RESUME is the on/off switch.

