Fact Grain Multiplication

A finance dashboard shows 104,300 customers against a customer table holding 100,000 rows. Revenue on the same page is about 5% high. The pipeline is green, the output is stable across re-runs, and the join reads as obviously correct: fact.customer_id = dim.customer_id.

The dimension is Type 2, so it holds one row per customer per version, and the join on the key alone duplicates each fact row once per version. This is an anti-pattern with no error message: the query is valid, the result is deterministic, and every freshness and volume check passes because the pipeline produced exactly what it was asked to produce.

The characteristic tell is that different measures inflate by different percentages. Customers who change segment, plan or address are the active ones, so the error correlates with activity: counts might be 4% high and revenue 7%. Two numbers wrong by two amounts reads as two bugs, which sends the investigation in two wrong directions.

Why it matters

The error is small, plausible and durable. A 4% overstatement survives review in a way that a doubling never would, so it reaches board packs, regulatory returns and commission calculations. It also appears months after the change that caused it, because a freshly loaded Type 2 dimension has one version per member and behaves identically to Type 1 until members start changing.

Implementation patterns

• Resolve the surrogate key at load time. The fact stores the specific dimension version current when the event happened; the query joins on the surrogate and needs no date logic. This is the design to aim for, because it puts the risk in one loader rather than in every downstream query.
• Where the fact carries only the business key, the predicate must be a range: fact_date >= valid_from AND fact_date < valid_to, with valid_to exclusive so an event on a change date matches exactly one version.
• Represent the open end as a high sentinel date, conventionally 9999-12-31, never NULL. A range predicate against NULL is unknown rather than true and silently drops every current row, which inverts the error into a large under-count.
• Assert row-count preservation in the build: the row count after dimension joins must equal the row count of the fact table. One test per fact model catches the entire class.
• Expose only the date-correct join in the semantic layer, so ad-hoc queries cannot reach the raw dimension without the predicate.

Industry example

This is the standard failure of the Type 2 pattern as set out in Kimball and Ross's dimensional modelling work from 1996 onward, and it is why warehouse teams treat the surrogate-key lookup during fact load as non-negotiable rather than as an optimisation. The pattern recurs unchanged in modern transformation frameworks: the fan-out test exists as a first-class test type in every mature dbt project precisely because this is the defect it was written to catch.

Failure scenarios

• The DISTINCT repair. Someone wraps the count in DISTINCT, the tile agrees with the customer table, and revenue stays wrong — now with the evidence removed.
• The NULL end date. A team adds the range predicate correctly and under-counts by 90%, concludes the range approach is broken, and reverts to the key-only join.
• Inclusive valid_to. Events landing exactly on a change date match both versions, producing a small residual error that survives the fix and is attributed to rounding.
• A dimension converted from Type 1 to Type 2 without auditing consumers, which turns every existing correct query into an incorrect one on the day of the change.

Trade-offs

Type 2 buys the ability to report what was true at a point in time, which some questions genuinely require and some regulators require in writing. It pays in surface area: every join to that dimension becomes a place this failure can occur, and the number of such places grows with every model an analyst adds. The storage is irrelevant — a few thousand extra rows — and the cost is entirely in correctness risk distributed across queries nobody reviews.

When not to use it

Keep Type 1 as the default until somebody actually asks a historical question. Most attribute changes are corrections rather than history, and a corrected postcode should not fork a dimension member.

It flips back even when history is wanted, if the historical question is asked by one report once a quarter: a periodic snapshot table answers that at a fraction of the ongoing risk and leaves the dimension simple. Reach for Type 2 when many queries need point-in-time correctness, and when you do, pair it with the surrogate-key lookup and the row-count assertion in the same change — the pattern without those two controls is worse than not having history at all, because the numbers are wrong rather than absent.

Interview question

Q: A dashboard tile reports 4% more customers than the customer table contains, revenue is 7% high, the pipeline is green and re-running changes nothing. Diagnose it, then tell me what single automated check would have stopped it reaching the dashboard.

What a strong answer covers: comparing distinct business keys against joined row count as the first move · the Type 2 fan-out mechanism · why the two percentages differ, and why that is the tell rather than a second bug · why the green pipeline is the misleading signal · the surrogate-key resolution at load as the correct fix and the date-range predicate as the fallback · the exclusive valid_to and sentinel-date details that decide whether the fallback works · and the row-count preservation test as the one check that covers the class.

Quick check

Quiz: Why does this error inflate revenue and customer count by different percentages? Because members who change are disproportionately active, so the duplication correlates with the measure rather than being uniform.

Flashcard: Why must a Type 2 current row end at 9999-12-31 rather than NULL? Because a range predicate against NULL is unknown rather than true, so every current row silently drops out of the result.