Transformation with dbt

Why dbt changed analytics engineering

For years, the "transform" step of a data pipeline was a graveyard of brittle practices: hand-run SQL scripts, stored procedures nobody could find, a 2,000-line query in a BI tool that one analyst understood. The logic worked, but it wasn't software — you couldn't review it, version it, test it, or trust that re-running it gave the same answer.

dbt (data build tool) made one deceptively simple bet: your transformations are just SELECT statements, so treat them like code. Put each one in a .sql file in a git repo. From there, everything good follows:

Models are just SELECT statements

A model is a single .sql file containing one SELECT. The file's name becomes the model's name. When you run dbt, it wraps your SELECT in CREATE VIEW or CREATE TABLE and materializes it in the warehouse. Here is a complete model — the whole file:

select
    rental_id,
    customer_id,
    gpu_model,
    hours_used,
    price_per_hour,
    hours_used * price_per_hour as revenue,
    rented_at::date            as rental_date
from {{ source('griddp', 'rentals') }}
where hours_used > 0

Two things in that file are pure dbt magic, and they're the whole point of the tool:

• {{ source('griddp', 'rentals') }} — instead of hard-coding the raw table name, you declare it as a source: data that arrives from outside dbt (here, the rentals table loaded from Postgres). dbt now knows this model depends on that raw input.
• {{ ref('other_model') }} — when one model reads from another, you reference it by name with ref() instead of writing the table name. This is how the DAG gets built.

Each model has a materialization — how dbt persists it. The two you'll use most:

Layering: staging → intermediate → marts

You don't dump all your SQL into one giant model. dbt projects follow a layered structure, and it maps cleanly onto the medallion layers you built in Chapter 04:

The three layers each have a job:

• Staging (≈ silver cleanup) — one model per source table. Rename cryptic columns, cast types, trim whitespace, drop obvious garbage. No joins, no business logic. The goal is a clean, predictable version of each raw input. Convention: prefix with stg_.
• Intermediate — optional "messy middle" models that join and reshape staging models into reusable building blocks. Prefix int_. Small projects often skip this layer entirely.
• Marts (= gold) — the business-facing tables analysts and dashboards actually query. Facts (fct_) and dimensions (dim_) from your dimensional modeling. This is where revenue, KPIs, and reportable numbers live.

Tests: catching bad data before anyone sees it

This is dbt's quietly revolutionary feature. You declare expectations about your data in a YAML file, and dbt turns each one into a query that should return zero rows. If a test query returns rows, the assumption is violated and the build fails. Four tests ship built-in:

You attach tests to models and columns in a schema.yml file that lives beside the models:

version: 2

models:
  - name: stg_rentals
    description: "One cleaned row per GPU rental."
    columns:
      - name: rental_id
        description: "Primary key for a rental."
        tests:
          - not_null
          - unique
      - name: gpu_model
        tests:
          - accepted_values:
              values: ['A100', 'H100', 'L40S', 'RTX4090']
      - name: customer_id
        tests:
          - not_null

Incremental models for large tables

Rebuilding a marts table from scratch every run is fine for thousands of rows. It is wasteful for billions. Incremental models let dbt process only the new rows since the last run — a direct payoff of the change-data-capture thinking from Chapter 06.

{{ config(materialized='incremental', unique_key='rental_id') }}

select *
from {{ ref('stg_rentals') }}

{% if is_incremental() %}
  -- only rows newer than what we've already loaded
  where rental_date > (select max(rental_date) from {{ this }})
{% endif %}

