Slowly changing dimensions (SCD Type 2) in dbt - Deep Dive

Overview - Slowly changing dimensions (SCD Type 2)

What is it?

Slowly changing dimensions (SCD) Type 2 is a method used in data warehousing to track changes in data over time. It keeps a full history of changes by creating new records instead of overwriting old ones. This way, you can see how data looked at any point in the past. It is especially useful for data like customer addresses or product details that change occasionally.

Why it matters

Without SCD Type 2, historical data would be lost when updates happen, making it impossible to analyze trends or changes over time accurately. For example, if a customer's address changes, you want to know what it was before and when it changed. SCD Type 2 solves this by preserving all versions, enabling better decision-making and accurate reporting.

Where it fits

Before learning SCD Type 2, you should understand basic data warehousing concepts like dimensions and facts, and simple data transformations. After mastering SCD Type 2, you can explore more complex data modeling techniques, performance optimization in dbt, and advanced analytics that rely on historical data.

Mental Model

Core Idea

SCD Type 2 tracks every change by adding new records with timestamps, preserving full history instead of overwriting data.

Think of it like...

Imagine a photo album where every time a family member changes their hairstyle, you add a new photo instead of erasing the old one. This way, you can look back and see how their style evolved over time.

┌───────────────┐      ┌───────────────┐      ┌───────────────┐
│ Original Row  │─────▶│ New Change    │─────▶│ Next Change   │
│ (Start Date)  │      │ (New Start)   │      │ (New Start)   │
│ (End Date)    │      │ (End Date)    │      │ (End Date)    │
└───────────────┘      └───────────────┘      └───────────────┘
Each row has a validity period showing when it was active.

Build-Up - 7 Steps

1

Foundation

Understanding Dimensions in Data

Concept: Dimensions are descriptive data that provide context to facts in a data warehouse.

In data warehousing, dimensions are tables that describe things like customers, products, or locations. They help answer questions like 'Who bought this?' or 'Where was it sold?'. Dimensions usually change slowly over time, unlike facts which record events or transactions.

Result

You can identify what kind of data needs tracking and understand why dimensions are important for analysis.

Knowing what dimensions are sets the stage for understanding why tracking their changes matters.

2

Foundation

What Are Slowly Changing Dimensions?

Concept: Slowly Changing Dimensions (SCD) are dimensions that change over time, requiring special handling to keep data accurate.

Some dimension data changes rarely but must be tracked, like a customer's address or product price. Without special methods, updates overwrite old data, losing history. SCD methods help keep track of these changes properly.

Result

You recognize the problem of losing historical data when dimension attributes change.

Understanding the problem motivates the need for SCD techniques.

3

Intermediate

SCD Type 1 vs Type 2 Differences

🤔Before reading on: Do you think SCD Type 1 keeps history or overwrites data? Commit to your answer.

Concept: SCD Type 1 overwrites old data, while Type 2 preserves history by adding new records.

Type 1 simply updates the existing record, losing old values. Type 2 adds a new record with the updated data and timestamps to show when it was valid. This way, you can see all past versions.

Result

You can choose the right SCD type based on whether history is needed.

Knowing the difference helps avoid data loss or unnecessary complexity.

4

Intermediate

Implementing SCD Type 2 in dbt

🤔Before reading on: Do you think dbt handles SCD Type 2 automatically or requires custom SQL? Commit to your answer.

Concept: dbt uses SQL models to implement SCD Type 2 by inserting new rows with effective dates and flags for current records.

In dbt, you write SQL that compares incoming data with existing dimension data. When a change is detected, you insert a new row with a new start date and mark the old row as expired by setting an end date. This keeps full history.

Result

You can build dbt models that track changes over time correctly.

Understanding how to write these SQL transformations in dbt is key to managing historical data.

5

Intermediate

Key Columns for SCD Type 2 Tracking

Concept: SCD Type 2 requires columns like start_date, end_date, and current_flag to manage record versions.

start_date shows when the record became valid, end_date shows when it stopped being valid, and current_flag indicates if the record is the latest version. These columns help queries find the right version for any point in time.

Result

You know how to structure your dimension tables for SCD Type 2.

These columns are essential for querying historical data accurately.

6

Advanced

Handling Overlapping and Missing Periods

🤔Before reading on: Do you think overlapping date ranges in SCD Type 2 are acceptable or cause problems? Commit to your answer.

Concept: Proper SCD Type 2 design prevents overlapping or gaps in validity periods to maintain data integrity.

When inserting new records, you must close the previous record's end_date just before the new start_date. Overlaps or gaps can cause confusion about which record is valid at a given time. Careful SQL logic ensures clean periods.

Result

Your dimension data remains consistent and reliable for time-based queries.

Maintaining clean date ranges avoids subtle bugs in historical analysis.

7

Expert

Optimizing SCD Type 2 for Performance in dbt

🤔Before reading on: Do you think storing full history always scales well in large data warehouses? Commit to your answer.

Concept: Storing full history can grow data size; optimization techniques help maintain performance.

Techniques include partitioning tables by date, indexing key columns, and using incremental models in dbt to process only changed data. Also, archiving very old records or summarizing history can reduce query time and storage costs.

Result

Your SCD Type 2 implementation remains efficient and scalable as data grows.

Balancing history tracking with performance is crucial for production systems.

Under the Hood

SCD Type 2 works by storing multiple versions of a dimension record, each with a validity period defined by start and end dates. When a change occurs, the system inserts a new record with the updated data and sets the previous record's end date to the day before. Queries use these dates to find the correct version for any analysis date. This approach relies on careful SQL logic to detect changes and manage date ranges.

Why designed this way?

