Late-Arriving Dimensions & Facts

## What "early arriving" means In every real pipeline the fact feed and the dimension feed are independent. Orders stream in every few minutes; the customer master is a nightly extract. A customer created at 09:00 places an order at 09:05, and the order reaches the warehouse hours before the dimension row does. This is the **early-arriving fact** — the fact is early relative to its dimension, which is equivalently described as a *late-arriving dimension*. It is a timing race between two systems, not a data-quality failure, and no amount of load ordering removes it permanently, because the two sources are not transactionally coupled. ## Why the obvious answers fail **Drop the fact.** The money vanishes. Yesterday's revenue silently understates and nobody knows by how much. This is the answer that ends interviews. **Set the dimension foreign key to NULL.** Warehouses generally do not enforce referential integrity, so nothing stops you — which is exactly the problem. Every inner star join then silently discards the fact, and every outer join forces all downstream SQL to handle NULLs. The star schema's contract is that every fact foreign key resolves to exactly one dimension row. **Hold the whole fact load until the dimension catches up.** The wait is unbounded. One unmatched key blocks an entire feed, and the fact is invisible to reporting for as long as the block lasts. **Park the row in a suspense/reject table.** Legitimate for genuinely unusable data, and every warehouse should have one, but the row stays invisible until a human works the queue. That is the right home for garbage, not for a valid key that simply arrived first. ## Option A — the unknown member A reserved dimension row, present from the day the table is created, whose descriptive attributes all read "Unknown". The fact points at it, the join stays inner, totals stay correct, and reports show an "Unknown" bucket that is itself a useful data-quality signal. The limitation is decisive: nothing on that row identifies *which* customer the fact belonged to. The arriving natural key has been discarded, so the fact can never be repaired without reprocessing the source. Use the unknown member when there is genuinely nothing to repair with — a null source id, an unparseable value, or a dimension role that does not apply to this fact at all. ## Option B — the inferred member Insert a real dimension row immediately: ```sql INSERT INTO dim_customer (customer_id, customer_name, segment, is_inferred) SELECT DISTINCT s.customer_id, 'Unknown', 'Unknown', TRUE FROM stg_orders s WHERE NOT EXISTS (SELECT 1 FROM dim_customer d WHERE d.customer_id = s.customer_id); ``` The row carries the durable business key from the fact feed, obviously-placeholder values for every descriptive attribute, and a flag marking it inferred. The fact resolves to a genuine surrogate key. When the master record finally lands and matches on `customer_id`, the placeholder attributes are overwritten in place. The surrogate key is untouched, so every fact loaded during the gap is instantly correct — no fact restatement, no reload. ## What the inferred row must carry - **The natural key.** This is the entire point; without it the row is just a more expensive unknown member. - **Non-null placeholder attributes**, consistently worded across the whole warehouse. Nulls in dimension attributes break `GROUP BY` and filter predicates in surprising ways; a literal `'Unknown'` groups and filters cleanly. - **An inferred marker** — a boolean or an audit column — so the population is countable and the resolution step is targetable. - If the dimension keeps Type 2 history, an effective window that starts at a low sentinel date, so the fact's own event date falls inside it rather than outside every version. ## Where teams get it wrong Creating one inferred row **per fact row** instead of one per distinct natural key duplicates members and causes fan-out on later joins — deduplicate the staging keys first. Leaving placeholders null pushes the problem into every downstream query. And most commonly, building the insert and never building the resolution, so the warehouse quietly fills with permanent "Unknown" customers. ## Operating it Because the inferred flag exists, monitoring is trivial: count inferred members daily and age them. Transient placeholders that resolve within one load cycle are normal and need no attention. Placeholders older than a few days mean something else — the master record was deleted upstream, the feed is broken, or the fact's key is genuinely invalid — and belong in a data-quality queue with an owner. Some organisations set a ceiling above which the load fails loudly rather than accumulating junk.