Skip to content
Back to challenges
Grain and KeysConcept Challengeintermediate20 min

Protect recycled identities

Model a customer dimension that survives an operational system reusing customer ids after account deletion.

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

4

Business needs the model must satisfy.

Scenario

A marketplace source system reuses customer ids six months after deletion to keep its operational id space small. Growth analysts still need lifetime value and churn history for the original customer and the later customer separately.

Why this matters

Recycled ids are a quiet analytics failure mode. If source_customer_id is the warehouse primary key, history for two real people can collapse into one row and contaminate retention, support, and revenue reporting.

Requirements
  • Create a customer dimension with a surrogate primary key.
  • Keep source_customer_id as an attribute for reconciliation.
  • Add an effective-from so each source-id assignment is ordered in time (the floor, which already keeps the two customers distinct).
  • Grade up by closing each window with an effective-to, so the exact moment a recycled id changed hands is unambiguous.
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

Recycled customer id starter workspace

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.