Building dbt data marts and snapshot models

Case Study: Building E-Commerce Data Models with dbt

Susan Sun

Freelance Data Scientist

Introducing dbt data mart models

Data marts are clean and accessible data models at the end of the pipeline.

Use cases

  • Feature stores for the data science team
  • Aggregated KPIs for the finance team
  • Latency metric for the engineering team
  • Reduces repetition by storing the SQL as code
Case Study: Building E-Commerce Data Models with dbt

Looker e-commerce data marts

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

Case Study: Building E-Commerce Data Models with dbt

Looker e-commerce data marts

A directed acyclic graph diagram showing all dbt sources and models and their relationships to each other, Sources are shown in green. Models and marts are shown in blue.

Case Study: Building E-Commerce Data Models with dbt

Building step-by-step

Step 1: Refine SQL logic outside of dbt, then replace references with dbt syntax.

A screenshot of a sample SQL logic file with various CTEs.

Step 2: Build out data tests and docs

A screenshot of a sample dbt models YAML file showcasing table and column descriptions for a dbt model

Step 3: Test the build! (e.g. dbt build)

Case Study: Building E-Commerce Data Models with dbt

Introducing dbt snapshot models

Five order status:

Processing, Shipped, Complete, Cancelled, and Returned

Data sample:

A preview of the orders data file containing two rows of data and 9 rows. Column names are order id, user id, status, gender, created at, returned at, shipped at, delivered at, and num of items.

Case Study: Building E-Commerce Data Models with dbt

Orders: snapshot status change

  • Create file 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 %}
  • Run a specific snapshot model
dbt snapshot -s orders_snapshot.sql
  • Run all snapshot models
dbt snapshot
  • Run all models (including snapshots)
dbt build
1 https://docs.getdbt.com/docs/build/snapshots
Case Study: Building E-Commerce Data Models with dbt

Let's practice!

Case Study: Building E-Commerce Data Models with dbt

Preparing Video For Download...