Scenario 1: End-to-End E-Commerce Pipeline
Business Context: You have just been hired as the Lead Data Engineer for an upcoming Indian e-commerce startup in Hyderabad. The CEO wants a dashboard showing daily revenue grouped by the customer's state.
Your Objective: Build a complete end-to-end dbt pipeline from raw data to a final presentation-ready Fact table.
1. Setup the Database Environment
(Execute this in your Snowflake worksheet to prepare the raw data. Find your username by running SELECT CURRENT_USER();)
-- 1. Create a dedicated database for this scenario
CREATE DATABASE IF NOT EXISTS BEEMS_SCENARIO_1_ECOMMERCE;
USE DATABASE BEEMS_SCENARIO_1_ECOMMERCE;
CREATE SCHEMA IF NOT EXISTS RAW;
CREATE SCHEMA IF NOT EXISTS STAGING;
CREATE SCHEMA IF NOT EXISTS MARTS;
-- 2. Create the raw source tables
CREATE OR REPLACE TABLE RAW.app_users (
user_id INT,
first_name VARCHAR,
state_code VARCHAR
);
CREATE OR REPLACE TABLE RAW.app_orders (
order_id INT,
user_id INT,
order_status VARCHAR,
amount_inr NUMBER(10,2),
created_at TIMESTAMP_NTZ
);
-- 3. Insert mock data
INSERT INTO RAW.app_users VALUES
(101, 'Arjun', 'MH'),
(102, 'Priya', 'KA'),
(103, 'Rohan', 'DL');
INSERT INTO RAW.app_orders VALUES
(1, 101, 'COMPLETED', 1500.00, CURRENT_TIMESTAMP()),
(2, 102, 'COMPLETED', 2500.00, CURRENT_TIMESTAMP()),
(3, 101, 'CANCELLED', 500.00, CURRENT_TIMESTAMP()),
(4, 103, 'COMPLETED', 4000.00, CURRENT_TIMESTAMP());
2. Step 1: Define Sources and Staging Models
Create the foundational layer of your DAG. We need to define the sources and create lightweight staging views.
File 1: models/staging/sources.yml
version: 2
sources:
- name: ecommerce_app
database: BEEMS_SCENARIO_1_ECOMMERCE
schema: RAW
tables:
- name: app_users
- name: app_orders
File 2: models/staging/stg_users.sql
{{ config(materialized='view') }}
SELECT
user_id,
first_name as customer_name, -- Renaming for clarity
state_code
FROM {{ source('ecommerce_app', 'app_users') }}
File 3: models/staging/stg_orders.sql
{{ config(materialized='view') }}
SELECT
order_id,
user_id,
order_status,
amount_inr,
DATE(created_at) as order_date -- Casting to date
FROM {{ source('ecommerce_app', 'app_orders') }}
3. Step 2: Build the Fact Table (Marts)
Now, we join the staging models together to build the final, presentation-ready fact table that the CEO's dashboard will query.
File 4: models/marts/fct_daily_revenue_by_state.sql
{{ config(materialized='table') }}
WITH orders AS (
SELECT * FROM {{ ref('stg_orders') }}
-- Filter out cancelled orders!
WHERE order_status = 'COMPLETED'
),
users AS (
SELECT * FROM {{ ref('stg_users') }}
),
joined AS (
SELECT
o.order_date,
u.state_code,
SUM(o.amount_inr) as total_revenue
FROM orders o
JOIN users u ON o.user_id = u.user_id
GROUP BY 1, 2
)
SELECT * FROM joined
4. Step 3: Run and Test the Pipeline
Run the entire pipeline in your terminal:
# This will build stg_users, stg_orders, and fct_daily_revenue_by_state in order
dbt run
Let's verify the results in Snowflake:
SELECT * FROM BEEMS_SCENARIO_1_ECOMMERCE.MARTS.fct_daily_revenue_by_state;
Expected Result:
| ORDER_DATE | STATE_CODE | TOTAL_REVENUE |
|---|---|---|
| (Today) | MH | 1500.00 |
| (Today) | KA | 2500.00 |
| (Today) | DL | 4000.00 |
Notice that the 500 INR cancelled order for Arjun (MH) was correctly filtered out!
Summary
You have successfully built an End-to-End dbt Pipeline.
- Sources read the raw data.
- Staging Models cleaned and renamed the columns.
- Marts applied business logic (
WHERE order_status = 'COMPLETED') and aggregated the data. - dbt run automatically figured out the DAG order and built the tables in Snowflake.