Published on

dbt (Data Build Tool): Complete Guide to Modern Data Transformation

Table of Contents

Introduction

dbt (Data Build Tool) has revolutionized how data teams build transformation workflows. Instead of writing complex ETL scripts, dbt enables analysts and engineers to transform data using simple SQL SELECT statements, bringing software engineering best practices to analytics.

This comprehensive guide covers everything you need to master dbt: from basic concepts to advanced patterns, deployment strategies, and real-world production examples.

What is dbt?

dbt is an open-source command-line tool that enables data analysts and engineers to transform data in their warehouse more effectively. It allows you to:

  • Write modular SQL transformations
  • Version control your analytics code
  • Test data quality
  • Document your data models
  • Deploy with confidence

The Modern Data Stack

dbt fits into the ELT (Extract, Load, Transform) paradigm:

Data Sources → Ingestion Tools → Data Warehouse → dbt (Transform) → BI Tools
              (Fivetran, Airbyte)  (Snowflake, BigQuery)              (Tableau, Looker)

Key Philosophy: Transform data where it lives (in the warehouse) using SQL, leveraging warehouse compute power.

dbt Core vs dbt Cloud

dbt Core (Open Source)

Free and open-source CLI tool that runs locally or in CI/CD.

Features:

  • Complete dbt functionality
  • Run from command line
  • Integrate with any orchestrator
  • Self-hosted and managed

Installation:

# Using pip
pip install dbt-core dbt-postgres

# Using Homebrew (macOS)
brew install dbt-postgres

# Check installation
dbt --version

dbt Cloud (Commercial)

Managed service with additional features for teams.

Additional Features:

  • Web-based IDE
  • Job scheduling
  • Integrated documentation
  • Run history and logging
  • Semantic layer
  • Discovery API
  • SSO and RBAC

Pricing: Free tier available, paid plans for teams.

Getting Started with dbt

Installation and Setup

1. Install dbt Core with your warehouse adapter:

# Snowflake
pip install dbt-snowflake

# BigQuery
pip install dbt-bigquery

# Redshift
pip install dbt-redshift

# Databricks
pip install dbt-databricks

# PostgreSQL (for learning)
pip install dbt-postgres

2. Initialize a new dbt project:

dbt init my_dbt_project
cd my_dbt_project

3. Configure warehouse connection:

Edit ~/.dbt/profiles.yml:

my_dbt_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: xy12345
      user: dbt_user
      password: "{{ env_var('DBT_PASSWORD') }}"
      role: TRANSFORMER
      database: ANALYTICS
      warehouse: TRANSFORMING
      schema: dbt_dev
      threads: 4

    prod:
      type: snowflake
      account: xy12345
      user: dbt_prod_user
      password: "{{ env_var('DBT_PROD_PASSWORD') }}"
      role: TRANSFORMER
      database: ANALYTICS
      warehouse: TRANSFORMING
      schema: analytics
      threads: 8

4. Test connection:

dbt debug

Project Structure

my_dbt_project/
├── dbt_project.yml          # Project configuration
├── profiles.yml             # Connection profiles (in ~/.dbt/)
├── models/                  # SQL transformation models
│   ├── staging/            # Raw data cleaning
│   ├── intermediate/       # Business logic transformations
│   ├── marts/              # Final business-facing models
│   └── schema.yml          # Tests and documentation
├── macros/                  # Reusable SQL functions
├── tests/                   # Custom data tests
├── snapshots/              # Slowly changing dimensions
├── analyses/               # Ad-hoc queries (not materialized)
├── seeds/                  # CSV files to load
└── target/                 # Compiled SQL (gitignored)

Core Concepts

1. Models

Models are SELECT statements that transform raw data. Each model is one SQL file.

Example: Staging Model

models/staging/stg_customers.sql:

with source as (
    select * from {{ source('raw', 'customers') }}
),

cleaned as (
    select
        id as customer_id,
        lower(trim(email)) as email,
        first_name,
        last_name,
        created_at,
        updated_at
    from source
    where email is not null
)

select * from cleaned

Run the model:

dbt run --select stg_customers

2. Materializations

Control how models are built in the warehouse.

View (Default)

Rebuilds the view on every run. Fast to build, slower to query.

{{ config(materialized='view') }}

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

Table

Drops and recreates table. Slower to build, fast to query.

{{ config(materialized='table') }}

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

Incremental

Builds only new/changed rows. Efficient for large datasets.

{{ config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='fail'
) }}

select
    order_id,
    customer_id,
    order_date,
    total_amount,
    updated_at
from {{ source('raw', 'orders') }}

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

