Slowly Changing Dimensions (SCD)

Introduction

SCDs are techniques used in dimensional modeling to manage and track changes in dimension table attributes over time. This article explores different SCD types, real-world examples, and best practices for implementation.

💡 What is a Slowly Changing Dimension?

A Slowly Changing Dimension refers to a dimension that changes slowly over time, rather than on a regular schedule. Common examples:

A customer changing their address
A product updated with a new category
A sales region being renamed

These changes must be handled in a way that supports both historical analysis and current reporting.

Types of Slowly Changing Dimensions

🔹 SCD Type 0: Fixed Dimension

No changes are tracked. The value remains static.
Example: A product's original launch date.

🔹 SCD Type 1: Overwrite

Updates the existing value with the new one.
Historical data is lost.
✅ Use when history is not important.
Example: Updating a misspelled customer name.

🔹 SCD Type 2: Add New Row (with history)

Inserts a new row with a new surrogate key.
Retains full history.
✅ Most common in BI systems.
Requires fields like: Effective_Date, End_Date, Is_Current.
Example: Customer moves to a new city.

🔹 SCD Type 3: Add New Column

Adds a new column to store the previous value.
Tracks only limited history.
✅ Use when only one level of change is needed.
Example: Tracking a customer's current and previous region.

SCD Type	Name	Tracks History?	How it Works	When to Use	Example
0	Fixed Dimension	❌ No	No changes are allowed to the attribute	Values that never change	Product launch date
1	Overwrite	❌ No	Overwrites the old value with the new one	Corrections or non-historical fields	Fixing a misspelled customer name
2	Add New Row	✅ Full history	Inserts a new row with updated values and a new surrogate key	Changes that need full tracking over time	The customer moves to a new city
3	Add New Column	✅ Partial	Adds a new column to retain the previous value	When only one previous value needs to be stored	Store both the current and previous region for the customer

Real-World Example: Customer Dimension

Let’s say a customer changes their city from Colombo to Kandy:

Type 1

City updated to "Kandy" in the same row. Colombo is lost.

Type 2

New row added: one for Colombo (inactive), one for Kandy (active).

Type 3

Original row has two columns: Current_City and Previous_City.

📊 BI Tip: Which SCD Type Should You Use?

Use Type 1 when correcting data.
Use Type 2 when you need to track full history.
Use Type 3 for limited, recent change tracking.

In practice, many systems use Type 2 combined with Type 1 fields.

Conclusion

SCDs are essential for designing accurate and insightful data warehouses. They ensure that your data reflects both the present and the past, enabling deeper business analysis.

In the next article, we will walk through the ETL design pattern to implement SCD Type 2 with SQL or tools like SSIS or dbt.