Data modeling · Advanced Modeling

Late-arriving dimensions

Hardlate-arriving dimensionorphan keysSCD2pipelines

Interview question

What are late-arriving dimensions, and how do you handle them?

How to answer

The short answer

A late-arriving dimension (or early-arriving fact) happens when a fact arrives before its matching dimension row exists, or the dimension update that should have applied at event time shows up late.

Output
Fact load 09:00: order for customer_id C-99, product SKU-7
Dim load  delayed: C-99 not in dim_customer yet

Why it hurts

  • Orphan
  • Facts temporarily map to "unknown"
  • SCD2 point-in-time lookup may pick the wrong version if history is repaired late

Common handling patterns

  1. 1Unknown member: assign customer_sk = -1 and repair later with an update/re-key job
  2. 2Inferred dimension row: insert a stub dim row with the and placeholder attributes; enrich when the real feed arrives
  3. 3Delay fact publish: wait for dim dependency (hurts latency)
  4. 4Reprocessing: when late dim history arrives, re-lookup for affected fact date ranges

Interview tip

> "Facts can arrive before dims. Use unknown or inferred members, then repair keys when the dimension catches up (especially careful with SCD2)."