Retail Category Analysis in Snowflake

Introduction

30 Second Summary

Every time you browse a grocery aisle, someone at headquarters is asking which product categories are growing, where margins are shrinking, and whether promotions are actually working. These questions drive millions in retail decisions every week.

In this project, you will load real retail scanner data into Snowflake, build SQL analytical views that calculate key performance metrics, and connect everything to a live Tableau dashboard that tells stakeholders a clear "so what" story about category performance.

What You'll Build

You will have a live dashboard where you can click on a product category, watch the charts update instantly, and point a stakeholder to a specific annotated insight about revenue trends or margin shifts.

By the end of this project, you'll have:

• A queryable data warehouse where you can instantly see weekly revenue, unit volume, and profit margins across multiple grocery categories.
• A set of SQL analytical views that automatically surface trends, week-over-week growth, and store rankings so every query reveals a pattern instead of a wall of numbers.
• An interactive Tableau dashboard connected live to Snowflake, where clicking a category filter updates every chart and a text annotation delivers the consultative "so what" a stakeholder can act on.
• Secret Mission: A private label vs. branded comparison layer that segments all KPIs by brand type to recommend category strategy.

Are there any prerequisites?

This project assumes you have an active Snowflake account (a free trial works perfectly), Tableau Desktop installed, and intermediate SQL knowledge (comfortable with JOINs and GROUP BY).

You will also need VS Code for inspecting CSV files before loading them.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters. This project mirrors the daily workflow of a BI Analyst: ingesting raw retail data into a cloud warehouse, transforming it with SQL, and presenting insights through an interactive dashboard.

Set Up Your Snowflake Workspace

Before you can analyze anything, you need a clean, dedicated workspace in Snowflake and the raw retail data files on your machine.

In this step you will create a database, schema, and warehouse in Snowflake, download real grocery scanner data, and build the empty tables that will hold it all.

In this step, get ready to:

• Create a dedicated Snowflake database, schema, and warehouse for this project.
• Download the Dominick's Finer Foods retail dataset CSV files.
• Create four empty tables and an internal stage to receive the data.

Create a project workspace in Snowflake

Snowsight is Snowflake's web interface where you write and run SQL.

• Open your browser and navigate to https://app.snowflake.com.
• Sign in with your Snowflake credentials.
• In the navigation menu, select Projects, then Worksheets.
• Click the + button and select SQL Worksheet to create a new worksheet.

• Create the project database, schema, and warehouse by pasting and running this SQL in your new worksheet:

-- Create project workspace
CREATE OR REPLACE DATABASE RETAIL_ANALYTICS;
USE DATABASE RETAIL_ANALYTICS;
CREATE OR REPLACE SCHEMA CATEGORY_PERFORMANCE;
USE SCHEMA CATEGORY_PERFORMANCE;

CREATE OR REPLACE WAREHOUSE BI_PROJECT_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;
USE WAREHOUSE BI_PROJECT_WH;

What does this SQL do?

• RETAIL_ANALYTICS is the dedicated database that keeps all project objects separate from anything else in your account.
• CATEGORY_PERFORMANCE is a schema inside that database to group tables and views logically.
• BI_PROJECT_WH is an X-Small warehouse that provides compute. It auto-suspends after 60 seconds of inactivity so you only use credits while queries are running.

• Select all the SQL in the worksheet and click Run (or press Cmd+Enter on macOS, Ctrl+Enter on Windows).
• Confirm that each statement shows a success message in the results pane.

Before you move on, what do you expect the worksheet's context to show now?

• Verify your workspace by running this query:

SELECT CURRENT_WAREHOUSE(), CURRENT_DATABASE(), CURRENT_SCHEMA();

You should see a single row with BI_PROJECT_WH, RETAIL_ANALYTICS, and CATEGORY_PERFORMANCE returned.

Seeing NULL for the warehouse?

If CURRENT_WAREHOUSE() returns NULL, your role may not have permission to use the warehouse. Make sure you ran the USE WAREHOUSE BI_PROJECT_WH; line successfully.

Also check that you selected the entire block of SQL before clicking Run. If you only ran the first line, the remaining statements were skipped.

help me troubleshoot why my Snowflake warehouse shows NULL

Download the Dominick's Finer Foods dataset

The Dominick's Finer Foods dataset contains real store-level scanner data from 93 grocery stores across 399 weeks. You will download two categories for comparison: Cereals and Soft Drinks.

• Open the Dominick's dataset page from the Kilts Center at the University of Chicago Booth School of Business.
• Download these four CSV files:
• Open wcer.csv in VS Code to confirm the file downloaded correctly.

You should see columns like STORE, UPC, WEEK, MOVE, QTY, PRICE, SALE, PROFIT, and OK. Each row represents one product's weekly sales at one store.

What are these columns?

• MOVE is the number of units sold that week.
• PRICE is the retail price.
• PROFIT is the dollar profit on sales.
• OK is a flag where 1 means the record is valid and reliable.

Can't find the CSV files on the download page?

The dataset page may require you to register or accept terms before downloading. Look for a section listing individual category files. Cereals use the abbreviation cer and Soft Drinks use sdr.

help me find and download the Dominick's Finer Foods CSV files

Create the target tables in Snowflake

Now you need matching tables in Snowflake to receive this data. You will also create an internal stage, which is a temporary holding area where files get uploaded before loading into tables.

• Back in your Snowsight SQL Worksheet, paste and run this SQL to create the stage and cereal tables:

-- Create internal stage for file uploads
CREATE OR REPLACE STAGE RETAIL_DATA_STAGE;

