Pick an SCD pattern
Pick the dimension strategy that preserves historical product prices for downstream revenue analysis.
Concept
slowly-changing-dimensions
The primary modeling idea this problem reinforces.
Requirements
2
Business needs the model must satisfy.
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
Track each price change as a new dimension row with its own effective date range. That is a Kimball Type 2 slowly changing dimension.
Optimizes for
- • Historical accuracy
- • Clean star-schema joins
Trade-offs
- • More rows in the dimension
- • Needs date-validity logic
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.