Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Late-arriving dimensions

Data modeling · Advanced Modeling

Late-arriving dimensions

Hardmodel-22
late-arriving dimensionorphan keysSCD2pipelines

Question

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

Solution

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.

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 foreign keys
  • Facts temporarily map to "unknown"
  • SCD2 point-in-time lookup may pick the wrong version if history is repaired late

Common handling patterns

1. Unknown member: assign customer_sk = -1 and repair later with an update/re-key job 2. Inferred dimension row: insert a stub dim row with the natural key and placeholder attributes; enrich when the real feed arrives 3. Delay fact publish: wait for dim dependency (hurts latency) 4. Reprocessing: when late dim history arrives, re-lookup surrogate keys 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)."

PreviousNext