Course contents

SnowPro Core COF-C03 Study GuidePart 2: Warehouses and performanceMaterialized Views

Materialized Views

Materialized Views

Definition

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 time
SELECT order_date, SUM(amount) FROM sales GROUP BY order_date;

-- Compute it once, keep it current automatically
CREATE 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_VIEW_REFRESH_HISTORY: cheap when the base table changes nightly, brutal when it changes every minute, which is exactly the selection logic to apply.

Regular viewMaterialized view
Stores resultsNo, query runs each timeYes, physically stored
CostsCompute at query time onlyStorage + serverless refresh credits + query compute
FreshnessAlways currentMaintained automatically; always consistent when queried
Joins / multiple tablesAnything SQL allowsSingle table only, no joins, no self-joins
Other limitsFewNo non-deterministic functions, no UDFs, limited aggregates; can't query Time Travel on it
EditionStandardEnterprise
Exam trap

"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 revenue
FROM sales_fact
GROUP 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.

Two flows around one materialized view. Top, at query time: the dashboard tile queries the SALES base table; the optimizer rewrites the query to read DAILY_REVENUE, the materialized view with 365 stored rows. Bottom, at load time: the nightly load inserts new rows into SALES, which triggers serverless maintenance, which updates the stored result of the materialized view. Queries always see current data.QUERY TIME · READ PATHLOAD TIME · MAINTENANCE PATHQUERYDashboard tileSUM(amount) FROM salesGROUP BY order_dateCLOUD SERVICESOptimizerrewrites the queryMATERIALIZED VIEWDAILY_REVENUE365 stored rowsalways currentquery onSALESread theMV insteadLOADNightly loadINSERT new rowsBASE TABLESALES40M rowsSERVERLESSMV maintenanceserverless creditschangesupdates the MVnew rows

QUERY TIME · READ PATH

QUERYDashboard tileSUM(amount) FROM salesGROUP BY order_date

query on SALES

CLOUD SERVICESOptimizerrewrites the query

read the MV instead

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

Figure 2.13 A materialized view trades a little serverless maintenance for much cheaper reads. Top: a query written against the base table is rewritten by the optimizer to read the stored result. Bottom: after a load changes the base table, a serverless service updates the view. If maintenance hasn't caught up, Snowflake combines the stored result with newer base data, so answers stay current.

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.

Key takeaways
  • 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.