Slowly Changing Dimension

Definition

Change of dimension over a course of time rather changing on regular scheduled time.

Why needed ?

In Datawarehouse world, it is sometimes critical to track the dimension change with course of time. This helps us to better track of data and also create efficient products depending on the use-cases.

Types of SCD

SCD Type 0

  • It can be considered as new insert into table

SCD Type 1

  • Overwrite old value
  • Example :

SCD Type 2

  • Add new record
  • We can capture attribute change by adding a new column as surrogate key
  • We make sure that there is only 1 record with surrogate key (Flag as Y)
  • Though it captures historical data , but it might result into expensive operation in database side.
  • Example : Flag as surrogate key

SCD Type 3

  • Adding new column
  • Limitation of this approach is it will only have current/previous not entire history
  • Example :

SCD Type 4

  • Use History table
  • In this approach , separate history table is created to track changees.
  • Main table will only have latest data
  • Pros : Faster response to queries which requires latest data . Ease to manage and code.
  • Cons : Sometimes aggregation / join between active and historical data might take time and get complex
  • Example :

SCD Type 6

  • Combination of Type (1+2+3)
  • Pros : Every data change capture is present in same table
  • Cons : Complex , hard to manage , huge table