Recap: dbt case study

Case Study: Building E-Commerce Data Models with dbt

Susan Sun

Freelance Data Scientist

Chapter 1: Setting up dbt

Set up:
- Check installtion: dbt --versions, which dbt
- Initialize dbt: dbt init
Load data:
- Load dbt seed files (e.g. distribution center): dbt seed
- Load dbt source files. E.g. {{ source('looker_ecommerce', 'orders') }}
Review dbt subcommands: dbt run, dbt test, dbt build
Review dbt project file and folder structure

Chapter 2: Building dbt models

Build dbt staging, mart, and snapshot models
Add table/column documentation and dbt data tests

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.

Chapter 3: Jinja with dbt

Reduce repeated code using Jinja expressions: set, loop, macros
Use dbt compile and whitespace management for development

{%- set order_statuses = ['Shipped', 'Complete', 'Processing'] -%}

SELECT 
    user_id,
    {%- for order_status in order_statuses %}
        COUNT(DISTINCT CASE WHEN status = '{{ order_status }}' THEN order_id END) 
        AS num_orders_{{ order_status }}
    {%- endfor %}
FROM {{ ref('stg_looker__orders') }}
GROUP BY 1

Congratulations!

Case Study: Building E-Commerce Data Models with dbt