-- Create tables for cereal data
CREATE OR REPLACE TABLE CEREAL_UPC (
  UPC NUMBER,
  COM_CODE INTEGER,
  NITEM INTEGER,
  DESCRIP STRING,
  SIZE STRING,
  CASE_PACK INTEGER
);

CREATE OR REPLACE TABLE CEREAL_MOVEMENT (
  STORE INTEGER,
  UPC NUMBER,
  WEEK INTEGER,
  MOVE INTEGER,
  QTY INTEGER,
  PRICE NUMBER(10,2),
  SALE STRING,
  PROFIT NUMBER(10,2),
  OK INTEGER
);

What does this SQL do?

• RETAIL_DATA_STAGE is an internal stage. Think of it as a temporary upload folder inside Snowflake where your CSV files land before being loaded into tables.
• CEREAL_UPC holds product details like description and package size for every cereal product.
• CEREAL_MOVEMENT holds weekly sales data: how many units moved, at what price, and the profit earned.

• Select all the SQL above and click Run.
• Now paste and run this SQL to create the matching soft drink tables:

-- Create tables for soft drinks data
CREATE OR REPLACE TABLE SOFTDRINK_UPC (
  UPC NUMBER,
  COM_CODE INTEGER,
  NITEM INTEGER,
  DESCRIP STRING,
  SIZE STRING,
  CASE_PACK INTEGER
);

CREATE OR REPLACE TABLE SOFTDRINK_MOVEMENT (
  STORE INTEGER,
  UPC NUMBER,
  WEEK INTEGER,
  MOVE INTEGER,
  QTY INTEGER,
  PRICE NUMBER(10,2),
  SALE STRING,
  PROFIT NUMBER(10,2),
  OK INTEGER
);

Why two separate categories?

Having Cereals and Soft Drinks side by side lets you compare performance across categories. This mirrors how a real BI Analyst slices data to find which categories are growing and which need attention.

• Select all and click Run.

What do you expect to see if all four tables were created successfully?

• Verify all tables exist by running:

SHOW TABLES;

You should see four rows listed: CEREAL_MOVEMENT, CEREAL_UPC, SOFTDRINK_MOVEMENT, and SOFTDRINK_UPC.

Only seeing some tables?

Make sure you are in the correct context. Run USE DATABASE RETAIL_ANALYTICS; and USE SCHEMA CATEGORY_PERFORMANCE; first, then try SHOW TABLES; again.

If a CREATE TABLE statement failed, scroll up in the results pane to find the error. A common cause is a typo in a column data type.

help me figure out why my Snowflake tables did not create properly

✔️ Awesome, I've got everything!

Great. Double check you ran all the SQL blocks above in order and that SHOW TABLES; returns four tables.

ⓧ I'd like to double check the full code

Your worksheet should contain the following blocks in order. Scroll up and confirm each one is present:

• Workspace creation: CREATE OR REPLACE DATABASE RETAIL_ANALYTICS through USE WAREHOUSE BI_PROJECT_WH;.
• Stage and cereal tables: CREATE OR REPLACE STAGE RETAIL_DATA_STAGE through CEREAL_MOVEMENT table definition.
• Soft drink tables: SOFTDRINK_UPC and SOFTDRINK_MOVEMENT table definitions.

Your Snowflake workspace is ready with a database, warehouse, stage, and four empty tables. Next up, you will load the CSV data into these tables and run your first real queries against retail scanner data.

Load and Validate the Retail Data

In the previous step, you set up your Snowflake workspace and created the empty tables that will hold retail scanner data. Now it is time to fill those tables.

Raw CSV files sitting on your laptop are useless for analysis. Loading them into Snowflake gives you the power to query tens of thousands of rows instantly and later connect Tableau for visualization.

In this step, get ready to:

• Upload your four CSV files to the Snowflake internal stage.
• Load staged data into tables using COPY INTO commands.
• Validate row counts and inspect live retail data in the cloud.

Upload CSV files to the Snowflake internal stage

Snowflake's COPY INTO command loads data from a stage, not directly from your laptop. You need to upload your CSV files to the internal stage first.

• In the Snowsight navigation menu, select Ingestion, then Add Data.
• On the Add Data page, select Load files into a Stage.
• In the Upload Your Files dialog, select all four CSV files from your computer: upccer.csv, wcer.csv, upcsdr.csv, and wsdr.csv.
• Select the RETAIL_ANALYTICS database and CATEGORY_PERFORMANCE schema, then select the RETAIL_DATA_STAGE stage.
• Click Upload.

Why upload to a stage first?

Snowflake separates the upload step from the load step for good reason. Staging lets you inspect files, retry failed loads, and load the same data into multiple tables without re-uploading.

The PUT SQL command also uploads files, but it cannot be executed from Snowflake Worksheets. The Snowsight UI upload is the simplest path for getting local files onto a stage.

Now confirm the files landed on the stage.

• Switch back to your SQL Worksheet and verify the upload by running this command:

LIST @RETAIL_DATA_STAGE;

You should see four rows in the results, one for each CSV file you uploaded.

Don't see all four files?

Make sure you selected the correct database, schema, and stage during upload. If a file is missing, repeat the upload steps for that file only.

Also confirm your worksheet context is set to RETAIL_ANALYTICS and CATEGORY_PERFORMANCE by running the USE statements from Step 1.

Still having trouble? Help me troubleshoot why my files are not showing in my Snowflake stage after upload.

Load data from the stage into tables using COPY INTO

With files on the stage, you can now pull them into your tables. Each COPY INTO command maps one CSV file to its target table.

• Load the cereal category data by running these commands in your SQL Worksheet:

USE DATABASE RETAIL_ANALYTICS;
USE SCHEMA CATEGORY_PERFORMANCE;
USE WAREHOUSE BI_PROJECT_WH;

