SCD (Slowly changing dimensions) Types.

In Data Warehouse there are dimension tables which contains static data which can slowly change with time, for example change in the name of supplier for store data, change in the name of office location.

IDCustomer NameLocation
1JohnGermany
2GeorgIndia

In above table the customer John changed his address from Germany to US.

SCD Type 0

The record will remain same and there will be no change in the location, all the records for the John will be having location as Germany.

SCD Type 1

The record will update location for John and all records related to John will update the location.

IDCustomer NameLocation
1JohnUS
2GeorgIndia

SCD Type 2

In SCD Type 2, we will maintain history of the records, for active and previous records there will be flag column which indicate active state of the record, and there will be track of the starting and ending dates of the records as show in table

It create new additional row to store updated record.

Identification IDIDCustomer NameLocationFlagStarting dateEnding date
11JohnGermany001-02-199011-08-2000
21JohnUS111-08-2000NULL
32GeorgIndia114-08-1998NULL

SCD Type 2 is the best method to maintain the history and current records.

SCD Type 3

In this type creates new column to store previous value.

IDCustomer NameLocationPrevious Location
1JohnUSGermany
2GeorgIndiaIndia

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top