Get practice building a dbt project from the ground up. Apply your skills at loading different types of data into the dbt project and setting up a variety of staging dbt models. This chapter focuses on the E and L parts of the ELT process.

Introducing the dbt case study

Check dbt installation

Initialize the dbt project

Setting up the dbt project and loading data

version: 2

seeds:
  - name: looker__distribution_centers



# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'looker_ecommerce'
version: '1.0.0'

# This setting configures which "profile" dbt uses for this project.
profile: 'looker_ecommerce'

# These configurations specify where dbt should look for different types of files.
# The `model-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


looker_ecommerce:
  outputs:
    dev:
      type: duckdb
      path: /home/repl/dbt.duckdb
  target: dev


id,name,latitude,longitude
1,Memphis TN,35.1174,-89.9711
2,Chicago IL,41.8369,-87.6847
3,Houston TX,29.7604,-95.3698
4,Los Angeles CA,34.05,-118.25
5,New Orleans LA,29.95,-90.0667
6,Port Authority of New York/New Jersey NY/NJ,40.634,-73.7834
7,Philadelphia PA,39.95,-75.1667
8,Mobile AL,30.6944,-88.0431
9,Charleston SC,32.7833,-79.9333
10,Savannah GA,32.0167,-81.1167

Load distribution center as a dbt seed file

version: 2

models:
  - name: _____ # Fill in the model name
    description: _____ # Fill in an informative description of the model


SELECT *
FROM {{ ref('looker__distribution_centers') }}

version: 2

models:
  - name: stg_looker__distribution_centers # Fill in the model name
    description: Distribution center name and location # Fill in an informative description of the model


Create distribution center staging model

version: 2

models:
  - name: stg_looker__distribution_centers
    description: Distribution center name and location    

  - name: ____ # Fill in the staging model name 
    description: Users demographics and location information 

version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: ____ 	# Fill in the source name

SELECT *
FROM {{ source(____, ____) }} -- Fill in the source 

SELECT *
FROM {{ source('looker_ecommerce', 'users') }} -- Fill in the source 

version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: users 	# Fill in the source name

version: 2

models:
  - name: stg_looker__distribution_centers
    description: Distribution center name and location    

  - name: stg_looker__users # Fill in the staging model name 
    description: Users demographics and location information

Load and create user model via dbt source

version: 2

models:
  - name: stg_looker__distribution_centers
    description: Distribution center name and location    

  - name: stg_looker__users
    description: Users demographics and location information
    
  - name: stg_looker__events
    description: User event data

  - name: stg_looker__inventory_items
    description: Inventory items information

  - name: stg_looker__order_items
    description: Order information on the item level

  - name: stg_looker__orders
    description: Order information such as status and timestamps
        
    # Fill in for the staging model for products
    

SELECT *
FROM {{ source('looker_ecommerce', 'users') }}

version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: users
      - name: events
      - name: inventory_items
      - name: order_items
      - name: orders
      # Fill in for the dbt source for products
      ____

SELECT *
FROM {{ source('looker_ecommerce', 'events') }}

SELECT *
FROM {{ source('looker_ecommerce', 'inventory_items') }}

SELECT *
FROM {{ source('looker_ecommerce', 'order_items') }}

SELECT *
FROM {{ source('looker_ecommerce', 'orders') }}

SELECT *
FROM {{ source('looker_ecommerce', 'products') }}

version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: users
      - name: events
      - name: inventory_items
      - name: order_items
      - name: orders
      # Fill in for the dbt source for products
      - name: products

version: 2

models:
  - name: stg_looker__distribution_centers
    description: Distribution center name and location    

  - name: stg_looker__users
    description: Users demographics and location information
    
  - name: stg_looker__events
    description: User event data

  - name: stg_looker__inventory_items
    description: Inventory items information

  - name: stg_looker__order_items
    description: Order information on the item level

  - name: stg_looker__orders
    description: Order information such as status and timestamps
    
  # Fill in for the staging model for products
  - name: stg_looker__products
    description: Product information such as SKU and category


More practice with dbt source and staging models

version: 2

models:
  - name: stg_looker__distribution_centers
    description: Distribution center name and location    

  - name: stg_looker__users
    description: Users demographics and location information
    
  - name: stg_looker__events
    description: User event data

  - name: stg_looker__inventory_items
    description: Inventory items information

  - name: stg_looker__order_items
    description: Order information on the item level

  - name: stg_looker__orders
    description: Order information such as status and timestamps
    
  - name: stg_looker__products
    description: Product information such as SKU and category


SELECT *
FROM {{ source('looker__distribution_centers') }}

version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: users
      - name: events
      - name: inventory_items
      - name: order_items
      - name: orders
      - name: stg_looker__products

version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: users
      - name: events
      - name: inventory_items
      - name: order_items
      - name: orders
      - name: products

Debug common errors

Setting up dbt

Dive deep into the weeds of dbt data modeling. Build the data pipeline from preliminary staging models to the final data mart models for answering critical business needs. Along the way, get experience creating data tests to guardrail against data quality drift.

Creating dbt descriptions and tests

Add dbt column descriptions

Add dbt column tests

Building dbt data marts and snapshot models

Refresher on dbt model hierarchy

Create mart model for customers

Create mart model for products

Debugging common errors

Debugging more errors

Building dbt snapshot models

Building dbt models

Learn to enhance your dbt projects with Jinja by streamlining code, reducing redundancy, and improving maintainability. You'll practice using variables, loops, and macros to build more efficient transformation workflows.

Introducing Jinja with dbt

Warming up with dbt compile

Warming up with dbt compile 2

Using dbt compile to debug

Setting variables with dbt Jinja

Setting variables with dbt Jinja 2

Complex usage of jinja with dbt

Using Jinja for loops on repeated SQL 1

Using Jinja for loops on repeated SQL 2

Macros with Jinja

Trimming whitespaces with Jinja

Recap: dbt case study

Improving dbt with Jinja

Unlock the power of data transformation with our hands-on dbt (Data Build Tool) Case Study Course. Designed for early stage dbt users, this course focuses on practical, real-world exercises that give you multiple opportunities to build, transform, and refine your grasp of essential dbt commands. Through hands-on practice, you’ll gain confidence in every step of the process—starting from setting up your dbt environment to creating advanced models that solve critical business challenges. By working through interactive exercises, you'll build the experience you need to tackle real-life data ETL pipeline projects with confidence.

Transform raw data into clean, reliable models using dbt (Data Build Tool) - a modern, SQL-based transformation framework used by data teams around the world. This hands-on case study course is designed for early-stage learners who want to build real-world skills through guided, practical exercises. You'll set up your own dbt environment, model data at scale, and write reusable code using dbt's built-in features.<br><br><h2>Set Up Your Project and Explore the Data</h2>Get started by setting up a dbt project and working with a real E-Commerce dataset. You'll structure raw data, configure profiles, and debug syntax issues while gaining insight into the business context behind each transformation step.<br><br><h2>Build and Validate Models</h2>Learn to create scalable staging models and apply data quality checks to ensure your datasets are accurate and analysis-ready. You'll build a solid foundation for answering key business questions.<br><br><h2>Automate with Jinja</h2>Finish the course by learning how to use Jinja to write reusable, maintainable code. You'll use variables, control flow, and loops to follow the DRY (Don't Repeat Yourself) principle to streamline your dbt workflow.

Data Manipulation in SQL

Intermediate dbt

Learn how to transform raw data into clean, reliable models with dbt through hands-on, real-world exercises. 

Check dbt installation

Case Study: Building E-Commerce Data Models with dbt

Praktische interactieve oefening