-- Load cereal UPC data
COPY INTO CEREAL_UPC
  FROM @RETAIL_DATA_STAGE/upccer.csv
  FILE_FORMAT = (
    TYPE = CSV
    FIELD_DELIMITER = ','
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
  )
  ON_ERROR = 'CONTINUE';

-- Load cereal movement data
COPY INTO CEREAL_MOVEMENT
  FROM @RETAIL_DATA_STAGE/wcer.csv
  FILE_FORMAT = (
    TYPE = CSV
    FIELD_DELIMITER = ','
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
  )
  ON_ERROR = 'CONTINUE';

What do these file format options do?

• SKIP_HEADER = 1 tells Snowflake the first row contains column names, not data.
• FIELD_OPTIONALLY_ENCLOSED_BY = '"' handles product descriptions that contain commas inside quotes.
• ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE prevents the load from failing if a row has extra or missing fields.
• ON_ERROR = 'CONTINUE' skips problematic rows instead of aborting the entire load.

After running, Snowflake returns a results table showing the file name, load status, rows parsed, and rows loaded for each COPY INTO statement. You should see a LOADED status for both files.

• Now load the soft drinks category data by running:

-- Load soft drink UPC data
COPY INTO SOFTDRINK_UPC
  FROM @RETAIL_DATA_STAGE/upcsdr.csv
  FILE_FORMAT = (
    TYPE = CSV
    FIELD_DELIMITER = ','
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
  )
  ON_ERROR = 'CONTINUE';

-- Load soft drink movement data
COPY INTO SOFTDRINK_MOVEMENT
  FROM @RETAIL_DATA_STAGE/wsdr.csv
  FILE_FORMAT = (
    TYPE = CSV
    FIELD_DELIMITER = ','
    SKIP_HEADER = 1
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
  )
  ON_ERROR = 'CONTINUE';

You should see LOADED status for both soft drink files as well.

Seeing LOAD_FAILED or 0 rows loaded?

Check that the file names in your COPY INTO commands match the exact file names you uploaded. Run LIST @RETAIL_DATA_STAGE; again to confirm the file names on the stage.

If you see a PARTIALLY_LOADED status, that is acceptable. Some rows may have data quality issues. The ON_ERROR = 'CONTINUE' setting skips those rows and loads the rest.

Need more help? Help me debug my COPY INTO command that is failing or loading 0 rows.

Run a quick validation query

Loading is only half the job. You need to confirm the data actually arrived and inspect what it looks like.

• Check the row counts across all four tables by running:

-- Validate row counts
SELECT 'CEREAL_UPC' AS TABLE_NAME, COUNT(*) AS ROW_COUNT FROM CEREAL_UPC
UNION ALL
SELECT 'CEREAL_MOVEMENT', COUNT(*) FROM CEREAL_MOVEMENT
UNION ALL
SELECT 'SOFTDRINK_UPC', COUNT(*) FROM SOFTDRINK_UPC
UNION ALL
SELECT 'SOFTDRINK_MOVEMENT', COUNT(*) FROM SOFTDRINK_MOVEMENT;

What should I see?

You should see four rows, one per table, each showing a non-zero row count. CEREAL_MOVEMENT should have tens of thousands of rows. The UPC tables will be smaller (hundreds of products).

Before you move on, take your first look at real retail scanner data in the cloud.

• What do you think you will see when you query actual weekly sales rows? Think about what columns like STORE, UPC, PRICE, and MOVE represent before running the next query.
• Inspect a sample of the cereal movement data by running:

SELECT STORE, UPC, WEEK, MOVE, PRICE, PROFIT
FROM CEREAL_MOVEMENT
WHERE OK = 1
LIMIT 20;

You should see 20 rows of weekly sales records showing individual store numbers, product UPCs, week identifiers, units sold, prices, and profit values. This is real retail scanner data, now queryable in seconds.

What does OK = 1 mean?

The OK column is a data quality flag in the Dominick's dataset. When OK = 1, the record passed quality checks. Filtering on this flag removes suspect data points from your analysis.

✔️ Awesome, I've got everything!

Great. Double check you have non-zero row counts for all four tables and that your sample query returned real sales data.

ⓧ I'd like to double check the full code

Here is the complete 02_load_data.sql file combining everything you ran in this step. Scroll up and confirm each section is present in your worksheet.

• The three USE statements at the top (database, schema, warehouse).
• Four COPY INTO commands (one per CSV file, each with the same FILE_FORMAT block).
• The validation row count query using UNION ALL across all four tables.

Your Snowflake warehouse now holds real retail data ready for analysis. Next up, you will write SQL queries to explore category performance and discover why raw numbers alone do not tell a useful story.

Explore Category Performance with SQL

Your Snowflake warehouse is loaded with real retail scanner data. Now it is time to query it and see what category performance actually looks like at the weekly level.

Before building anything fancy, you need to understand the raw shape of the data. In this step, you will write simple aggregation queries to surface basic category metrics. The goal is to see what SQL alone can tell you about weekly revenue and volume.

In this step, get ready to:

• Write aggregation queries for weekly revenue and units across two categories.
• Observe the limitation of raw number output for stakeholder insights.

Write basic aggregation queries

The simplest way to measure category performance is to calculate total revenue and total units sold per week. Revenue comes from multiplying the selling price by the number of units moved.

• In Snowsight, click the + button in the top left area of the Worksheets panel.
• Select SQL Worksheet to open a fresh worksheet.
• Rename the worksheet to 03_explore by clicking on the worksheet name at the top.

• Set the worksheet context to your project database and warehouse by pasting this at the top:

USE DATABASE RETAIL_ANALYTICS;
USE SCHEMA CATEGORY_PERFORMANCE;
USE WAREHOUSE BI_PROJECT_WH;

• Select all three lines and click the Run button (or press Cmd+Return on macOS, Ctrl+Enter on Windows) to set the context.

You should see a "Statement executed successfully" message for each USE command.

Now for the actual exploration. You will calculate weekly revenue (PRICE * MOVE) and total units sold (SUM(MOVE)) for both Cereals and Soft Drinks, combined into a single result set using UNION ALL.

• Add the following query below your USE statements in the worksheet:

-- Basic weekly revenue and volume for Cereals
SELECT
  'Cereals' AS CATEGORY,
  WEEK,
  SUM(PRICE * MOVE) AS WEEKLY_REVENUE,
  SUM(MOVE) AS TOTAL_UNITS
FROM CEREAL_MOVEMENT
WHERE OK = 1
GROUP BY WEEK

UNION ALL

-- Basic weekly revenue and volume for Soft Drinks
SELECT
  'Soft Drinks' AS CATEGORY,
  WEEK,
  SUM(PRICE * MOVE) AS WEEKLY_REVENUE,
  SUM(MOVE) AS TOTAL_UNITS
FROM SOFTDRINK_MOVEMENT
WHERE OK = 1
GROUP BY WEEK

ORDER BY CATEGORY, WEEK;

What does this query do?

• PRICE * MOVE calculates the dollar revenue for each row (price per unit multiplied by units sold that week).
• WHERE OK = 1 filters out flagged records that Dominick's marked as suspect data.
• UNION ALL stacks the Cereals and Soft Drinks results into one output so you can compare them side by side.
• ORDER BY CATEGORY, WEEK sorts the output alphabetically by category, then chronologically by week number.

Attempt to derive insights from raw output

Your query is valid SQL that should return results. Before you run it, think about what you expect to see. Will the output clearly show you which category is growing, which weeks had spikes, or where margins are thin?

• Select the entire query (from the first SELECT down to the ORDER BY line) and click Run.

You should see a results table appear below the worksheet with four columns: CATEGORY, WEEK, WEEKLY_REVENUE, and TOTAL_UNITS. There are hundreds of rows. Scroll through a few dozen.

This is a wall of numbers. Week 14 had $42,000 in cereal revenue. Week 15 had $43,500. Week 16 had $41,200. Is that a decline or normal fluctuation? Is soft drinks growing faster? The numbers alone do not answer these questions.

Why is this output not useful yet?

This is the core BI challenge. Raw aggregations are technically correct, but they are not insights. A stakeholder looking at this output cannot quickly see trends, compare growth rates, or identify where margins are under pressure.

Even if you filtered to a narrower time range (say, 20 weeks), you would still see rows of numbers with no context about whether those numbers are good, bad, or changing. Stakeholders need the "so what" layer on top of the data.

Seeing zero rows or an error?

• Make sure your USE statements ran first. The worksheet context must point to RETAIL_ANALYTICS.CATEGORY_PERFORMANCE.
• If you see zero rows, confirm that your data loaded in the previous step by running SELECT COUNT(*) FROM CEREAL_MOVEMENT; separately. You should see a count in the tens of thousands.
• Check that you selected the full query including the ORDER BY line. Running only the first SELECT without the UNION ALL will still return results, but missing the ORDER BY can cause a syntax error if partially selected.

Help me debug my exploration query in Snowflake

✔️ Awesome, I've got everything!

Great. Confirm you have saved your worksheet as 03_explore and that the query runs without errors.

ⓧ I'd like to double check the full code

Here is the complete 03_explore.sql worksheet. Make sure yours matches exactly.

USE DATABASE RETAIL_ANALYTICS;
USE SCHEMA CATEGORY_PERFORMANCE;
USE WAREHOUSE BI_PROJECT_WH;

-- Basic weekly revenue and volume for Cereals
SELECT
  'Cereals' AS CATEGORY,
  WEEK,
  SUM(PRICE * MOVE) AS WEEKLY_REVENUE,
  SUM(MOVE) AS TOTAL_UNITS
FROM CEREAL_MOVEMENT
WHERE OK = 1
GROUP BY WEEK

UNION ALL

-- Basic weekly revenue and volume for Soft Drinks
SELECT
  'Soft Drinks' AS CATEGORY,
  WEEK,
  SUM(PRICE * MOVE) AS WEEKLY_REVENUE,
  SUM(MOVE) AS TOTAL_UNITS
FROM SOFTDRINK_MOVEMENT
WHERE OK = 1
GROUP BY WEEK

ORDER BY CATEGORY, WEEK;

You have proven the point. Raw SQL aggregations give you correct numbers, but numbers alone are not insights. In the next step, you will build analytical views that calculate growth rates, profit margins, and store rankings automatically. That is the "so what" layer that turns data into decisions.

Build Analytical Views That Tell a Story

In Step 3, you ran aggregation queries against your retail data and saw the core BI challenge firsthand. The output was a wall of numbers with no clear trend, no context, and no actionable insight.

Stakeholders do not want raw numbers. They want to see trends, comparisons, and anomalies at a glance. In this step, you'll create analytical views that pre-calculate KPIs and structure the data so every query surfaces meaningful patterns automatically.

In this step, get ready to:

• Create a category-level KPI view with revenue, margins, and week-over-week growth.
• Create a store performance view for Pareto analysis.
• Test the views and confirm they surface actionable insights.

Create a category-level KPI view

A SQL view is a saved query that acts like a virtual table. Every time you (or Tableau) queries the view, it recalculates the results from the underlying data. This means your KPIs stay fresh without manual re-runs.

