How do you test referential integrity between a fact table and its dimensions when the warehouse doesn't enforce it?

## Why this test carries so much weight in a warehouse In an operational database, a foreign-key constraint refuses the insert and you find out immediately. Analytical warehouses generally do not enforce referential integrity — the constraint is either unavailable or accepted purely as metadata. So the guarantee has to be re-created as an assertion you run yourself, on every load. The failure mode is what makes it urgent. A dangling key throws no error. A report joining fact to dimension with an inner join silently drops those rows; a report using an outer join shows a blank or unlabelled row. In both cases the total is wrong and nothing anywhere says so. That is the single most expensive class of warehouse bug because it is invisible and plausible at the same time. ## The assertion ```sql select f.customer_key, count(*) as orphan_rows from fct_sales f left join dim_customer d on d.customer_key = f.customer_key where d.customer_key is null group by f.customer_key ``` Zero rows means every fact key resolves. Grouping by the offending key makes the result immediately diagnosable — you see *which* keys are missing and how many rows each one costs, which usually names the cause on sight. A `NOT EXISTS` formulation says the same thing and is often clearer; the anti-join form above is preferred by many engines' planners. Either is portable standard SQL. Avoid `NOT IN` against a subquery here: if the dimension's key column contains a single NULL, `NOT IN` returns no rows at all and the test passes vacuously forever. Run it once per foreign key on the fact — customer, product, store, date, and every role-playing date key separately. A fact with eight dimension keys needs eight assertions, not one. ## The three usual causes **Load ordering.** The fact ran before the dimension refreshed, so members created today do not exist yet. The test is doing its job; the fix is dependency ordering in the pipeline, not weakening the test. **A fact genuinely arrived before its dimension member.** The event references an entity the source system has not published yet. This is a real modelling situation with its own handling patterns; the test is what tells you it happened and how often. **Key derivation disagrees between the two sides.** The fact builds its key from a trimmed, uppercased source id and the dimension does not, or one side casts to a different type, or one includes a source-system prefix. This is the cause people miss longest because both sides look correct in isolation. ## Test the reverse direction too — but read it differently Dimension rows with no matching fact are *not* an error. A product nobody bought is a perfectly valid dimension member, and a warehouse holds many of them by design. But a sudden change in the proportion of unused members is a signal worth watching: if half your customer dimension stopped matching any fact this month, something upstream changed. ```text fct_sales dim_customer customer_key customer_key 501 501 502 (missing) 503 503 inner join -> 2 rows reported, one sale silently disappears anti-join -> 1 orphan detected, key 502 named ``` ## Versioned dimensions need a sharper test When the dimension keeps history, the fact stores the surrogate key of the *version* that was current when the event happened. The plain existence check above still applies to that surrogate key and still catches genuine orphans. What it cannot catch is a fact pointing at a version whose validity window does not contain the event's date — a subtler defect where the key resolves but resolves to the wrong version of the entity. That validity check belongs with the effective-dating discipline itself; the point here is that "the key exists" and "the key is the right one" are two different assertions, and the cheap one does not imply the other. ## Severity and cost Orphan keys are a blocking failure on a published fact. On very large tables the anti-join is expensive, so the usual pattern is to test the slice each incremental run wrote plus a scheduled full pass, the same compromise used for grain uniqueness. One caution: a warehouse convention that redirects unmatched keys to a reserved default dimension member makes the orphan test pass by construction. That convention is useful — it keeps the rows in reports instead of dropping them — but once it is in force, the test to watch shifts from "are there orphans?" to "how many rows landed on the default member, and is that number moving?" A silently growing default-member count is the same bug wearing a hat.