dbt Slowly Changing Dimensions

A Slowly Changing Dimension (SCD) is a dimension table where records change over time — but not frequently. Customer addresses, employee departments, product categories, and account statuses are all slowly changing. The way you handle these changes in your data warehouse determines whether you can answer historical questions accurately.

Why SCDs Matter

Question: "What was the revenue from customers in California last year?"

Without SCD tracking:
  A customer moves from California to Texas in March.
  Your query uses today's address.
  You undercount California revenue for January and February.
  The number is wrong.

With SCD tracking:
  You know the customer was in California until March.
  Historical revenue correctly attributes January-February to California.
  The number is accurate.

The Three SCD Types

Type 1 — Overwrite

Simply overwrite the old value with the new value. No history kept. Use this for corrections (fixing a typo) where the old value was always wrong anyway.

customer_id | name  | city
42          | Alice | Texas      ← old: California, overwritten, lost forever

Type 2 — Add New Row (Most Common)

Keep the old row and add a new row for each change. Use valid_from and valid_to dates to identify which row is current.

customer_id | city       | valid_from | valid_to
42          | California | 2024-01-01 | 2025-03-15   ← historical
42          | Texas      | 2025-03-15 | NULL          ← current (NULL = active)

Type 3 — Add New Column

Keep the original value in a separate column alongside the current value. Only tracks one level of history.

customer_id | city_current | city_previous
42          | Texas        | California

Implementing SCD Type 2 with dbt Snapshots

dbt's snapshot feature is the native way to implement SCD Type 2:

-- snapshots/customers_scd.sql
{% snapshot customers_scd %}

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

select
    customer_id,
    name,
    email,
    city,
    state,
    country,
    updated_at
from {{ source('crm', 'customers') }}

{% endsnapshot %}

Run dbt snapshot daily to capture changes. dbt manages the dbt_valid_from and dbt_valid_to columns automatically.

Querying SCD Type 2 Data

Get current customers only

select *
from snapshots.customers_scd
where dbt_valid_to is null

Point-in-time query: where was Alice on Feb 1, 2025?

select city, state
from snapshots.customers_scd
where customer_id = 42
  and '2025-02-01' >= dbt_valid_from
  and ('2025-02-01' < dbt_valid_to or dbt_valid_to is null)

Historical revenue attribution

select
    c.state,
    sum(o.amount_dollars) as revenue
from {{ ref('fct_orders') }} o
join snapshots.customers_scd c
  on o.customer_id = c.customer_id
  and o.order_date >= c.dbt_valid_from
  and (o.order_date < c.dbt_valid_to or c.dbt_valid_to is null)
group by c.state

This join uses the snapshot to find what state the customer was in at the time each order was placed — not their current state.

Implementing SCD Type 2 Without Snapshots

If your source already provides a history stream, you can build it yourself.