SCD (Slowly Changing Dimensions)

Slowly Changing Dimensions (SCD) are methods used in data warehouses to manage changes in data that happen slowly over time. For example, dimension tables store descriptive details like customer names, product categories, or employee roles, which can gradually change over time. SCD ensures these changes are managed effectively, preserving historical information and enabling accurate trend analysis.

There are different types of SCDs, each suited for specific scenarios:

SCD Type 0: Retain Original Data

In Type 0, no changes are made to the original data, even when updates occur.

Example

Customer ID	Name	Address
1	Alice	New York

Even if Alice moves to Boston, the table still shows her address as New York.
Use Case: Historical consistency is prioritized, and updates are ignored.

SCD Type 1: Overwrite Data

In Type 1, old data is replaced with new data, and no history is preserved.

Before Update:

Customer ID	Name	Address
1	Alice	New York

After Update (Alice moves to Boston):

Customer ID	Name	Address
1	Alice	New York

Use Case: Only current data matters, such as updating product prices or customer details.

SCD Type 2: Add New Row

In Type 2, a new row is added for every change, and the original data is retained with a history of changes.

Before Update:

Customer ID	Name	Address	Start Date	End Date
1	Alice	New York	2023-01-01	NULL

After Update (Alice moves to Boston):

Customer ID	Name	Address	Start Date	End Date
1	Alice	New York	2023-01-01	2023-06-30
1	Alice	Boston	2023-07-01	NULL

Use Case: Track a complete history of changes, such as customer address history or employee roles.

SCD Type 3: Add New Column

In Type 3, a new column is added to store the previous value while keeping the current value.

Example Table

Customer ID	Name	Current Address	Previous Address
1	Alice	Boston	New York

After Update (Alice moves to London from Boston):

Customer ID	Name	Current Address	Previous Address
1	Alice	London	Boston

If Alice moves again, the current address updates, and the previous address becomes Boston.
Use Case: Track the most recent change without maintaining a full history.

SCD Type 4: Separate History Table

In Type 4, the main table stores only the current data, while a separate table records all historical changes.

Main Table (Current Data):

Customer ID	Name	Current Address
1	Alice	Boston

History Table:

Customer ID	Address	Start Date	End Date
1	New York	2023-01-01	2023-06-30
1	Boston	2023-07-01	NULL

Use Case: Useful when the main table should remain lightweight, and detailed history is kept in a separate table.

SCD Type 6: Hybrid Approach

Type 6 combines elements of Types 1, 2, and 3. It stores the current value, a previous value column, and maintains full historical rows.

Example Table:

CustomerID	Name	Current Address	Previous Address	StartDate	End Date
1	Alice	Boston	New York	2023-07-01	NULL

After Update (Alice moves to London from Boston ):

Customer ID	Name	Current Address	Previous Address	Start Date	End Date
1	Alice	London	Boston	2024-01-01	NULL
1	Alice	Boston	New York	2023-07-01	2023-12-31
1	Alice	New York	NULL	2023-01-01	2023-06-30

Use Case: Ideal for scenarios requiring a mix of current state, recent changes, and full historical tracking.

In the next section, we will look at stage tables, which are used to temporarily store, clean, and transform raw data before loading it into the DWH (fact or dimension tables). Stage tables act as a temporary area to handle raw or intermediate data before it moves into the main DWH.