Motivation

  • A dimension does not change often. For example, suppliers do not move to a different state often. Customers rarely update demographical info. Products dont change their description often
  • Track both current and historical data over time. An aggregate table summarizing facts should continue to reflect the historical state

Type 1: Overwrite

  • No historical data

Type 2: add a new row

  • Multiple rows for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. Each row represents the full details at that version.
  • Ways to track versions by
    • a version number. Can use a new surrogate key for the new version
    • Start date + end date. The latest version use a null end date or a sentinel value
    • Single effective date + current flag
  • If there are retroactive changes made to the contents of the dimension, or if new attributes are added to the dimension which have different effective dates from those already defined, then this can result in the existing transactions needing to be updated to reflect the new situation. This can be an expensive database operation, so Type 2 SCDs are not a good choice if the dimensional model is subject to frequent change.

Type 3: previous value column

  • Only the previous history is stored
  • No new row, and thus no change to the surrogate key

Type 4: historical table

  • The dimension table does not change, i.e., it holds only the latest version
  • The history table keeps track of create date

Skipping type 5 - 7 since they seem less popular