“Slowly changing dimension type 2 changes add a new row in the dimension with the updated attribute values. This requires generalizing the primary key of the dimension beyond the natural or durable key because there will potentially be multiple rows describing each member.”Ralph Kimball, Margy Ross · The Data Warehouse Toolkit · 2013 · Kimball Group, Dimensional Modeling Techniques — Type 2: Add New Row (The Data Warehouse Toolkit, ed. a 3-a, cap. 5)
Type 1 overwrites, type 2 adds a row with a period, type 3 keeps a column with the previous value.
A customer moves from one city to another. What do you do with their row in the Customer dimension? Type 1: overwrite. Simple, no history — and the 2023 sales suddenly show up in the new city, so old reports change. Good for fixing mistakes and for attributes with no historical value. Type 2: add a new row, with a new surrogate key, valid_from and valid_to, and a flag for the current one. Old facts stay tied to the version of the time, new facts to the new version; the 2023 report stays exactly as it was. Type 3: keep a "previous value" column — a single step of history, useful when sales territories are realigned and you want both cuts at once. The choice is per attribute, not per table: the same dimension can have type 1 and type 2 columns. Two traps. First: joining facts on the natural key instead of the surrogate, which multiplies rows by the number of versions the customer has. Second: a type 1 "update" run by mistake on a type 2 dimension, which rewrites every version at once and erases the history without a single error.
Why it matters Without type 2, "sales by region in 2023" gives a different answer every year as customers move. History has to live in the dimension, not be guessed from the facts.