Late Arriving Dimension

A late arriving dimension is the situation where facts reach the data warehouse before the descriptive context they refer to. The Kimball Group notes that facts from an operational process can arrive “minutes, hours, days, or weeks” before the associated dimension data. A typical invented case: an order from a new customer is loaded at 09:00, but the customer master data is only extracted in the nightly batch, so at load time there is no customer row for the order to point at.

The two tempting responses, and their costs

• Drop the fact. The order is real, so revenue is understated until someone reprocesses it — if anyone notices.
• Hold the fact until the dimension arrives. This suits a batch that can wait in staging and be published complete before its deadline. The pipeline has to store and retry pending records; a figure published while they wait is incomplete if its stated period and scope were supposed to include them, and should say so — a report cut off at 08:00 is not short for an order held from 09:00 — and a customer record that never arrives needs a timeout rather than leaving orders stuck indefinitely.

The placeholder approach

The Kimball Group’s documented approach is to insert a placeholder dimension row with generic “unknown” values in the descriptive columns, load the fact against it, and then update that same row with Type 1 overwrites when the real dimension data arrives. Because the fact already points at the row, it picks up the correct attributes without being reloaded.

1. A fact arrives with a customer ID that has no dimension row.
2. Insert a customer row with that customer ID (the natural key) and “Unknown” for name, segment, and region; the row receives a surrogate key.
3. Load the fact with that surrogate key.
4. When the customer record arrives, overwrite the placeholder’s descriptive columns.

Carrying the natural key on the placeholder is what makes step 4 possible: without it, nothing links the placeholder to the record that eventually arrives.

What to add in practice

A placeholder whose description reads “Unknown” looks like any other row in a report. Mark placeholders explicitly — a flag column — and track how many remain unresolved and for how long. Placeholders that are never revisited become permanent holes, and reports quietly aggregate them into an “Unknown” group that nobody investigates.

The retroactive case

A harder variant arises when a dimension change turns out to have taken effect in the past — the customer’s segment changed three weeks ago, and the warehouse learns of it today. The Kimball Group notes that this requires inserting a new dimension row and restating the associated fact rows so they point to the correct version. That is a genuine rewrite of history: reports already published for those weeks will change, so the restatement should be recorded and announced.

The mirror-image problem, a fact that arrives after its dimension has changed, is a late arriving fact. Both are worked through in Modeling Time: Three Clocks, History, and Corrections, and the change types involved are covered in slowly changing dimensions.

References: Kimball Group, Late Arriving Dimensions.