Materialized views for repeated queries in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

How does Snowflake update a materialized view?

When you query a materialized view in Snowflake, how is the data updated?

A The materialized view is updated asynchronously by Snowflake after base table changes, usually within minutes.

B The materialized view is updated only when explicitly refreshed by the user.

C The materialized view never updates; it is static after creation.

D The materialized view is updated automatically and immediately after every base table change.

💡 Hint

Think about how Snowflake balances performance and freshness for materialized views.

Snowflake updates materialized views asynchronously after changes to the base tables. This means the view is refreshed automatically but not immediately, allowing faster query performance while keeping data reasonably fresh.

❓ Architecture

intermediate

Choosing materialized views for query optimization

You have a query that aggregates sales data by region daily. Which scenario best justifies using a materialized view?

A The query runs once a month and scans a small table.

B The query runs multiple times a day on a large table and involves complex joins and aggregations.

C The query is simple and runs only once after data loads.

D The query is ad-hoc and unpredictable in frequency.

💡 Hint

Materialized views help most when queries are frequent and expensive.

Materialized views are most beneficial when queries are run frequently and involve costly operations like joins and aggregations on large datasets. This reduces query time by precomputing results.

❓ security

advanced

Access control on materialized views in Snowflake

Which statement about access control on materialized views in Snowflake is correct?

A Users need SELECT privilege on the base tables to query the materialized view.

B Materialized views inherit all privileges from the base tables automatically.

C Granting SELECT on the materialized view alone allows querying without base table privileges.

D Materialized views cannot have privileges granted directly.

💡 Hint

Consider how Snowflake separates privileges for views and base tables.

In Snowflake, granting SELECT on a materialized view allows users to query it without needing SELECT privileges on the underlying base tables. This simplifies access control.

✅ Best Practice

advanced

Best practice for maintaining materialized views with frequent base table updates

You have a base table updated every minute. What is the best practice to keep the materialized view efficient and fresh?

A Use clustering keys on the base table and monitor refresh lag to balance performance and freshness.

B Avoid materialized views; use direct queries instead for fresh data.

C Create multiple materialized views for each update batch to avoid conflicts.

D Manually drop and recreate the materialized view after each update.

💡 Hint

Think about how to optimize refresh performance and data freshness.

Using clustering keys helps Snowflake efficiently maintain materialized views with frequent updates. Monitoring refresh lag allows balancing query speed and data freshness without manual intervention.

🧠 Conceptual

expert

Materialized view limitations in Snowflake

Which of the following is a true limitation of materialized views in Snowflake?

A Materialized views can be created on external tables.

B Materialized views support all SQL functions and complex subqueries.

C Materialized views automatically update instantly with zero lag.

D Materialized views cannot reference other materialized views.

💡 Hint

Consider restrictions on dependencies and supported features.

Snowflake does not allow materialized views to reference other materialized views. This prevents complex dependency chains and ensures maintainability.

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