When attributes change
A dimension table describes entities like customers or products. Their attributes change slowly, and how you record that change is the slowly changing dimension problem. The main strategies are numbered types.
- Type 1 overwrites the old value with the new one. History is lost, but the table stays simple and small. Use it when only the current value matters.
- Type 2 keeps history by adding a new row for each change, with start and end dates and a current flag. The old row is closed and the new row opened. This preserves the past so reports can be reproduced as they looked then.
- Type 3 keeps only the previous value in an extra column, capturing one step of history cheaply.
Why Type 2 matters
Type 2 lets a fact recorded last year join to the customer attributes that were true last year, not today. Without it, a customer who moved cities would make all old sales appear in the new city.
Key idea
Type 1 overwrites and loses history, Type 2 adds dated rows to preserve full history for point in time accuracy, and Type 3 keeps just the previous value.