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.

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