Ephemeral

Not materialized, exists only as CTE. Reduces warehouse objects.

{{ config(materialized='ephemeral') }}

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

3. Sources

Define raw data sources for lineage and testing.

models/staging/sources.yml:

version: 2

sources:
  - name: raw
    database: raw_data
    schema: public
    tables:
      - name: customers
        description: Raw customer data from production database
        loaded_at_field: _loaded_at
        freshness:
          warn_after: { count: 12, period: hour }
          error_after: { count: 24, period: hour }
        columns:
          - name: id
            description: Primary key
            tests:
              - unique
              - not_null

      - name: orders
        description: Raw order transactions
        columns:
          - name: id
            tests:
              - unique
              - not_null
          - name: customer_id
            tests:
              - relationships:
                  to: source('raw', 'customers')
                  field: id

Check source freshness:

dbt source freshness

4. Refs and Dependencies

Use {{ ref('model_name') }} to reference other models. dbt builds a DAG automatically.

Example: Customer Orders Model

models/marts/fct_customer_orders.sql:

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

orders as (
    select * from {{ ref('stg_orders') }}
),

customer_orders as (
    select
        c.customer_id,
        c.email,
        c.first_name,
        c.last_name,
        count(o.order_id) as total_orders,
        sum(o.total_amount) as lifetime_value,
        min(o.order_date) as first_order_date,
        max(o.order_date) as most_recent_order_date
    from customers c
    left join orders o on c.customer_id = o.customer_id
    group by 1, 2, 3, 4
)

select * from customer_orders

dbt automatically knows to build stg_customers and stg_orders first.

5. Tests

Built-in data quality tests.

models/marts/schema.yml:

version: 2

models:
  - name: fct_customer_orders
    description: Customer-level order aggregations
    columns:
      - name: customer_id
        description: Unique customer identifier
        tests:
          - unique
          - not_null

      - name: email
        description: Customer email address
        tests:
          - unique
          - not_null

      - name: lifetime_value
        description: Total revenue from customer
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0
              inclusive: true

      - name: total_orders
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 10000

Run tests:

dbt test
dbt test --select fct_customer_orders

Custom tests in tests/:

tests/assert_positive_revenue.sql:

-- Returns rows that fail the test
select
    order_id,
    total_amount
from {{ ref('stg_orders') }}
where total_amount <= 0

6. Documentation

Document your models with descriptions.

models/marts/schema.yml:

version: 2

models:
  - name: fct_customer_orders
    description: |
      This table contains customer-level aggregations of order data.

      **Refresh Schedule**: Daily at 2 AM UTC
      **Owner**: Analytics Team
      **Grain**: One row per customer

      ## Business Logic
      - Includes all customers, even those with zero orders
      - Lifetime value excludes refunds
      - First order date is based on order_date, not created_at

    columns:
      - name: customer_id
        description: Unique identifier for the customer

      - name: lifetime_value
        description: |
          Sum of all order totals for this customer.
          Calculated as: SUM(order_total - refund_amount)

Generate and serve docs:

dbt docs generate
dbt docs serve

Opens a beautiful interactive documentation site with lineage graphs.

Advanced Patterns

Jinja and Macros

dbt uses Jinja templating for dynamic SQL.

Example: Dynamic Date Filtering

select
    order_id,
    order_date,
    total_amount
from {{ ref('stg_orders') }}
where order_date >= '{{ var("start_date") }}'
  and order_date < '{{ var("end_date") }}'

Run with variables:

dbt run --vars '{"start_date": "2024-01-01", "end_date": "2024-12-31"}'

Reusable Macros

macros/cents_to_dollars.sql:

{% macro cents_to_dollars(column_name, scale=2) %}
    round({{ column_name }} / 100.0, {{ scale }})
{% endmacro %}

Usage:

select
    order_id,
    {{ cents_to_dollars('amount_cents') }} as amount_dollars,
    {{ cents_to_dollars('tax_cents', 4) }} as tax_dollars
from {{ ref('stg_orders') }}

Generating Multiple Models

Generate date spine:

macros/generate_date_spine.sql:

{% macro generate_date_spine(start_date, end_date) %}
    with date_spine as (
        {{ dbt_utils.date_spine(
            datepart="day",
            start_date="cast('" ~ start_date ~ "' as date)",
            end_date="cast('" ~ end_date ~ "' as date)"
        ) }}
    )
    select
        date_day,
        extract(year from date_day) as year,
        extract(month from date_day) as month,
        extract(day from date_day) as day,
        extract(dayofweek from date_day) as day_of_week
    from date_spine
{% endmacro %}

