Slowly changing dimensions (SCD Type 2) in dbt - Step-by-Step Execution

Concept Flow - Slowly changing dimensions (SCD Type 2)

Start with existing dimension table

↓

New source data arrives

↓

Compare source with dimension on business key

↓

No change

↓

Keep old row

↓

Insert new row with updated data and new start date

↓

Updated dimension table

This flow shows how SCD Type 2 tracks changes by expiring old rows and adding new rows for changes.

Execution Sample

dbt

WITH source_data AS (
  SELECT 1 AS id, 'Alice' AS name, '2024-01-01' AS effective_date
),
existing_dim AS (
  SELECT 1 AS id, 'Alicia' AS name, '2023-01-01' AS start_date, '9999-12-31' AS end_date
)
SELECT * FROM source_data
-- Further logic to compare and update dimension would follow here

This example compares new source data with existing dimension rows to detect changes and update accordingly.

Execution Table

Step	Action	Source Data	Existing Dim Row	Change Detected?	Old Row End Date	New Row Inserted
1	Load source data	{id:1, name:'Alice', effective_date:'2024-01-01'}	N/A	N/A	N/A	N/A
2	Load existing dimension	N/A	{id:1, name:'Alicia', start_date:'2023-01-01', end_date:'9999-12-31'}	N/A	N/A	N/A
3	Compare source and existing	name='Alice'	name='Alicia'	Yes	N/A	N/A
4	Expire old row	N/A	Set end_date='2023-12-31'	Yes	2023-12-31	No
5	Insert new row	{id:1, name:'Alice', start_date:'2024-01-01', end_date:'9999-12-31'}	N/A	Yes	N/A	Yes
6	Final dimension state	N/A	Two rows: old expired, new active	N/A	2023-12-31	Yes

💡 Process stops after inserting new row and updating old row's end date.

Variable Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	Final
old_row_end_date	9999-12-31	9999-12-31	2023-12-31	2023-12-31	2023-12-31
new_row_inserted	No	No	No	Yes	Yes
change_detected	No	Yes	Yes	Yes	Yes

Key Moments - 3 Insights

Why do we keep the old row instead of just updating it?

Because SCD Type 2 tracks history, the old row is expired by setting an end date (see Step 4), so we keep past data intact.

How do we know when to insert a new row?

When the source data differs from the current active row (Step 3 shows change detected), we insert a new row with updated info (Step 5).

What does the end_date '9999-12-31' mean?

It marks the row as currently active; when a change happens, this end_date is updated to expire the row (Step 4).

Visual Quiz - 3 Questions

Look at the execution table, at which step is the old row expired?

A Step 4

B Step 3

C Step 5

D Step 6

💡 Hint

Check the 'Old Row End Date' column in the execution table.

According to the variable tracker, what is the value of 'new_row_inserted' after Step 5?

A No

B Yes

C Maybe

D Not set

💡 Hint

Look at the 'new_row_inserted' row under 'After Step 5' in variable_tracker.

If the source data name matched the existing dimension name, what would happen in the execution table?

A Change detected would be Yes

B Old row would be expired

C No new row inserted

D Process would insert duplicate rows

💡 Hint

Refer to Step 3 and Step 5 in the execution table for change detection and row insertion.

Concept Snapshot

SCD Type 2 tracks history by:
- Comparing new data with current active rows
- If change, expire old row by setting end_date
- Insert new row with updated data and new start_date
- Use '9999-12-31' as active row end_date
- Keeps full change history in dimension table

Full Transcript

Slowly Changing Dimensions Type 2 (SCD Type 2) is a method to track changes in dimension data over time. When new source data arrives, it is compared to existing dimension rows using a business key. If no change is detected, the existing row remains unchanged. If a change is detected, the old row is expired by setting its end date to the day before the new data's start date. Then, a new row is inserted with the updated data and a start date matching the change date. The end date '9999-12-31' marks the row as currently active. This process preserves historical data and allows analysis of changes over time. The execution table shows each step: loading data, detecting changes, expiring old rows, and inserting new rows. The variable tracker follows key variables like old row end date and whether a new row was inserted. Key moments clarify why old rows are kept and how changes trigger new rows. The visual quiz tests understanding of these steps. This method is common in data warehousing and dbt models to maintain accurate historical records.