Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 38 ยท Projects and Practice

Project: A Retail Analytics Pipeline

Build a retail analytics pipeline step by step: raw tables, cleaning, dimensions and facts, a change feed, KPI queries and a Snowflake deployment plan with roles and cost controls.

This project joins the ideas of the course into one pipeline: land raw data, clean it, model it for analysis, apply incremental updates, answer business questions and plan the Snowflake deployment (roles, scheduling, cost). You will build it on the sample online shop data. The SQL is portable, so everything in the runnable blocks executes in your browser; the Snowflake-specific parts are listed as read-only code at the end.

The goal

The shop wants a daily dashboard: revenue, orders, best categories, top customers and a view of how many customers order more than once. Data arrives as orders and order lines from an application database.

flowchart LR
  A["Source tables<br/>orders, items, customers"] --> R["RAW<br/>copies + load metadata"]
  R --> C["CLEAN<br/>validated rows"]
  C --> D["MART<br/>fact_sales + dimensions"]
  D --> K["KPI queries<br/>dashboard"]
  R -. "failed rows" .-> Q["QUARANTINE"]

The same shape you would deploy in Snowflake with schemas RAW, CLEAN and MART.

Step 1: RAW and CLEAN

SQL Land and validate the data ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Cancelled orders are excluded from the clean layer here. In a real project you would keep them in a separate "cancelled" fact rather than lose the information.

Step 2: the star schema

A fact table holds measurable events (one row per order line). Dimensions describe the context: product, customer, date.

SQL Facts and dimensions ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Having keys in the fact table lets every KPI query read the small fact plus the dimension it needs.

Step 3: KPI queries

SQL The dashboard ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Each query is a few lines because the model did the work. In Snowflake, windowed queries like the running total use the same syntax.

Step 4: incremental updates

New orders must reach the fact table without reloading everything. Model it with a watermark: remember the highest item loaded and load only newer rows.

SQL Incremental load with a watermark ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The count grows by exactly one. In Snowflake a stream replaces the watermark and also catches updates and deletes.

Step 5: the Snowflake deployment plan (read-only)

SQL
-- Environment and layers
CREATE DATABASE retail;
CREATE SCHEMA retail.raw;  CREATE SCHEMA retail.clean;  CREATE SCHEMA retail.mart;

-- Compute with guardrails
CREATE WAREHOUSE etl_wh   WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60  AUTO_RESUME = TRUE;
CREATE WAREHOUSE bi_wh    WAREHOUSE_SIZE = 'SMALL'  AUTO_SUSPEND = 180 AUTO_RESUME = TRUE;
CREATE RESOURCE MONITOR retail_rm WITH CREDIT_QUOTA = 200 FREQUENCY = MONTHLY
  TRIGGERS ON 80 PERCENT DO NOTIFY ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE etl_wh SET RESOURCE_MONITOR = retail_rm;

-- Roles: access roles per layer, functional roles for people
CREATE ROLE retail_mart_read;  CREATE ROLE retail_engineer;  CREATE ROLE retail_analyst;
GRANT USAGE ON DATABASE retail TO ROLE retail_mart_read;
GRANT USAGE ON SCHEMA retail.mart TO ROLE retail_mart_read;
GRANT SELECT ON ALL TABLES IN SCHEMA retail.mart TO ROLE retail_mart_read;
GRANT SELECT ON FUTURE TABLES IN SCHEMA retail.mart TO ROLE retail_mart_read;
GRANT ROLE retail_mart_read TO ROLE retail_analyst;

-- Pipeline: a stream and a task for the fact table
CREATE STREAM retail.raw.items_stream ON TABLE retail.raw.order_items;
CREATE TASK retail.mart.load_fact_sales
  WAREHOUSE = etl_wh SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('retail.raw.items_stream')
AS MERGE INTO retail.mart.fact_sales f USING retail.raw.items_stream s ON f.item_id = s.id
   WHEN NOT MATCHED AND s.METADATA$ACTION = 'INSERT' THEN INSERT (item_id, revenue)
   VALUES (s.id, s.quantity * s.unit_price);
ALTER TASK retail.mart.load_fact_sales RESUME;

-- Protect personal data
CREATE MASKING POLICY retail.mart.email_mask AS (v STRING) RETURNS STRING ->
  CASE WHEN IS_ROLE_IN_SESSION('RETAIL_ENGINEER') THEN v ELSE '*** MASKED ***' END;

For the dashboard, a dynamic table with TARGET_LAG = '15 minutes' over the fact and dimensions would replace the stream and task if you prefer a fully declarative design (see dynamic tables).

Extension ideas

  • Add a dim_date table and join facts through it.
  • Add a data-quality table with daily counts and failures.
  • Track price history with slowly changing dimensions (valid-from and valid-to columns).
  • Estimate the monthly credits of the pipeline using the credit rules.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a fact table hold in a star schema?

    Facts record events and measures; dimensions describe their context.
  2. 2. Why use a watermark or stream for loads?

    Incremental loads save compute and time.
  3. 3. Which grants make future tables in the mart readable automatically?

    Future grants apply to objects created later.
  4. 4. What is the role of the RESOURCE MONITOR in the plan?

    It is a guardrail against runaway warehouse spending.

Summary

  • Layered pipeline: RAW, CLEAN, MART, with quarantine for bad rows.
  • A star schema (fact plus dimensions) makes KPI queries short.
  • Incremental loads use watermarks or streams.
  • Deploy with separate warehouses, access roles, masking, a task or dynamic table and a resource monitor.

Next: Practice: SQL challenges