SCD Type 2 was designed to solve the problem of losing historical context in data warehouses. Early methods like overwriting data (Type 1) were simpler but erased history. Type 2 balances the need for full history with manageable complexity by using date ranges and flags. Alternatives like Type 3 store limited history but are less flexible. The design reflects a tradeoff between storage cost and analytical power.

┌───────────────┐      ┌───────────────┐      ┌───────────────┐
│ Record v1     │      │ Record v2     │      │ Record v3     │
│ Start: 2020   │─────▶│ Start: 2021   │─────▶│ Start: 2022   │
│ End: 2020-12-31│      │ End: 2021-12-31│      │ End: NULL     │
│ Current: False│      │ Current: False│      │ Current: True │
└───────────────┘      └───────────────┘      └───────────────┘
Each record shows a version with its active period and current status.

Myth Busters - 4 Common Misconceptions

Quick: Does SCD Type 2 overwrite old data or keep all versions? Commit to your answer.

Common Belief: SCD Type 2 just updates the existing record with new data, overwriting old values.

Reality: SCD Type 2 never overwrites old records; it inserts new rows and marks old ones as expired to keep full history.

Why it matters: Overwriting data causes loss of historical information, making trend analysis and audits impossible.

Quick: Is it okay for SCD Type 2 records to have overlapping validity dates? Commit to yes or no.

Common Belief: Overlapping date ranges in SCD Type 2 records are acceptable and don't cause issues.

Reality: Overlapping validity periods cause confusion about which record is current and lead to incorrect query results.

Why it matters: Data integrity breaks down, and reports may show conflicting or duplicated information.

Quick: Does SCD Type 2 always require storing every single change, no matter how small? Commit to yes or no.

Common Belief: Every tiny change must be stored as a new record in SCD Type 2.

Reality: Often, only meaningful changes to tracked attributes trigger new records; irrelevant changes can be ignored to reduce data bloat.

Why it matters: Storing unnecessary changes bloats the database and slows queries without adding value.

Quick: Can SCD Type 2 be implemented automatically without custom SQL in dbt? Commit to yes or no.

Common Belief: dbt has built-in automatic support for SCD Type 2 without needing custom SQL.

Reality: dbt requires writing custom SQL models to implement SCD Type 2 logic; it does not automate it out of the box.

Why it matters: Assuming automation leads to incorrect implementations and data errors.

Expert Zone

1

SCD Type 2 implementations often include a 'current_flag' column for fast filtering of the latest records, improving query performance.

2

Handling late-arriving data requires careful backfilling and adjusting validity dates to maintain consistent history.

3

Incremental models in dbt can optimize SCD Type 2 by processing only changed records, but require precise change detection logic.

When NOT to use

SCD Type 2 is not ideal when history is not needed or when changes are frequent and minor, as it can cause data bloat. In such cases, SCD Type 1 or Type 3, or event-based tracking might be better alternatives.

Production Patterns

In production, SCD Type 2 is often combined with partitioning and indexing strategies to handle large datasets. Teams use dbt incremental models with snapshots or merge statements to efficiently update dimension tables while preserving history.

Connections

Temporal Databases

SCD Type 2 is a practical implementation of temporal data concepts in data warehousing.

Understanding temporal databases helps grasp how time-based validity is managed and queried in SCD Type 2.

Version Control Systems

Both track changes over time by storing versions rather than overwriting data.

Seeing SCD Type 2 like version control clarifies why preserving history is valuable for auditing and analysis.

Legal Document Archiving

Like SCD Type 2, legal archives keep all versions of documents to maintain a full history of changes.

This connection shows how preserving history is critical beyond data science, ensuring accountability and traceability.

Common Pitfalls

#1 Not updating the end_date of the old record when inserting a new version.

Wrong approach:

INSERT INTO dimension_table (id, attr, start_date, end_date, current_flag) VALUES (1, 'New Value', '2023-01-01', NULL, TRUE);
-- Old record still has end_date = NULL and current_flag = TRUE

Correct approach:

UPDATE dimension_table SET end_date = '2022-12-31', current_flag = FALSE WHERE id = 1 AND current_flag = TRUE;
INSERT INTO dimension_table (id, attr, start_date, end_date, current_flag) VALUES (1, 'New Value', '2023-01-01', NULL, TRUE);

Root cause:

Forgetting to close the old record's validity period causes overlapping current records, breaking data integrity.

#2 Treating SCD Type 2 as a simple update instead of inserting new rows.

Wrong approach:

UPDATE dimension_table SET attr = 'New Value' WHERE id = 1;

Correct approach:

INSERT INTO dimension_table (id, attr, start_date, end_date, current_flag) VALUES (1, 'New Value', '2023-01-01', NULL, TRUE);
-- Also update old record's end_date and current_flag

Root cause:

Misunderstanding that SCD Type 2 requires preserving old versions by inserting new records.

#3 Not filtering queries to select only current records when needed.

Wrong approach:

SELECT * FROM dimension_table WHERE id = 1;

Correct approach:

SELECT * FROM dimension_table WHERE id = 1 AND current_flag = TRUE;

Root cause:

Ignoring the current_flag leads to multiple rows returned, confusing analysis.

Key Takeaways

SCD Type 2 preserves full history by adding new records with validity dates instead of overwriting data.

Key columns like start_date, end_date, and current_flag enable tracking and querying of data changes over time.

Proper management of date ranges prevents overlapping or gaps, ensuring data integrity.

dbt requires custom SQL logic to implement SCD Type 2, often using incremental models for efficiency.

Understanding SCD Type 2 is essential for accurate historical analysis and auditing in data warehouses.

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