What does an SCD Type 3 previous-value column let a business report?

## The shape of a Type 3 change Slowly Changing Dimension Type 3 responds to an attribute change by **adding a column, not a row**. The dimension keeps one row per member; alongside the live attribute it carries a companion column holding the value from before the last change. ```sql CREATE TABLE dim_sales_rep ( rep_sk INTEGER PRIMARY KEY, rep_id VARCHAR(20), -- durable business key territory VARCHAR(40), -- current value prior_territory VARCHAR(40), -- value before the last realignment territory_changed_on DATE ); ``` On change, the load copies `territory` into `prior_territory`, writes the new value into `territory`, and stamps the change date. The surrogate key does not move, so every fact row already pointing at this rep continues to point at the same row and silently gains access to both values. ## Why anyone would want this The motivating case is an **organisational realignment**, not the ordinary drift of an attribute. A company redraws its sales territories on 1 January. Every historical sale was made under the old map. The VP of Sales wants two things simultaneously: the current year plan measured under the new territories, and last year's actuals *restated* under those same new territories so the comparison is apples-to-apples — and also the ability to flip back to the old map so the reps recognise their own numbers. Type 2 versioning does not give you that flip. Under Type 2, each fact is welded to the version in force when it happened; "restate all history under the new map" is not a grouping choice, it requires re-deriving keys. Under Type 3, both columns sit on every row, so restating is just choosing a column: ```sql -- last year's actuals under the new territory map SELECT d.territory, SUM(f.amount) FROM fact_sales f JOIN dim_sales_rep d ON d.rep_sk = f.rep_sk GROUP BY d.territory; -- the same actuals under the map the reps worked under SELECT d.prior_territory, SUM(f.amount) FROM fact_sales f JOIN dim_sales_rep d ON d.rep_sk = f.rep_sk GROUP BY d.prior_territory; ``` Both queries scan the same facts and produce different, equally legitimate answers. This "alternate reality" reporting is the entire point of Type 3, and it is the phrase to use in an interview. ## The three hard limits 1. **Fixed depth.** One prior column holds one prior value. You may add `territory_2_ago` if the business genuinely needs two, but the depth is baked into the schema; every extra level of history is a schema change plus a load change. It does not scale, and it is not meant to. 2. **Silent loss on the next change.** The second realignment overwrites the prior column with the first realignment's value. Whatever was there before is gone, exactly as under Type 1. 3. **No per-fact attribution.** The columns say what this member's current and previous values are. They do not say which value was in force on the date of any given fact. A sale from four years ago cannot be attributed to the territory of its own time unless that value happens to still be sitting in the prior column. Those limits are why Type 3 is a *supplement*. A Type 3 column that is asked to carry real history is a defect. ## Variants worth knowing Some designs keep an **original** value rather than the immediately-previous one — `original_territory` frozen at the member's first load, which is effectively a Type 0 column living beside a Type 1 column. Others keep a small fixed set of named alternates (current, prior, budget map) because those specific maps are the ones the business reports against. Both are still Type 3 in spirit: fixed-width alternate values on one row. Type 3 is also one of the three ingredients in the Type 6 hybrid, where it sits beside genuine Type 2 versioning to give the model both an as-was and an as-is view. ## Practical cautions Name the columns so an analyst cannot confuse them — `territory_current` / `territory_prior` beats `territory` / `territory_old`, and document what "prior" is prior *to*. Publish the change date next to the pair; without it, nobody can tell whether the prior value is a month old or four years old. And be honest in the model documentation that the column is a comparison aid: the failure mode is a report author who assumes it means history and quietly builds a trend on it.