dbt Data Transformation Guide: Models, Testing, Incremental Builds, and Documentation

dbt (data build tool) has transformed analytics engineering by bringing software engineering best practices — version control, testing, documentation, and modular design — to SQL transformations. This guide covers project organization, model layering, testing strategies, incremental models, macros, and automated documentation for production dbt projects targeting modern cloud warehouses.

dbt Core Philosophy

dbt focuses exclusively on the T in ELT. It assumes data is already loaded into your warehouse (Snowflake, BigQuery, Redshift, DuckDB, etc.) and handles transformation through SQL SELECT statements. dbt compiles these into DDL/DML and executes them against the warehouse.

Key benefits:

• Every transformation is a versioned SQL file in Git
• Built-in testing framework catches data quality issues early
• Auto-generated data lineage graph and documentation site
• Incremental processing minimizes compute costs on large tables
• Jinja templating enables DRY, reusable SQL patterns

Project Structure

A well-organized dbt project follows a layered architecture:

my_project/
├── dbt_project.yml
├── profiles.yml
├── models/
│   ├── staging/           # Raw data cleaning, 1:1 with source tables
│   │   ├── _sources.yml
│   │   ├── _staging.yml
│   │   ├── stg_orders.sql
│   │   └── stg_customers.sql
│   ├── intermediate/      # Business logic, multi-table joins
│   │   └── int_order_items_joined.sql
│   └── marts/             # Final analytical models for BI tools
│       ├── core/
│       │   ├── dim_customers.sql
│       │   └── fct_orders.sql
│       └── finance/
│           └── fct_revenue.sql
├── tests/                 # Custom singular tests (SQL assertions)
├── macros/                # Reusable Jinja macros
├── seeds/                 # Static CSV reference data
└── snapshots/             # SCD Type 2 change tracking

Staging Models

Staging models clean and standardize raw source data. They rename columns to a consistent convention, cast types, and apply simple filters. One staging model per source table is the golden rule — no business logic here.

-- models/staging/stg_orders.sql
with source as (
    select * from {{ source('ecommerce', 'raw_orders') }}
),

renamed as (
    select
        id                               as order_id,
        customer_id,
        lower(status)                    as status,
        amount                           as order_amount_usd,
        cast(created_at as timestamp)    as created_at,
        cast(updated_at as timestamp)    as updated_at
    from source
    where id is not null
)

select * from renamed

Define sources with freshness checks:

# models/staging/_sources.yml
sources:
  - name: ecommerce
    database: raw
    schema: public
    freshness:
      warn_after:  {count: 24, period: hour}
      error_after: {count: 48, period: hour}
    tables:
      - name: raw_orders
        loaded_at_field: _loaded_at
      - name: raw_customers
        loaded_at_field: _loaded_at

Intermediate Models

Intermediate models join multiple staging models and encode business logic. They are not typically exposed to end consumers and sit between staging and marts.

-- models/intermediate/int_order_items_joined.sql
with orders as (select * from {{ ref('stg_orders') }}),
order_items  as (select * from {{ ref('stg_order_items') }}),
products     as (select * from {{ ref('stg_products') }})

select
    oi.order_item_id,
    oi.order_id,
    o.customer_id,
    o.status            as order_status,
    oi.product_id,
    p.product_name,
    p.category,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price  as line_total,
    o.created_at        as order_created_at
from order_items oi
left join orders   o  using (order_id)
left join products p  using (product_id)

Mart Models

Mart models are the final analytical tables consumed by BI tools. Configure materialization and warehouse-specific settings at this layer.

-- models/marts/core/fct_orders.sql
{{
    config(
        materialized='table',
        cluster_by=['customer_id'],
        partition_by={'field': 'order_date', 'data_type': 'date', 'granularity': 'day'}
    )
}}

with orders as (select * from {{ ref('stg_orders') }}),
customers   as (select * from {{ ref('dim_customers') }}),
items_agg   as (
    select
        order_id,
        count(*)        as item_count,
        sum(line_total) as calculated_total
    from {{ ref('int_order_items_joined') }}
    group by 1
)

