Slowly Changing Dimension type 2

Slowly Changing Dimension Type 2


In SCD type 2 a new record will be inserted in addition to the old record. In SCD type 2 historical data is maintained so you will have both the old record and the new record

Let us consider the example we used in SCD type 1

EmpKey      Name       Location
1001             Mark           London

Now, Mark moves from London to Manchester. In SCD type 2 a new record is created to the old record. So, now you have  

EmpKey      Name       Location
1001             Mark           London
2001             Mark           Manchester

If he moves from Manchester to Dublin then you have


EmpKey      Name       Location
1001             Mark           London
2001             Mark           Manchester

3001             Mark           Dubin

So, historical data is maintained

Advantages:

As SCD type 2 contains historical data the organisation can track where Mark worked before moving to Manchester

Disadvantages:

As it maintains historical data the size of the table increases gradually

Note:  SCD type 2 stores Current data + Historical data