> For the complete documentation index, see [llms.txt](https://gchandra.gitbook.io/data-warehousing/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://gchandra.gitbook.io/data-warehousing/data-warehousing-concepts/types-of-dimensions/slowly-changing-dimensions/scd-type-6.md).

# 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.