What is the difference between an SCD Type 1 and an SCD Type 2 change in a dimension table?

## What "slowly changing" means A dimension table describes the things a business measures *by*: customer, product, store, employee, sales rep. Its rows are mostly stable, but they are not immutable — a customer relocates, a product is moved to a new category, a rep is reassigned a territory. **Slowly Changing Dimension (SCD)** is the numbered taxonomy of what the warehouse does to the dimension table when one of those source attributes changes. Types 1 and 2 are the two poles of that taxonomy and the two an interviewer will always ask about. The decision is not a technical preference. It is a statement about what the business means by a historical number. ## Type 1 — overwrite The existing dimension row is updated in place. There is exactly one row per member, and it always shows the latest values. ```sql UPDATE dim_customer SET region = 'Oregon' WHERE cust_id = '4711'; ``` The consequence is that *all* history re-computes under the new value. If customer 4711 lived in Texas for three years and then moved to Oregon, a sales-by-region report now credits every one of those Texas years to Oregon. Sometimes that is exactly what you want — the value was simply wrong (a misspelled name, a mis-keyed category) or the label is cosmetic and nobody reports on it historically. Sometimes it is a silent falsification that finance will find out about at quarter close. Type 1 is the cheap option: no row growth, no key churn, no version bookkeeping, and current-state queries need no filters. ## Type 2 — a new row per change Instead of updating, the load retires the current row for that member and inserts a new one carrying the new attribute values. The member now has several rows in the dimension — each one a **version** of that member during some stretch of time. Versions are distinguished by the dimension's own surrogate key; the durable business key (`cust_id` above) repeats across all versions of the same member, which is how you know they are the same customer. The mechanism that makes history work is simple and worth saying out loud in an interview: **the fact table stores the surrogate key of the version that was current when the fact was loaded.** A sale from 2023 holds the key of the Texas version; a sale from 2025 holds the key of the Oregon version. Nothing is recomputed, nothing is re-derived — the join just lands on the right version. ```sql SELECT d.region, SUM(f.amount) AS sales FROM fact_sales f JOIN dim_customer d ON d.cust_sk = f.cust_sk GROUP BY d.region; ``` That query is "as it was" reporting, and it needed no special syntax. ## What Type 2 costs - **Row growth.** A dimension with volatile attributes multiplies: 40 million customers times a weekly-changing flag is not a dimension, it is a fact table. - **Counting.** `COUNT(*)` on the dimension is no longer a customer count; it is a version count. Count distinct on the durable business key, or restrict to current rows. - **Change detection.** The load has to compare incoming attribute values against the current version to know whether to version at all. - **Analyst confusion.** Someone will eventually ask why the customer appears twice. ## Choosing between them Ask one question, per attribute rather than per table: *does any report need the value as it was at the time of the fact?* If yes, Type 2. If the change is a correction — the old value was never true — Type 1, even inside a dimension that is Type 2 for other columns. Mixed policies within one dimension are normal and expected. ## What you cannot fix later Type 1 is destructive. Once the source has moved on and you have overwritten, the old value is gone from both sides and no amount of later engineering recovers it. Switching an attribute from Type 1 to Type 2 gives you history *from the cutover date forward only*. The practical hedge is to land raw source snapshots in an untransformed ingest layer, so a Type 2 dimension can be rebuilt retroactively if the requirement shows up later. ## Mistakes that get candidates marked down Calling a design Type 2 while the load still overwrites; updating already-loaded fact rows to point at the newest version (this destroys the entire point of versioning and turns your Type 2 dimension into an expensive Type 1); treating dimension rows as members when counting; and applying Type 2 to a high-churn attribute without considering the row explosion.