• In your Snowsight SQL Worksheet, set the session context by running these USE statements:

USE DATABASE RETAIL_ANALYTICS;
USE SCHEMA CATEGORY_PERFORMANCE;
USE WAREHOUSE BI_PROJECT_WH;

Now you'll build the CATEGORY_KPI_SUMMARY view. This view uses a CTE (Common Table Expression) to gather weekly metrics from both category tables, then applies window functions to calculate growth trends.

• Paste the first part of the view definition, which builds the foundation data using a CTE:

-- Category KPI Summary View
CREATE OR REPLACE VIEW CATEGORY_KPI_SUMMARY AS
WITH CATEGORY_WEEKLY AS (
  SELECT
    'Cereals' AS CATEGORY,
    WEEK,
    SUM(PRICE * MOVE) AS REVENUE,
    SUM(MOVE) AS UNITS_SOLD,
    AVG(PRICE) AS AVG_PRICE,
    SUM(PROFIT) AS TOTAL_PROFIT,
    COUNT(DISTINCT STORE) AS ACTIVE_STORES,
    COUNT(DISTINCT UPC) AS ACTIVE_PRODUCTS
  FROM CEREAL_MOVEMENT
  WHERE OK = 1 AND PRICE > 0 AND MOVE > 0
  GROUP BY WEEK

  UNION ALL

  SELECT
    'Soft Drinks' AS CATEGORY,
    WEEK,
    SUM(PRICE * MOVE) AS REVENUE,
    SUM(MOVE) AS UNITS_SOLD,
    AVG(PRICE) AS AVG_PRICE,
    SUM(PROFIT) AS TOTAL_PROFIT,
    COUNT(DISTINCT STORE) AS ACTIVE_STORES,
    COUNT(DISTINCT UPC) AS ACTIVE_PRODUCTS
  FROM SOFTDRINK_MOVEMENT
  WHERE OK = 1 AND PRICE > 0 AND MOVE > 0
  GROUP BY WEEK
)

What does this CTE do?

The CATEGORY_WEEKLY CTE aggregates weekly data for each category independently, then unions them together.

• REVENUE is calculated as PRICE * MOVE (price times units sold).
• The WHERE OK = 1 AND PRICE > 0 AND MOVE > 0 filter removes suspect records and zero-value rows that would distort the averages.
• ACTIVE_STORES and ACTIVE_PRODUCTS count how many stores and products contributed data each week.

• Directly below the CTE (no blank line between them), paste the outer SELECT that adds calculated KPIs and window functions:

SELECT
  CATEGORY,
  WEEK,
  REVENUE,
  UNITS_SOLD,
  AVG_PRICE,
  TOTAL_PROFIT,
  CASE WHEN REVENUE > 0 THEN ROUND(TOTAL_PROFIT / REVENUE * 100, 2) ELSE 0 END AS PROFIT_MARGIN_PCT,
  ACTIVE_STORES,
  ACTIVE_PRODUCTS,
  LAG(REVENUE) OVER (PARTITION BY CATEGORY ORDER BY WEEK) AS PREV_WEEK_REVENUE,
  CASE
    WHEN LAG(REVENUE) OVER (PARTITION BY CATEGORY ORDER BY WEEK) > 0
    THEN ROUND((REVENUE - LAG(REVENUE) OVER (PARTITION BY CATEGORY ORDER BY WEEK))
         / LAG(REVENUE) OVER (PARTITION BY CATEGORY ORDER BY WEEK) * 100, 2)
    ELSE NULL
  END AS REVENUE_GROWTH_PCT
FROM CATEGORY_WEEKLY
ORDER BY CATEGORY, WEEK;

What do the window functions calculate?

• PROFIT_MARGIN_PCT divides total profit by revenue to show what percentage of each dollar is profit.
• LAG(REVENUE) OVER (PARTITION BY CATEGORY ORDER BY WEEK) looks at the previous week's revenue within the same category.
• REVENUE_GROWTH_PCT calculates the percentage change from last week to this week. A positive number means growth. A negative number means decline.

• Select the entire statement (from CREATE OR REPLACE VIEW down to the final semicolon) and click the **Run** button (or press Ctrl+Enter).

You should see a success message confirming the view was created.

• Verify the view works by running this quick query:

SELECT * FROM CATEGORY_KPI_SUMMARY ORDER BY CATEGORY, WEEK LIMIT 10;

You should see a results table with columns for CATEGORY, WEEK, REVENUE, UNITS_SOLD, AVG_PRICE, TOTAL_PROFIT, PROFIT_MARGIN_PCT, and REVENUE_GROWTH_PCT. The first row's growth will be NULL (there is no previous week to compare against).

Seeing an error when you run the view?

• Make sure both code blocks are pasted as one continuous statement. The closing ) of the CTE should be immediately followed by the outer SELECT on the next line.
• Confirm your USE statements ran first. If the worksheet lost context, re-run the three USE lines at the top.
• Check for a missing semicolon at the very end of the statement.

Why is my CATEGORY_KPI_SUMMARY view failing to create?

Create a store performance view

The second view answers a different question: which stores drive the majority of category sales? This is called a Pareto analysis. It ranks stores by revenue and shows a running cumulative percentage so you can see how many stores account for most of the business.

• Paste the first part of the store performance view, which calculates total revenue per store in each category:

