Late Arriving Fact

A late arriving fact is a measurement that reaches the data warehouse well after the event it describes — a sale recorded from an offline device days later, an invoice batch delayed by a partner, an adjustment entered at month end for an event in the middle of the month. The Kimball Group describes the resulting problem precisely: by the time the fact arrives, “the most current dimensional context for new fact rows does not match the incoming row.”

Why it matters

A normal load attaches each new fact to the current version of each dimension, which is correct only when that version was also in effect at the fact’s event time. How late the fact is does not decide it: a sale at 09:59:59 that arrives at 10:00:01, after a change at 10:00, is attached to the wrong version too, while a fact that arrives weeks late is fine if no version change falls between its event time and now. What decides it is whether a change lies between the event and the load. In an invented example, a customer moved from the North region to the South region on 10 March, and a sale they made on 5 March arrives on 20 March. Attaching it to the current customer row credits the sale to South. The sale happened while the customer was in North.

The Kimball Group’s prescription is to search the relevant dimensions for the dimension keys that were effective when the measurement event occurred, and attach the fact to those.

How the lookup works

This needs the dimension’s history. The usual source is the Type 2 technique: one row per version, each with its own surrogate key and effective date range, and the load then matches on the natural key and the event’s date. A retained source history or dated snapshots can provide the same answer when the dimension itself keeps only current values.

SELECT d.customer_key
FROM customer_dim d
WHERE d.customer_id = :source_customer_id
  AND :event_date >= d.row_effective_date
  AND :event_date <  d.row_expiration_date;

The half-open range (inclusive start, exclusive end) matters. The Kimball Group specifies that a version’s end must equal the next version’s start exactly, so a condition that includes both ends would match two versions on the change date. This lookup is the same operation as a point-in-time join.

Decisions around it

• If history was kept nowhere, there is no correct version to find. A dimension that overwrites changes cannot attribute late facts correctly on its own; unless the history survives elsewhere — the source system, an archive, snapshots — no load can recover it.
• If the period is already published, the late fact changes a reported number. Decide whether to restate the period or record it as an adjustment in the current period, and tell consumers which.
• Record the lateness. Keeping both the event time and the load time on the fact shows how late data really is, which is what any reprocessing window has to cover — see late data and lookback.

The opposite case, where the fact arrives before its dimension context exists, is a late arriving dimension. Both are worked through with the clocks a model needs in Modeling Time: Three Clocks, History, and Corrections.

Further reading

• Kimball Group, Late Arriving Facts
• Kimball Group, Slowly Changing Dimensions, Part 2