Snapshots (Slowly Changing Dimensions)

Track changes over time using SCD Type 2.

snapshots/customers_snapshot.sql:

{% snapshot customers_snapshot %}

{{
    config(
      target_schema='snapshots',
      unique_key='id',
      strategy='timestamp',
      updated_at='updated_at'
    )
}}

select * from {{ source('raw', 'customers') }}

{% endsnapshot %}

Run snapshots:

dbt snapshot

Result: Historical table with dbt_valid_from, dbt_valid_to columns.

Packages

Use community packages for common functionality.

packages.yml:

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1

  - package: calogica/dbt_expectations
    version: 0.10.0

  - package: dbt-labs/codegen
    version: 0.12.0

Install packages:

dbt deps

Example: Using dbt_utils:

select
    customer_id,
    {{ dbt_utils.surrogate_key(['customer_id', 'order_date']) }} as unique_key,
    {{ dbt_utils.generate_series(10) }} as numbers
from {{ ref('stg_customers') }}

Project Organization Best Practices

Layered Architecture

models/
├── staging/              # 1:1 with source tables
│   ├── _staging.yml
│   ├── stg_customers.sql
│   └── stg_orders.sql
├── intermediate/         # Business logic transformations
│   ├── _intermediate.yml
│   ├── int_customer_orders.sql
│   └── int_order_items_joined.sql
└── marts/               # Business-facing models
    ├── finance/
    │   ├── _finance.yml
    │   └── fct_revenue.sql
    ├── marketing/
    │   ├── _marketing.yml
    │   ├── dim_customers.sql
    │   └── fct_customer_orders.sql
    └── operations/
        └── fct_inventory.sql

Naming Conventions

  • Staging: stg_<source>_<table>
  • Intermediate: int_<business_logic>
  • Facts: fct_<business_process>
  • Dimensions: dim_<entity>

dbt_project.yml Configuration

name: 'my_dbt_project'
version: '1.0.0'
config-version: 2

profile: 'my_dbt_project'

model-paths: ['models']
analysis-paths: ['analyses']
test-paths: ['tests']
seed-paths: ['seeds']
macro-paths: ['macros']
snapshot-paths: ['snapshots']

target-path: 'target'
clean-targets:
  - 'target'
  - 'dbt_packages'

models:
  my_dbt_project:
    +materialized: view

    staging:
      +materialized: view
      +schema: staging

    intermediate:
      +materialized: ephemeral

    marts:
      +materialized: table
      +schema: marts

      finance:
        +schema: finance
        +tags: ['finance', 'pii']

      marketing:
        +schema: marketing
        +tags: ['marketing']

vars:
  start_date: '2020-01-01'

on-run-start:
  - "{{ log('Starting dbt run', info=True) }}"

on-run-end:
  - "{{ log('Completed dbt run', info=True) }}"

Testing Strategies

Built-in Tests

columns:
  - name: customer_id
    tests:
      - unique
      - not_null
      - relationships:
          to: ref('dim_customers')
          field: customer_id
      - accepted_values:
          values: ['active', 'inactive', 'pending']

dbt Expectations (Advanced Tests)

columns:
  - name: email
    tests:
      - dbt_expectations.expect_column_values_to_match_regex:
          regex: "^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$"

  - name: revenue
    tests:
      - dbt_expectations.expect_column_values_to_be_between:
          min_value: 0
          max_value: 1000000

  - name: created_at
    tests:
      - dbt_expectations.expect_column_values_to_be_of_type:
          column_type: timestamp

Store Test Failures

tests:
  my_dbt_project:
    +store_failures: true
    +schema: test_failures

Deployment and Orchestration

CI/CD with GitHub Actions

.github/workflows/dbt_ci.yml:

name: dbt CI

on:
  pull_request:
    branches: [main]

jobs:
  dbt-test:
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3

      - name: Set up Python
        uses: actions/setup-python@v4
        with:
          python-version: '3.11'

      - name: Install dbt
        run: |
          pip install dbt-snowflake

      - name: Run dbt debug
        run: dbt debug
        env:
          DBT_PASSWORD: ${{ secrets.DBT_PASSWORD }}

      - name: Run dbt deps
        run: dbt deps

      - name: Run dbt build (slim CI)
        run: |
          dbt build --select state:modified+ --defer --state ./prod-manifest
        env:
          DBT_PASSWORD: ${{ secrets.DBT_PASSWORD }}

Production Deployment

.github/workflows/dbt_prod.yml:

name: dbt Production

on:
  push:
    branches: [main]

