Skip to content
Back to challenges
SCD DepthConcept Challengeintermediate20 min

Avoid SCD2 row explosion

Split a frequently changing loyalty-points attribute out of the customer dimension.

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

4

Business needs the model must satisfy.

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

A retail loyalty team wants to analyze point balances and point adjustments by customer over time. The current customer dimension stores loyalty_points_current, but points can change many times per day after purchases, returns, promotions, and manual adjustments.

Why this matters

Type 2 is not the right answer for every changing attribute. Versioning the whole customer dimension for every point change creates row explosion and makes stable customer context noisy.

Requirements
  • Keep durable customer identity in a customer dimension.
  • Add a loyalty point event or snapshot fact for point changes.
  • Record loyalty event id, event time, and point balance or point delta.
  • Connect loyalty point rows back to the customer surrogate key.
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

Loyalty row-explosion 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.