dbt Tutorial: Transformations in the Modern Data Stack

dbt is a tool for the transformation step of a data pipeline. Its documentation calls it the T in ELT: you write SQL select statements, called models, and dbt turns them into tables and views in your warehouse (dbt docs: introduction). The SQL is ordinary. What dbt adds is everything around it: the order models run in, tests, documentation, and a project structure that lives in version control like application code. The fastest way to understand it is to run a complete project.

A whole project in one script

A dbt project is a folder of files. The script below writes a minimal one, a project file, a connection profile, two models and a file of tests, downloads January 2024 New York taxi data (NYC TLC: trip record data), runs dbt build, and prints what dbt did. It uses DuckDB as the warehouse, so nothing but pip install dbt-duckdb is needed; with a different profile the same models run on Snowflake, BigQuery or Databricks.

import json
import subprocess
from collections import Counter
import urllib.request
from pathlib import Path

project = Path("taxi_dbt")
files = {
    "dbt_project.yml": """
name: taxi
profile: taxi
model-paths: [models]
seed-paths: [seeds]
flags: {send_anonymous_usage_stats: false}
""",
    "profiles.yml": """
taxi:
  target: dev
  outputs:
    dev: {type: duckdb, path: taxi.duckdb, threads: 1}
""",
    "models/stg_trips.sql": """
select tpep_pickup_datetime as pickup_at, PULocationID as pickup_zone_id,
       payment_type, passenger_count, fare_amount as fare
from read_parquet('yellow_tripdata_2024-01.parquet')
where tpep_pickup_datetime >= '2024-01-01' and tpep_pickup_datetime < '2024-02-01' and fare_amount >= 0
""",
    "models/fct_daily_zone.sql": """
{{ config(materialized='table') }}
select cast(t.pickup_at as date) as day, z.Borough as borough, z.Zone as zone,
       count(*) as trips, round(sum(t.fare), 2) as fares
from {{ ref('stg_trips') }} t
join {{ ref('taxi_zone_lookup') }} z on z.LocationID = t.pickup_zone_id
group by all
""",
    "models/schema.yml": """
seeds:
  - name: taxi_zone_lookup
    columns:
      - name: LocationID
        data_tests: [unique, not_null]
models:
  - name: stg_trips
    columns:
      - name: pickup_zone_id
        data_tests:
          - not_null
          - relationships:
              arguments: {to: ref('taxi_zone_lookup'), field: LocationID}
      - name: payment_type
        data_tests:
          - accepted_values:
              arguments: {values: [0, 1, 2, 3, 4, 5, 6]}
      - name: passenger_count
        data_tests:
          - not_null: {config: {severity: warn}}
  - name: fct_daily_zone
    columns:
      - name: trips
        data_tests: [not_null]
""",
}
for name, text in files.items():
    path = project / name
    path.parent.mkdir(parents=True, exist_ok=True)
    path.write_text(text.lstrip())
base = "https://d37ci6vzurychx.cloudfront.net"
for path, url in {project / "yellow_tripdata_2024-01.parquet": f"{base}/trip-data/yellow_tripdata_2024-01.parquet",
                  project / "seeds" / "taxi_zone_lookup.csv": f"{base}/misc/taxi_zone_lookup.csv"}.items():
    if not path.exists():
        path.parent.mkdir(parents=True, exist_ok=True)
        urllib.request.urlretrieve(url, path)

run = subprocess.run(["dbt", "build", "--profiles-dir", "."], cwd=project, capture_output=True, text=True)
print("dbt build exit code:", run.returncode)
results = json.loads((project / "target" / "run_results.json").read_text())["results"]
for r in results:
    kind, name = r["unique_id"].split(".")[0], r["unique_id"].split(".")[2]
    extra = f" ({r['failures']} failing rows)" if r.get("failures") else ""
    print(f"{kind:<6}{r['status']:<8}{name[:58]}{extra}")
print(dict(Counter(r["status"] for r in results)))

