Slowly Changing Dimensions (SCD)

✅ What are Slowly Changing Dimensions (SCD)?

Slowly Changing Dimensions (SCD) are dimensions in a data warehouse that change slowly over time, rather than frequently (like transactional data). In other words, they manage how attribute values in dimension tables change over time while keeping historical context intact.

For example, a customer might change their address, name, or email. How the data warehouse handles and preserves these changes defines the SCD type.

📘 Why SCD Matters in Data Warehousing

• Preserve historical data for analytics
• Maintain data accuracy and reporting consistency
• Track changes in dimensional attributes over time
• Support time-based analysis (e.g., what region a customer belonged to at the time of purchase)

🧩 Types of SCD

🔹 SCD Type 0 – Passive

• No changes allowed. Data stays as initially loaded.
• Used when historical data must never change (e.g., date of birth).

✅ Example: A customer’s birthdate is stored once and never updated.

🔹 SCD Type 1 – Overwrite

• Old data is overwritten with new data.
• No history is preserved.

✅ Example: If a customer moves to a new city, the address is updated, and old address is lost.

Trending

Exception handling in SQL Server

📌 Use Case: When historical data is not important.

🔹 SCD Type 2 – Add New Row (History Preserved)

• A new row is inserted for each change with a new surrogate key.
• Keeps a full history of changes.
• Typically uses:
  • Effective_Start_Date
  • Effective_End_Date
  • Current_Flag

✅ Example: A customer changes their address → A new row is added with the new address, and the old row is closed.

📌 Use Case: For tracking history of attributes like location, job title, etc.

🔹 SCD Type 3 – Add New Column (Limited History)

• Old value is stored in a separate column.
• Only current and previous values are stored.

✅ Example: Add a column like Previous_City.

📌 Use Case: When limited change tracking is required.

🔹 SCD Type 4 – History Table (Separate Table)

• Maintain the current data in the main table.
• Store historical changes in a separate history table.

✅ Example: Customer table + Customer_History table.

📌 Use Case: When performance needs to be optimized by separating current and historical data.

🔹 SCD Type 6 – Hybrid (1+2+3)

• Combines Type 1, 2, and 3.
• Maintains:
  • Current value
  • Previous value
  • Full historical rows

✅ Example: Add new rows (Type 2), update a “current value” column (Type 1), and preserve the previous value (Type 3).

📌 Use Case: For complex tracking and analytics needs.

📝 Summary Table

SCD Type	Description	History Preserved?	Structure Change
Type 0	No changes	No	No
Type 1	Overwrite	No	No
Type 2	New row for every change	Yes	No
Type 3	Add new column for old value	Partial	Yes
Type 4	Historical changes in new table	Yes	Yes
Type 6	Combination of 1, 2, and 3	Yes	Yes