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

Late-arriving fact (SCD2)

Model an events fact so a late-arriving event joins to the customer version valid at event time, not the current one.

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

Concept

slowly-changing-dimensions

The primary modeling idea this problem reinforces.

Requirements

3

Business needs the model must satisfy.

Read the concept guide: Slowly changing dimensions: Type 2
Scenario

Activity events sometimes arrive days late. The customer dimension is Type 2, so a customer may have several versions. Analysts need each event attributed to the customer version that was valid when the event happened.

Why this matters

Late-arriving facts against a Type 2 dimension fail quietly if they join to the current version. The event time must select the version whose validity window contains it.

Requirements
  • Add an events fact with an event-time column.
  • Reference the customer version surrogate key valid at the event time.
  • Keep effective-from and effective-to on the customer dimension.
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 fact 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.