Type 3: Add New Attribute (Limited History)
With Type 3, instead of adding a new row, a new column is added to the existing row to track the previous value. The primary column is then updated with the current value, similar to a Type 1 overwrite.
This technique creates an "alternate reality" for reporting. It allows business users to group and filter fact data by either the current value or the previous value. Because it only preserves a single step of history rather than a complete timeline, this technique is used relatively infrequently.
Example (Type 3)
A sales representative is reassigned from the East district to the North district. district is managed using a Type 3 approach so the business can compare performance using both the old and new organizational structures.
Before the change:
| employee_key | employee_id | name | current_district | previous_district (Type 3) |
|---|---|---|---|---|
| 505 | E-99 | Bob | East | NULL |
After the change:
| employee_key | employee_id | name | current_district | previous_district (Type 3) |
|---|---|---|---|---|
| 505 | E-99 | Bob | North | East |
Notice that no new rows are created and the employee_key remains unchanged. All existing fact records still point to this single row. Analysts can now run reports showing sales grouped by the new North district or switch to the alternate reality to see the same sales grouped by the old East district.
When to use Type 3
- Classification Changes: When a business classification or grouping changes and the business needs to analyze facts using both the current and previous classifications.
- Dual Perspectives, Not Timelines: When you need to preserve a limited number of versions of an attribute (typically the current and previous value), rather than maintaining a complete history of every change. If you need point-in-time accuracy and the ability to determine which value was valid at any past date, use Type 2.