Skip to content

dbt

dbt is a tool for building transformations as SQL SELECT statements in version-controlled files; it works out their order, runs them on a query engine, and tests and documents the results.

Before reading

Medallion layers: bronze, silver and gold · SQL query engines

Why it matters

Transformations written as scripts in a scheduler grow into a tangle: nobody knows what depends on what, changes are untested, and documentation drifts. dbt applies software practices to SQL: code review, tests, environments and generated documentation. It has become the most common way to build the clean and gold layers in SQL.

How it works

A model is one file containing one SELECT statement. dbt turns it into a table or view in the engine. Models refer to each other with ref('model_name') instead of table names. From these references dbt builds a graph of dependencies, runs models in the right order, and can run a model together with everything upstream or downstream of it. Raw tables loaded by other tools are declared as sources and referenced with source().

flowchart LR
  S[(source:<br/>raw_orders)] --> STG[stg_orders<br/>view: rename, cast,<br/>_cdc.offset to source_offset]
  STG --> CL[clean_orders<br/>incremental: merge]
  CL --> F[fct_daily_sales<br/>incremental: microbatch by day]
  T1{{tests: unique, not_null}} -.-> CL
  T2{{test: totals reconcile}} -.-> F

The materialization decides how a model is built. A view stores only the query. A table is rebuilt completely on each run. An incremental model processes only new or changed rows and merges or appends them into the existing table; it uses is_incremental() in the SQL to filter the new rows and a unique_key to merge on. The microbatch strategy, added in dbt 1.9, splits an incremental model into time batches, such as one per day, which can be reprocessed independently. Ephemeral models are inlined into the models that use them.

Tests are declared next to models. Generic tests check columns: unique, not_null, accepted_values, relationships to another model. Singular tests are SQL queries that should return no rows. Unit tests, added in dbt 1.8, run a model's logic on small fixed inputs and compare the output with the expected rows, before any real data is touched. dbt build runs models and their tests in graph order and stops downstream models when a test fails.

Configuration lives in YAML files: descriptions of models and columns, tests, owners, and model contracts that enforce column names and types. From this, dbt generates a documentation site with the lineage graph.

Packages share macros and models between projects, for example common test libraries. Macros, written in Jinja templating, generate repeated SQL. They remove repetition but make SQL harder to read when overused.

dbt runs on many engines through adapters, for example Trino, Spark, Snowflake, BigQuery, Postgres and DuckDB. The SQL in the models is still the engine's dialect. dbt v1, the original Python implementation of dbt Core, is open source under Apache 2.0. dbt v2, a rewrite in Rust that grew out of the Fusion engine, became generally available in September 2026. It ships as dbt-oss under Apache 2.0 and as dbt, which adds proprietary features and is free to use under dbt Labs' product licence. dbt Labs also sells a managed platform. dbt Labs and Fivetran completed their merger in June 2026 (checked October 2026).

Key terms

  • Model: one SELECT statement in a file, built into a table or view.
  • ref(): a reference to another model, from which dbt builds the dependency graph.
  • Source: a raw table loaded outside dbt, declared so models can reference it.
  • Materialization: how a model is built: view, table, incremental, ephemeral.
  • Incremental model: a model that processes only new or changed rows on each run.
  • Generic, singular and unit tests: column checks, custom queries, and logic tests on fixed inputs.
  • Adapter: the plugin that connects dbt to an engine.

Example

clean_orders as an incremental model, built hourly, that merges the latest change per order (ranking as in Deduplication and merge). raw_orders is written by the CDC pipeline, which keeps each change's operation and Kafka offset in a _cdc column (see Writing to Iceberg from Kafka). A staging model (stg_) is a thin view over one source that only renames and casts columns; stg_orders renames _cdc.offset to source_offset:

-- models/clean/clean_orders.sql
{{ config(materialized='incremental', incremental_strategy='merge',
          unique_key='order_id') }}

SELECT order_id, customer_id, status, amount, ordered_at, updated_at,
       shipping_address, customer_note, source_offset, _ingested_at
FROM (
  SELECT *, row_number() OVER (PARTITION BY order_id
                               ORDER BY source_offset DESC) AS rn
  FROM {{ ref('stg_orders') }}
  {% if is_incremental() %}
  -- overlap by an hour: rows can be committed after the last run
  WHERE _ingested_at > (SELECT max(_ingested_at) - INTERVAL '1' HOUR FROM {{ this }})
  {% endif %}
)
WHERE rn = 1

Its tests and documentation:

# models/clean/clean_orders.yml
models:
  - name: clean_orders
    description: One row per order, current state.
    columns:
      - name: order_id
        data_tests: [unique, not_null]
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: [placed, paid, shipped, cancelled]

dbt build --select +fct_daily_sales builds stg_orders, clean_orders and fct_daily_sales in order and runs their tests between steps. dbt's merge strategy overwrites matched rows without comparing source_offset. That is safe here because each run reads only newer ingestions, but reprocessing older raw rows needs a full refresh, not an incremental run. Deletes also need extra handling that this model leaves out, as described in Deduplication and merge.

Pitfalls

  • Incremental filters on event time. Late rows are missed; filter on ingestion time, with an overlap of at least the time between a row's _ingested_at and its commit to the raw table (minutes for a streaming sink).
  • Macros everywhere. SQL that only makes sense after rendering is hard to review.
  • Tests only on gold. Problems are cheaper to find in clean models, close to their cause.
  • One huge project with no ownership. Split by domain, or at least assign owners per folder.
  • Assuming adapters hide dialects. Models still need changes when the engine changes.

Learn more

  • dbt documentation, about dbt models: https://docs.getdbt.com/docs/build/models
  • dbt documentation, incremental models: https://docs.getdbt.com/docs/build/incremental-models
  • dbt documentation, unit tests: https://docs.getdbt.com/docs/build/unit-tests

Testing data pipelines · Data contracts · Backfill and replay · Naming conventions · Semantic layer