dbt (data build tool)

dbt turns a folder of SELECT statements into a tested, documented, dependency-ordered transformation pipeline that runs inside your warehouse. This page covers models and refs, materialisations, incremental models, tests and documentation, with a small runnable project.

1. What dbt Is

dbt (data build tool) is an open-source framework for the transformation step of ELT. Data is first loaded into a warehouse or lakehouse; dbt then turns it into clean, tested, documented tables using nothing but SELECT statements. Each model is a SQL file. dbt wraps it in the right DDL (CREATE VIEW, CREATE TABLE AS, or a MERGE), figures out the order to run models from their dependencies, and executes them in the warehouse. dbt itself moves no data and has no compute engine: the warehouse does all the work.

It is available as dbt Core (open source, command line) and as a managed cloud product from dbt Labs. Adapters exist for Snowflake, BigQuery, Redshift, Databricks, Postgres, DuckDB and many others.

2. Why It Matters

Before tools like dbt, warehouse transformations were often stored procedures or SQL scripts run in a hand-maintained order, without tests or version control. dbt brought software-engineering practice to analytics SQL: code in Git with pull-request review, modular models that reference each other instead of copy-pasted subqueries, automated tests, environments (dev, CI, prod), and documentation generated from the code. It also lets analysts who know SQL own production transformations, which is why the role of "analytics engineer" grew up around it.

3. How It Works: Models, ref and Materialisations

Three concepts carry most of dbt:

• ref() and source(). Instead of hard-coding analytics.stg_orders, a model writes {{ ref('stg_orders') }}. dbt replaces it with the correct schema for the current environment (your dev schema, or production) and records an edge in the dependency graph. source() does the same for raw tables loaded by other tools, declared in YAML. The graph above is built entirely from these calls, which is why dbt always runs models in a valid order and can draw lineage.
• Materialisations. A config setting decides how a model is persisted: view (default, always fresh, recomputed on read), table (rebuilt on every run), incremental (only new or changed rows are processed), and ephemeral (inlined as a CTE). Snapshots record slowly changing history (SCD type 2).
• Jinja templating. SQL files are Jinja templates, so you can use variables, loops and reusable macros. Packages such as dbt_utils provide common macros.

The conventional project layout follows the layered design: staging models (one per source table, rename and cast), intermediate models (joins and business logic), and marts (facts and dimensions for consumers).

4. Incremental Models

Rebuilding a billion-row table every hour is wasteful. An incremental model wraps a filter in {% if is_incremental() %} so that, on runs after the first, only rows newer than the latest already in the target are selected. With a unique_key, dbt merges them so updated rows replace old versions rather than duplicating them, which makes reruns idempotent. Strategies vary by adapter: merge, delete+insert, insert_overwrite (by partition) and append. A --full-refresh rebuilds from scratch when logic changes. Late-arriving data needs care: filtering on max(updated_at) misses records with older timestamps that land late, so many teams subtract a lookback window.

Snapshots and seeds. Two further building blocks round out a project. A snapshot watches a mutable source table and, on each run, records changed rows with dbt_valid_from and dbt_valid_to columns, giving you type 2 history even when the source system only keeps the current value. Snapshots should read from sources, not from transformed models, so that history captures what actually arrived. A seed is a small CSV file checked into the repository, such as a country-code mapping, that dbt loads as a table; seeds are for static reference data, not for loading real datasets.

Environments. Each developer works in a personal schema configured in their profile, so running models never touches production tables. Production jobs run the same code against the production target. Because every reference goes through ref(), the switch is purely configuration, and a pull request can be built and tested in an isolated CI schema before merging.

5. Worked Example Project

Two models, one staging and one incremental fact:

-- models/staging/stg_orders.sql
select
    id                        as order_id,
    customer_id,
    cast(total as numeric(10,2)) as order_total,
    status,
    updated_at
from {{ source('shop', 'orders') }}

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

select o.order_id, o.customer_id, c.country, o.order_total, o.status, o.updated_at
from {{ ref('stg_orders') }} o
join {{ ref('stg_customers') }} c using (customer_id)
{% if is_incremental() %}
  where o.updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Sources and tests are declared in YAML. Each test compiles to a query that returns failing rows; zero rows means pass:

# models/marts/schema.yml
version: 2
sources:
  - name: shop
    schema: raw
    tables:
      - name: orders
      - name: customers
models:
  - name: fct_orders
    description: "One row per order, latest status."
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - relationships: {to: ref('stg_customers'), field: customer_id}
      - name: status
        tests:
          - accepted_values: {values: ['placed', 'shipped', 'returned']}

And the everyday commands:

dbt run   --select fct_orders+        # build fct_orders and everything downstream
dbt test  --select fct_orders         # run its tests
dbt build                             # run + test + seed + snapshot in DAG order
dbt run   --select fct_orders --full-refresh   # rebuild an incremental model from scratch
dbt docs generate && dbt docs serve   # browse catalog and lineage

The + graph operator selects a model plus its descendants, which is how you rebuild everything affected by a change. dbt build interleaves tests with model builds, so a failing test stops bad data from flowing into downstream models. In CI, a common pattern is "slim CI": build and test only models changed in the pull request and their children, comparing against the production manifest.

6. Comparison with Alternatives

dbt is not an orchestrator for ingestion or ML; it typically runs as one task in Airflow, Dagster or dbt Cloud, after loaders such as Fivetran or Airbyte.

7. Pitfalls

• Hard-coded table names. Bypassing ref() breaks lineage, environment isolation and run order.
• Everything as a table. Materialising every model multiplies cost; use views for light staging models and incremental for large facts.
• Incremental without unique_key. Reruns append duplicates.
• Tests only on primary keys. Add relationship, accepted-values and freshness checks where errors actually occur.
• Giant models. 800-line SQL files defeat modularity; split into intermediate models.
• Schema drift in incremental models. New source columns are ignored unless on_schema_change is configured or a full refresh is run.

8. Related Topics

• Layered Data Design — staging, intermediate and marts
• Idempotent Pipelines and Backfills — why unique_key and MERGE matter
• ETL Pipeline — the broader pipeline dbt sits in
• Apache Airflow — scheduling dbt runs
• Star Schema — the shape of typical marts
• Delta Live Tables — an alternative on Databricks
• Data Pipelines — section home

9. Further Reading

• dbt documentation at docs.getdbt.com: models, materializations, incremental models, tests, snapshots.
• dbt Labs, How we structure our dbt projects (best-practice guide).
• Tristan Handy (2016), The dbt Viewpoint.
• Reis and Housley, Fundamentals of Data Engineering (O'Reilly, 2022), transformation chapter.