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.

Practice

1. What is the main benefit of using a materialized view in Snowflake for repeated queries?

easy

A. It allows users to write queries without SQL knowledge.

B. It automatically deletes old data to save storage space.

C. It encrypts data to improve security during queries.

D. It stores the query results physically to speed up repeated queries.

Solution

1. Step 1: Understand what a materialized view does

   A materialized view saves the results of a query physically, unlike a normal view which runs the query every time.

2. Step 2: Identify the benefit for repeated queries

   Since the results are saved, repeated queries run faster because they read stored data instead of recalculating.

3. Final Answer:

   It stores the query results physically to speed up repeated queries. -> Option D

4. Quick Check:

   Materialized view = stored results for speed [OK]

Hint: Materialized views save query results for faster repeated access [OK]

Common Mistakes:

• Confusing materialized views with normal views
• Thinking materialized views automatically delete data
• Assuming materialized views improve security by encryption

2. Which of the following is the correct syntax to create a materialized view in Snowflake?

easy

A. CREATE MATERIALIZED my_view VIEW AS SELECT * FROM sales;

B. CREATE VIEW MATERIALIZED my_view AS SELECT * FROM sales;

C. CREATE MATERIALIZED VIEW my_view AS SELECT * FROM sales;

D. CREATE VIEW my_view MATERIALIZED AS SELECT * FROM sales;

Solution

1. Step 1: Recall Snowflake syntax for materialized views

   The correct syntax starts with CREATE MATERIALIZED VIEW followed by the view name and query.

2. Step 2: Match syntax with options

   CREATE MATERIALIZED VIEW my_view AS SELECT * FROM sales; matches the correct order and keywords exactly.

3. Final Answer:

   CREATE MATERIALIZED VIEW my_view AS SELECT * FROM sales; -> Option C

4. Quick Check:

   CREATE MATERIALIZED VIEW = correct syntax [OK]

Hint: Materialized views start with CREATE MATERIALIZED VIEW [OK]

Common Mistakes:

• Mixing order of keywords 'MATERIALIZED' and 'VIEW'
• Placing MATERIALIZED after VIEW keyword
• Using incorrect keyword order causing syntax errors

3. Given a materialized view created as CREATE MATERIALIZED VIEW mv_sales AS SELECT region, SUM(amount) AS total FROM sales GROUP BY region;, what will be the result of SELECT * FROM mv_sales WHERE region = 'West'; if the sales table has 3 rows for 'West' with amounts 100, 200, and 300?

medium

A. Empty result set

B. [{"region": "West", "total": 600}]

C. [{"region": "West", "total": 100}]

D. [{"region": "West", "total": 300}]

Solution

1. Step 1: Understand the materialized view query

   The view groups sales by region and sums the amount for each region.

2. Step 2: Calculate sum for 'West' region

   Amounts 100 + 200 + 300 = 600, so total for 'West' is 600.

3. Final Answer:

   [{"region": "West", "total": 600}] -> Option B

4. Quick Check:

   Sum amounts for region 'West' = 600 [OK]

Hint: Sum grouped amounts to find materialized view result [OK]

Common Mistakes:

• Selecting single amount instead of sum
• Ignoring GROUP BY effect
• Expecting empty result when data exists

4. You created a materialized view but notice it returns outdated data after updates to the base table. What is the best way to fix this in Snowflake?

medium

A. Snowflake automatically refreshes the materialized view in the background.

B. Drop and recreate the materialized view every time data changes.

C. Run VACUUM command on the materialized view.

D. Restart the Snowflake warehouse to update the view.

Solution

1. Step 1: Identify how to update materialized views

   Snowflake materialized views are automatically and continuously refreshed in the background when base tables change. Temporary staleness is normal due to refresh latency.

2. Step 2: Use the correct behavior

   Snowflake handles refreshes automatically--no manual command needed.

3. Final Answer:

   Snowflake automatically refreshes the materialized view in the background. -> Option A

4. Quick Check:

   Snowflake MV = automatic background refresh [OK]

Hint: Snowflake automatically refreshes materialized views [OK]

Common Mistakes:

• Unnecessarily dropping and recreating the view
• Using VACUUM which is for table maintenance
• Restarting warehouse does not trigger refresh

5. You want to optimize a dashboard that runs a complex query repeatedly on a large sales table. Which approach using materialized views is best to improve performance while keeping data reasonably fresh?

hard

A. Create a materialized view with aggregated data and schedule periodic refreshes during low-usage hours.

B. Create a materialized view without aggregation and never refresh it.

C. Use a normal view instead of a materialized view to always get fresh data.

D. Create multiple materialized views with full data copies for each query variation.

Solution

1. Step 1: Consider performance and freshness needs

   Aggregated data reduces size and speeds queries; periodic refresh balances freshness and cost.

2. Step 2: Evaluate options for dashboard optimization

   Create a materialized view with aggregated data and schedule periodic refreshes during low-usage hours. uses aggregation and scheduled refresh, best for performance and reasonable freshness.

3. Final Answer:

   Create a materialized view with aggregated data and schedule periodic refreshes during low-usage hours. -> Option A

4. Quick Check:

   Aggregate + scheduled refresh = best practice [OK]

Hint: Aggregate data and refresh periodically for dashboard speed [OK]

Common Mistakes:

• Not refreshing materialized views causing stale data
• Using normal views which are slower for repeated queries
• Creating many full-copy views wasting storage