How do you add a tracked attribute to a live SCD Type 2 dimension without exploding its history?

## Why the change is disruptive at all A Type 2 load decides whether to version a member by comparing a stored hash of the tracked attributes against a freshly computed one. That stored hash is a function of a specific column list, in a specific order, with a specific NULL sentinel and delimiter — but the list itself is not stored anywhere. It lives only in the transformation code. So the moment you add a column to the hash expression, every stored hash in the dimension was computed under the old definition and every incoming hash under the new one. They differ for essentially every row. The next run closes every current version and inserts a replacement, producing a version wave across the entire dimension dated on deployment day, with no business change behind any of it. On a large dimension that is an expensive write, a distorted history, and a pile of confused questions from analysts who see every customer "change" on the same date. The same trap fires for changes that look purely cosmetic: reordering the columns in the expression, switching the delimiter, adding a `trim()`, or changing a cast. Any edit to the hash input is a breaking change to comparability. ## Option 1 — backfill the hash under the new definition The cheapest and usually correct answer. In the same deployment that changes the expression, run a one-time update that recomputes the hash for every current row using the new column list and the values already on that row: ```sql update dim_customer set hashdiff = md5( coalesce(cast(customer_name as varchar(4000)), '~') || '|' || coalesce(cast(city as varchar(4000)), '~') || '|' || coalesce(cast(tier as varchar(4000)), '~') || '|' || coalesce(cast(segment as varchar(4000)), '~') -- newly tracked ) where is_current = 1; ``` After this, the stored and computed hashes agree again for unchanged members, and the next run versions only members whose attributes genuinely differ. Two details matter: run the backfill and the code change atomically enough that no load executes between them, and use the values on the dimension row rather than re-reading the source, so you do not accidentally absorb a real pending change into the backfill and lose its version boundary. ## Option 2 — accept one deliberate version wave If the dimension is small, or if the new attribute's introduction is itself a meaningful event, you can let the wave happen — but only deliberately: announce it, date it at a known boundary, and mark the resulting versions (a load-reason or change-reason column) so nobody mistakes deployment noise for business change. This is rarely worth it on a large dimension and never worth it as an accident. ## Option 3 — replay history from source If the source can supply the new attribute's full change history, you can rebuild the dimension so the new column has real history rather than starting today. This is the only option that produces genuinely correct historical values for the new attribute — and it is the most expensive. A rebuild reassigns surrogate keys, so every fact table, extract and cached report holding those keys must be remapped or reloaded. Reserve it for cases where the historical values of the new attribute actually drive decisions. ## The honest limitation: no history before today Whatever you choose, be explicit that a newly tracked attribute has no past. Existing versions were written without it. You have two defensible treatments and must pick one and publish it: - **Stamp the current value onto all existing versions.** Queries never hit NULL, but the dimension now asserts something false — that the member always had today's value. That is exactly the retroactive restatement Type 2 exists to prevent. - **Leave it NULL on versions that predate the change.** Honest and self-documenting: a NULL means "not tracked then". Consumers must handle it, which is a cost, but the model does not lie. The second is usually right, and the deciding factor is whether downstream consumers can tolerate NULL. ## Governance, which is the real answer at this level The recurring lesson is that the tracked set is a contract, not a detail. Treat it accordingly: keep the column list in code next to the dimension so a reviewer sees it in the diff; require the hash backfill in the same change that edits the expression, ideally as a checklist item in the review; assert after deployment that the number of versions created is in a plausible range, so an accidental full-table wave fails the run instead of landing; and remember that removing a column from the tracked set is the same breaking change in reverse — plus a policy decision that changes to that attribute will no longer be recorded at all.