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:

-- 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