Comparison
Late-arriving dimensionvsReferential integrity check
Late-arriving dimension
the order arrived before the customer record did, so the fact points at a placeholder row labelled unknown.
A fact whose dimension row does not exist yet at load time, usually because two source systems publish at different speeds. The standard handling is an inferred member — insert a stub dimension row keyed on the business key and fill in its attributes when the real record shows up. Doing nothing instead means either dropping the fact or breaking the join, and both are worse than a temporary unknown.
Full entry →Referential integrity check
eight thousand fact rows point at a customer id that does not exist in the customer dimension.
Checking that every foreign key has a matching row, in a system where the database does not enforce it. Analytical stores generally do not enforce constraints at all, so this is a test rather than a guarantee, and orphans are normal in a warehouse fed by several pipelines at different speeds. The interesting output is the trend: a stable orphan rate is a known timing artefact, a rising one is a broken load.
Full entry →