Late-arriving dimensions and fact repair

Last updated: 6 Oct 20265 min read

tutorial

A late-arriving dimension is an entity description that becomes available after facts referencing that entity have already reached the warehouse.

Keep the fact visible without inventing attributes

An invoice event for merchant m-99 can be complete even when the merchant catalog is delayed. Publish the fact with an explicit unknown dimension key and retain the source merchant key. Do not assign it to a nearby merchant name; that turns missing reference data into a false assertion. Readiness policy decides whether the mart may publish with unknowns.

Distinguish absent from invalid

A source identifier that is well formed but not yet in a completed dimension snapshot is late; one that violates the source contract is invalid. Set an age or count threshold for unresolved facts. Quarantine impossible identifiers, but route plausible late facts to a repair queue. Record the source snapshot and first-seen time so the operator can distinguish delivery lag from permanent drift.

Repair references, not measures

When the merchant row arrives, select the version valid for each fact event timestamp and replace the unknown surrogate key. Preserve invoice ID, cents and event time. If the dimension has multiple historical versions, resolving every fact to the current version corrupts earlier regional totals. Versioned keys make that choice explicit.

Avoid double publication

A repair job should update or atomically replace targeted fact rows under their stable grain. Appending corrected copies produces two facts unless all consumers filter old generations. Pin the input facts and dimension snapshot; validate unresolved count falls and total invoice cents stays unchanged. Snapshot commit shields readers from a half-repaired mart.

Retain an audit trail

Record the old key, new key, affected fact IDs, dimension version and repair run ID. A downstream report may need restatement because grouping changed even though total cents did not. Link that restatement to the manifest and notify owners through lineage rather than silently replacing history.

Implementation

UNKNOWN_MERCHANT = 0
fact_rows = [
    {"invoice_id": "inv-47", "merchant_source": "m-99", "merchant_key": 0, "event_day": 47, "cents": 5800},
    {"invoice_id": "inv-83", "merchant_source": "m-31", "merchant_key": 331, "event_day": 47, "cents": 2100},
]
merchant_versions = [
    {"merchant_source": "m-99", "start": 40, "end": 50, "dim_key": 991},
]

def matching_version(versions, merchant_source, event_day):
    matches = [row["dim_key"] for row in versions
               if row["merchant_source"] == merchant_source
               and row["start"] <= event_day < row["end"]]
    if len(matches) > 1:
        raise ValueError("overlapping merchant versions")
    return matches[0] if matches else UNKNOWN_MERCHANT

def repair_merchant_keys(facts, versions):
    repaired = []
    for fact in facts:
        updated = dict(fact)
        if updated["merchant_key"] == UNKNOWN_MERCHANT:
            updated["merchant_key"] = matching_version(
                versions, updated["merchant_source"], updated["event_day"])
        repaired.append(updated)
    return repaired

fixed = repair_merchant_keys(fact_rows, merchant_versions)
assert fixed[0]["merchant_key"] == 991
assert sum(row["cents"] for row in fixed) == sum(row["cents"] for row in fact_rows)

Performance and operating cost

Scanning N candidate facts is O(N) expected time with a keyed dimension lookup and O(N) output space. In production, target only unresolved keys and use a version-aware index; repeated full-table repair scans waste compute. Restatement work can exceed the key update itself.

Common Mistakes

Do not guess a dimension from a similar name.
Do not point old facts to the dimension version current today.
Do not append repaired facts beside the old generation.