Views and materialized views in Snowflake - Step-by-Step Execution

Process Flow - Views and materialized views

Create View

↓

View stores SQL query

↓

Query View

↓

Execute stored SQL on base tables

↓

Return fresh data

↓

Create Materialized View

↓

Materialized View stores data snapshot

↓

Query Materialized View

↓

Return stored snapshot quickly

↓

Background refresh updates snapshot

Views store SQL queries and run them fresh on demand; materialized views store data snapshots for faster reads and refresh in background.

Execution Sample

Snowflake

CREATE VIEW v_sales AS SELECT * FROM sales WHERE region = 'East';
CREATE MATERIALIZED VIEW mv_sales AS SELECT * FROM sales WHERE region = 'East';
SELECT * FROM v_sales;
SELECT * FROM mv_sales;

Creates a view and a materialized view filtering sales by region, then queries both to show data retrieval differences.

Process Table

Step	Action	Object	Result	Notes
1	Create view	v_sales	Stores SQL query: SELECT * FROM sales WHERE region = 'East'	No data stored yet
2	Create materialized view	mv_sales	Stores data snapshot matching query	Data physically stored for fast access
3	Query view	v_sales	Executes stored SQL on sales table	Returns fresh data each time
4	Query materialized view	mv_sales	Returns stored snapshot data	Faster response, may be slightly stale
5	Update base table sales	sales	Data changes in sales table	View will reflect changes next query
6	Query view again	v_sales	Executes SQL on updated sales	Returns updated fresh data
7	Query materialized view again	mv_sales	Returns old snapshot data	Data not updated yet
8	Background refresh	mv_sales	Updates snapshot with latest sales data	Materialized view now current
9	Query materialized view after refresh	mv_sales	Returns updated snapshot data	Data now matches base table
10	End	-	-	Execution stops after refresh and queries

💡 Execution stops after materialized view refresh and queries show updated data

Status Tracker

Variable	Start	After Step 2	After Step 5	After Step 8	Final
v_sales (query)	Not created	Stores SQL query	No change	No change	Stores SQL query
mv_sales (data snapshot)	Not created	Stores initial snapshot	Stale snapshot after sales update	Updated snapshot after refresh	Updated snapshot
sales (base table data)	Initial data	No change	Updated data	No change	Updated data

Key Moments - 2 Insights

Why does querying the view always return fresh data but the materialized view might not?

Because the view runs the stored SQL query on the base tables every time (see execution_table rows 3 and 6), while the materialized view returns stored snapshot data that only updates during background refresh (rows 4, 7, and 8).

What happens to the materialized view data when the base table changes?

The materialized view data becomes stale until a background refresh updates it (see execution_table rows 5, 7, and 8). The view, however, always reflects the latest base table data.

Visual Quiz - 3 Questions

Look at the execution_table at step 4. What data does querying the materialized view return?

A An error because data is not stored

B Fresh data from the base table

C Stored snapshot data

D Empty result set

💡 Hint

Refer to execution_table row 4 under 'Result' column

At which step does the materialized view snapshot get updated to match the base table?

A Step 5

B Step 8

C Step 6

D Step 9

💡 Hint

Check execution_table row 8 describing background refresh

If the base table 'sales' is updated, what will the view return on the next query?

A Fresh updated data

B Old data from before update

C Error due to data mismatch

D Empty result set

💡 Hint

See execution_table row 6 where view query runs after sales update

Concept Snapshot

Views store SQL queries and run them fresh each time you query.
Materialized views store a snapshot of data for faster reads.
Materialized views need background refresh to update data.
Views always show current data from base tables.
Use views for always fresh data, materialized views for speed.

Full Transcript

This lesson shows how views and materialized views work in Snowflake. A view saves a SQL query and runs it fresh every time you ask for data, so it always shows the latest information. A materialized view saves a snapshot of the data matching the query, so it returns results faster but may be slightly out of date until refreshed. When the base table changes, the view reflects those changes immediately on the next query. The materialized view only updates its stored data during a background refresh process. This means querying a view always runs the SQL on the base tables, while querying a materialized view returns stored data until refreshed. Understanding this helps you choose the right option for your needs: fresh data or faster reads.

Practice

1. What is the main difference between a view and a materialized view in Snowflake?

easy

A. A view does not store data, while a materialized view stores query results.

B. A view stores data permanently, but a materialized view does not.

C. A materialized view updates automatically with every query, but a view does not.

D. A view can only be used once, while a materialized view can be reused multiple times.

