Choose the right SCD pattern for price history
Pick the dimension strategy that preserves historical product prices for downstream revenue analysis.
Concept
slowly-changing-dimensions
The primary modeling idea this problem reinforces.
Objectives
2
Concrete things this problem is meant to teach.
A merchandising team changes product prices each quarter. Analysts need to report historical revenue using the price customers actually paid at the time of purchase.
This is one of the first places dimensional modeling either preserves truth or quietly destroys it. If you choose the wrong change-tracking pattern, every downstream revenue trend becomes harder to trust.
- Preserve historical prices for each product.
- Keep fact rows analyzable against the price that was valid at order time.
- Choose the option that preserves historical attribute versions instead of overwriting them.
- Be able to explain how analysts would recover the price that was valid when the order happened.
- Ask whether the old attribute value must remain queryable later.
- Choosing the current-state overwrite pattern because it looks simpler operationally.
- Assuming fact tables alone can recover historical dimensional context without explicit versioning.
Use a Type 2 product dimension
Following Kimball Type 2 slowly changing dimension guidance, track price changes as new dimension rows with effective date ranges.
Optimizes for
- • Historical accuracy
- • Clean star-schema joins
Trade-offs
- • More rows in the dimension
- • Needs date-validity logic