Late-arriving data: what to do when facts turn up before their dimensions

Data does not always arrive in the order a model expects. A sale can turn up before its customer, or a week after the month closed. Handling both properly keeps totals right without losing rows.

A data model assumes an orderly world. Customers exist before they buy things. Transactions arrive on the day they happen. Real data is less tidy. A new customer's first order can arrive before the customer record does. A branch's sales can turn up three days late. A supplier invoice can be backdated into a month that has already been reported.

This is the fourth post in our loading patterns series, after natural, surrogate and hash keys.

Late-arriving dimensions

The most common case is a fact that refers to a dimension member that does not exist yet. A sale comes in for customer C-9001, but the customer table has no C-9001, because the CRM feed runs later than the sales feed, or because the customer was created minutes before the order.

There are three ways to handle it, and one is much better than the others.

• Drop the fact until the customer arrives. The totals are wrong in the meantime, and dropped rows are easily forgotten.
• Point it at a single "Unknown" row. The total is right, but when the customer does arrive, nothing links the sale back to them.
• Create an inferred member. This is usually the right answer.

Inferred members

An inferred member is a placeholder row added to the dimension, with the natural key you know and a surrogate key of its own, but with the descriptive columns marked as unknown. The fact links to it straight away, so no rows are lost and totals are correct.

When the real customer record arrives, the load updates the placeholder in place with the real name, region and other details. The fact already points at the right surrogate key, so it is automatically attributed correctly, without reprocessing the fact table.

A flag on the row showing it was inferred is worth adding. It lets you report how many placeholders exist and how long they wait, which is a useful data quality measure in its own right.

Late-arriving facts

The other case is a fact that arrives well after the event it describes: a backdated invoice, a delayed branch upload, a correction to last month.

The loading side is usually straightforward if you load by change rather than by transaction date, as described in full or incremental loads. The row is new or changed, so it is picked up whatever its date.

Two things need more care:

• History-tracked dimensions. A late fact must link to the version of the customer that applied on the transaction date, not today's version. With Type 2 dimensions, that means looking up the surrogate key by date range, not just taking the current row.
• Closed periods. If finance has already reported a month, a late transaction changes a number people have seen. The business needs an agreed rule: does the transaction go into the closed month, or into the current one as an adjustment? That is a finance and governance decision, and the model should follow it consistently.

Snapshots and aggregates

Late data also affects anything summarised. If a Gold table holds monthly totals or a daily stock snapshot, a late row means that summary needs rebuilding for the affected period. Design summary loads to reprocess a rolling window, for example the last few days or the current and previous month, rather than only today. Power BI incremental refresh uses the same idea.

Next in the series

The final post brings the series together with the checks that catch wrong numbers before anyone else does. If late data is causing arguments about your month-end numbers, our data platform team can help design the rules.

Data arrives when it arrives. The model's job is to put it in the right place anyway.