Why this exists

Slowly changing dimensions are one of those patterns everyone working with analytics data has implemented, and almost everyone has gotten subtly wrong at least once. Type 2 in particular - versioning a row instead of overwriting it, so a customer's address or an account's plan tier can be reconstructed as of any past date - has a specific boundary condition that's easy to get backwards, and a bug there doesn't announce itself. It hides inside otherwise-correct-looking data.

This is what that looked like in practice: a dimension table that passed every row-count check, every null check, every schema test, and was still quietly doubling metrics on a predictable subset of days.

The starting point

A monthly reconciliation between a revenue dashboard and the source billing system turned up a small, consistent discrepancy - not huge, not on every day, but real. Aggregate totals for the month were slightly high. Spot-checking individual days found most of them matched exactly. A handful didn't.

The dimension underneath the fact table - an account dimension tracking plan tier, status, and a few billing attributes - was built as Type 2: every change to a tracked column closed out the current row with an effective_end date and inserted a new row starting on the date of the change. That part had been tested when it was built and had been running for months without an obvious failure.

What was actually happening

The mismatched days weren't random. Every one of them was a day on which at least one account had a tracked attribute change - and the dimension had recorded that change by closing the old version and opening the new one on the same date, both boundaries inclusive.

  • The outgoing version's effective_end was set to the change date itself, not the day before it.
  • The incoming version's effective_start was also set to the change date - so for that one day, both versions of the same account satisfied fact_date BETWEEN effective_start AND effective_end.
  • Every fact row for that account on that specific day joined to both dimension versions, doubling that row in the join output.
  • On any day with no dimension changes, there was exactly one valid version to join against, so the totals were correct - which is why this didn't show up as a constant, obvious error.

The dimension table itself wasn't invalid by its own constraints - there were no duplicate natural keys, no gaps in coverage, nothing a typical dbt schema test would flag. The problem was one day of overlap between two otherwise-correct adjacent versions, and it only existed on the specific dates where a change actually happened.

The decision log

Finding a bug that only shows up on a subset of days requires ruling out the more common causes first, because the intermittent pattern looks a lot like other, more familiar failure modes.

Decision 1: Rule out fact-table duplication before touching the dimension

An intermittent doubling looks like a duplicate-load problem in the fact table first - a job that reran, an idempotency key that didn't hold. Checking the fact table's own row counts and load history against its source ruled that out early and redirected the search toward the join itself.

Decision 2: Correlate the bad dates against dimension activity, not fact volume

The mismatched dates didn't correlate with unusually high transaction volume, which ruled out a load-size or batch-processing explanation. They did correlate, exactly, with dates that had at least one row in the dimension's own change history - which pointed straight at the dimension's versioning logic instead of anything on the fact side.

Decision 3: Check the boundary condition directly, not the row counts

With the dimension's change-tracking logic in view, the actual check was simple: for any account with more than one version, did the outgoing version's effective_end equal the incoming version's effective_start? It did, every time - which is the one-line signature of this exact bug, and faster to confirm directly than to keep reasoning about it indirectly through fact table symptoms.

Decision 4: Fix the boundary, then backfill only the affected windows

The fix itself was a one-column change - closing the outgoing version the day before the new version's start date instead of on the same date, so adjacent windows touch without overlapping. The more deliberate part was backfilling only the historical partitions that contained a change event, rather than reprocessing the full table, and reconciling each backfilled month against source before calling it resolved.

Before and after

Before

  • Outgoing and incoming versions sharing one inclusive boundary date
  • Fact-to-dimension joins fanning out on any date with a tracked change
  • Reported totals correct on quiet days, silently inflated on change days
  • No schema test catching it, since the dimension table was internally valid

After

  • Adjacent version windows touching with no overlapping date
  • Historical partitions with known changes backfilled
  • Each backfilled month reconciled against source before close-out
  • A boundary check added alongside the existing schema tests

What this actually took

The fix was a single date-arithmetic correction. Finding it took longer than that, because the symptom - a small, inconsistent discrepancy - fit several more common explanations before it fit the actual one. What made the difference was correlating the bad dates against the dimension's own change history instead of staying focused on the fact table, which is where the investigation naturally starts by default.

The broader point: a Type 2 dimension can pass every standard data-quality check and still be wrong, because the bug isn't a constraint violation - it's a relationship between two otherwise valid rows. That's not something a not-null or uniqueness test was ever going to catch. Boundary bugs like this one are exactly what the Analytics & Data practice exists to catch before they reach a dashboard.

Frequently asked questions

How would I know if this is happening in my own SCD Type 2 tables?

For any natural key with more than one version, compare the outgoing row's effective_end against the next row's effective_start. If they're ever equal instead of one day apart, adjacent versions are overlapping on that boundary date, and any fact table joined with an inclusive BETWEEN on both ends will double-count rows on that date.

Why doesn't a uniqueness test catch this?

Because nothing about the dimension table itself is duplicated - each row has a distinct natural key plus effective date, so a standard uniqueness or not-null test passes cleanly. The bug is in the relationship between two valid rows, not in either row on its own, which is why it needs a boundary-specific check rather than a general schema test.

Is making the end date exclusive always the right fix?

The fix depends on which side of the join is inclusive. What matters is that the dimension's versioning logic and the fact table's join condition agree on exactly one convention - either the end date is exclusive, or the start date is, but not neither and not both. The bug here was two boundaries that were both inclusive, which is the version of this mistake worth checking for first since it's the most common one.

Not sure whether your own dimension history holds up?

A short conversation is usually enough to tell whether there's a quick answer or something deeper worth tracing.