Slowly Changing Dimensions
On 1 July PedalCity moved station 17 from the "Old Town" zone to the new "Riverside" zone. Finance then asked, "What was Old Town's revenue in June?" and the answer had dropped by a fifth overnight. Nobody had deleted anything. The dimension had simply been overwritten. Dimensions change slowly, and you must decide how the model remembers those changes.
The rule
When a dimension changes, choose: overwrite history (Type 1) or keep it (Type 2).
Type 1: overwrite. Update the attribute in place. Every past fact now appears under the new value.
UPDATE dim_station SET zone = 'Riverside' WHERE station_id = 17;This is right for corrections. If a rider's name was misspelled, nobody wants the typo preserved.
Type 2: add a new row. Close the old row and insert a new one, each with its own surrogate key (a warehouse-made ID) and a validity range:
| station_key | station_id | zone | valid_from | valid_to | is_current |
|---|---|---|---|---|---|
| 301 | 17 | Old Town | 2024-03-01 | 2026-06-30 | false |
| 588 | 17 | Riverside | 2026-07-01 | 9999-12-31 | true |
Rides from June still point to key 301, so June's revenue stays in Old Town. Rides from July onward point to key 588. The business ID station_id stays 17 in both rows, which is why the surrogate key is needed.
When loading a ride, look up the dimension row that was valid on the ride's date, not simply the current one.
Two other types are worth knowing by name. Type 0 never changes the value, such as a rider's original sign-up date. Type 3 keeps a previous_zone column next to zone, which remembers just one earlier value.
The shortcut
Ask: "Should old facts be reported under the old value?" No, because it was an error or the history does not matter → Type 1. Yes, because history must stay as it was → Type 2.
Should June's rides stay in Old Town?
Yes. Finance's past reports must not change, so use Type 2.
Drills
Answer fast — trust the rule.
A rider's surname was misspelled at sign-up and is now corrected. Which type fits?
Finance must report revenue by the zone each station belonged to at the time of the ride. Which type fits?
After station 17 changes zone under Type 2, how many rows does dim_station hold for station_id 17?
A ride on 15 June is loaded late, on 2 July. Which station key should it get?
Remember
- When a dimension changes, choose: overwrite (Type 1) or keep history (Type 2).
- Ask whether old facts should stay under the old value.
- Type 2 needs surrogate keys, validity dates, and lookups by the fact's date.