Case Study: Building E-Commerce Data Models with dbt
Susan Sun
Freelance Data Scientist
Data marts are clean and accessible data models at the end of the pipeline.
Use cases
Customers
Answers the questions:
Who are our customers?
What are their purchase behaviors?
Data grain:
One row per customer
Sample columns:
Customer name, amount customers spent
Products
Answers the questions:
What is our revenue, cost, and profit?
Data grain:
One row per product
Sample columns:
Product category, revenue, profit
Step 1: Refine SQL logic outside of dbt, then replace references with dbt syntax.
Step 2: Build out data tests and docs
Step 3: Test the build! (e.g. dbt build
)
Five order status:
Processing
, Shipped
, Complete
, Cancelled
, and Returned
Data sample:
orders_snapshot.sql
{% snapshot orders_snapshot %}
{{
config(target_schema='main',
unique_key='order_id',
strategy='timestamp',
updated_at='created_at')
}}
SELECT *
FROM
{{source('looker_ecommerce', 'orders')}}
{% endsnapshot %}
dbt snapshot -s orders_snapshot.sql
dbt snapshot
dbt build
Case Study: Building E-Commerce Data Models with dbt