SCD Types .... Part III

Here is the final post for the last  SCD type ...

Type 6

The Type 6 method combines the approaches of types 1, 2 and 3 (1 + 2 + 3 = 6).

The Customer table starts out with one record for our example Customer:

Customer_Key	Customer_Code	Customer_Name	Current_State	Historical_State	Start_Date	End_Date	Current_Flag
1	ABC	Oracle	NJ	CA	01-Jan-2005	31-Dec-9999	Y

The Current_State and the Historical_State are the same. The Current_Flag attribute indicates that this is the current or most recent record for this Customer.

When Oracle company moves to New Jersey, we add a new record, as in Type 2 processing:

Customer_Key	Customer_Code	Customer_Name	Current_State	Historical_State	Start_Date	End_Date	Current_Flag
1	ABC	Oracle	NJ	CA	01-Jan-2005	21-Dec-2009	N
2	ABC	Oracle	NJ	NJ	22-Dec-2009	31-Dec-9999	Y

We overwrite the Current_State information in the first record (Customer_Key = 1) with the new information, as in Type 1 processing. We create a new record to track the changes, as in Type 2 processing. And we store the history in a second State column (Historical_State), which incorporates Type 3 processing.

For example if the Customer were to relocate again, we would add another record to the Customer dimension, and we would overwrite the contents of the Current_State column:

Customer_Key	Customer_Code	Customer_Name	Current_State	Historical_State	Start_Date	End_Date	Current_Flag
1	ABC	Oracle	NY	CA	01-Jan-2005	21-Dec-2009	N
2	ABC	Oracle	NY	NJ	22-Dec-2009	03-Feb-2013	N
3	ABC	Oracle	NY	NY	04-Feb-2013	31-Dec-9999	Y

Note that, for the current record (Current_Flag = 'Y'), the Current_State and the Historical_State are always the same

SCD Part I     SCD Part 2