dbt

dbt (Data Build Tool)

dbt manages data transformation in the warehouse using SQL. v2.0 introduces the Fusion Engine (Rust) for performance.

When to Use

• Analytics Engineering & Data Transformations: Transforming raw data inside modern cloud warehouses (Snowflake, BigQuery, Databricks, Redshift).
• Modular SQL with Jinja & Version Control: Building reusable SQL models with DAG dependencies and macros.
• Data Quality & Schema Testing: Enforcing unique, not_null, referential integrity, and custom singular tests.
• Automated Data Documentation & Lineage: Generating interactive dependency lineage graphs directly from codebase schemas.

Quick Start

-- models/marts/fct_orders.sql
{{ config(materialized='table') }}

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

select orders.order_id, orders.customer_id, orders.order_date, coalesce(payments.amount, 0) as total_amount from orders left join payments using (order_id)

## Core Concepts

### Declarative SQL Modeling with ref() & source()

Building transformation models with dependency resolution:

```sql
-- models/marts/core/fct_customer_orders.sql
{{
  config(
    materialized = 'incremental',
    unique_key = 'order_id',
    on_schema_change = 'fail'
  )
}}

with orders as (
    select * from {{ ref('stg_orders') }}
    {% if is_incremental() %}
      where order_date >= (select coalesce(max(order_date), '1970-01-01') from {{ this }})
    {% endif %}
),

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

select
    o.order_id,
    o.customer_id,
    c.full_name as customer_name,
    o.order_date,
    o.total_amount_usd
from orders o
inner join customers c on o.customer_id = c.customer_id

Schema Testing & Documentation in YAML

Enforcing column constraints and documentation:

# models/marts/core/schema.yml
version: 2

models:
  - name: fct_customer_orders
    description: "Daily customer order fact table combining staged orders and customer dimensions."
    columns:
      - name: order_id
        description: "Primary key for orders."
        tests:
          - unique
          - not_null

      - name: customer_id
        description: "Foreign key referencing staging customers."
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id

      - name: total_amount_usd
        description: "Total transaction value in USD."
        tests:
          - not_null

Jinja Macros for DRY Reusable Logic

Creating custom reusable SQL utilities:

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, decimal_places=2) -%}
    round(cast(({{ column_name }} / 100.0) as numeric), {{ decimal_places }})
{%- endmacro %}

-- Usage in model:
select
    id,
    {{ cents_to_dollars('amount_in_cents') }} as amount_usd
from {{ ref('stg_payments') }}

Common Patterns

Incremental Models with Timestamp Filtering

Problem: Rebuilding multi-billion-row tables from scratch on every dbt run is slow and expensive.

Solution: Use incremental materialization with is_incremental():

{{ config(
    materialized='incremental',
    unique_key='event_id'
) }}

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

{% if is_incremental() %}
  -- Only query rows created after the most recent event in the target table
  where event_timestamp > (select max(event_timestamp) from {{ this }})
{% endif %}

Best Practices

Do:

• Organize projects into standard layers: staging (1-to-1 with raw sources), intermediate, and marts.
• Always use {{ ref('model_name') }} and {{ source('source_name', 'table_name') }} to maintain DAG lineage.
• Implement incremental models (materialized='incremental') for multi-million row fact tables.
• Run dbt test in CI pipelines on every pull request to catch data schema regressions.

Don't:

• Write raw database table references (e.g. analytics.raw.users); always use source() or ref().
• Perform business logic inside staging models; staging should only clean, cast, and rename columns.
• Hardcode environments (dev vs prod); use target.name conditionals in profiles.yml.

Troubleshooting

Error	Cause	Solution
Compilation Error: Model '...' depends on a node named '...' which was not found	Typo in {{ ref('model_name') }} or model file missing.	Check model filename matches referenced string exactly.
dbt test failed: unique constraint violated	Duplicate keys generated by non-unique join condition.	Inspect duplicate keys in compiled SQL in target/compiled/.
Schema change error during incremental run	Upstream model added/removed columns breaking existing incremental table.	Run with --full-refresh once to rebuild schema: dbt run --full-refresh.

References

• dbt Documentation