Capstone Project: Sales Data Pipeline
Welcome to your second Capstone!
In this scenario, you are the Data Architect for a retail brand based in Mumbai. The business is struggling because their data is siloed:
- Sales data comes from the ERP system.
- Customer data comes from a SaaS CRM (Salesforce).
Your job is to build a unified Declarative Pipeline using Dynamic Tables. You will ingest the raw silos, clean the data, join it into a dimensional model, and serve a real-time dashboard aggregation.
Project Scope
- Multi-source ingestion (Simulated)
- Data cleansing (The Silver Layer)
- Dimensional modeling (Star Schema)
- Declarative orchestration with Dynamic Tables
Step 1: Database Setup and Raw Ingestion (Bronze Layer)
First, we set up the database and the raw landing tables representing the data silos.
Run the following code in your Snowflake worksheet.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_RETAIL_DB;
USE DATABASE BEEMS_RETAIL_DB;
CREATE SCHEMA IF NOT EXISTS BRONZE;
CREATE SCHEMA IF NOT EXISTS SILVER;
CREATE SCHEMA IF NOT EXISTS GOLD;
-- 1. ERP Sales Data
CREATE OR REPLACE TABLE BRONZE.ERP_SALES (
ORDER_ID STRING,
CRM_CUSTOMER_ID STRING,
STORE_CITY STRING,
REVENUE_INR NUMBER,
ORDER_DATE TIMESTAMP
);
-- 2. CRM Customer Data
CREATE OR REPLACE TABLE BRONZE.CRM_CUSTOMERS (
CUSTOMER_ID STRING,
FIRST_NAME STRING,
LAST_NAME STRING,
EMAIL STRING,
IS_ACTIVE BOOLEAN
);
-- Insert Mock Data
INSERT INTO BRONZE.ERP_SALES VALUES
('ORD-001', 'C-100', 'mumbai', 4500, CURRENT_TIMESTAMP()),
('ORD-002', 'C-101', 'Delhi', 12000, CURRENT_TIMESTAMP()),
('ORD-003', 'C-100', 'MUMBAI', 2300, CURRENT_TIMESTAMP()); -- Notice the inconsistent casing
INSERT INTO BRONZE.CRM_CUSTOMERS VALUES
('C-100', 'Aarav', 'Patel', 'aarav@example.com', TRUE),
('C-101', 'Neha', 'Gupta', 'neha@example.com', TRUE);
Step 2: Data Cleansing (Silver Layer)
Data from source systems is rarely clean. Notice how "mumbai", "Delhi", and "MUMBAI" are formatted inconsistently in the ERP data.
We will use Dynamic Tables to build our Silver layer. The Silver layer will standardize the formatting and act as the single source of truth for downstream models. We will set the TARGET_LAG to DOWNSTREAM so it only refreshes when the Gold layer asks for it.
(To ensure you have the correct privileges to create Dynamic Tables, verify your role with SELECT CURRENT_ROLE();)
-- Clean the Sales Data
CREATE OR REPLACE DYNAMIC TABLE SILVER.CLEAN_SALES
TARGET_LAG = 'DOWNSTREAM'
WAREHOUSE = COMPUTE_WH
AS
SELECT
ORDER_ID,
CRM_CUSTOMER_ID,
-- Standardize city names
INITCAP(STORE_CITY) AS STORE_CITY,
REVENUE_INR,
ORDER_DATE
FROM BRONZE.ERP_SALES;
-- Clean the Customer Data (e.g., filtering out inactive customers)
CREATE OR REPLACE DYNAMIC TABLE SILVER.CLEAN_CUSTOMERS
TARGET_LAG = 'DOWNSTREAM'
WAREHOUSE = COMPUTE_WH
AS
SELECT
CUSTOMER_ID,
FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME,
LOWER(EMAIL) AS EMAIL
FROM BRONZE.CRM_CUSTOMERS
WHERE IS_ACTIVE = TRUE;
Step 3: Dimensional Modeling (Gold Layer)
Now we join the clean, siloed data into a unified Star Schema model. This table represents the "Fact" table joined with the "Dimension" table.
Because this table drives the business dashboard, we give it a specific lag: 10 minutes. This tells Snowflake to automatically orchestrate the entire pipeline (including the Silver tables) to ensure this data is never more than 10 minutes stale.
CREATE OR REPLACE DYNAMIC TABLE GOLD.FACT_SALES_ENRICHED
TARGET_LAG = '10 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT
s.ORDER_ID,
s.ORDER_DATE,
s.STORE_CITY,
s.REVENUE_INR,
c.FULL_NAME AS CUSTOMER_NAME,
c.EMAIL AS CUSTOMER_EMAIL
FROM SILVER.CLEAN_SALES s
LEFT JOIN SILVER.CLEAN_CUSTOMERS c
ON s.CRM_CUSTOMER_ID = c.CUSTOMER_ID;
Step 4: Dashboard Aggregation (Gold Layer)
Finally, the Regional Manager in Mumbai doesn't want to see individual rows; they want a summary of revenue by city. We create a final aggregate Dynamic Table on top of our fact table.
CREATE OR REPLACE DYNAMIC TABLE GOLD.CITY_REVENUE_SUMMARY
TARGET_LAG = '10 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT
STORE_CITY,
SUM(REVENUE_INR) AS TOTAL_REVENUE,
COUNT(ORDER_ID) AS TOTAL_ORDERS
FROM GOLD.FACT_SALES_ENRICHED
GROUP BY STORE_CITY;
Step 5: Verification
Because Dynamic Tables refresh asynchronously in the background, you cannot immediately query them. You must either wait for the background task to run, or force a manual refresh.
-- Force a manual refresh for testing
ALTER DYNAMIC TABLE GOLD.FACT_SALES_ENRICHED REFRESH;
ALTER DYNAMIC TABLE GOLD.CITY_REVENUE_SUMMARY REFRESH;
-- Query the final dashboard table
SELECT * FROM GOLD.CITY_REVENUE_SUMMARY ORDER BY TOTAL_REVENUE DESC;
You should see Mumbai combined into a single row with 6,800 INR, proving the Silver layer successfully cleaned the data.
Summary
Congratulations! You have built a unified multi-source pipeline using Declarative programming.
- Bronze: You ingested raw, siloed data.
- Silver: You built
DOWNSTREAMDynamic Tables to clean and standardize the data. - Gold: You joined the silos together and built aggregations that automatically refresh every 10 minutes, completely removing the need for manual Task scheduling or Stream management.