Slowly changing dimensions
Data Warehousing Concepts
Aaren Stubberfield
Data Scientist
The challenge
Original
ProductID
Description
Category
12345
Tesla-ModelY
electric-veh.
Update Category:
Current:
electric-veh.
New:
electric-crossover
Type I
Update value in table
Will lose any history
Original
ProductID
Description
Category
12345
Tesla-ModelY
electric-veh.
New
ProductID
Description
Category
12345
Tesla-ModelY
electric-crossover
Type II
Add a row with the updated value
The history is retained
Original
ProductID
Description
Category
12345
Tesla-ModelY
electric-veh.
New
ProductID
Description
Category
StartDate
EndDate
12345
Tesla-ModelY
electric-veh.
1970-01-01
2022-03-10
20053
Tesla-ModelY
electric-crossover
2022-03-11
2050-12-31
Type III
Add column to dimension table to track changes
Can view past and current data together
Can require reporting changes and limited tracking
Original
ProductID
Description
Category
12345
Tesla-ModelY
electric-veh.
New
ProductID
Description
Category
PastCategory
12345
Tesla-ModelY
electric-crossover
electric-veh.
Modern approach
Snapshot the whole dimension table
Use historical snapshots for historical reports
Let's practice!
Data Warehousing Concepts
Preparing Video For Download...
Preparing Video For Download...
