Materialized views for repeated queries in Snowflake - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a materialized view in Snowflake?

A materialized view is a database object that stores the result of a query physically. It helps speed up repeated queries by avoiding recalculating the data each time.

beginner

How does a materialized view improve query performance?

It stores precomputed results, so when you run a query, Snowflake reads the stored data instead of running the full query again, making it faster.

intermediate

What is the difference between a materialized view and a regular view?

A regular view runs the query every time you use it, while a materialized view stores the query result physically and refreshes it automatically and asynchronously.

intermediate

How often are materialized views refreshed in Snowflake?

Materialized views in Snowflake are refreshed automatically and asynchronously when the base tables change, but the refresh timing is managed by Snowflake.

beginner

Name one best practice when using materialized views in Snowflake.

Use materialized views for queries that are run frequently and involve complex calculations or large data scans to improve performance efficiently.

What does a materialized view store in Snowflake?

A Only the query text

B The physical result of a query

C User permissions

D Temporary session data

Materialized views store the physical result of a query to speed up repeated access.

How does a materialized view differ from a regular view?

A It is slower to query

B It never updates

C It cannot be used in joins

D It stores query results physically

Materialized views store results physically, unlike regular views that run the query each time.

When are materialized views refreshed in Snowflake?

A Automatically and asynchronously when base tables change

B Only manually by the user

C Every second exactly

D Never refreshed

Snowflake refreshes materialized views automatically and asynchronously after base table changes.

Which scenario is best for using a materialized view?

A Storing user passwords

B One-time simple queries

C Frequently run complex queries

D Temporary session data

Materialized views are best for speeding up frequently run complex queries.

What is a limitation of materialized views in Snowflake?

A They may not reflect real-time data instantly

B They cannot be queried

C They delete base tables

D They require manual refresh always

Materialized views refresh asynchronously, so data may not be real-time immediately.

Explain what a materialized view is and how it helps with repeated queries in Snowflake.

Think about how saving work ahead of time helps you do things faster later.

Describe the differences between a regular view and a materialized view in Snowflake.

Compare how often the query runs and where the data is stored.

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