dbt

Overview

dbt is a tool that turns SQL select statements into tested, versioned and documented tables and views inside a data warehouse. You write plain SQL files. dbt works out the order in which to run them, creates the objects, runs checks on the results and builds a documentation site with lineage. It is the "T" in ELT: the data is already loaded, and dbt transforms it where it sits.

The tool gave rise to a job title, the analytics engineer, who treats SQL like software. Models live in a git repository, changes go through review, tests run on every build and documentation is generated from the code. For anyone building clean tables for reports or for machine learning, dbt is a practical way to make a transformation layer that people can trust. It works with warehouses and lakehouse engines such as Snowflake, Databricks, Trino and ClickHouse through adapters.

What it is

dbt stands for data build tool. It is an open source command line tool, with a commercial hosted offering around it. The core runs on your machine or in a scheduler, compiles your project into SQL and sends that SQL to the warehouse. dbt does not store or move data itself. All computation happens in the warehouse.

What a dbt project contains

• Models: SQL files with a single select statement. Each becomes a table or view.
• Sources: declarations of the raw tables that dbt reads but does not create.
• Tests: checks on data, such as "this column is never null".
• Seeds: small CSV files loaded as tables, handy for lookup lists.
• Snapshots: records of how a mutable table changes over time.
• Macros: reusable code written with a templating language.
• Docs: descriptions in YAML that become a documentation website.
• Exposures: declarations of downstream uses such as dashboards or models.
• Configuration: a project file and a profile file for connections.

The templating language

dbt uses Jinja, a text templating language, on top of SQL. Jinja adds loops, conditions and functions. The most important function is ref(). It refers to another model by name instead of by hard-coded table path.

When dbt sees ref('orders'), it learns that this model depends on orders. It also swaps in the correct schema for the current environment. The same code runs in development, testing and production without edits.

Everyday analogy

Think of a kitchen. Raw ingredients arrive in the pantry. That is the loaded data. Recipes describe how to turn ingredients into dishes, and some dishes are ingredients for others. Each model is a recipe. ref() says "use the sauce from recipe B". dbt is the head chef who reads all the recipes, decides the cooking order and tastes each dish before it leaves the kitchen. The tests are the taste checks.

Where it came from

dbt appeared in the mid 2010s, created by a small consultancy that was helping startups build analytics. They kept writing the same scaffolding: scripts to run SQL in order, copy results and check them. They turned that scaffolding into a reusable tool and released it as open source.

Two trends made it successful. First, cloud warehouses became fast and cheap enough to do heavy transformation after loading. This flipped the older ETL pattern, where data was changed before loading, into ELT. Second, analysts already knew SQL. A tool that let them apply software practices to the language they used every day spread quickly.

Over the years the project added incremental models, snapshots, packages, a documentation site, a semantic layer and, in later versions, model contracts, versions and unit tests for models. The surrounding community built packages for common tasks and adapters for many engines.

Release details change often, so consult the official documentation for the current feature list.

The shift from ETL to ELT

• ETL: extract, transform, then load. Transformation happens in a separate engine before data lands.
• ELT: extract, load, then transform. Raw data lands first and the warehouse does the work.

ELT keeps raw data around, so you can re-run logic when requirements change. dbt is the standard tool for the last step. Pipelines that load raw data with Kafka Connect or batch jobs often finish with dbt models.

How it works

This section follows one dbt build from start to finish.

Step 1: Read the project

dbt parses every file in the project. It reads model SQL, YAML property files, macros and configuration. The result is a manifest, which is a complete in-memory description of all nodes and their settings.

Step 2: Build the dependency graph

Each ref() and source() call creates an edge. The edges form a directed acyclic graph, often shortened to DAG. Acyclic means no loops: model A cannot depend on B if B depends on A.

The DAG is the heart of dbt. It tells the tool what to run first, what can run in parallel and what is affected when something changes.

Step 3: Compile

dbt renders Jinja and produces pure SQL for each model. It replaces ref() with the real table name for the target environment. The compiled files can be inspected in the target folder, which is great for debugging.

