Views and materialized views in Snowflake - Mini Project: Build & Apply

Creating Views and Materialized Views in Snowflake

📖 Scenario: You work as a data engineer for a retail company. Your team wants to create reusable queries to analyze sales data efficiently. You will create a simple view and a materialized view in Snowflake to help the analysts get quick insights without rewriting queries every time.

🎯 Goal: Build a standard view and a materialized view in Snowflake using sales data. The view will show total sales per product category. The materialized view will pre-aggregate total sales per region for faster queries.

📋 What You'll Learn

Create a table called sales with columns product_id, category, region, and amount.

Create a view called category_sales_view that sums amount grouped by category.

Create a materialized view called region_sales_mv that sums amount grouped by region.

💡 Why This Matters

🌍 Real World

Views and materialized views help data teams provide reusable, efficient queries for business analysts and applications without duplicating data.

💼 Career

Data engineers and analysts often create views and materialized views to optimize query performance and simplify data access in cloud data warehouses like Snowflake.

1

Create the sales table with sample data

Write a SQL statement to create a table called sales with columns product_id (integer), category (string), region (string), and amount (number). Insert these exact rows: (1, 'Electronics', 'North', 100), (2, 'Clothing', 'South', 50), (3, 'Electronics', 'East', 75), (4, 'Clothing', 'North', 60).

Snowflake

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

Need a hint?

Use CREATE OR REPLACE TABLE to define the table and INSERT INTO to add rows.

2

Create the category_sales_view view

Write a SQL statement to create or replace a view called category_sales_view. This view should select category and the sum of amount as total_sales from the sales table, grouped by category.

Snowflake

CREATE OR REPLACE TABLE sales (
    product_id INT,
    category STRING,
    region STRING,
    amount NUMBER
);

INSERT INTO sales (product_id, category, region, amount) VALUES
(1, 'Electronics', 'North', 100),
(2, 'Clothing', 'South', 50),
(3, 'Electronics', 'East', 75),
(4, 'Clothing', 'North', 60);

-- Create the category_sales_view view
-- Your code here

Need a hint?

Use CREATE OR REPLACE VIEW and a GROUP BY query.

3

Create the region_sales_mv materialized view

Write a SQL statement to create or replace a materialized view called region_sales_mv. This materialized view should select region and the sum of amount as total_sales from the sales table, grouped by region.

Snowflake

CREATE OR REPLACE TABLE sales (
    product_id INT,
    category STRING,
    region STRING,
    amount NUMBER
);

INSERT INTO sales (product_id, category, region, amount) VALUES
(1, 'Electronics', 'North', 100),
(2, 'Clothing', 'South', 50),
(3, 'Electronics', 'East', 75),
(4, 'Clothing', 'North', 60);

CREATE OR REPLACE VIEW category_sales_view AS
SELECT category, SUM(amount) AS total_sales
FROM sales
GROUP BY category;

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

Need a hint?

Use CREATE OR REPLACE MATERIALIZED VIEW with a grouped query.

4

Verify the views and materialized view exist

Write SQL statements to select all columns from category_sales_view and from region_sales_mv to verify they return the aggregated sales data.

Snowflake

CREATE OR REPLACE TABLE sales (
    product_id INT,
    category STRING,
    region STRING,
    amount NUMBER
);

INSERT INTO sales (product_id, category, region, amount) VALUES
(1, 'Electronics', 'North', 100),
(2, 'Clothing', 'South', 50),
(3, 'Electronics', 'East', 75),
(4, 'Clothing', 'North', 60);

CREATE OR REPLACE VIEW category_sales_view AS
SELECT category, SUM(amount) AS total_sales
FROM sales
GROUP BY category;

CREATE OR REPLACE MATERIALIZED VIEW region_sales_mv AS
SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

-- Select from category_sales_view and region_sales_mv
-- Your code here

Need a hint?

Use simple SELECT * FROM queries to check the views.