Views and materialized views in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Materialized View Refresh Behavior

In Snowflake, what happens when you query a materialized view that is not yet refreshed after the base table has changed?

A The query automatically triggers a full refresh of the materialized view before returning data.

B The query returns the data from the last refresh, ignoring recent changes in the base table.

C The query returns an error indicating the materialized view is stale.

D The query returns the latest data from the base table directly, bypassing the materialized view.

💡 Hint

Think about how materialized views store data separately and when they update.

Materialized views in Snowflake return data from their last refresh. They do not automatically refresh on query, so recent changes in the base table are not reflected until a manual or scheduled refresh occurs.

❓ Architecture

intermediate

Choosing Between Views and Materialized Views

You have a large table that updates frequently. You want to optimize query speed for a complex aggregation that is used often. Which approach is best in Snowflake?

A Create a copy of the table with precomputed aggregation and update it manually.

B Use a temporary table to store aggregation results for each query.

C Create a materialized view with the aggregation to speed up queries but refresh it regularly.

D Create a standard view with the aggregation query to always get fresh data.

💡 Hint

Consider trade-offs between query speed and data freshness.

Materialized views store precomputed results and speed up queries but require refresh to update data. This balances performance and data freshness better than standard views or manual copies.

❓ security

advanced

Access Control on Views vs Materialized Views

In Snowflake, if a user has SELECT privilege on a base table but no privileges on a materialized view built on that table, what happens when the user tries to query the materialized view?

A The query fails with an access denied error on the materialized view.

B The query succeeds because the user has access to the base table data.

C The query succeeds but returns no rows.

D The query triggers a privilege escalation warning but returns data.

💡 Hint

Think about how Snowflake enforces privileges on objects.

In Snowflake, privileges are required on the object being queried. Having access to the base table does not grant access to the materialized view. Without SELECT privilege on the materialized view, the query fails.

❓ Configuration

advanced

Materialized View Refresh Scheduling

Which Snowflake feature allows automatic refresh of materialized views without manual intervention?

A Using Snowflake Tasks to schedule refresh commands.

B Setting the materialized view property AUTO_REFRESH = TRUE.

C Enabling continuous data protection on the base table.

D Configuring the warehouse to auto-scale during queries.

💡 Hint

Think about how to automate SQL commands in Snowflake.

Snowflake does not have an AUTO_REFRESH property for materialized views. Instead, Snowflake Tasks can be used to schedule SQL commands that refresh materialized views automatically.

🧠 Conceptual

expert

Impact of Materialized Views on Storage and Compute Costs

Which statement best describes the cost implications of using materialized views in Snowflake compared to standard views?

A Materialized views have no impact on storage costs but increase compute costs due to refresh overhead.

B Materialized views reduce both storage and compute costs by compressing data more efficiently than base tables.

C Materialized views increase compute costs because queries always trigger a full refresh.

D Materialized views increase storage costs because they store data, but reduce compute costs by speeding up queries.

💡 Hint

Consider what materialized views store and how they affect query processing.

Materialized views store precomputed data, increasing storage usage. However, they reduce compute costs by allowing faster query execution. Refreshing the view does consume compute, but queries do not always trigger full refreshes.

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