Slowly Changing Dimension (SCD)

In dimensional data warehouse, most dimensions change over time. When a dimension changes, e.g. when a product get renamed or product get a new category. Therefore we must maintain the dimension history to ensure that old sales orders will still be able to reference after the changes. In order to achieve this, the slowly changing dimension or in short SCD comes into the picture.

SCD is a technique for implementing history maintenance in dimensional data warehouse. In general, there are three types of SCD : SCD Type 1, SCD Type 2 and SCD Type 3. Let's define the types of SCD.

a) SCD Type 1

• Updates dimension records by overwriting the existing data.
• Used when no history of the records is maintained in the table like situation where we directly overwrite the incorrect data.

b) SCD Type 2

• Doesn't delete or modify any existing data.
• Whenever there is a change in source, a newer version of records will be created. This means that it will keep a full history of dimension data in the table.

c) SCD Type 3

• Maintains just previous and current data.
• It keep history by adding more than one column to records to maintain its history. For example, in customer dimension table, we will have two extra columns: Current_Address and Previous_Address.
• Used to maintain a limited history where we have a database space constraint.