A "customers by segment" dashboard tile reports 104,300 where the customer table holds 100,000 rows. Revenue on the same dashboard is also about 4% high. The join looks correct, the pipeline is green, and re-running changes nothing. What is happening?

The first three things I would look at, and why in that order

1. Distinct count of the business key against the row count of the joined result. If distinct is 100,000 and rows are 104,300, the join is multiplying rows and nothing else needs investigating yet.
2. Whether the dimension is Type 2. One row per customer per version is the condition that makes multiplication possible.
3. The join predicate. Specifically, whether it contains an effective-date range or only the key.

The diagnosis

The fact is joined to a Type 2 dimension on the business key alone, so every fact row is duplicated once per version of that customer. The 4.3% inflation says roughly four in a hundred customers now have a second version — exactly what you would expect a few months after a Type 2 dimension went in, and exactly why this surfaces long after the change that caused it.

Revenue is high by the same mechanism and by a different amount, because the customers who changed are not a random sample: the ones who change segment, plan or address are the active ones, so the revenue error is larger than the count error whenever the metric correlates with activity. A team that sees the two numbers inflated by different percentages often concludes there are two bugs.

The misleading signal

Green pipeline, deterministic output. Because the wrong answer is stable, it reads as a data problem in the source rather than a modelling problem in the join, and the investigation starts in the wrong place. Every freshness and volume check passes: the row counts are exactly what the pipeline produced.

The fix

The correct design is to resolve the surrogate key at load time, so the fact stores the specific dimension version that was current when the event happened, and the join is on the surrogate with no date logic at all. That is the version to aim for.

Where the fact only carries the business key, the predicate must be a range: fact_date >= valid_from AND fact_date < valid_to. Two details decide whether that works:

• valid_to is exclusive, or a fact on a change date matches both versions and you have moved the problem rather than fixed it.
• The current row's valid_to is a high sentinel date, not NULL. A range predicate against NULL is not false, it is unknown, so current rows drop out entirely — which turns a 4% over-count into a much larger under-count and looks like a different bug.

The trap: wrapping the count in DISTINCT. It corrects the tile, leaves revenue wrong, and removes the evidence.

The test that catches the whole class

An assertion in the transformation build that the row count after the dimension joins equals the row count of the fact table. It costs one test per fact model and catches every fan-out of this shape before it reaches a dashboard.

When this is not a problem at all

A Type 1 dimension holds one row per key and cannot fan out, which is a real argument for keeping Type 1 as the default until somebody actually asks a historical question. The cost of Type 2 is not the storage, which for a 100,000-row dimension is nothing. It is that every downstream join becomes a place this failure can occur, and the number of such places grows with every model an analyst adds.

Choose Type 2 when a query must reproduce what was true at a point in time, and when you do, resolve the surrogate key in the loader so that the risk lives in one job rather than in fifty queries. It flips back when the historical question turns out to be asked by one report once a quarter: a periodic snapshot table answers that at a fraction of the ongoing cost, and leaves the dimension simple. Warehouse teams have been making this trade since Kimball described it in the 1990s, and the failure above is the standard way it goes wrong.