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.

Practice

1. What is the main purpose of implementing Slowly Changing Dimension (SCD) Type 2 in a data warehouse?

easy

A. To keep full history of changes by adding new rows for each change

B. To overwrite old data with new data without keeping history

C. To delete old records when data changes

D. To aggregate data for faster queries

Solution

1. Step 1: Understand SCD Type 2 concept

   SCD Type 2 tracks changes by adding new rows instead of overwriting existing data.

2. Step 2: Compare options with concept

   Only To keep full history of changes by adding new rows for each change describes adding new rows to keep full history, which matches SCD Type 2.

3. Final Answer:

   To keep full history of changes by adding new rows for each change -> Option A

4. Quick Check:

   SCD Type 2 = Add new rows for history [OK]

Hint: Remember: SCD Type 2 adds rows, not overwrites [OK]

Common Mistakes:

• Confusing SCD Type 2 with Type 1 which overwrites data
• Thinking SCD deletes old records
• Assuming SCD aggregates data

2. Which SQL clause is essential in a dbt model to implement incremental loading for SCD Type 2?

easy

A. GROUP BY customer_id

B. HAVING COUNT(*) > 1

C. ORDER BY created_at DESC

D. WHERE updated_at > (SELECT MAX(updated_at) FROM target_table)

Solution

1. Step 1: Identify incremental loading condition

   Incremental models use a WHERE clause to filter new or changed rows based on a timestamp.

2. Step 2: Match clause to SCD Type 2 incremental logic

   WHERE updated_at > (SELECT MAX(updated_at) FROM target_table) filters rows with updated_at greater than the max in the target, enabling incremental load.

3. Final Answer:

   WHERE updated_at > (SELECT MAX(updated_at) FROM target_table) -> Option D

4. Quick Check:

   Incremental WHERE clause = WHERE updated_at > (SELECT MAX(updated_at) FROM target_table) [OK]

Hint: Use WHERE with max timestamp for incremental loads [OK]

Common Mistakes:

• Using GROUP BY instead of filtering new rows
• Ordering data without filtering
• Using HAVING without aggregation context

3. Given this simplified SQL snippet in a dbt model for SCD Type 2:

SELECT
  id,
  name,
  effective_from,
  effective_to,
  is_current
FROM customer_scd
WHERE is_current = TRUE

What will this query return?

medium

A. Only records where effective_to is NULL

B. All historical records including old versions

C. Only the latest active records for each customer

D. Records with duplicate customer IDs

Solution

1. Step 1: Analyze WHERE clause filtering

   The query filters rows where is_current = TRUE, meaning only the latest active records.

2. Step 2: Understand SCD Type 2 flags

   In SCD Type 2, is_current marks the latest version; older versions have FALSE.

3. Final Answer:

   Only the latest active records for each customer -> Option C

4. Quick Check:

   is_current = TRUE returns latest records [OK]

Hint: Filter is_current = TRUE for latest records [OK]

Common Mistakes:

• Assuming it returns all history
• Confusing effective_to NULL with is_current TRUE
• Ignoring the is_current flag meaning

4. You wrote this incremental dbt model SQL for SCD Type 2 but it duplicates current records instead of updating:

WITH new_data AS (
  SELECT * FROM source_table WHERE updated_at > (SELECT MAX(updated_at) FROM target_table)
),
updated_records AS (
  SELECT * FROM target_table WHERE is_current = TRUE
)
SELECT * FROM updated_records
UNION ALL
SELECT * FROM new_data

What is the main issue causing duplicates?

medium

A. Incorrect WHERE clause in new_data filtering

B. Missing logic to expire old current records before inserting new ones

C. Using UNION ALL instead of UNION

D. Not selecting distinct rows in updated_records

Solution

1. Step 1: Understand SCD Type 2 update process

   Old current records must be marked expired (is_current = FALSE) before adding new versions.

2. Step 2: Identify missing expiration step

   The query selects current records but does not update their is_current flag to FALSE, causing duplicates.

3. Final Answer:

   Missing logic to expire old current records before inserting new ones -> Option B

4. Quick Check:

   Expire old records before insert = Missing logic to expire old current records before inserting new ones [OK]

Hint: Expire old records before inserting new versions [OK]

Common Mistakes:

• Thinking UNION ALL causes duplicates here
• Believing WHERE clause is wrong without checking logic
• Assuming distinct needed without expiration logic

5. You want to implement SCD Type 2 in dbt for a customer dimension. Which combination of columns should you include to correctly track history and identify current records?

hard

A. customer_id, name, effective_from, effective_to, is_current

B. customer_id, name, last_updated, deleted_flag

C. customer_id, name, created_at, updated_at

D. customer_id, name, version_number, is_active

Solution

1. Step 1: Identify key SCD Type 2 columns

   SCD Type 2 needs effective_from and effective_to timestamps plus a flag like is_current to mark active rows.

2. Step 2: Compare options for completeness

   customer_id, name, effective_from, effective_to, is_current includes customer_id, name, effective_from, effective_to, and is_current, covering history and current record tracking.

3. Final Answer:

   customer_id, name, effective_from, effective_to, is_current -> Option A

4. Quick Check:

   Effective dates + is_current = customer_id, name, effective_from, effective_to, is_current [OK]

Hint: Use effective dates and is_current flag for SCD Type 2 [OK]

Common Mistakes:

• Using only timestamps without current flag
• Confusing deleted_flag with is_current
• Using version_number without effective dates