-- Store Performance View (Pareto Analysis)
CREATE OR REPLACE VIEW STORE_PERFORMANCE AS
WITH STORE_REVENUE AS (
  SELECT
    'Cereals' AS CATEGORY,
    STORE,
    SUM(PRICE * MOVE) AS TOTAL_REVENUE,
    SUM(MOVE) AS TOTAL_UNITS,
    SUM(PROFIT) AS TOTAL_PROFIT
  FROM CEREAL_MOVEMENT
  WHERE OK = 1 AND PRICE > 0 AND MOVE > 0
  GROUP BY STORE

  UNION ALL

  SELECT
    'Soft Drinks' AS CATEGORY,
    STORE,
    SUM(PRICE * MOVE) AS TOTAL_REVENUE,
    SUM(MOVE) AS TOTAL_UNITS,
    SUM(PROFIT) AS TOTAL_PROFIT
  FROM SOFTDRINK_MOVEMENT
  WHERE OK = 1 AND PRICE > 0 AND MOVE > 0
  GROUP BY STORE
)

• Directly below the CTE, paste the outer SELECT that applies ranking and cumulative calculations:

SELECT
  CATEGORY,
  STORE,
  TOTAL_REVENUE,
  TOTAL_UNITS,
  TOTAL_PROFIT,
  RANK() OVER (PARTITION BY CATEGORY ORDER BY TOTAL_REVENUE DESC) AS REVENUE_RANK,
  SUM(TOTAL_REVENUE) OVER (PARTITION BY CATEGORY ORDER BY TOTAL_REVENUE DESC ROWS UNBOUNDED PRECEDING) AS RUNNING_REVENUE,
  SUM(TOTAL_REVENUE) OVER (PARTITION BY CATEGORY) AS CATEGORY_TOTAL_REVENUE,
  ROUND(SUM(TOTAL_REVENUE) OVER (PARTITION BY CATEGORY ORDER BY TOTAL_REVENUE DESC ROWS UNBOUNDED PRECEDING)
    / SUM(TOTAL_REVENUE) OVER (PARTITION BY CATEGORY) * 100, 2) AS CUMULATIVE_REVENUE_PCT
FROM STORE_REVENUE
ORDER BY CATEGORY, REVENUE_RANK;

What do these window functions calculate?

• RANK() assigns each store a position within its category based on total revenue (highest first).
• RUNNING_REVENUE uses ROWS UNBOUNDED PRECEDING to sum revenue from the top-ranked store down to the current one.
• CUMULATIVE_REVENUE_PCT divides the running total by the category total. When this reaches 80%, you know those stores drive 80% of all sales.

• Select the entire statement (from CREATE OR REPLACE VIEW STORE_PERFORMANCE through the semicolon) and click **Run**.

You should see a success message confirming the second view was created.

• Verify the Pareto view works by running:

SELECT * FROM STORE_PERFORMANCE WHERE CATEGORY = 'Cereals' LIMIT 10;

You should see stores ranked from highest to lowest revenue, with the CUMULATIVE_REVENUE_PCT climbing toward 100% as you move down the list.

View created but returns zero rows?

• Check that your CEREAL_MOVEMENT table has data by running SELECT COUNT(*) FROM CEREAL_MOVEMENT WHERE OK = 1;. If this returns zero, revisit Step 2 to reload the data.
• Confirm the filter conditions PRICE > 0 AND MOVE > 0 are not eliminating all rows. Some datasets may have different zero-handling.

Why is my STORE_PERFORMANCE view returning no data?

Test the views with a full query

Both views exist. Now run the definitive test to see whether the structured output actually surfaces the "so what" that raw queries could not.

• Before you run the next query, predict: will you see flat numbers again, or something that immediately tells a story?

• Run the final test query to pull structured KPIs from your view:

-- Quick test
SELECT * FROM CATEGORY_KPI_SUMMARY ORDER BY CATEGORY, WEEK LIMIT 20;

You should see a clean table with CATEGORY, WEEK, REVENUE, UNITS_SOLD, AVG_PRICE, TOTAL_PROFIT, PROFIT_MARGIN_PCT, and REVENUE_GROWTH_PCT columns. The growth percentages immediately show you which weeks had spikes and which had declines. That is the "so what" layer.

Why is this better than the raw queries?

Compare this output to what you saw in Step 3. The raw query gave you revenue and units with no context. Now you can instantly see profit margins, week-over-week growth, and how many stores were active.

When you connect Tableau to these views in the next step, it will pull this pre-structured data directly. No complex SQL needed on the visualization side.

✔️ Awesome, I've got everything!

Great. Confirm you've saved your worksheet with all the SQL from this step.

ⓧ I'd like to double check the full code

Your worksheet should contain these sections in order. Scroll up through your worksheet and confirm each is present:

• Three USE statements at the top (DATABASE, SCHEMA, WAREHOUSE).
• The CREATE OR REPLACE VIEW CATEGORY_KPI_SUMMARY statement with the CATEGORY_WEEKLY CTE and outer SELECT including LAG window functions.
• The CREATE OR REPLACE VIEW STORE_PERFORMANCE statement with the STORE_REVENUE CTE and outer SELECT including RANK and running SUM.
• The quick test query: SELECT * FROM CATEGORY_KPI_SUMMARY ORDER BY CATEGORY, WEEK LIMIT 20;.

Your data warehouse now has an analytical layer that transforms raw scanner data into structured insights. Next up, you'll connect Tableau Desktop to these views and build an interactive dashboard that stakeholders can explore themselves.

Build a Tableau Dashboard Connected to Snowflake

In the previous step, you built analytical views that surface trends, growth rates, and margins automatically from your retail data. Now you have a "so what" layer sitting in Snowflake.

Stakeholders do not run SQL queries. They need to filter by category, compare trends visually, and spot anomalies at a glance. Tableau connects directly to your Snowflake views and turns that structured data into an interactive dashboard anyone can explore.

