What Is dbt and How It Transforms Data in the Warehouse

The short answer

dbt (data build tool) handles the T in ELT: Extract, Load, Transform. Raw data is first loaded into the warehouse as is (from a CRM, the app database, payment systems), and then dbt turns it into clean tables for reporting.

dbt does not pull data from sources and does not store anything itself. It:

• takes your SQL models — files containing a single SELECT;
• builds them as tables or views in the warehouse (PostgreSQL, BigQuery, Snowflake, ClickHouse and others via adapters);
• works out the build order from dependencies between models;
• runs tests on data quality;
• generates documentation and a data lineage graph.

In practice, dbt brings software engineering habits to analytics SQL: Git, code review, tests, dev and prod environments.

Models and ref()

A model is a .sql file in the models/ folder. The file name becomes the table or view name. Instead of hardcoded table names, models reference each other with ref():

-- models/marts/fct_orders.sql
select
    o.order_id,
    o.customer_id,
    o.created_at::date as order_date,
    sum(i.price * i.quantity) as order_total
from {{ ref('stg_orders') }} o
join {{ ref('stg_order_items') }} i on i.order_id = o.order_id
group by 1, 2, 3

ref() does two jobs: it inserts the correct schema for the current environment, and it tells dbt that fct_orders depends on two staging models. These links form a graph (DAG), and dbt run executes models in the right order.

Raw tables loaded by an external tool are declared as sources and referenced with source('shop', 'orders').

Layers: staging and marts

A typical project structure:

The rule is simple: marts read only from staging or intermediate models, never from raw tables. When a source changes, you fix one staging model instead of a dozen reports.

Tests and documentation

Tests are declared in YAML next to the models:

# models/staging/stg_orders.yml
version: 2
models:
  - name: stg_orders
    description: "Orders from the online store database, one row per order"
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: status
        tests:
          - accepted_values:
              values: ['new', 'paid', 'shipped', 'cancelled']

Built-in tests are unique, not_null, accepted_values and relationships. You can also write custom ones — a SQL query that must return zero rows. dbt build builds the models and tests them in one go.

dbt docs generate compiles descriptions, columns and the lineage graph into a documentation site. You can see which sources feed each metric and what will break if a table changes.

A minimal project

my_project/
  dbt_project.yml
  models/
    staging/
      sources.yml
      stg_orders.sql
      stg_orders.yml
    marts/
      fct_orders.sql

The warehouse connection lives in profiles.yml. The daily loop: change a model, run dbt build --select fct_orders+ (the model and everything downstream), check the result, commit.

Models can be materialized in different ways: view, table, incremental (append or update only new rows) and ephemeral (inlined into other models as a CTE).

When a small team actually needs dbt

You need it if:

• you already have a warehouse with regular loads from several sources;
• reporting SQL has grown, and different dashboards calculate the same metric differently;
• at least two people work on analytics and you need change history;
• reports have broken silently after a change in a source system.

It is too early if:

• you have one data source and a handful of reports;
• analytics lives in the built-in reports of your CRM or BI tool and everyone is happy;
• there is no warehouse yet — solve data loading first.

Common mistakes

• Building marts directly on raw tables and skipping staging.
• No tests on key columns — these are exactly what catch duplicates after a JOIN.
• Materializing every model as table from day one: views are cheaper while volumes are small.
• Keeping business logic in both dbt and the BI tool.