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

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

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.

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)

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.

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

¹ https://docs.getdbt.com/docs/build/snapshots

Let's practice!

Case Study: Building E-Commerce Data Models with dbt