SCD Type-2 Without the Duplicate-Current-Row Footgun

Slowly Changing Dimension (SCD) Type-2 is how we keep history in a dimension: when a
row's attributes change, we close the old version and open a new one. Done right,
exactly one row per business key is "current" at any time. Done wrong, you get
two current rows for the same key — and every fact that joins to that dimension
silently doubles.

The failure mode

A hand-rolled Type-2 loader usually does two steps:

1. Expire the existing current row when a change is detected.
2. Insert the new version as current.

The classic bug: the expire step and the insert step don't cover the same set of
rows. For example, the insert fires for both new keys and changed keys, but the
expire only fires for changed keys. If a key that already has a current row slips
into the "new" path, you insert a second current row without closing the first —
and now the key has two is_current = true records with open end-dates.

Downstream, a fact table joining on that key fans out: every matching fact row is
duplicated, and any SUM() over it inflates. The dimension looks fine at a glance;
the damage shows up as mysteriously doubled metrics.

Why it hides so well

• The duplicate rows are often identical on the business attributes, so casual inspection of the dimension reveals nothing.
• Aggregate totals can still look plausible if only a handful of keys are affected.
• It frequently appears right after a load that should have been a no-op, which makes it easy to dismiss.

How to prevent it

1. Prefer a single atomic MERGE over separate update + insert.
A MERGE keyed on the business key lets you expire-and-insert in one statement with
consistent matching logic, removing the "two steps disagree" class of bug.

2. If you keep update+insert, expire every prior current row you're about to replace — not just the subset flagged as "changed."

3. Add a uniqueness guarantee as a hard invariant. A post-load assertion that
must return zero rows:

select business_key, count(*)
from dim
where is_current = true
group by business_key
having count(*) > 1;

Wire this into your tests (e.g., a dbt unique test on the current-rows view, or a
data-quality check) so a regression fails the pipeline instead of reaching marts.

4. Make consumers defensive. Views that read the dimension can enforce one row
per key with a QUALIFY ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY
valid_from DESC) = 1, so a single upstream glitch can't fan out sales everywhere.

Takeaways

• "Current" must mean exactly one row per key — enforce it, don't assume it.
• A MERGE keyed on the business key avoids the update/insert mismatch entirely.
• Guard it with a uniqueness assertion in CI/tests, and make downstream views defensive with a de-duplicating window function.

Next in this series: fan-out joins — how a duplicated dimension row quietly inflates every metric built on top of it.