In this step, get ready to:

• Connect Tableau Desktop live to your Snowflake warehouse.
• Build line charts, bar charts, and a highlight table from the KPI view.
• Add interactivity so clicking a category updates all charts simultaneously.

Connect Tableau Desktop to Snowflake

Tableau needs your Snowflake account URL to establish a live connection. You can find this URL inside Snowsight.

• Switch back to Snowsight in your browser.
• Click your username in the bottom-left corner to open the user menu.
• Select Connect a tool to Snowflake to open the Account Details dialog.
• Copy the value in the Account/Server URL field. It looks like orgname-account_name.snowflakecomputing.com.

What is the Account/Server URL?

This is your Snowflake account's unique address. Tableau uses it to route queries to your specific Snowflake instance. The format combines your organization name and account name into a single hostname.

• Open Tableau Desktop on your computer.
• On the start page, click More... under To a Server in the Connect pane on the left.
• Select Snowflake from the list.

A connection dialog appears asking for your Snowflake details.

• Paste your Account/Server URL into the Server field.
• Select Username and Password for Authentication.
• Enter the username and password you use to sign in to Snowsight.
• Click Sign In.

After signing in, Tableau shows dropdown fields for Warehouse, Database, and Schema.

• Select BI_PROJECT_WH from the Warehouse dropdown.
• Select RETAIL_ANALYTICS from the Database dropdown.
• Select CATEGORY_PERFORMANCE from the Schema dropdown.

You should now see your views and tables listed in the left panel.

• Drag CATEGORY_KPI_SUMMARY onto the canvas area in the center of the screen.
• Tableau displays a data preview showing columns like CATEGORY, WEEK, REVENUE, and PROFIT_MARGIN_PCT.

Not seeing your views in the list?

Make sure you selected CATEGORY_PERFORMANCE as the schema, not PUBLIC. Your views live in the CATEGORY_PERFORMANCE schema you created in Step 1.

