Slowly changing dimensions (SCD Type 2) in dbt

Introduction

Slowly changing dimensions (SCD) help track changes in data over time. Type 2 keeps full history by adding new records when data changes.

When you want to keep a full history of customer address changes.

When tracking product price changes over time is important.

When employee job titles change and you want to keep old titles.

When you need to analyze how data evolved for reporting or auditing.

Syntax

dbt

with source_data as (
    select * from {{ ref('your_source_table') }}
),
new_records as (
    select
        sd.id,
        sd.attribute,
        current_timestamp as effective_from,
        null as effective_to,
        true as is_current
    from source_data sd
    left join {{ this }} t
    on sd.id = t.id and t.is_current = true
    where t.id is null or sd.attribute <> t.attribute
),
expired_records as (
    select
        t.id,
        t.attribute,
        t.effective_from,
        current_timestamp as effective_to,
        false as is_current
    from {{ this }} t
    join source_data sd
    on t.id = sd.id
    where t.is_current = true and sd.attribute <> t.attribute
),
final as (
    select * from {{ this }} where is_current = false
    union all
    select * from expired_records
    union all
    select * from new_records
    union all
    select * from {{ this }} where is_current = true and id not in (select id from expired_records)
)

select * from final

This example shows the main steps: find new or changed records, expire old ones, and keep current data.

Use {{ ref('table_name') }} to refer to source tables and {{ this }} for the current model.

Examples

Basic source data selection before applying SCD logic.

dbt

select * from source_table

Get only the current active records from the dimension table.

dbt

select * from target_table where is_current = true

Expire the old record when a change is detected.

dbt

update target_table set effective_to = current_timestamp, is_current = false where id = '123' and is_current = true

Insert a new record with the updated attribute and mark it as current.

dbt

insert into target_table (id, attribute, effective_from, effective_to, is_current) values ('123', 'new_value', current_timestamp, null, true)

Sample Program

This dbt model updates a customer dimension table using SCD Type 2 logic. It finds new or changed customers, expires old records, and inserts new current records.

dbt

with source_data as (
    select * from {{ ref('customers_source') }}
),
new_records as (
    select
        sd.customer_id,
        sd.customer_name,
        current_timestamp as effective_from,
        null as effective_to,
        true as is_current
    from source_data sd
    left join {{ this }} t
    on sd.customer_id = t.customer_id and t.is_current = true
    where t.customer_id is null or sd.customer_name <> t.customer_name
),
expired_records as (
    select
        t.customer_id,
        t.customer_name,
        t.effective_from,
        current_timestamp as effective_to,
        false as is_current
    from {{ this }} t
    join source_data sd
    on t.customer_id = sd.customer_id
    where t.is_current = true and sd.customer_name <> t.customer_name
),
final as (
    select * from {{ this }} where is_current = false
    union all
    select * from expired_records
    union all
    select * from new_records
    union all
    select * from {{ this }} where is_current = true and customer_id not in (select customer_id from expired_records)
)

select * from final

Important Notes

Always include effective_from and effective_to timestamps to track when records are valid.

Mark current records with a boolean flag like is_current for easy filtering.

Test your SCD logic on a small dataset before applying to large tables.

Summary

SCD Type 2 keeps full history by adding new rows for changes.

Use timestamps and flags to track current and past records.

dbt models can implement SCD Type 2 with SQL and incremental logic.