Step 4: Wrap the select in DDL

You only write a select. dbt wraps it in the statement needed for the chosen materialization.

• view: create view as select .... Cheap to build, computed when queried.
• table: create table as select .... Rebuilt in full each run.
• incremental: only new or changed rows are inserted or merged.
• ephemeral: no object is created. The SQL is inlined into models that use it.
• materialized view: the engine keeps the result fresh, where supported.

Step 5: Run in order, in parallel

dbt executes models in dependency order and uses several threads for independent branches. If a model fails, models that depend on it are skipped, while other branches continue.

Step 6: Test

Tests are queries that return the rows that violate a rule. If the query returns zero rows, the test passes. dbt ships four generic tests.

• unique: no duplicate values.
• not_null: no missing values.
• accepted_values: only listed values appear.
• relationships: every value exists in another table, like a foreign key.

You can also write singular tests as a SQL file, or custom generic tests as macros. Packages add many more, such as range checks and row count comparisons.

Step 7: Document

Descriptions in YAML plus the DAG are rendered as a static website. You can browse models, columns, tests and lineage. The docs are always in sync with the code because they are generated from it.

Step 8: Select what to run

dbt has a flexible selection syntax.

dbt build --select stg_orders+          # a model and everything downstream
dbt build --select +fct_sales           # a model and everything upstream
dbt build --select tag:finance          # by tag
dbt build --select state:modified+      # only what changed, plus children

Selecting changed models only is the base of slim continuous integration: test just what a pull request touches.

Step 9: Environments

A profile file defines targets, such as dev and prod. Developers build into personal schemas. A scheduler builds into production schemas. The same code runs in both because ref() resolves names per target.

Key concepts and terms

A worked example

This example builds a small order analytics layer. The raw tables live in a fake schema called raw_shop.

Project layout

shop_analytics/
  dbt_project.yml
  models/
    staging/
      _sources.yml
      stg_orders.sql
      stg_customers.sql
      _staging.yml
    marts/
      fct_orders.sql
      dim_customers.sql
      _marts.yml
  seeds/
    country_codes.csv
  snapshots/
  macros/
  tests/

Declare sources

# models/staging/_sources.yml
version: 2
sources:
  - name: shop
    schema: raw_shop
    tables:
      - name: orders
        loaded_at_field: _loaded_at
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}
      - name: customers

The freshness block lets dbt source freshness warn when loading is late.

A staging model

-- models/staging/stg_orders.sql
{{ config(materialized='view') }}

select
    id                                  as order_id,
    customer_id,
    cast(ordered_at as timestamp)       as ordered_at,
    lower(status)                       as status,
    cast(total_amount as numeric(12,2)) as order_total
from {{ source('shop', 'orders') }}
where id is not null

Staging models rename, cast and clean. They do no joins and no business logic. One staging model per source table is a common convention.

A mart model

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

with orders as (
    select * from {{ ref('stg_orders') }}
    {% if is_incremental() %}
      where ordered_at > (select max(ordered_at) from {{ this }})
    {% endif %}
),

customers as (
    select * from {{ ref('stg_customers') }}
)

select
    o.order_id,
    o.customer_id,
    c.country_code,
    o.ordered_at,
    o.status,
    o.order_total,
    case when o.status = 'completed' then 1 else 0 end as is_completed
from orders o
left join customers c using (customer_id)

On the first run is_incremental() is false and dbt builds the full table. On later runs only newer orders are processed and merged by order_id.

Add tests and documentation

# models/marts/_marts.yml
version: 2
models:
  - name: fct_orders
    description: One row per order with customer attributes.
    config:
      contract:
        enforced: true
    columns:
      - name: order_id
        data_type: bigint
        description: Primary key.
        tests: [unique, not_null]
      - name: customer_id
        data_type: bigint
        tests:
          - relationships:
              to: ref('stg_customers')
              field: customer_id
      - name: status
        data_type: varchar
        tests:
          - accepted_values:
              values: ['completed', 'pending', 'cancelled', 'refunded']
      - name: order_total
        data_type: numeric