select
    o.order_id,
    o.customer_id,
    c.customer_name,
    c.country,
    o.status,
    o.order_amount_usd,
    ia.item_count,
    ia.calculated_total,
    date(o.created_at)  as order_date,
    o.created_at
from orders o
left join customers c  using (customer_id)
left join items_agg ia using (order_id)

Testing Strategy

dbt provides two test types: generic tests (configured in YAML, reusable) and singular tests (custom SQL assertions that must return zero rows to pass).

Generic tests in schema YAML:

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'completed', 'cancelled', 'refunded']
      - name: order_amount_usd
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 1000000

Singular tests for complex business rules:

-- tests/assert_no_future_orders.sql
-- Fails if any order has a future creation date
select order_id
from {{ ref('fct_orders') }}
where order_date > current_date

dbt-expectations for statistical data quality:

- name: order_amount_usd
  tests:
    - dbt_expectations.expect_column_mean_to_be_between:
        min_value: 50
        max_value: 500
    - dbt_expectations.expect_column_quantile_values_to_be_between:
        quantile: 0.99
        min_value: 0
        max_value: 10000

Incremental Models

Incremental models process only new or changed rows, dramatically reducing compute costs for large tables that grow daily.

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

select
    event_id,
    user_id,
    event_type,
    properties,
    occurred_at
from {{ source('events', 'raw_events') }}

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

Incremental strategies by warehouse:

• append: Insert new rows only (no deduplication)
• merge: Upsert via unique_key — Snowflake, BigQuery, Spark
• insert_overwrite: Overwrite full partitions — efficient on BigQuery and Spark
• delete+insert: Delete matching rows then insert — useful on Redshift

Macros and Reusability

Macros let you write DRY SQL using Jinja templating, encapsulating common patterns.

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, precision=2) %}
    round({{ column_name }} / 100.0, {{ precision }})
{% endmacro %}

-- macros/date_spine.sql
{% macro date_spine(start_date, end_date) %}
    {{ dbt_utils.date_spine(
        datepart="day",
        start_date="cast('" ~ start_date ~ "' as date)",
        end_date="cast('" ~ end_date ~ "' as date)"
    ) }}
{% endmacro %}

Usage in models:

select
    {{ dbt_utils.generate_surrogate_key(['order_id', 'product_id']) }} as order_item_sk,
    {{ cents_to_dollars('amount_cents') }} as amount_usd
from {{ ref('stg_order_items') }}

Custom generic test:

-- macros/test_is_positive.sql
{% test is_positive(model, column_name) %}
select {{ column_name }}
from {{ model }}
where {{ column_name }} is not null and {{ column_name }} <= 0
{% endtest %}

Snapshots for SCD Type 2

Snapshots track how dimension data changes over time, enabling point-in-time queries.

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

Running dbt snapshot adds dbt_valid_from and dbt_valid_to columns, creating a full history of all changes.

Documentation Automation

dbt auto-generates a documentation site from YAML descriptions.

models:
  - name: fct_orders
    description: >
      One row per order. The primary fact table for order analysis.
      Updated daily from the raw_orders source.
    columns:
      - name: order_id
        description: Unique identifier for the order from the e-commerce platform.
      - name: order_amount_usd
        description: Total order value in US dollars, inclusive of taxes and shipping.

dbt docs generate   # compile docs + lineage graph
dbt docs serve --port 8080  # launch interactive docs site

The resulting site includes an interactive DAG lineage graph, column descriptions, test coverage status, and source freshness indicators.

Slim CI for Faster Pipelines

Only run modified models and their downstream dependencies in CI:

# Fetch the production manifest for comparison
dbt run  --select state:modified+ --defer --state ./prod-manifest/
dbt test --select state:modified+

This cuts CI time dramatically — only changed models and their children are re-executed.

Conclusion

dbt brings engineering rigor to data transformation: modular SQL organized in staging, intermediate, and mart layers; a comprehensive testing framework; incremental models that reduce compute costs; Jinja macros for reusable logic; snapshots for slowly-changing dimensions; and auto-generated documentation that keeps teams aligned. Whether running on Snowflake, BigQuery, or Redshift, dbt is the foundation of a modern, maintainable analytics stack.