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 when facts arrive promptly. For a late fact it is wrong. 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 only works if the dimension keeps history with the Type 2 technique: one row per version, each with its own surrogate key and effective date range. The load then matches on the natural key and the event’s date.

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 not kept, there is no correct version to find. A dimension that overwrites changes cannot attribute late facts correctly, no matter how the load is written.
• 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.

References: Kimball Group, Late Arriving Facts; Kimball Group, Slowly Changing Dimensions, Part 2.