jobs:
  dbt-deploy:
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3

      - name: Install dbt
        run: pip install dbt-snowflake

      - name: Run dbt seed
        run: dbt seed --target prod
        env:
          DBT_PROD_PASSWORD: ${{ secrets.DBT_PROD_PASSWORD }}

      - name: Run dbt run
        run: dbt run --target prod
        env:
          DBT_PROD_PASSWORD: ${{ secrets.DBT_PROD_PASSWORD }}

      - name: Run dbt test
        run: dbt test --target prod
        env:
          DBT_PROD_PASSWORD: ${{ secrets.DBT_PROD_PASSWORD }}

      - name: Generate docs
        run: dbt docs generate --target prod

      - name: Upload docs artifact
        run: dbt docs generate --target prod
        env:
          DBT_PROD_PASSWORD: ${{ secrets.DBT_PROD_PASSWORD }}

Airflow Integration

from airflow import DAG
from airflow.operators.bash import BashOperator
from datetime import datetime, timedelta

default_args = {
    'owner': 'analytics',
    'depends_on_past': False,
    'email_on_failure': True,
    'email': ['[email protected]'],
    'retries': 2,
    'retry_delay': timedelta(minutes=5)
}

dag = DAG(
    'dbt_daily_run',
    default_args=default_args,
    description='Daily dbt transformation',
    schedule_interval='0 2 * * *',
    start_date=datetime(2024, 1, 1),
    catchup=False
)

dbt_seed = BashOperator(
    task_id='dbt_seed',
    bash_command='cd /opt/dbt && dbt seed --profiles-dir ~/.dbt --target prod',
    dag=dag
)

dbt_run = BashOperator(
    task_id='dbt_run',
    bash_command='cd /opt/dbt && dbt run --profiles-dir ~/.dbt --target prod',
    dag=dag
)

dbt_test = BashOperator(
    task_id='dbt_test',
    bash_command='cd /opt/dbt && dbt test --profiles-dir ~/.dbt --target prod',
    dag=dag
)

dbt_seed >> dbt_run >> dbt_test

Performance Optimization

Incremental Model Best Practices

{{
    config(
        materialized='incremental',
        unique_key='order_id',
        on_schema_change='sync_all_columns',
        incremental_strategy='merge',  -- or 'delete+insert'
        partition_by={
            "field": "order_date",
            "data_type": "date",
            "granularity": "day"
        },
        cluster_by=['customer_id', 'order_date']
    )
}}

with source as (
    select * from {{ source('raw', 'orders') }}

    {% if is_incremental() %}
        -- Only process new data
        where updated_at > (select max(updated_at) from {{ this }})
           or updated_at is null
    {% endif %}
),

transformed as (
    select
        order_id,
        customer_id,
        order_date,
        total_amount,
        updated_at
    from source
)

select * from transformed

Query Optimization

-- ❌ Inefficient: Multiple scans
select
    customer_id,
    (select count(*) from orders where customer_id = c.id) as order_count,
    (select sum(total) from orders where customer_id = c.id) as total_spent
from customers c

-- ✅ Efficient: Single scan with aggregation
with order_stats as (
    select
        customer_id,
        count(*) as order_count,
        sum(total) as total_spent
    from orders
    group by customer_id
)

select
    c.customer_id,
    coalesce(o.order_count, 0) as order_count,
    coalesce(o.total_spent, 0) as total_spent
from customers c
left join order_stats o on c.customer_id = o.customer_id

Real-World Example: E-Commerce Analytics

Project Structure

models/
├── staging/
│   ├── stg_customers.sql
│   ├── stg_orders.sql
│   └── stg_products.sql
├── intermediate/
│   ├── int_orders_pivoted.sql
│   └── int_customer_order_history.sql
└── marts/
    ├── marketing/
    │   ├── dim_customers.sql
    │   ├── fct_customer_orders.sql
    │   └── fct_customer_ltv.sql
    └── finance/
        └── fct_daily_revenue.sql

Complete Model Example

Staging Layer:

models/staging/stg_orders.sql:

with source as (
    select * from {{ source('postgres', 'orders') }}
),

cleaned as (
    select
        id as order_id,
        user_id as customer_id,
        cast(order_date as date) as order_date,
        status,
        -- Convert cents to dollars
        {{ cents_to_dollars('amount') }} as order_amount,
        created_at,
        updated_at
    from source
    where status not in ('cancelled', 'pending')
      and order_date >= '2020-01-01'
)

select * from cleaned

Intermediate Layer:

models/intermediate/int_customer_order_history.sql:

with orders as (
    select * from {{ ref('stg_orders') }}
),

