Skip to main content

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_DATESTATE_CODETOTAL_REVENUE
(Today)MH1500.00
(Today)KA2500.00
(Today)DL4000.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.

  1. Sources read the raw data.
  2. Staging Models cleaned and renamed the columns.
  3. Marts applied business logic (WHERE order_status = 'COMPLETED') and aggregated the data.
  4. dbt run automatically figured out the DAG order and built the tables in Snowflake.