Slowly Changing Dimension Types Quiz

Slowly Changing Dimension (SCD) Types

• When a salesperson relocates to a new region, a new version of the salesperson is created to preserve historical facts associated with the former region.
• The dimension table must store versions of salespeople and their associated regions, along with start and end date values to define time validity.
• Current versions may have an empty end date (or 12/31/9999), indicating that the row is the current version.
• The table must define a surrogate key because the business key (employee ID) won't be unique.

Type 1 SCD

• Always reflects the latest values, overwriting dimension table data when changes in source data are detected.
• Common for columns storing supplementary values, like customer email addresses or phone numbers.
• Updates the customer row with new values when a change is detected, as if the customer always had this contact information.

Type 2 SCD

• Supports versioning of dimension members, often requiring a data warehouse load process to detect and manage changes.
• Uses a surrogate key to provide a unique reference to a version of the dimension member.
• Includes columns defining date range validity (e.g., StartDate and EndDate) and possibly a flag column (e.g., IsCurrent) to filter by current dimension members.

Type 3 SCD

• Supports storing two versions of a dimension member as separate columns.
• Tracks one key instance of history, using additional columns rather than storing additional rows.
• Often used in combination with Type 1 or Type 2 members.

Type 6 SCD

• Combines Type 1, 2, and 3, storing the current value in all versions of an entity.
• Allows for easy reporting on current or historical values.
• Example: splitting the Region column into CurrentRegion and HistoricalRegion, where CurrentRegion shows the latest value and HistoricalRegion shows the region valid between StartDate and EndDate.