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

Need a 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

Need a 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

Need a 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

Need a hint?

Use SELECT * FROM mv_total_sales_by_category to see the aggregated results.