Advanced Concepts
Slowly Changing Dimensions (SCD) address how to handle changes to dimension attributes over time while preserving analytical capabilities and historical accuracy.
🎠Real-World Scenario
Customer Sarah Johnson moves from New York to California. How do we handle this change in our Customer dimension while maintaining accurate historical sales reporting?
SCD Types
Type 0: Retain Original
No ChangeStrategy: Never change the original value, even when source data changes
Example: Birth Date, Social Security Number, Original Account Opening Date
When to Use: Attributes that should never change
Type 1: Overwrite
OverwriteStrategy: Update the attribute value, losing historical values
Example: Current phone number, email address, current job title
When to Use: When historical values are not needed
Type 2: Add New Record
VersionStrategy: Create a new dimension record with effective dates to track history
Example: Customer addresses, employee departments, product prices
When to Use: When historical accuracy is critical for analysis
Type 3: Add New Attribute
Previous/CurrentStrategy: Keep both old and new values in separate columns
Example: Previous and current department, old and new product category
When to Use: Limited history needed, enable before/after analysis
Type 4: History Table
History TableStrategy: Store detailed change history in a separate table while keeping the main dimension row current and lightweight
Example: Customer_History table with one row per version of a customer's address and segment
When to Use: When you need rich historical tracking but want to optimize the primary dimension for fast lookups