SCD Type 6 Implementation

SCD Type 6 / Hybrid Implementation:

The Type 6 method combines the approaches of types 1, 2 and 3  (1 + 2 + 3 = 6). One possible explanation of the origin of the term was  that it was coined by Ralph  Kimball during a conversation with Stephen Pace from Kalido^{[citation needed]}.  Ralph Kimball calls this method "Unpredictable Changes with  Single-Version Overlay" in The Data Warehouse Toolkit^[1].

The Supplier table starts out with one record for our example  supplier:

Supplier_Key	Supplier_Code	Supplier_Name	Current_State	Historical_State
123	ABC	Acme Supply Co	CA	CA

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

When Acme Supply Company moves to Illinois, we add a new record, as  in Type 2 processing:

Supplier_Key	Supplier_Code	Supplier_Name	Current_State	Historical_State
123	ABC	Acme Supply Co	IL	CA
124	ABC	Acme Supply Co	IL	IL

We overwrite the Current_State information in the first record  (Supplier_Key = 123) 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.

If our example supplier company were to relocate again, we would add  another record to the Supplier dimension, and we would once again  overwrite the contents of the Current_State column:

Supplier_Key	Supplier_Code	Supplier_Name	Current_State	Historical_State
123	ABC	Acme Supply Co	NY	CA
124	ABC	Acme Supply Co	NY	IL
125	ABC	Acme Supply Co	NY	NY

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