dbt Intro

Lesson 08 · Data Engineering with Python · ~15 min read

1. Why dbt happened

Before dbt (circa 2017), every analytics team had the same setup: a scripts/ folder full of .sql files, a wiki page listing the order to run them in, and a cron job firing them off in sequence. Nobody knew which model fed which dashboard. Nobody tested anything. Adding a new model meant grepping every other model to see what referenced the source table.

dbt’s insight was that SQL-pipeline tooling could borrow exactly the patterns software engineering had figured out twenty years earlier: dependency graphs (just like a build system), unit-test-style assertions (any SQL that should return zero rows is a test), and version-controlled source files. The result is the dominant SQL-pipeline framework as of 2026: every analytics team has either adopted dbt or built an in-house clone of it.

dbt Labs is the company; dbt Core is the open-source CLI; dbt Cloud is the hosted IDE and scheduler. This course uses dbt Core throughout — the entire workflow runs on a laptop against DuckDB with zero cloud dependency. In Lesson 11 the same project re-points at Snowflake or BigQuery with a five-line profiles.yml change.

2. Models — one .sql file = one table or view

The atomic unit in dbt is a model: a single .sql file in your models/ directory containing a single SELECT statement. dbt wraps that SELECT in CREATE OR REPLACE TABLE / CREATE OR REPLACE VIEW / INSERT INTO based on the model’s materialization (covered in section 5). Three conventions matter:

• One model = one output object. Don’t write models that produce multiple tables; split them.
• The file’s stem becomes the table’s name. models/staging/stg_events.sql becomes a table or view named stg_events. Some teams change this with {{ config(alias='...') }}, but the default is enough for most projects.
• Models go into layers. The convention popularised by dbt Labs: staging/ (one-to-one with sources, light cleaning), intermediate/ (joins and reshapes), marts/ (business-ready tables consumed downstream). The lab follows this layering.

A staging model for the GitHub events dataset looks like this:

-- models/staging/stg_events.sql
SELECT
  type,
  actor,
  repo,
  created_at,
  date AS event_date
FROM {{ source('raw', 'events_raw') }}

The {{ }} delimiters are Jinja — dbt renders the template before sending SQL to the warehouse. source('raw', 'events_raw') references an external table declared in sources.yml; dbt resolves it to the right schema and table name in both dev and production.

3. ref() — the dependency primitive

{{ ref('model_name') }} is the Jinja function that turns a model name into the right table identifier and records a dependency edge for the lineage graph. Three things it does simultaneously:

1. Resolves the schema. Your dev environment writes to dev_analytics.stg_events; prod writes to prod_analytics.stg_events. dbt picks the right schema from your profiles.yml, not from hard-coded strings. The same .sql file runs unchanged in both environments.
2. Builds the DAG. When dbt parses your project, every ref('x') adds an edge current model → x. dbt run then walks the DAG in topological order, so upstream models are always built before the models that depend on them.
3. Catches typos at parse time. A ref('stg_evnets') (typo) fails immediately with "Model 'stg_evnets' not found." Hard-coded table names fail at runtime — usually in production, at 3 AM.

The companion to ref() is source(): {{ source('raw', 'events_raw') }} references an external table that dbt didn’t create (raw data loaded by L01–L06). Sources are declared in sources.yml alongside their tests. The rule: every model either ref()s another model or source()s an external table — no bare table names anywhere in the project.

4. Tests — cheap, declarative, run on every build

dbt ships four built-in schema tests, declared in YAML next to the model they guard:

• unique — the column has no duplicate values.
• not_null — the column has no NULL values.
• accepted_values: [a, b, c] — the column only contains values from an allowed list. Useful for event types, status codes, and other bounded enumerations.
• relationships: {to: ref('parent'), field: id} — every value in the child column exists in the parent’s field. The foreign-key check your warehouse may not enforce.

Beyond schema tests, dbt supports singular tests: any .sql file in tests/ that should return zero rows. "Does any row have n_events <= 0?" — write SELECT * FROM mart_daily_event_counts WHERE n_events <= 0 in tests/no_negative_event_counts.sql; if it returns anything, the test fails. No framework, no assertion library — just SQL.

