Skip to content
Back to challenges
Keys and Late-Arriving DataConcept Challengeadvanced25 min

Late-arriving dimension

Let an order reference a customer whose dimension row has not loaded yet by creating an inferred placeholder.

How this preview works
Preview this problem before signing in. Sign in to save progress and submit work.

Concept

surrogate-keys

The primary modeling idea this problem reinforces.

Requirements

3

Business needs the model must satisfy.

Scenario

Orders sometimes arrive before the customer master record. The fact cannot wait, and the foreign key must hold. The team needs to record the order now and backfill the customer attributes later.

Why this matters

Late-arriving dimensions are a load-ordering reality. An inferred member (a surrogate row created on the fly and flagged for backfill) keeps the fact loadable and referentially sound without dropping or stalling rows.

Requirements
  • Add a customer dimension with a surrogate key so a placeholder member can hold the late-arriving customer, and reference that surrogate from the order (the floor, which keeps the order loadable and referentially sound).
  • Grade up with an is_inferred flag so the placeholder is findable and backfillable, not silently blank.
  • Keep the natural customer id available for later matching.
Read-only starter workspace
Inspect the authored starting ERD, then open the workspace to edit and run the checks. Saving your progress needs a free account.

Template

Late-arriving dimension starter

The model users start from in the full workspace.

Entities

1

Seeded tables visible in the public preview.

Dialect

postgresql

Generated SQL target for this starter model.

Mini Map

This preview is read-only. Open the workspace to edit and verify; saving needs a free account.

Open workspace

Try the question first.

The discussion has other people's approaches and solutions. Give it a real attempt before you read them.