dbt Explained: Transforming Data in the Warehouse With SQL

dbt (data build tool) is a framework for transforming data that already lives in your warehouse using SQL select statements, version control, and software engineering practices. If you are asking what is dbt in practical terms: you write each transformation as a SQL file, dbt figures out the order to build them, runs them in your warehouse, tests the results, and generates documentation with lineage. It handles the "T" in ELT and does not extract or load data itself.

What is dbt and why do teams use it?

Before tools like dbt, warehouse transformations often lived in a tangle of stored procedures, scheduled queries, and BI tool logic. Nobody knew which table depended on which, changes were risky, and there were no tests. dbt brings a few simple ideas to that problem:

• Every transformation is a select statement stored in a file under version control.
• Dependencies are declared in code, so dbt can build a dependency graph automatically.
• Tests and documentation live next to the models they describe.
• The same project runs in development, CI, and production with different targets.

dbt compiles your models into SQL for the specific warehouse you use, such as Snowflake, BigQuery, Redshift, Databricks, or PostgreSQL, through adapters. The warehouse does the heavy lifting; dbt orchestrates the SQL. It is available as open-source dbt Core, run from a CLI, and as a managed cloud product.

How does dbt work?

Models

A model is a .sql file in the models/ directory containing a single select statement. The file name becomes the name of the table or view dbt creates. You never write CREATE TABLE yourself; dbt wraps your select in the right DDL based on the model's materialization.

ref() and the dependency graph

Instead of hard-coding table names, models refer to each other with {{ ref('model_name') }}. This does two jobs. At compile time it resolves to the correct schema and table for the current environment, so development builds do not touch production tables. And it tells dbt that one model depends on another, which is how dbt builds a DAG and runs models in the right order.

Sources

Raw tables loaded by an ingestion tool are declared as sources in a YAML file and referenced with {{ source('source_name', 'table_name') }}. Declaring sources makes raw inputs visible in lineage, lets you test them, and supports freshness checks that warn when a loader has stopped delivering data.

Tests

dbt has two kinds of data tests. Generic tests are reusable assertions declared in YAML, and four ship with dbt: unique, not_null, accepted_values, and relationships. Singular tests are SQL files that return failing rows; if the query returns anything, the test fails. Packages such as dbt-utils and dbt-expectations add many more. Recent dbt versions also support unit tests that check model logic against small, hand-written inputs.

version: 2

sources:
  - name: shop
    schema: raw_shop
    tables:
      - name: orders
        loaded_at_field: _loaded_at
        freshness:
          warn_after: {count: 12, period: hour}

models:
  - name: fct_orders
    description: One row per order, deduplicated to the latest version.
    columns:
      - name: order_id
        description: Primary key from the shop system.
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - not_null

In dbt versions before 1.8 the data_tests key was called tests, and older projects still use that name.

dbt materializations explained

A materialization controls how dbt persists a model in the warehouse. You set it per model or per folder.

A common layout is staging models as views, intermediate models as views or ephemeral, and marts as tables or incremental models. The table layout of those marts is a modeling choice; see star schema vs snowflake schema for how dimensional models are usually shaped.

What are incremental models in dbt?

Rebuilding a large fact table from scratch every run gets slow and expensive. An incremental model builds the full table the first time, then on later runs processes only new or changed rows and merges them in.

{{
  config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge'
  )
}}

select
    order_id,
    customer_id,
    status,
    amount,
    updated_at
from {{ source('shop', 'orders') }}

{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

is_incremental() is true only when the target table already exists and you are not running with --full-refresh. {{ this }} refers to the existing target table. With unique_key and the merge strategy, rows whose order_id already exists are updated instead of duplicated. Available strategies vary by adapter and include append, merge, delete+insert, and insert_overwrite.

Incremental models need care. Late-arriving rows with an older updated_at can be skipped by a strict > filter, so many teams use a lookback window, such as reprocessing the last few days on every run. When logic changes, run dbt run --full-refresh --select fct_orders to rebuild the table from scratch.

Running a dbt project

The commands you use most:

1. dbt run builds models.
2. dbt test runs data tests.
3. dbt build runs models, tests, seeds, and snapshots together in DAG order, so a failing test stops downstream models from building.
4. dbt source freshness checks source freshness rules.
5. dbt docs generate and dbt docs serve create and host a documentation site.

Node selection keeps runs focused. For example, dbt build --select +fct_orders builds fct_orders and everything upstream of it, while fct_orders+ selects it and everything downstream.

Documentation and lineage

Because every dependency is declared with ref() or source(), dbt knows the full lineage graph. dbt docs generate combines that graph with your YAML descriptions and warehouse metadata into a browsable site. You can click any model and see what feeds it and what depends on it, which makes impact analysis before a change far easier. Lineage is also how dbt pairs well with data quality testing and monitoring: you can trace a failed test to its upstream cause.

Where dbt fits in the data stack

dbt sits after ingestion and before consumption. A loader or a CDC pipeline lands raw data; dbt transforms it into clean models; BI tools, notebooks, and reverse ETL tools read the results. Scheduling is handled by dbt's cloud offering or an orchestrator such as Apache Airflow. If you need a refresher on writing the underlying queries, the SQL guide covers the fundamentals.

Key takeaways

• dbt handles the transformation step of ELT using SQL select statements inside the warehouse.
• ref() and source() resolve environment-specific table names and build the dependency graph.
• Tests and documentation live beside models, so quality checks run with every build.
• Materializations decide how models are stored; incremental models save cost on large tables.
• dbt build runs models and tests together, stopping downstream work when a test fails.

Frequently asked questions

Is dbt an ETL tool?

dbt only covers transformation. It assumes data has already been extracted and loaded into the warehouse by another tool. That is why it is usually described as the T in ELT.

Do I need to know Python to use dbt?

No. Most dbt work is SQL plus a little Jinja templating and YAML configuration. Some adapters support Python models for cases that are awkward in SQL, but they are optional.

What is the difference between dbt Core and the cloud product?

dbt Core is the open-source CLI that compiles and runs your project. The managed cloud product adds a hosted development environment, job scheduling, and other features on top. The project files themselves work with either.

When should I use an incremental model instead of a table?

Use a table until full rebuilds become too slow or costly. Switch to incremental when the table is large, new data arrives in a predictable way, and you have a reliable column, such as an updated timestamp, to identify changes.