A user frequently runs a reporting query that returns the total record count and the...

A materialized view stores precomputed query results. Snowflake maintains the materialized view when its underlying base-table data changes. Because the source table changes only once per week, maintenance occurs much less frequently than the reporting query is executed.

Repeated queries can read the much smaller precomputed result instead of scanning the 100 TB base table hundreds of times per day. Snowflake’s optimizer can also automatically rewrite an eligible query to use a materialized view even when the query references the base table.

A standard view stores only its SQL definition and normally executes the underlying query whenever it is referenced. A temporary table would require manual creation, refresh, and lifecycle management. A dynamic table could materialize transformed results, but it requires refresh scheduling and refresh compute. For this repeated, stable aggregation pattern, the intended optimization object is a materialized view.

Materialized-view maintenance itself can consume credits, so it is most beneficial when query savings outweigh maintenance costs. The table’s infrequent load schedule makes that condition more likely.