Solution

1. Step 1: Understand what a view does

   A view saves a query for reuse but does not store the actual data; it runs the query each time.

2. Step 2: Understand what a materialized view does

   A materialized view stores the results of the query physically, so it can return data faster without rerunning the query.

3. Final Answer:

   A view does not store data, while a materialized view stores query results. -> Option A

4. Quick Check:

   View = no stored data, Materialized view = stored data [OK]

Hint: Remember: views save queries, materialized views save data [OK]

Common Mistakes:

• Thinking views store data permanently
• Believing materialized views update instantly with every query
• Confusing reuse capability between views and materialized views

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

easy

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

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

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

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

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: Check each option's order and keywords

   Only CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_table; uses the correct order and keywords: CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_table;

3. Final Answer:

   CREATE MATERIALIZED VIEW my_view AS SELECT * FROM my_table; -> Option B

4. Quick Check:

   Correct syntax = CREATE MATERIALIZED VIEW [OK]

Hint: Materialized view syntax starts with CREATE MATERIALIZED VIEW [OK]

Common Mistakes:

• Mixing order of keywords
• Placing MATERIALIZED after VIEW
• Using incorrect keyword sequences

3. Given the following Snowflake SQL code:

CREATE TABLE sales (id INT, amount FLOAT);
INSERT INTO sales VALUES (1, 100.0), (2, 200.0);
CREATE MATERIALIZED VIEW sales_mv AS SELECT SUM(amount) AS total FROM sales;
INSERT INTO sales VALUES (3, 300.0);
SELECT * FROM sales_mv;

What will the SELECT query return?

medium

A. total = 600.0

B. total = 300.0 (only new rows)

C. total = 300.0 plus previous sums

D. total = 300.0

Solution

1. Step 1: Understand materialized view refresh behavior

   Materialized views in Snowflake do not automatically update after data changes; they show data as of last refresh.

2. Step 2: Analyze the timing of inserts and query

   The materialized view was created after inserting two rows (100.0 + 200.0 = 300.0). The third row (300.0) was inserted after the view creation but before the select.

3. Final Answer:

   total = 300.0 -> Option D

4. Quick Check:

   Materialized view shows data at last refresh, not latest inserts [OK]

Hint: Materialized views show data as of last refresh, not latest inserts [OK]

Common Mistakes:

• Assuming materialized views auto-update instantly
• Adding new rows to materialized view results without refresh
• Confusing materialized views with normal views

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

medium

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

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

C. Manually refresh the materialized view using ALTER MATERIALIZED VIEW ... REFRESH.

D. Restart the Snowflake warehouse to update the materialized view.

Solution

1. Step 1: Identify how to update materialized views

   Materialized views do not update automatically; they require manual refresh to show latest data.

2. Step 2: Choose the correct refresh method

   Snowflake supports manual refresh with ALTER MATERIALIZED VIEW ... REFRESH to update the stored data.

3. Final Answer:

   Manually refresh the materialized view using ALTER MATERIALIZED VIEW ... REFRESH. -> Option C

4. Quick Check:

   Manual refresh updates materialized view data [OK]

Hint: Use ALTER MATERIALIZED VIEW ... REFRESH to update data [OK]

Common Mistakes:

• Dropping and recreating instead of refreshing
• Restarting warehouse has no effect on view data
• Confusing materialized views with normal views for freshness

5. You want to speed up queries that aggregate large sales data but also need the most current totals. Which approach best balances speed and freshness using Snowflake views?

hard

A. Use a materialized view and schedule frequent refreshes to keep data updated.

B. Use a normal view only, since it always shows fresh data but may be slower.

C. Create a materialized view and never refresh it to save compute costs.

D. Use both a normal view and a materialized view simultaneously in the same query.

Solution

1. Step 1: Understand trade-offs between views and materialized views

   Normal views provide fresh data but can be slow on large data; materialized views are fast but need refresh to update.

2. Step 2: Find a balanced solution

   Scheduling frequent refreshes of materialized views keeps data reasonably fresh while improving query speed.

3. Final Answer:

   Use a materialized view and schedule frequent refreshes to keep data updated. -> Option A

4. Quick Check:

   Materialized view + refresh = speed + freshness balance [OK]

Hint: Schedule refreshes on materialized views for fresh and fast data [OK]

Common Mistakes:

• Not refreshing materialized views and expecting fresh data
• Using only normal views and ignoring performance
• Trying to combine views in one query without refresh strategy