customer_orders as (
    select
        customer_id,
        order_id,
        order_date,
        order_amount,
        row_number() over (partition by customer_id order by order_date) as order_number,
        sum(order_amount) over (
            partition by customer_id
            order by order_date
            rows between unbounded preceding and current row
        ) as cumulative_amount
    from orders
)

select * from customer_orders

Mart Layer:

models/marts/marketing/fct_customer_ltv.sql:

{{
    config(
        materialized='table',
        tags=['marketing', 'ltv']
    )
}}

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

order_history as (
    select * from {{ ref('int_customer_order_history') }}
),

customer_metrics as (
    select
        customer_id,
        count(distinct order_id) as total_orders,
        sum(order_amount) as lifetime_value,
        avg(order_amount) as avg_order_value,
        min(order_date) as first_order_date,
        max(order_date) as last_order_date,
        {{ datediff('max(order_date)', 'current_date', 'day') }} as days_since_last_order,

        -- Customer segment
        case
            when sum(order_amount) >= 1000 then 'High Value'
            when sum(order_amount) >= 500 then 'Medium Value'
            else 'Low Value'
        end as value_segment,

        -- Recency segment
        case
            when {{ datediff('max(order_date)', 'current_date', 'day') }} <= 30 then 'Active'
            when {{ datediff('max(order_date)', 'current_date', 'day') }} <= 90 then 'At Risk'
            else 'Churned'
        end as recency_segment

    from order_history
    group by customer_id
),

final as (
    select
        c.customer_id,
        c.email,
        c.first_name,
        c.last_name,
        c.signup_date,
        m.total_orders,
        m.lifetime_value,
        m.avg_order_value,
        m.first_order_date,
        m.last_order_date,
        m.days_since_last_order,
        m.value_segment,
        m.recency_segment
    from customers c
    left join customer_metrics m on c.customer_id = m.customer_id
)

select * from final

Troubleshooting Common Issues

Issue 1: Model Won't Compile

Error: Compilation Error: model not found

Solution: Check ref() and source() names match exactly.

# Verify model exists
dbt ls --select stg_customers

# Compile to see generated SQL
dbt compile --select fct_customer_ltv

Issue 2: Incremental Model Full Refresh

Error: Incremental model rebuilding from scratch every time

Solution: Check is_incremental() logic and unique_key

-- Add debug logging
{% if is_incremental() %}
    {{ log("Running incremental mode", info=True) }}
    where updated_at > (select coalesce(max(updated_at), '1900-01-01') from {{ this }})
{% else %}
    {{ log("Running full refresh", info=True) }}
{% endif %}

Issue 3: Tests Failing

Error: FAIL 1 unique_order_id

Solution: Store failures to investigate

tests:
  +store_failures: true
  +schema: dbt_test_failures

-- Query failures
select * from dbt_test_failures.unique_orders_order_id

Issue 4: Slow dbt Runs

Solution: Use state-based execution and parallelism

# Only run modified models
dbt run --select state:modified+ --state ./prod-manifest

# Increase threads
dbt run --threads 8

# Profile performance
dbt run --profile --threads 4

Best Practices Checklist

  • Use consistent naming conventions
  • Document all models and columns
  • Test critical data quality assumptions
  • Use staging → intermediate → marts architecture
  • Version control with Git
  • Implement CI/CD pipelines
  • Use incremental models for large tables (>1M rows)
  • Partition and cluster large tables
  • Run dbt source freshness to monitor data delays
  • Generate and publish dbt docs
  • Use packages for common utilities
  • Set up alerts for test failures
  • Implement pre-commit hooks
  • Use dbt Cloud or orchestrator for scheduling
  • Monitor query performance and costs

Resources and Next Steps

Official Documentation

Learning Resources

Package Ecosystem

Conclusion

dbt has transformed how data teams work by bringing software engineering best practices to analytics. By treating data transformations as code, teams can:

  • Collaborate more effectively with version control
  • Trust their data with automated testing
  • Document their work automatically
  • Deploy with confidence using CI/CD
  • Scale their analytics infrastructure

Whether you're a solo analyst or part of a large data team, dbt provides the tools and patterns to build production-grade analytics workflows.

Start with dbt Core locally, master the fundamentals, and scale up to dbt Cloud as your team grows. The modern data stack awaits!

Related Topics

• View on GitHub (private)

Related Articles

Data Engineering Salary

Uncover the factors influencing data engineering salaries, including education, company culture, and individual performance. Explore advanced negotiation strategies, salary projections, and tips for a successful career in this lucrative field.

data-engineering