With the contract enforced, a build fails if the SQL returns different column names or types than declared. This guards downstream consumers.

A custom singular test

-- tests/assert_no_negative_totals.sql
select order_id, order_total
from {{ ref('fct_orders') }}
where order_total < 0

If any row comes back, the test fails.

Run it

dbt deps                    # install packages
dbt seed                    # load seed CSVs
dbt build                   # run models, tests, snapshots, seeds in DAG order
dbt docs generate
dbt docs serve

dbt build runs each model and then its tests before moving on to children. A failed test on a parent stops children from using bad data.

A snapshot

-- snapshots/customers_snapshot.sql
{% snapshot customers_snapshot %}
{{ config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='updated_at'
) }}
select * from {{ source('shop', 'customers') }}
{% endsnapshot %}

When a customer changes, dbt closes the old row by setting a valid-to timestamp and inserts a new row. You get a history without writing merge logic.

Numeric check

Suppose the raw table has 1,000 orders and 12 have a null id. The staging model filters them, so stg_orders has 988 rows. If 3 of those belong to a customer id missing from the customer table, the relationships test returns 3 rows and fails. That failure surfaces a real data problem before anyone reads a wrong report.

Strengths and limitations

Strengths

• Plain SQL. Anyone who knows SQL can contribute.
• Software practices. Version control, reviews, tests and continuous integration become normal for analytics.
• Dependency awareness. ref() builds the graph and a correct run order automatically.
• Built-in testing and docs. Quality checks and documentation live beside the code.
• Portable. Adapters let much of the code move between engines.
• Incremental builds. Large tables update cheaply.
• Lineage. The docs show where each column and table comes from.
• Big ecosystem. Many shared packages and integrations.

Limitations

• Batch oriented. dbt runs on a schedule. It is not a streaming engine. For live logic use Apache Flink.
• SQL only for most logic. Complex procedural code or ML training is outside its scope, although some adapters allow Python models.
• No data movement. It does not extract or load data. You need other tools for that.
• Warehouse cost. Careless full refreshes of big tables can be expensive.
• Jinja complexity. Heavy macro use makes code hard to read.
• Test coverage is up to you. Tests only catch what you write.
• Dialect differences. Portable code is not automatic when functions differ between engines.

How it compares with alternatives

dbt does not replace an orchestrator. It decides the order inside a project, while a scheduler decides when to run the project and what happens around it.

Role in tabular foundation models

Tabular foundation models learn from raw tables. They deal with rows and columns, mixed types such as numbers, categories, timestamps and text, missing values and sometimes columns without meaningful names. They are often pre-trained on large collections of synthetic or real tables, and many use in-context learning, where labelled example rows are given as context and no weights are updated. dbt is not a model, but it controls the quality and shape of the tables these models consume.

Producing the training and context tables

A model is only as good as its input table. dbt builds the "one row per entity" tables that a foundation model expects. A mart such as fct_orders with an is_completed label is a ready-made table for classification. Because the table is defined in version control, you can rebuild the exact same table for a past date and reproduce an experiment.

Mixed types and clear contracts

Model contracts in dbt declare column names and types. That matters for tabular models, which treat numeric, categorical and date columns differently. A column that silently flips from integer to string can change how a model reads it. Contracts and tests catch the flip at build time. See schema evolution for how table definitions change safely.

Missing values

dbt can make missing values explicit. A model can add indicator columns such as is_country_missing, apply simple defaults or leave nulls untouched. Tabular foundation models often learn their own imputation, so a good rule is not to over-fill. Keep the null, add the flag and let the model decide. For learned methods see imputation networks and masked cell modeling.

Column names and semantics

Some tabular models use column names as text hints. Others work with no names at all. dbt documentation gives each column a description, which can be exported and used to create clean names or text hints for models that benefit from them. If you test a model with and without names, dbt makes it easy to produce both versions of the table.

Leakage control

Feature tables must use only information available at prediction time. dbt helps by encoding the as-of logic in SQL and using snapshots to preserve history. A review of the SQL can check that no column uses future data. Tests can also assert that label columns do not appear in feature models.

