Materialized views for repeated queries in Snowflake - Mini Project: Build & Apply

Materialized Views for Repeated Queries in Snowflake

📖 Scenario: You work as a data engineer for a retail company. The sales team frequently runs a query to get the total sales amount per product category. Running this query repeatedly on the large sales data table is slow and costly.To improve performance, you decide to create a materialized view in Snowflake that precomputes the total sales per category. This will speed up repeated queries and reduce compute costs.

🎯 Goal: Create a materialized view in Snowflake that calculates the total sales amount grouped by product category from the sales table.This view will be used by the sales team to quickly get aggregated sales data without running the full query each time.

📋 What You'll Learn

Create a table named sales with columns product_category (string) and sales_amount (number).

Insert sample data into the sales table.

Create a materialized view named mv_total_sales_by_category that sums sales_amount grouped by product_category.

Verify the materialized view is created with the correct query.

💡 Why This Matters

🌍 Real World

Materialized views help speed up repeated queries on large datasets by storing precomputed results, saving time and compute resources.

💼 Career

Data engineers and analysts use materialized views to optimize query performance and reduce costs in cloud data platforms like Snowflake.

1

Create the sales table with sample data

Write SQL to create a table called sales with columns product_category as VARCHAR and sales_amount as NUMBER. Then insert these exact rows: ('Electronics', 100), ('Clothing', 50), ('Electronics', 150), ('Clothing', 70), ('Books', 40).

Snowflake

-- Create the sales table and insert sample data
-- Your code here

Hint

Use CREATE OR REPLACE TABLE to create the table. Use INSERT INTO sales with multiple rows for sample data.

2

Define the materialized view query

Create a SQL variable called mv_query that holds the exact query string: SELECT product_category, SUM(sales_amount) AS total_sales FROM sales GROUP BY product_category.

Snowflake

CREATE OR REPLACE TABLE sales (
    product_category VARCHAR,
    sales_amount NUMBER
);

INSERT INTO sales (product_category, sales_amount) VALUES
('Electronics', 100),
('Clothing', 50),
('Electronics', 150),
('Clothing', 70),
('Books', 40);

-- Create a variable mv_query with the materialized view SQL query
-- Your code here

Hint

Use SET mv_query = 'your SQL query' to store the query string.

3

Create the materialized view using the query

Write SQL to create or replace a materialized view named mv_total_sales_by_category using the query stored in mv_query. Use the exact query string from mv_query in the AS clause.

Snowflake

CREATE OR REPLACE TABLE sales (
    product_category VARCHAR,
    sales_amount NUMBER
);

INSERT INTO sales (product_category, sales_amount) VALUES
('Electronics', 100),
('Clothing', 50),
('Electronics', 150),
('Clothing', 70),
('Books', 40);

SET mv_query = 'SELECT product_category, SUM(sales_amount) AS total_sales FROM sales GROUP BY product_category';

-- Create the materialized view mv_total_sales_by_category
-- Your code here

Hint

Use CREATE OR REPLACE MATERIALIZED VIEW mv_total_sales_by_category AS followed by the query.

4

Verify the materialized view creation

Write a SQL query to select all columns from the materialized view mv_total_sales_by_category to verify it returns the aggregated sales per category.

Snowflake

CREATE OR REPLACE TABLE sales (
    product_category VARCHAR,
    sales_amount NUMBER
);

INSERT INTO sales (product_category, sales_amount) VALUES
('Electronics', 100),
('Clothing', 50),
('Electronics', 150),
('Clothing', 70),
('Books', 40);

SET mv_query = 'SELECT product_category, SUM(sales_amount) AS total_sales FROM sales GROUP BY product_category';

CREATE OR REPLACE MATERIALIZED VIEW mv_total_sales_by_category AS
SELECT product_category, SUM(sales_amount) AS total_sales FROM sales GROUP BY product_category;

-- Query the materialized view to verify
-- Your code here

Hint

Use SELECT * FROM mv_total_sales_by_category to see the aggregated results.

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