SLOWLY CHANGING DIMENSION (SCD) TYPES

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