Type 3: previous value
Recognize when a previous-value column is enough and Type 2 is unnecessary.
Concept
slowly-changing-dimensions
The primary modeling idea this problem reinforces.
Requirements
2
Business needs the model must satisfy.
Sales operations realigned territories for the new fiscal year. Executives only ask to compare current territory with the immediately previous territory for the next two quarterly reviews. They do not need unlimited territory history.
Type 3 is narrow but useful: it supports one prior value without the row-versioning cost of Type 2.
- Pick the SCD strategy for current-versus-previous territory analysis.
- Name what would be lost compared with Type 2.
- The answer chooses a previous_territory attribute on the current row.
- The rationale explains that unlimited territory history would require Type 2 or an assignment fact.
- Count how many prior values the business asked for.
- Type 3 is a current-row design with one alternate or previous attribute.
- Building a full Type 2 territory history when only one previous value is needed.
- Overwriting territory without keeping the prior value requested by executives.
- Adding many previous_territory_n columns and creating an unbounded Type 3 pattern.
Type 3 previous territory column
Add a previous_territory attribute when only the immediately prior value is needed. A Kimball Type 3 slowly changing dimension.
Optimizes for
- • Simple current-versus-previous comparison
- • Low row churn
- • Easy executive reporting
Trade-offs
- • Cannot answer unlimited territory-history questions
- • Needs Type 2 or an assignment fact if prior history becomes more than one value deep
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.