The mechanics: materialized='incremental' tells dbt to build the table once, then append on later runs. The {% if is_incremental() %} block only kicks in on those later runs (on the first build, and on a --full-refresh, it's skipped and the whole table is built). Inside it, {{ this }} refers to the model's own existing table, so max(rental_date) is the high-water mark of what you already have. You append only what's past it.

Docs & lineage — for free

Because dbt already knows every dependency (from your ref() and source() calls) and every description (from your schema.yml), it can generate a documentation site and a visual lineage graph with two commands:

dbt docs generate     # build docs from your models, refs, and descriptions
dbt docs serve        # open a local site with searchable docs + a DAG viewer

The result is a browsable site where every model lists its columns, descriptions, tests, and the exact SQL — plus an interactive lineage graph showing how data flows from sources through staging into marts. Click any node to see what feeds it and what it feeds. When an analyst asks "where does fct_revenue_by_gpu come from?", you point them at the graph instead of explaining it. The documentation can't drift from reality, because it's generated from the same code that runs.

Hands-on: build a dbt project on DuckDB

Time to make it real. We'll declare the rentals source, build one staging model and one marts model, add tests, and run the whole thing with a single command. Work inside your mini-griddp repo.

1. Scaffold the project. Create a folder and the four files we need. First, the project config:

name: 'griddp'
version: '1.0.0'
profile: 'griddp'

model-paths: ["models"]

models:
  griddp:
    staging:
      +materialized: view
    marts:
      +materialized: table

2. Tell dbt how to connect. The profiles.yml holds connection details. Point it at a local DuckDB file:

griddp:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: griddp.duckdb     # a local file, created on first run
      threads: 4

3. Seed some raw data. So the project has something to read, create the rentals table in DuckDB. (In your real pipeline this is loaded from Postgres; here we fake it so the chapter is self-contained.)

python -c "
import duckdb
con = duckdb.connect('griddp_dbt/griddp.duckdb')
con.execute('''
  CREATE OR REPLACE TABLE rentals AS
  SELECT * FROM (VALUES
    (1, 101, 'A100',  10, 2.50, TIMESTAMP '2026-06-01 09:00'),
    (2, 102, 'H100',   5, 4.00, TIMESTAMP '2026-06-01 11:00'),
    (3, 101, 'A100',   8, 2.50, TIMESTAMP '2026-06-02 09:00'),
    (4, 103, 'L40S',  20, 1.25, TIMESTAMP '2026-06-02 14:00'),
    (5, 102, 'H100',   3, 4.00, TIMESTAMP '2026-06-03 08:00')
  ) AS t(rental_id, customer_id, gpu_model, hours_used, price_per_hour, rented_at)
''')
print('rows:', con.execute('SELECT count(*) FROM rentals').fetchone()[0])
"

4. Declare the source. Tell dbt the raw rentals table exists:

version: 2

sources:
  - name: griddp
    description: "Raw tables loaded into the warehouse."
    tables:
      - name: rentals

5. The staging model — clean one row per rental:

select
    rental_id,
    customer_id,
    gpu_model,
    hours_used,
    price_per_hour,
    hours_used * price_per_hour as revenue,
    rented_at::date            as rental_date
from {{ source('griddp', 'rentals') }}
where hours_used > 0

6. The marts model — revenue rolled up per GPU, reading the staging model via ref():

select
    gpu_model,
    count(*)        as rental_count,
    sum(hours_used) as total_hours,
    sum(revenue)    as total_revenue
from {{ ref('stg_rentals') }}
group by gpu_model
order by total_revenue desc

7. Add tests in a schema file beside the models:

version: 2

models:
  - name: stg_rentals
    description: "One cleaned row per GPU rental, with revenue computed."
    columns:
      - name: rental_id
        description: "Primary key."
        tests:
          - not_null
          - unique
      - name: gpu_model
        tests:
          - accepted_values:
              values: ['A100', 'H100', 'L40S', 'RTX4090']

8. Build it. The dbt build command runs your models and their tests in DAG order — one command for the whole project:

cd griddp_dbt
dbt build --profiles-dir .

Found 2 models, 3 data tests, 1 source

1 of 5 START sql view model main.stg_rentals ............ [RUN]
1 of 5 OK created sql view model main.stg_rentals ....... [OK in 0.04s]
2 of 5 START test accepted_values_stg_rentals_gpu_model . [RUN]
2 of 5 PASS accepted_values_stg_rentals_gpu_model ....... [PASS in 0.02s]
3 of 5 START test not_null_stg_rentals_rental_id ........ [RUN]
3 of 5 PASS not_null_stg_rentals_rental_id .............. [PASS in 0.02s]
4 of 5 START test unique_stg_rentals_rental_id .......... [RUN]
4 of 5 PASS unique_stg_rentals_rental_id ................ [PASS in 0.02s]
5 of 5 START sql table model main.fct_revenue_by_gpu .... [RUN]
5 of 5 OK created sql table model main.fct_revenue_by_gpu [OK in 0.05s]

Finished running 1 view model, 3 data tests, 1 table model in 0.31s

Completed successfully

Done. PASS=5 WARN=0 ERROR=0 SKIP=0 TOTAL=5

You now have a real dbt project: a declared source, a layered staging→marts DAG, tests that gate the build, and a reproducible dbt build. Commit griddp_dbt/ to mini-griddp — this is a centerpiece of your portfolio.

✓ Check yourself

• What does ref() do that hard-coding a table name doesn't — and why does dbt need it?
• Which medallion layer does a stg_ model correspond to? A fct_ model?
• What does a dbt test actually run, and what makes it "pass"?
• When would you reach for an incremental materialization instead of table?

version: 2

models:
  - name: fct_revenue_by_gpu
    description: "Revenue and usage rolled up per GPU model."
    columns:
      - name: gpu_model
        tests:
          - not_null
          - unique

select
    customer_id,
    count(*)     as rental_count,
    sum(revenue) as total_revenue
from {{ ref('stg_rentals') }}
group by customer_id
order by total_revenue desc

dbt build --profiles-dir .

Next

Your transformations are clean, tested, and version-controlled — but where do the tables physically live? Next we give them a home that combines the cheapness of a data lake with the reliability of a warehouse. → The Lakehouse