The Customer Who Changed

A hard Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
hard
Seniority
L5

Problem

Our customer dimension needs to track historical changes to attributes like address, membership tier, and email. When we look at an order from 6 months ago, we need the customer's attributes as of that date, not today's values. Design the schema.

She moved. She upgraded. She became someone new. The record has to keep up.

Worked solution and explanation

Why this problem exists in real interviews

Type 2 SCD is the single most common data modeling interview question because it forces the candidate to articulate a trade-off in plain language: do historical facts report the attributes that were true at the time, or the attributes that are true now? The signal is whether the candidate names Type 1 and Type 2 explicitly and picks between them with a justification rooted in the business question, not textbook dogma.

Break down the requirements

Step 1: Introduce a surrogate key

customer_sk is a BIGINT generated per version. customer_id stays as the natural key that joins versions of the same customer together.

Step 2: Add the effective-dated columns

valid_from and valid_to define the window during which this version was live. is_current is a boolean convenience for hot-path lookups of the present state.

Step 3: Version-lock the fact

fact_orders.customer_sk points at the version that was current at order time. This is the load-bearing decision: any fact joined to dim on customer_sk returns the historical attributes, not the current ones.

Step 4: Close out the prior version on change

When a customer's segment changes, the ETL sets the old row's valid_to and is_current = false, then inserts a new row with valid_from = now() and a fresh customer_sk. The original row stays untouched for audit.

The solution

Below is one conceptually sound approach. The surrogate key plus effective dates is the Kimball canon; the fact's surrogate FK is what makes historical queries work without temporal join gymnastics.

`dim_customer` is Type 2; `fact_orders`.customer_sk locks each order to the version of the customer that existed at order time
  • dim_customer: customer_sk BIGINT PK, customer_id TEXT, segment TEXT, loyalty_tier TEXT, valid_from TIMESTAMP, valid_to TIMESTAMP, is_current BOOLEAN
  • fact_orders: order_line_id BIGINT PK, order_id BIGINT, customer_sk BIGINT FK, order_timestamp TIMESTAMP, net_amount DECIMAL
  • dim_customer references fact_orders

The analysis pattern

Revenue by historical customer segment
SELECT
    c.segment AS segment_at_order_time,
    COUNT(DISTINCT f.order_id) AS orders,
    SUM(f.net_amount) AS revenue
FROM fact_orders f
JOIN dim_customer c ON c.customer_sk = f.customer_sk
WHERE f.order_timestamp >= '2025-01-01'
GROUP BY c.segment
ORDER BY revenue DESC

Trade-offs and alternatives

Type 2 with surrogate FK on fact
Historical fidelity, fast equi-joins, clean audit. Cost: ETL writes a new row on every change, storage scales with change rate, and is_current needs an index.
Type 1 with natural key on fact
One row per customer, minimal storage, simple ETL. Cost: historical reports silently show today's attributes, and segment-lift analysis becomes impossible.

Common follow-up questions