Tests run as part of dbt build, after the model they test is created, before downstream models that depend on it. A failing test aborts the build for that branch of the DAG, so bad data cannot propagate to the mart layer.

5. Materializations — how dbt builds the model

A model’s materialization controls the SQL dbt generates around your SELECT. Four built-ins:

• view — CREATE OR REPLACE VIEW. The SELECT runs every time someone queries the view. Cheap to “build” (no storage), expensive to query if the underlying SELECT is heavy. The default; appropriate for thin staging models.
• table — CREATE OR REPLACE TABLE. The SELECT runs once at build time; the result is stored. Expensive to build, cheap to query. Appropriate for marts that downstream dashboards hit many times per day.
• incremental — full rebuild on first run; subsequent runs INSERT INTO ... WHERE created_at > MAX(created_at). Required for tables too large to rebuild from scratch nightly. More complex — you must handle late-arriving rows, schema changes, and unique_key for upserts.
• ephemeral — not actually materialised; inlined as a CTE in any downstream model. Useful for reusable SQL that shouldn’t pollute the warehouse with extra objects.

The standard recommendation: start everything as view, promote heavy marts to table when query latency matters, and promote to incremental only when full-rebuild time becomes a real problem. Premature incremental is the primary source of dbt project complexity.

6. The build command

dbt build is the headline command: parses the project, builds models in DAG order, runs every test after the model it tests, and exits non-zero if anything fails. The other commands:

• dbt run — builds models only; skips tests.
• dbt test — runs tests only; skips models.
• dbt compile — parses and renders Jinja, writes the resulting SQL to target/compiled/, but executes nothing. Invaluable for debugging what SQL the template will actually send to the warehouse.
• dbt docs generate && dbt docs serve — builds the static documentation site with the lineage graph.

The selector syntax is dbt’s killer ergonomic feature: dbt build --select stg_events+ runs stg_events and everything downstream. dbt build --select +mart_daily_event_counts runs everything upstream of the mart. Once you know +, you stop running full project builds during development.

7. dbt fits into the ELT story

dbt is the T in ELT (extract → load → transform). L01–L06 of this course built the EL: Python extractors and Airflow scheduling that land raw GitHub events into Parquet files partitioned by date (and optionally loaded into DuckDB or a cloud warehouse). dbt picks up from there: it reads those raw tables as sources, builds staging models that clean and rename columns, intermediate models that join and reshape, and mart models that ship to BI.

The warehouse (DuckDB locally, Snowflake / BigQuery / Redshift in production) executes everything — dbt itself is just a CLI on your laptop or CI runner that compiles SQL and submits it. This is what makes dbt portable: the same dbt_project.yml runs unchanged against DuckDB (free, offline) and Snowflake (paid, cloud); only the adapter section in profiles.yml changes.

Module 4 of this course exploits exactly that property. The lab project lives on DuckDB through Lessons 08–10, then re-points to a cloud warehouse in Lesson 11 with a five-line config change — the models, tests, and documentation carry over without modification.

References

• dbt docs — docs.getdbt.com. The canonical reference; the Best Practices guide and the Materializations page in particular are required reading for anyone running dbt in production.
• dbt-duckdb adapter — github.com/duckdb/dbt-duckdb. The DuckDB adapter used by this lab; lets the entire course run offline on a laptop.
• dbt Labs — Modular Data Modeling — the staging / intermediate / marts layer convention used throughout this course. docs.getdbt.com/best-practices.
• The Analytics Engineering Guide by Claire Carroll (dbt Labs) — the design rationale behind dbt and why analytics engineering emerged as a discipline distinct from data engineering.
• Lesson 09 of this course — DuckDB locally as the development warehouse the dbt project runs against; deeper exploration of read_parquet, in-process joins, and query performance.
• Lesson 11 of this course — re-pointing the same dbt project at a cloud warehouse with a five-line profiles.yml change; zero changes to models or tests.