Tests as data quality gates for in-context learning

When a model uses a few labelled rows as context, bad rows have an outsized effect. Tests such as not_null, accepted_values and range checks on the label column keep the context clean. Failing builds stop bad tables from reaching the model.

Medallion layers

dbt fits the medallion pattern well. Raw loads are the bronze layer, staging and cleaning models are the silver layer and feature-ready marts are the gold layer. See medallion architecture for the full picture. Foundation models usually read from gold tables.

Anomaly detection and sector classification

dbt can compute simple anomaly signals in SQL, such as z-scores, row count changes and null rates, and store them in tables. A foundation model can then score rows for anomaly detection, and dbt can store the scores for review. For semantic tasks like assigning an industry sector to a business record, model outputs can be written back to a table and joined into marts with ref().

Continual learning

Because models are rebuilt on a schedule, dbt provides a stream of fresh, versioned tables. Pipelines can compare each new build to the previous one, measure drift in column distributions and decide whether to refresh the context set or fine-tune. Snapshots keep the history needed for such comparisons.

Limits of the role

dbt does not train or serve models. It will not fix poor source data, only expose it. Use it to make tables reliable, then hand them to the model stack. See the architecture, research and benchmarks pages for the model side.

Practical guidance and best practices

Structure the project in layers

• Staging: one model per source table, light clean-up only.
• Intermediate: joins and reusable logic that are not final outputs.
• Marts: business-facing tables named by purpose, such as facts and dimensions.

Name models with prefixes like stg_, int_, fct_ and dim_ so their role is clear.

Test at the right places

• Put unique and not_null on every primary key.
• Test relationships between facts and dimensions.
• Use accepted_values for status-like columns.
• Test sources as well, so failures point at the load rather than your logic.
• Use severity levels, so noisy checks warn instead of block.

Prefer incremental models for large facts

Use a reliable unique_key and a safe filter. Plan a periodic full refresh to correct late-arriving data. Decide how to handle deleted rows, since a plain incremental run will not remove them.

Control cost

• Use views for light models and tables for heavy ones that are queried often.
• Avoid select star in marts. Name the columns.
• Use --select and state comparison in development.
• Partition or cluster big tables with the config options your engine supports.

Run continuous integration

# example CI steps (pseudo configuration)
steps:
  - run: dbt deps
  - run: dbt build --select state:modified+ --defer --state ./prod-artifacts

Deferring to production artifacts lets a pull request build only changed models and read unchanged parents from production.

Document as you go

Write a description for every model and for key columns. Use doc blocks for long text. The docs site becomes the first place new team members look.

Use packages wisely

Shared packages save time, but pin their versions. Review what a package does before adding it to production.

Handle secrets safely

Keep credentials in environment variables or a secrets manager such as HashiCorp Vault, never in the repository. Use separate roles for development and production.

Think about upstream and downstream

Upstream, loaders such as Kafka Connect feed raw tables. Downstream, you may expose marts to Trino, BI tools or model pipelines. Agree on a contract with each side and monitor it with data lineage.

Common mistakes

1. Putting business logic in staging models. Keep staging boring and put logic in later layers.
2. Hard-coding table names. Always use ref() and source().
3. Skipping tests on primary keys. Duplicates then spread silently into every join.
4. Using full-refresh tables for huge facts. Costs grow quickly.
5. Incremental models with no unique key. Reruns create duplicates.
6. Forgetting late-arriving data. The incremental filter drops rows that arrive after the watermark date.
7. Too many views in a deep chain. Every query recomputes the whole chain.
8. Overusing macros. Hidden logic makes review difficult.
9. Ignoring source freshness. The build succeeds on stale data.
10. Not pinning versions. An upgrade of dbt or a package changes behaviour unexpectedly.
11. Treating warnings as noise. Warnings pile up until a real problem hides among them.
12. Mixing environments. Developers building into production schemas can overwrite live tables.
13. Select star into feature tables. Adding a source column silently changes the model input.
14. Letting label columns leak into features. Always review feature models for future information.