If Tableau shows a connection error, double-check that your Server field contains only the hostname (no https:// prefix) and that your warehouse BI_PROJECT_WH is selected.

help me connect Tableau Desktop to my Snowflake account

Build the core visualizations

With the live connection established, you can now build three visualizations that together tell a complete category performance story.

• Click the Sheet 1 tab at the bottom of the Tableau window to open your first worksheet.

You will build a line chart showing weekly revenue by category.

• Drag WEEK from the data pane to the Columns shelf.
• Right-click WEEK on the Columns shelf and select Dimension (this ensures Tableau treats it as a discrete sequence, not an aggregated measure).
• Drag REVENUE to the Rows shelf.
• Drag CATEGORY to the Color mark on the Marks card.

You should see two colored lines trending across the weeks. One line represents Cereals and the other Soft Drinks.

• Double-click the sheet tab at the bottom and rename it to Weekly Revenue.

Why use Dimension instead of the default?

Tableau auto-detects numeric fields as measures and tries to aggregate them. Since WEEK is a week number (not a quantity to sum), treating it as a dimension tells Tableau to plot each week individually on the axis.

Now build a bar chart showing average profit margin by category.

• Click the new sheet icon (the tab with a plus sign) at the bottom to create a second worksheet.
• Drag CATEGORY to the Columns shelf.
• Drag PROFIT_MARGIN_PCT to the Rows shelf.
• Right-click PROFIT_MARGIN_PCT on the Rows shelf, hover over Measure, and select Average.

You should see two bars side by side, one taller than the other, showing each category's average margin.

• Rename this sheet to Profit Margin.

Finally, build a highlight table showing week-over-week revenue growth.

• Create a third worksheet using the new sheet icon.
• Drag WEEK to Columns and set it to Dimension (right-click, select Dimension).
• Drag CATEGORY to Rows.
• Drag REVENUE_GROWTH_PCT to the Color mark on the Marks card.
• Change the mark type from Automatic to Square using the dropdown at the top of the Marks card.
• Click the Color mark, then click Edit Colors.
• Choose a diverging palette (such as Red-Green Diverging) so positive growth shows green and negative growth shows red.
• Check the Stepped Color checkbox and set the center to 0.
• Click OK.

You should see a grid of colored squares where green indicates revenue growth and red indicates decline.

• Rename this sheet to Growth Heatmap.

Seeing mostly null values in the growth column?

The first week for each category will always be null because there is no prior week to compare against. This is expected from the LAG window function in your view. If most values are null, check that your CATEGORY_KPI_SUMMARY view loaded data correctly by switching back to Snowsight and running a quick count query.

help me troubleshoot null values in my Tableau highlight table

Add interactivity and the "so what"

Individual charts answer individual questions. A dashboard ties them together and lets a stakeholder explore by clicking, not by writing SQL.

• Click the New Dashboard icon at the bottom of the Tableau window (it looks like a grid with a plus sign).
• From the Sheets pane on the left, drag Weekly Revenue onto the top half of the dashboard canvas.
• Drag Profit Margin onto the bottom-left area.
• Drag Growth Heatmap onto the bottom-right area.

You should see all three visualizations arranged in a single dashboard view.

• Double-click the dashboard title at the top and change it to Category Performance Dashboard.

Now add a category filter so stakeholders can isolate one category at a time.

• Click on the Weekly Revenue chart in the dashboard to select it.
• Click the small dropdown arrow that appears in the top-right corner of the selected chart.
• Select Filters, then check CATEGORY.

A CATEGORY filter card appears on the dashboard. By default it only filters the one chart. You need it to filter all charts.

• Click the dropdown arrow on the CATEGORY filter card.
• Select Apply to Worksheets, then choose All Using This Data Source.

Now add a filter action so clicking directly on a category in any chart also filters the others.

• In the top menu, click Dashboard, then select Actions.
• Click Add Action, then select Filter.
• In the Source Sheets section, check all three worksheets.
• In the Target Sheets section, check all three worksheets.
• Set Run action on to Select.
• Click OK to close the action dialog, then OK again to close the Actions dialog.

What does a filter action do?

A filter action lets the dashboard respond to clicks. When a stakeholder clicks the Cereals line in the revenue chart, every other chart on the dashboard instantly filters to show only Cereals data. This is the interactive exploration that separates a real BI dashboard from a static report.

The last piece is the consultative "so what." A dashboard without interpretation is just pretty charts.

• From the top menu, click Dashboard, then Objects in the left pane (switch from Sheets to Objects).
• Drag a Text object onto the dashboard below the line chart.
• In the text editor that appears, type an insight annotation. For example: Cereal margins declined 3% in weeks 120-140 while volume held steady, suggesting promotional pressure without demand lift.
• Click OK to place the annotation.

Why add a text annotation?

The annotation is the difference between a chart and an insight. BI stakeholders want you to tell them what the data means, not just show them the data. This single sentence demonstrates the consultative thinking that job postings describe as delivering the "so what."

• Save your workbook by pressing Cmd+S (macOS) or Ctrl+S (Windows).
• Name it Category_Performance_Dashboard and save it to your Desktop.

Before you test the interactivity, predict what you expect to happen when you click a single category value in the filter. Will all three charts change, or just one?

• In the CATEGORY filter card on your dashboard, click Cereals to select only that category.

All three charts update simultaneously to show only Cereals data. The line chart shows a single line, the bar chart shows one bar, and the heatmap shows only the Cereals row.

• Click (All) in the filter card to reset and show both categories again.

Only one chart updating when you filter?

This means the filter is not applied to all worksheets. Click the dropdown arrow on the CATEGORY filter card and confirm that Apply to Worksheets is set to All Using This Data Source.

help me fix my Tableau dashboard filter so it applies to all charts

Your dashboard is live, interactive, and annotated with a real insight. You have built what a BI analyst delivers to stakeholders every week: a self-service exploration tool backed by structured analytical views.

Secret mission

Private Label vs. Branded Analysis

The job posting specifically calls out private label analysis. Can you add a segmentation layer that classifies products as private label or branded, recalculates all KPIs by brand type, and surfaces the margin differences in your Tableau dashboard? This extension proves you can dissect category performance at the brand-type level and recommend strategies that improve category profitability.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project uses a Snowflake warehouse that auto-suspends after 60 seconds of inactivity, so ongoing costs are near-zero (just storage for a few MB of data).

Resources you used:

• Snowflake database: RETAIL_ANALYTICS (contains all tables, views, and the internal stage).
• Snowflake warehouse: BI_PROJECT_WH (only consumes credits when running queries).
• Downloaded CSV files: upccer.csv, wcer.csv, upcsdr.csv, wsdr.csv (local files in your Downloads folder).
• Tableau workbook (.twb or .twbx file saved locally).

Keep everything running

No action needed. Choose this if you plan to continue building on this analysis or want to keep it as a portfolio piece.

• The BI_PROJECT_WH warehouse auto-suspends after 60 seconds of inactivity. You will not be charged credits while it is suspended.
• Storage for a few MB of retail data costs a negligible fraction of a credit per month.
• Your Tableau workbook remains on your local machine and incurs no cloud cost.

Pause - I'll come back to this later

The warehouse already auto-suspends after 60 seconds of inactivity, so there is no separate pause action needed for Snowflake.

• Close Tableau Desktop to free up local memory.
• Your Snowflake database, views, and data remain intact for when you return.
• When you come back, open a new SQL Worksheet in Snowsight and run USE WAREHOUSE BI_PROJECT_WH; to resume querying.

Delete - I don't want to use this again

Remove all project resources and start fresh. This is permanent and cannot be undone.

• Open a new SQL Worksheet in Snowsight.
• Drop the entire database (this removes all tables, views, the schema, and the internal stage) by running:

DROP DATABASE RETAIL_ANALYTICS;

• Drop the warehouse by running:

DROP WAREHOUSE BI_PROJECT_WH;

• Delete the downloaded CSV files from your local machine. On macOS or Linux, open your terminal and run:

rm ~/Downloads/upccer.csv ~/Downloads/wcer.csv ~/Downloads/upcsdr.csv ~/Downloads/wsdr.csv

• On Windows, open PowerShell and run:

Remove-Item ~\Downloads\upccer.csv, ~\Downloads\wcer.csv, ~\Downloads\upcsdr.csv, ~\Downloads\wsdr.csv

• Delete your Tableau workbook file from wherever you saved it (check your Documents folder or Desktop).
• Any Snowflake Worksheets you created in Snowsight can be deleted from the Worksheets tab by clicking the three-dot menu on each worksheet and selecting Delete.

Nice Work!

Nice Work!

You just built a complete business intelligence pipeline from raw retail data to interactive dashboard. You loaded real scanner data into Snowflake, transformed it with analytical SQL, and delivered stakeholder-ready insights through Tableau.

You've learned how to:

• Load real retail data into Snowflake using internal stages and COPY INTO, then write SQL that progresses from raw aggregations to structured analytical views with window functions.
• Build a live Tableau dashboard connected to Snowflake that visualizes category performance trends with filters, interactivity, and presentation-ready layouts.
• Deliver the "so what" by annotating insights that a stakeholder can act on, not just charts with numbers.
• Secret Mission: Segmented analysis by private label vs. branded to recommend category strategy.

Ready to quiz yourself?