A customer moves cities. A product gets reclassified into a different category. Slowly changing dimensions type 1 vs type 2 is the decision about what your warehouse does when that happens: overwrite the old value, or keep a record of what it used to be.
Type 1: overwrite and move on
Type 1 simply updates the dimension row in place. The old value is gone. This is the simpler option, and it’s the right one when history doesn’t matter, correcting a typo in a customer’s name doesn’t need a permanent record of the misspelling.
Type 2: keep every version
Type 2 inserts a new row for the changed dimension instead of overwriting the old one, typically with effective-date columns marking when each version was valid. This preserves history: a sales report from last year can still show a customer’s old city, matching what was actually true at the time the sale happened.
Why this choice actually matters for reporting
If a customer’s region changes and the dimension is Type 1, every historical sale tied to that customer silently gets reassigned to the new region in any report run after the change. That can quietly rewrite historical numbers in a way that looks like the past changed, when really only the dimension did. Type 2 avoids this by keeping the old region attached to sales that happened while it was still accurate.
A real example: where this tradeoff would apply
The Olist e-commerce analytics engineering project models customer and seller dimensions around a fact table of 99,441 real orders. A seller’s registered category or location changing over time is exactly the kind of scenario slowly changing dimensions type 1 vs type 2 addresses. Type 1 would keep the model simple, at the cost of historical orders appearing to have shipped from a seller’s current location rather than where they actually shipped from at the time.
Type 2’s real cost
Preserving history isn’t free. Type 2 dimensions grow over time as changes accumulate, and every join against that dimension needs to account for effective dates, not just a simple key match, otherwise a query can accidentally join a fact row to the wrong historical version of the dimension.
A quick checklist
- Does this dimension attribute change in a way that historical reports need to reflect accurately?
- Would overwriting the old value silently distort past reports if this attribute changed?
- Is your team prepared to handle effective-date logic correctly in every join against this dimension?
- Is Type 1’s simplicity actually worth the historical accuracy it gives up for this specific attribute?
FAQ
Can a warehouse use Type 1 for some dimensions and Type 2 for others?
Yes, and this is common. A customer’s name might be Type 1, while their region or tier might be Type 2, depending on whether history matters for that specific attribute.
Is Type 2 always the safer choice?
Not automatically. It adds real complexity and storage growth, which isn’t worth it for attributes where nobody will ever need the historical value.
Does dbt support building Type 2 dimensions?
Yes, snapshot features in dbt are built specifically for tracking Type 2 style historical changes over time.