Frequently asked questions

What is dbt used for?

dbt is used to transform data that is already in a warehouse or lakehouse. Teams use it to build clean staging tables, business-ready marts, tests that guard data quality and documentation with lineage. It brings version control and testing to SQL work.

Is dbt an ETL tool?

dbt handles only the transform step of ELT. It does not extract data from sources or load it into the warehouse. You pair it with a loading tool or pipeline, and then dbt shapes the raw tables into models for analysis.

Do I need to know Python to use dbt?

No. Most dbt work is SQL with a little Jinja templating and YAML configuration. Some adapters also support Python models for tasks that are hard in SQL, but they are optional.

How does dbt know the order to run models?

It reads every ref() and source() call, builds a dependency graph and runs models in an order that respects it. Independent models can run in parallel. If a model fails, downstream models that depend on it are skipped.

What is the difference between a view and a table in dbt?

A view stores only the query and is computed each time someone reads it, so builds are fast but reads can be slow. A table stores the computed result, so builds take longer but reads are quick. Incremental models are tables that update only new or changed rows.

Can dbt handle real-time data?

Not by itself. dbt runs in batches on a schedule, though frequent small runs can approach near real time. For true event-by-event processing, a streaming engine is a better fit, and dbt can then model the results that land in the warehouse.

What are dbt tests?

Tests are queries that look for rows that break a rule. A test passes when it returns no rows. Built-in tests check for uniqueness, nulls, allowed values and valid references, and you can add custom tests in SQL.

Does dbt work with data lakes and lakehouses?

Yes, through adapters for engines that query lake tables, such as distributed SQL engines and lakehouse platforms. The capabilities vary by adapter, so check which materializations and features your engine supports.

How does dbt help machine learning projects?

It produces consistent, tested, documented tables that serve as training and feature inputs. Version control makes it possible to rebuild a table as it was for a past experiment, and tests help prevent bad rows and leakage from reaching a model.

What is a dbt model contract?

A contract is a declaration of the columns, types and constraints a model must produce. dbt checks the contract at build time and fails if the SQL does not match. It protects downstream users, including model pipelines, from surprise changes.

Glossary

• Adapter: A plugin that lets dbt connect to a specific database or engine.
• Analytics engineering: The practice of applying software engineering habits to analytical data models.
• Artifact: A JSON file such as the manifest or run results that dbt writes after a command.
• Contract: A declared schema that a model must meet.
• DAG: Directed acyclic graph, the dependency structure of a project.
• ELT: Extract, load, transform. Data lands first and is transformed in the warehouse.
• Ephemeral model: A model that is inlined into others and not created as an object.
• Exposure: A declared downstream use of dbt models.
• Generic test: A reusable test that can be applied to any column.
• Incremental model: A model that processes only new or changed data.
• Jinja: The templating language used inside dbt SQL files.
• Macro: A reusable function written in Jinja.
• Manifest: The parsed description of the full project.
• Materialization: The way a model is stored in the warehouse.
• Mart: A business-facing model built for consumption.
• Package: A shared collection of models, macros and tests.
• Seed: A small CSV file loaded as a table.
• Singular test: A test written as one SQL file for a specific check.
• Snapshot: A history-tracking table built from changing source data.
• Source: A raw table declared in YAML.
• Target: A named environment configuration such as dev or prod.

Summary

dbt turns SQL select statements into a managed transformation layer. It builds a dependency graph from ref() calls, creates tables and views in the right order, runs tests and generates documentation and lineage. The strength of the tool is its workflow: version control, review, testing and documentation for SQL.

For tabular foundation models, dbt is the quality gate on the way in. It builds the clean, typed, well-documented tables that such models read, keeps nulls explicit, guards against leakage and schema drift, and stores model outputs such as anomaly scores or sector labels beside the source data. Start with sources and staging models, add tests to keys, adopt contracts for the tables that models depend on and run everything in continuous integration. That discipline pays off for reports and for machine learning alike.

Related

It belongs to Batch and streaming processing in my stack, under Data engineering and lakehouse. Others in the same group: