Dimensions data change slowly over time, rather than changing on regular basis.
Slowly changing dimension (SCD) is the concept which describes how we store dimensional data (Historical or current) in warehouse.
Below are the types of SCD:
Type 0 – In this the old dimensional data will be persisted and remains unchanged.
Customer_ID | Customer_Name | Customer_Type |
1 | Cust_1 | Corporate |
Type 1 – In this the old dimensional data will be overwritten by the new data and history will not be preserved.
Before change
Customer_ID | Customer_Name | Customer_Type |
1 | Cust_1 | Corporate |
After change
Customer_ID | Customer_Name | Customer_Type |
1 | Cust_1 | Retail |
Type 2 – In this the new record will be inserted for any change in the source and history will be preserved by creating a new additional record
Before the change:
Customer_ID | Customer_Name | Customer_Type | Start_Date | End_Date | Current_Flag |
1 | Cust_1 | Corporate | 22-07-2010 | 31-12-9999 | Y |
After the change:
Customer_ID | Customer_Name | Customer_Type | Start_Date | End_Date | Current_Flag |
1 | Cust_1 | Corporate | 22-07-2010 | 17-05-2012 | N |
2 | Cust_1 | Retail | 18-05-2012 | 31-12-9999 | Y |
Type 3 – This is similar to Type 2 SCD the only difference being instead of adding a new row we will be adding a new column.
Unlike Type 2 SCD this will only store partial history as shown in below table.
Before the change:
Customer_ID | Customer_Name | Current_Type | Previous_Type |
1 | Cust_1 | Corporate | Corporate |
After the change:
Customer_ID | Customer_Name | Current_Type | Previous_Type |
1 | Cust_1 | Retail | Corporate |
Type 4 – This is combination of SCD Type 1 and Type 2 where we create 2 dimension table one with the current data and other which stores historical data similar to type 2 dimension.
Current table:
Customer_ID | Customer_Name | Customer_Type |
1 | Cust_1 | Corporate |
Historical table:
Customer_ID | Customer_Name | Customer_Type | Start_Date | End_Date |
1 | Cust_1 | Retail | 01-01-2010 | 21-07-2010 |
1 | Cust_1 | Oher | 22-07-2010 | 17-05-2012 |
1 | Cust_1 | Corporate | 18-05-2012 | 31-12-9999 |
Type 6 – It is a Combination of SCD type 1,2,3 (1+2+3=6) which stores historical data in rows and columns both as shown in below table.
Customer_ID | Customer_Name | Current_Type | Historical_Type | Start_Date | End_Date | Current_Flag |
1 | Cust_1 | Corporate | Retail | 01-01-2010 | 21-07-2010 | N |
2 | Cust_1 | Corporate | Other | 22-07-2010 | 17-05-2012 | N |
3 | Cust_1 | Corporate | Corporate | 18-05-2012 | 31-12-9999 | Y |
