dbt (Data Build Tool)
dbt is the standard tool for the T in ELT: transforming raw data already loaded into a warehouse into clean, tested, documented tables that analytics can trust. You write SELECT statements; dbt turns them into a dependency-ordered graph of tables and views, runs them in your warehouse, tests the results, and generates docs.
Its real innovation was cultural: dbt brought software engineering practice to SQL — version control, code review, CI, tests, environments, documentation — and in doing so created the "analytics engineer" role. If your company has a Snowflake/BigQuery warehouse and dashboards people trust, there's a good chance dbt sits in between.
TL;DR
- A dbt model is a
SELECTstatement in a.sqlfile; dbt materializes it as a view or table in the warehouse — you never write DDL. ref()builds the DAG: models reference each other with{{ ref('stg_orders') }}, and dbt derives the run order (and lineage graph) from those references.- The conventional layering: sources → staging (clean/rename) → intermediate → marts (business-facing).
- Tests are one line of YAML:
unique,not_null,accepted_values,relationships— plus custom SQL tests. Bad data fails the build, not the boardroom. - Incremental models process only new rows — the difference between a 5-minute and a 5-hour nightly run on large event tables.
- dbt transforms inside the warehouse — it moves no data. Loading is someone else's job (Fivetran/Airbyte); orchestration is Airflow/Dagster or dbt Cloud.
Quick Example
A model, its upstream reference, and its tests:
Core Concepts
ref(), Sources, and the DAG
{{ ref('model') }} does three jobs: resolves to the right schema per environment (your dev schema vs production), declares the dependency edge dbt uses to order execution, and powers the lineage graph in generated docs. {{ source('shop', 'raw_orders') }} does the same for raw loaded tables — with freshness checks (dbt source freshness) that alert when the loader has silently stalled.
Never hardcode analytics.prod.stg_orders in a model — you'd break environment isolation and the DAG both.
Layered Project Structure
The discipline that keeps 500-model projects navigable: staging models touch raw sources (nothing else does), business logic lives in one intermediate/mart place (not copy-pasted into five dashboards), and BI tools read only marts.
Materializations
How a model becomes a warehouse object:
The incremental pattern that saves the nightly run:
First run builds the table; later runs process only rows newer than the current max — with unique_key deduplicating late arrivals via merge. (Late-arriving data beyond your lookback is the classic incremental bug; periodic --full-refresh is the blunt-but-effective safety net.)
Tests, Docs, and Jinja
- Data tests compile to
SELECTstatements that must return zero rows — the four generics cover most needs; packages (dbt-utils, dbt-expectations) and custom SQL cover the rest. Unit tests (dbt 1.8+) check model logic against fixture inputs. dbt docs generateproduces a browsable site: every model, column description, test, and an interactive lineage DAG — documentation that regenerates from code instead of rotting in a wiki.- Jinja + macros make SQL programmable: loops for repetitive case statements, environment conditionals, and shared logic in macros. Used sparingly, a superpower; used heavily, unreadable — prefer plain SQL until repetition genuinely hurts.
dbt in the Stack
dbt issues SQL; the warehouse does the compute. Scheduling comes from dbt Cloud (managed scheduler + IDE + CI) or an orchestrator — Airflow with Cosmos renders each model as a task; Dagster has first-class dbt integration. CI on pull requests (dbt build against a temp schema, often with --defer and state comparison to build only changed models) is what makes warehouse changes reviewable like application code.
dbt Core vs dbt Cloud vs alternatives: Core is the open-source CLI (bring your own scheduler); Cloud adds the managed platform. SQLMesh is the notable challenger (column-level lineage, virtual environments, built-in unit testing); the concepts transfer directly.
Common Mistakes
Business Logic in the BI Layer
Revenue defined one way in a dashboard, another way in a Looker formula, a third in an ad-hoc query. The point of marts is one tested definition — push logic down into dbt and make dashboards thin.
Skipping Staging
Marts that select directly from raw tables couple every downstream model to source schema quirks. When the source renames a column, you fix it in one staging model — or in forty marts.
select * Through the DAG
A new PII column added upstream silently propagates to every mart and export. Explicit column lists in staging are the cheap firewall (dbt model contracts enforce this formally).
Testing Nothing — or Everything
Zero tests means dashboards break before builds do. But 40 tests per model with warn severity that everyone ignores is the same outcome with extra steps. Test what would actually page you: primary keys, accepted statuses, critical relationships, source freshness — with error severity.
One 400-Line Model
If a model has six CTEs doing distinct jobs, they want to be intermediate models — individually testable, reusable, and visible in lineage.
FAQ
Is dbt just templated SQL? Why the fuss?
Mechanically, close — Jinja + SQL + a DAG runner. The fuss is what it enabled: transformations in git with review and CI, tests as a norm, generated lineage/docs, environments. It made warehouse code maintainable by teams instead of heroic individuals. The workflow, not the templating, is the product.
Do I need dbt if I just have PostgreSQL?
dbt works fine on Postgres (and DuckDB) — if you're maintaining a pile of interdependent views/CREATE TABLE scripts for analytics, dbt's structure and testing pay off at any scale. If you have three queries, you don't need it yet.
dbt vs Airflow — which do I need?
Different layers: dbt defines what transformations run (and their internal order); Airflow/Dagster orchestrates when, alongside the loaders before it and consumers after. Small stacks run fine on dbt Cloud's scheduler alone; bigger pipelines embed dbt inside the orchestrator.
Can dbt do Python?
Yes — Python models run as DataFrames on warehouses that support them (Snowpark, BigQuery DataFrames, Databricks) for the ML-ish steps SQL handles poorly. The center of gravity remains SQL; reach for Python models sparingly.
What does "analytics engineer" actually mean?
The role dbt crystallized: someone who applies software engineering practice (modeling, testing, review, CI) to the transformation layer — between data engineers (who build ingestion/platform) and analysts (who answer questions). In small teams, one person wears all three hats and dbt is where they overlap.
Related Topics
- Data Warehousing — The platform dbt transforms within
- SQL — The language of every model
- Apache Airflow — Orchestrating dbt in larger pipelines
- Data Engineering — The ingestion layer upstream
- Web Analytics — A common source feeding the models
- Product Analytics — A common consumer of the marts