dbt: SQL-Based Data Transformation That Brings Software Engineering to Analytics

-- models/staging/stg_orders.sql
SELECT
    id AS order_id,
    user_id,
    created_at,
    status,
    CAST(total_cents AS FLOAT) / 100 AS total_usd
FROM {{ source('raw', 'orders') }}
WHERE created_at >= '2024-01-01'

-- models/marts/revenue_by_region.sql
WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}  -- dependency tracked automatically
),
users AS (
    SELECT * FROM {{ ref('stg_users') }}
)
SELECT
    u.region,
    DATE_TRUNC('month', o.created_at) AS month,
    SUM(o.total_usd) AS revenue
FROM orders o
JOIN users u ON o.user_id = u.id
GROUP BY 1, 2

# models/staging/schema.yml
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - not_null
          - unique
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'cancelled']
      - name: user_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_users')
              field: user_id

dbt test  # runs all tests, fails loudly if data quality issues found

dbt run                          # build all models
dbt run --select stg_orders      # build one model
dbt run --select tag:staging     # build all staging models
dbt build                        # run + test in one command
dbt docs generate && dbt docs serve  # generate and serve documentation

-- snapshots/user_status_snapshot.sql
{% snapshot user_status_snapshot %}
  {{
    config(
      target_schema='snapshots',
      unique_key='user_id',
      strategy='check',
      check_cols=['status', 'plan'],
    )
  }}
  SELECT * FROM {{ source('raw', 'users') }}
{% endsnapshot %}