Materialized views for repeated queries in Snowflake - Step-by-Step Execution

Process Flow - Materialized views for repeated queries

Create Materialized View

↓

Snowflake stores precomputed data

↓

User runs query on Materialized View

↓

Snowflake returns fast results from stored data

↓

Materialized View auto-refreshes when base data changes

↓

Repeat queries run faster

Materialized views store query results ahead of time. When you query them, Snowflake returns results quickly without recomputing. They refresh automatically when base data changes.

Execution Sample

Snowflake

CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT region, SUM(sales) AS total_sales
FROM sales_data
GROUP BY region;

This creates a materialized view that precomputes total sales by region for faster repeated queries.

Process Table

Step	Action	Evaluation	Result
1	Create materialized view	Parse and store query definition	Materialized view 'mv_sales_summary' created
2	Initial data scan	Compute SUM(sales) grouped by region	Store precomputed results in materialized view
3	User queries materialized view	Retrieve stored results	Return total sales by region instantly
4	Base table data changes	Detect changes in 'sales_data'	Trigger materialized view refresh
5	Materialized view refresh	Recompute aggregation	Update stored results
6	User queries again	Retrieve updated stored results	Return fresh total sales by region quickly
7	End	No more changes	Materialized view ready for next query

💡 Materialized view stops refreshing when no base data changes occur

Status Tracker

Variable	Start	After Step 2	After Step 4	After Step 5	Final
mv_sales_summary	Not created	Precomputed totals stored	Detected base data change	Refreshed totals stored	Up-to-date precomputed totals

Key Moments - 3 Insights

Why does querying the materialized view return results faster than querying the base table?

Because the materialized view stores precomputed results (see execution_table step 3), so Snowflake does not recompute the aggregation each time.

How does the materialized view stay up to date when the base data changes?

Snowflake detects changes in the base table (step 4) and automatically refreshes the materialized view (step 5) to keep results current.

What happens if the base data does not change?

The materialized view does not refresh and continues to serve stored results instantly (step 7).

Visual Quiz - 3 Questions

Look at the execution_table, at which step does Snowflake store the precomputed results?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Check the 'Result' column for when precomputed results are stored.

According to variable_tracker, what is the state of 'mv_sales_summary' after step 5?

A Refreshed totals stored

B Not created

C Detected base data change

D Up-to-date precomputed totals

💡 Hint

Look at the 'After Step 5' column for 'mv_sales_summary' in variable_tracker.

If the base data never changes, which step in execution_table will not occur?

A Step 2: Initial data scan

B Step 4: Base table data changes

C Step 3: User queries materialized view

D Step 1: Create materialized view

💡 Hint

Step 4 involves detecting changes in base data; if no changes happen, this step is skipped.

Concept Snapshot

Materialized views store query results ahead of time.
They speed up repeated queries by returning stored data.
Snowflake auto-refreshes them when base data changes.
Use CREATE MATERIALIZED VIEW with a query.
Queries on them run faster than on base tables.

Full Transcript

Materialized views in Snowflake are special database objects that store the results of a query in advance. When you create one, Snowflake runs the query once and saves the results. Later, when you query the materialized view, Snowflake returns the saved results quickly without recalculating. If the underlying data changes, Snowflake automatically refreshes the materialized view to keep it current. This makes repeated queries much faster and efficient.