ETL / ELT
Intermediate 14 min read ETL / ELT

dbt for Transformation

Transformations as code your whole team can review

dbt turns transformation into versioned SQL models with dependency resolution, tests, docs, and re-runnable builds. ref() replaces copy-pasted joins and makes the warehouse a codebase.

dbtmodelrefstagingtestsdocsversion control
Prerequisites: ETL / ELT Pipeline Design Facts and Measures
Before we start

What you will learn

  • Structure a dbt project: sources, staging, marts
  • Use ref() for dependency-driven builds
  • Add tests and docs so models are provable and discoverable
Introduction

What is this?

dbt is a command-line tool (and the holistic concept) that treats SQL transformations as a project: models are SELECT statements in files, ref() declares dependencies, and dbt builds them in order, testing and documenting as it goes. It turned ELT's 'T' into reviewable, versionable software.

Motivation

Why do you need it?

  • Dependency garbage (copy-pasted CTEs) is the #1 modeling rework; ref() kills it.
  • Model + test + doc in one repo is the modern governance story.
  • It is the lingua franca concept behind nearly every analytics-engineering job posting.
Think about it

Mental model

Imagine

A cookbook where each recipe references other recipes by name

Think of

dbt ref() + models

You write 'use the tomato sauce recipe' instead of pasting the sauce inline. When the sauce changes, every dish updates; the kitchen (dbt build) bakes dishes in dependency order and the taste tests (tests) run automatically.

Mechanics

How it works

  • Sources: definitions of raw tables (FROM {{ source('shop','orders') }}).
  • Models: SELECTs with ref('stg_orders') resolved to real dependencies by dbt.
  • Layers: staging (clean+type), intermediates (business joins), marts (facts/dims), materialized as views/tables/incremental.
  • Tests: uniqueness, not_null, relationships, custom SQL — run with dbt test in CI.
  • Docs & lineage: dbt docs generates a dependency graph the whole team reads.

A typical dbt project lineage.

raw.orders
stg_orders
int_orders_items
fct_order_lines
tests + docs
Reference

Key commands

dbt run --select +fct_order_lines

Build the model and all its upstream dependencies.

dbt test

Run every declared test across the project.

dbt docs generate && dbt docs serve

Render lineage and model docs for the team.

dbt build

Run seed/run/test in the right dependency order with failure isolation.

Hands-on

Examples

Model + test in one file set

Model + test in one file set
-- models/marts/fct_order_lines.sql
SELECT
  o.order_id,
  o.customer_id,
  o.sold_at,
  sum(oi.qty)          AS items_qty,
  sum(oi.gross_amount) AS gross_amount
FROM {{ ref('stg_orders') }}   o
JOIN {{ ref('stg_order_items') }} oi USING (order_id)
GROUP BY 1, 2, 3
 
-- schema.yml
version: 2
models:
  - name: fct_order_lines
    columns:
      - name: order_id
        tests: [unique, not_null]

ref() resolves the true deps; schema.yml declares the tests that run in CI.

Real world

In production

  • A 300-model project builds marts nightly; CI runs dbt test on every PR.
  • A team lands raw partitions in the lake and dbt coupons incremental models over them.
  • Source definitions feed 'freshness' checks so a quiet source table alerts before it bites.
Guidelines

Best practices

  • Staging models should only clean/type; business logic lives in intermediates/marts.
  • Every mart gets a unique/not_null test on its grain keys.
  • Materialize incrementally only when the grain demands it; tables/views are simpler.
  • Review dbt artifacts (manifest, run_results) in CI to enforce lineage constraints.
Watch out

Common mistakes

  • Pasting {{ ref() }} output into other models instead of ref()-ing it (the dependency lie).
  • Building business logic in staging, making the 'clean' layer untrustworthy.
  • No tests, so a schema drift sails silently into marts.
When things break

Debugging tips

  • A model that 'changed but ran upstream' usually means a ref() mismatch or an un-invalidated cache.
  • Use dbt ls --resource-type model and --select 'model:' filters to trace lineage issues.
  • When a stage test fails, diff the raw source vs stg SELECT to find the transformation bug.
Interviews

Interview checkpoint

Practice

Hands-on lab

Build a Mini-dbt: ref() Resolution, Order, and Tests
40 min · intermediate
In progress
Objectives
  • Write SQL models with ref() placeholders
  • Resolve refs and execute models in dependency order on DuckDB
  • Run simple unique/not_null tests against the output

Build a Mini-dbt: ref() Resolution, Order, and Tests

40 min · intermediate

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

ref() models declare dependencies so dbt builds models in the correct order.

2

dbt is best described as…

Go deeper

Official documentation

dbt docs
docs
dbt models
docs
dbt tests
docs
dbt models with ref()
docs