dbt Core: Data Transformation, Models & Testing Guide

dbt (data build tool) is the analytics engineering standard for transforming data inside your warehouse. Write modular SQL, test every column, generate documentation automatically, and ship production-quality data models with confidence.

What is dbt?

dbt (data build tool) is an open-source command-line tool that enables analytics engineers to transform data in their warehouse using plain SQL. It handles only the T in ELT — data must already be in the warehouse via a loader. dbt compiles your SELECT-based models into DDL/DML statements, executes them in the correct dependency order, tests the output, and generates a documentation site with full lineage — all from your SQL and YAML configuration files.

dbt works with all major warehouses: Snowflake, BigQuery, Redshift, Databricks, DuckDB, and Postgres. A typical dbt project contains staging models that clean raw source data, intermediate models that join and enrich, and marts (fact and dimension tables) that serve BI tools. Version control, peer review, and CI testing of data transformations become standard practice.

Project Structure

# dbt project layout
my_project/
├── dbt_project.yml          # Project config (name, version, model paths)
├── profiles.yml             # Connection credentials (usually in ~/.dbt/)
├── models/
│   ├── staging/             # stg_* models: clean raw sources
│   │   ├── schema.yml       # Source + model definitions and tests
│   │   └── stg_orders.sql
│   ├── intermediate/        # int_* models: business logic joins
│   └── marts/               # fct_* and dim_* models: final outputs
├── tests/                   # Singular SQL tests (return failing rows)
├── macros/                  # Reusable Jinja macros
├── snapshots/               # SCD Type 2 change tracking
├── seeds/                   # CSV files loaded as reference tables
└── analyses/                # Ad-hoc SQL (not materialized)

SQL Models with ref() and source()

-- models/staging/stg_orders.sql
-- ref() creates a dependency on the source model
WITH source AS (
    SELECT * FROM {{ source('raw', 'orders') }}
),
renamed AS (
    SELECT
        id                    AS order_id,
        user_id,
        status,
        amount / 100.0        AS amount_usd,   -- convert cents
        created_at::TIMESTAMP AS created_at
    FROM source
    WHERE status != 'cancelled'
)
SELECT * FROM renamed;

-- models/marts/fct_orders.sql
WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),
customers AS (
    SELECT * FROM {{ ref('stg_customers') }}
),
final AS (
    SELECT
        o.order_id,
        o.amount_usd,
        o.created_at,
        c.email,
        c.country
    FROM orders o
    LEFT JOIN customers c USING (user_id)
)
SELECT * FROM final;

Schema Tests & Source Freshness

# models/staging/schema.yml — generic tests
version: 2

models:
  - name: stg_orders
    description: "Cleaned orders from the raw source"
    columns:
      - name: order_id
        description: "Primary key"
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'delivered', 'returned']
      - name: amount_usd
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 100000

sources:
  - name: raw
    database: analytics
    schema: raw_data
    tables:
      - name: orders
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}
        loaded_at_field: _etl_loaded_at

Incremental Models & CLI Commands

-- models/marts/fct_events_incremental.sql
{{
  config(
    materialized='incremental',
    unique_key='event_id',
    on_schema_change='sync_all_columns'
  )
}}

SELECT
    event_id,
    user_id,
    event_type,
    properties,
    occurred_at
FROM {{ source('raw', 'events') }}

{%- if is_incremental() %}
    -- Only process rows newer than the latest in the table
    WHERE occurred_at > (SELECT MAX(occurred_at) FROM {{ this }})
{%- endif %};

-- Run commands
-- dbt run                        # run all models
-- dbt run --select stg_orders    # run one model
-- dbt test                       # run all tests
-- dbt docs generate && dbt docs serve  # open docs
-- dbt source freshness           # check source freshness

dbt vs Stored Procedures vs Custom Python ETL

Best Practices

• Follow the staging → intermediate → marts layer pattern; never join raw sources directly in marts
• Always define sources in schema.yml — never reference raw tables with hardcoded names
• Add at minimum unique and not_null tests on all primary keys
• Use incremental models for large event tables, full table refreshes for smaller dimensions
• Run dbt docs generate in CI so documentation is always current
• Use the --select flag and state-based CI to test only changed models in pull requests