import duckdb
con = duckdb.connect(str(project / "taxi.duckdb"), read_only=True)
print(con.sql("select borough, sum(trips)::bigint as trips from fct_daily_zone group by 1 order by 2 desc limit 3").fetchall())

dbt build exit code: 0
model success stg_trips
seed  success taxi_zone_lookup
test  pass    accepted_values_stg_trips_payment_type__0__1__2__3__4__5__
test  warn    not_null_stg_trips_passenger_count (138096 failing rows)
test  pass    not_null_stg_trips_pickup_zone_id
test  pass    not_null_taxi_zone_lookup_LocationID
test  pass    relationships_stg_trips_pickup_zone_id__LocationID__ref_ta
test  pass    unique_taxi_zone_lookup_LocationID
model success fct_daily_zone
test  pass    not_null_fct_daily_zone_trips
{'success': 3, 'pass': 6, 'warn': 1}
[('Manhattan', 2616416), ('Queens', 266767), ('Brooklyn', 24930)]

What the files do

The project and profile files name the project and say where to build: here a local DuckDB file. In a team, the profile holds each developer's own schema and the production target, so the same code builds in different places.

A seed is a small CSV file kept in the project and loaded as a table, here the taxi zone lookup. Seeds suit reference data that changes rarely.

Models are select statements. stg_trips reads the raw file, renames columns and applies basic rules. fct_daily_zone joins it to the zones and aggregates. Neither contains a CREATE TABLE; dbt writes that.

ref is the important part. The second model does not name the first table directly. It writes ref around the model's name, and dbt both substitutes the right schema for the environment and uses those references to build the dependency graph, so models are deployed in the correct order (dbt docs: ref). In the output, stg_trips and the seed are built first, their tests run, and only then is fct_daily_zone built. Had a staging test failed with an error, dbt would have skipped the model that depends on it.

Tests

The schema file attaches tests to columns. dbt's built-in generic tests are unique, not_null, accepted_values and relationships; each compiles to a query that selects failing rows and passes when none are returned (dbt docs: data tests). The run shows six passing: the zone ID is unique and never null, every trip's pickup zone exists in the lookup, payment types are known codes, and so on.

One test is set to warning severity on purpose. Passenger count is null in 138,096 staged trips, so the test reports a warn with the number of failing rows, and the build continues. With the default severity it would be an error and stop downstream models. Choosing between the two for each test is how a project separates "must never happen" from "worth knowing about"; data quality testing covers thresholds in more depth.

Materializations

A materialization is how a model is stored. The staging model uses the default, a view; the fact model is configured as a table. dbt's built-in options (dbt docs: materializations):

Changing one configuration line switches a model between them, with no change to its SQL.

The mart built by this run can be queried like any table: its top three boroughs by trips are Manhattan, Queens and Brooklyn, the last line of the output. Because the fact model is a table, that query reads stored results; because the staging model is a view, it costs nothing to keep and is recomputed whenever the table is rebuilt.

The commands you will use

• dbt build runs seeds, models and tests together in dependency order.
• dbt run and dbt test do the two halves separately.
• dbt build --select fct_daily_zone+ builds one model and everything downstream of it.
• dbt docs generate produces browsable documentation with the lineage graph.

Habits of maintainable projects

• Layer models: staging models that clean one source each, then intermediate and mart models; the same shape as medallion architecture.
• Only staging models read raw sources. Everything else uses ref.
• Test keys on every model: unique and not_null on primary keys, relationships on foreign keys.
• Run it in CI, building and testing changed models on every pull request.
• Schedule it with an orchestrator alongside ingestion; the Airflow tutorial covers that.
• Agree what upstream teams must deliver; data contracts formalise it.

Why transforming inside the warehouse became standard practice is covered in ETL vs ELT.

Data transformation pipelines, ETL and ELT concepts, and data quality and validation are part of the Data Engineering course.

Sources

• What is dbt? (dbt documentation) — dbt Labs
• About ref function (dbt documentation) — dbt Labs
• Add data tests to your DAG (dbt documentation) — dbt Labs
• Materializations (dbt documentation) — dbt Labs
• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission