Dimensional Data Modeling - Slowly Changing Dimensions (SCD)

Type 1 - Overwrite Original Value

A change does not require tracking

Type 2 - Add a new record

With Type II SCD, a new version of the dimension record is created, and the existing version is marked as history.

Each row does not correspond to a different instance of an entity but a different “state”, a “snapshot” of the instance at a point in time.

To accommodate this, extra metadata is required for the dimension table, including an effective date column and an expiration date column. These columns are used to differentiate a current version from a historical version as follows:

• Effective date column stores the effective date of the version, also known as start date.
• Expiration date column stores the expiration date of the version, also known as end date.
• Expiration date value of the current version is always set to NULL or a default date value.

The user must identify the columns whose history will be tracked (by creating a new version) whenever their values are changed. These columns are known as trigger columns.

Facts or type 2 dimensions may contain effective and expiration dates. It is common to leave them as dates/timestamps for the reason their ONLY use is to filter queries in a temporal context.

Type 3 - Add a new column to store the previous value

With Type III SCD, a current value field is created to keep the current value of dimension record apart from its previous value.

To accomplish this, two columns are created for each data field:

• one storing the current value
• and one storing the previous value, respectively.