Late-Arriving Data: Facts and Dimensions

Two kinds of lateness

Late facts

1. Extract by arrival time, not event time, so every row is picked up exactly once.
2. Map each row back to its business date and recompute the affected partitions.
3. Write with MERGE or partition overwrite, so recomputing a day replaces it rather than appending.

-- recompute only the days touched by this batch
MERGE INTO fact_sales_daily t
USING (SELECT business_date, store_id, SUM(net) AS net
       FROM curated_lines
       WHERE business_date IN (SELECT DISTINCT business_date FROM this_batch)
       GROUP BY 1, 2) s
ON t.business_date = s.business_date AND t.store_id = s.store_id
WHEN MATCHED THEN UPDATE SET net = s.net
WHEN NOT MATCHED THEN INSERT VALUES (s.business_date, s.store_id, s.net);

Late dimensions

• Insert an inferred member: a placeholder dimension row with the business key and "unknown" attributes. Point the fact at it.
• When the real dimension row arrives, update the placeholder. Facts keep the same surrogate key.

Knowing when a day is complete

A control total from the source (the store's end-of-day count and sales) lets you mark a day complete only when your totals match. Until then, publish it as provisional or hold it.