SCD - Type 6

SCD Type 6 is a hybrid approach that combines the features of SCD Types 1, 2, and 3. It maintains the current attribute value, the previous attribute value, and a full history of changes with effective dates. Using the "Product" dimension example with the mobile product category change:

Recall that the Product dimension table contains the following columns:

1. Product_ID

2. Product_Name

3. Category

4. Price

For a Type 6 SCD, we will need to add the following columns to the table:

5. Previous_Category

6. Valid_From

7. Valid_To

8. Is_Current

Now, imagine that the store decides to re-categorize the "Smartphone" product from the "Electronics" category to the "Mobile Devices" category.

With a Type 6 SCD approach, the store will insert a new row in the "Product" dimension table with the updated category and set "Is_Current" to 'Y' (Yes) for the new row and 'N' (No) for the old row. The new row will have the "Previous_Category" column set to the old category value.

Before the change, the Product dimension table looks like this:

Product_ID	Product_Name	Category	Price	Previous_Category	Valid_From	Valid_To	Is_Current
1	Smartphone	Electronics	800	NULL	2020-01-01	NULL	Y

After the change, the Product dimension table will look like this:

Product_ID	Product_Name	Category	Price	Previous_Category	Valid_From	Valid_To	Is_Current
1	Smartphone	Electronics	800	NULL	2020-01-01	2023-01-01	N
2	Smartphone	Mobile Devices	800	Electronics	2023-01-01	NULL	Y

With a Type 6 SCD approach, the table now reflects the updated category for the "Smartphone" product, retains the previous category, and maintains a full history of changes with effective dates. This approach provides the benefits of SCD Types 1, 2, and 3 while optimizing the storage and query performance to some extent. However, it still requires managing additional columns and more complex logic for managing the Is_Current flag and Valid_From/Valid_To dates.