# Late-Arriving Data: Facts and Dimensions

> What to do when a fact arrives after its day was processed, or before its dimension exists: reprocessing windows, placeholders and reconciliation.

- Source: https://akshaythakare.com/notes/etl-elt/late-arriving-data
- Topic: ETL / ELT
- Level: advanced
- Reading time: 2 min
- Tags: etl, late-data, data-modeling, interview
- Updated: 2026-10-11

## Two kinds of lateness

| Case | Example |
| --- | --- |
| **Late fact** | A sale from Monday reaches the database on Tuesday |
| **Early fact / late dimension** | A sale references a product that is not in `dim_product` yet |

## 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.

```sql
-- 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.

> **Warning: Never silently drop late rows**
>
> A pipeline that ignores rows older than the watermark under-reports forever. Late rows must either be processed or counted in an alert.