Logic behind implementing SCD 2

Lets talk about the logic behind SCD type 2 today.

We know that SCD 2 is about preserving all the changes in the dimension records. Let us see the logic behind how we can implement it.

NOTE: The steps below assume SCD 2 having a Begin_date and End_date and a Current_Flag column

1. Check if the incoming row is already present in the target table (dimension) using the source primary key
2. If it doesn’t exist in the target dimension
   1. Generate a surrogate key
   2. Enter source record’s date as the Begin_date
   3. Enter the default end date (which could be 31/12/2099) as the End_date
   4. If you have a Current_flag column, set it as ‘Y’ or ’1′ (or whatever you want)
   5. Insert into the dimension
3. If the row exists in the target
   1. Check if the incoming and target current record are different (at least for one chosen attribute)
   2. If they are same, do nothing
   3. If they are different, do the following
      1. For the record in the target table, change (update) the End_date to source record’s date and set the Current_flag to ‘N’ or ’0′ or whatever
      2. Take the incoming record, generate a surrogate key, enter source record’s date as the Begin_date and the default date as the End_date. Also set the Current_flag to ‘Y’ or ’1′. Insert into the dimension

NOTE: The End_date of the previous record and the Begin_date of the current record are assumed to be the same. But some people prefer them to be different dates ie) End_date is 1 day lesser than the next records Current_date.