One Door Each

A medium 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
medium
Seniority
L5

Problem

We run dozens of product experiments at once on our consumer app, and each user who enters an experiment is locked to a single variant for its duration: either the control or one of the treatment groups. Analysts measure lift by joining each user's variant assignment onto our existing event log, which stays untouched, and counting only the events that occurred after the moment that user was enrolled. Design the data model so a user can never land in two variants of the same experiment, and so an analyst rolling metrics up by variant can tell the control apart from the treatment groups.

Every user picks a lane and stays in it. Model the world so the numbers stay honest.

Worked solution and explanation

Why this problem exists in real interviews

This is a set-membership-over-time problem wearing an experimentation costume. Beneath the vocabulary of control and treatment, the question is whether you can record 'this user belongs to exactly this variant, starting at this instant' as its own fact, and leave the event log alone. Anyone can name three tables. The separation that decides pass from fail is refusing to touch fact_events: the event log is a shared, immutable stream that a dozen experiments read from, and the moment you bolt experiment_id or variant_id onto it you have made every future correction a rewrite of billions of rows and made post-enrollment filtering impossible.

Break down the requirements

Step 1: Identify the entities

Three distinct entities: experiments (the test), variants (control/treatment groups), and assignments (user placed into a variant at a specific time). The event log is a fourth, pre-existing thing you read from but do not change.

Step 2: Separate assignments from events

Assignments must live in their own table. Embedding variant info on the event log makes temporal filtering impossible and turns the 0.1% of assignment corrections into billion-row rewrites.

Step 3: Nail the grain

Set the grain to one row per (user_id, experiment_id). The two key columns together carry that grain, which is what stops a user from leaking into multiple variants and invalidating every metric for that experiment.

Step 4: Include assignment_timestamp

This is the join key for post-assignment analysis. The query pattern is WHERE event_timestamp >= assignment_timestamp. Store it on the assignment, never on the event.

The solution

The (`user_id`, `experiment_id`) composite key on `fact_experiment_assignments` prevents variant leakage; `fact_events` keeps its original columns and carries no experiment context
  • dim_experiments: experiment_id INT PK, name TEXT, hypothesis TEXT, start_date DATE, end_date DATE, status TEXT
  • dim_variants: variant_id INT PK, experiment_id INT FK, variant_name TEXT, is_control BOOLEAN, traffic_pct FLOAT
  • fact_experiment_assignments: user_id UUID PK, experiment_id INT PK, variant_id INT FK, assignment_timestamp TIMESTAMP, is_active BOOLEAN
  • fact_events: event_id BIGINT PK, user_id UUID, event_timestamp TIMESTAMP, event_type TEXT, revenue FLOAT
  • dim_experiments references dim_variants
  • dim_experiments references fact_experiment_assignments
  • dim_variants references fact_experiment_assignments

The analysis pattern

Metric lift query
SELECT
    v.variant_name,
    COUNT(DISTINCT e.user_id) AS users,
    AVG(e.revenue)            AS avg_revenue
FROM fact_experiment_assignments a
JOIN dim_variants v ON v.variant_id = a.variant_id
JOIN fact_events e
  ON e.user_id = a.user_id
 AND e.event_timestamp >= a.assignment_timestamp
WHERE a.experiment_id = 42
  AND a.is_active = true
GROUP BY v.variant_name

Trade-offs and alternatives

Dedicated assignments table
Clean separation of concerns. Temporal joins are clean via assignment_timestamp. The composite grain enforces one variant per user per experiment. The cost is one extra join on every metric query.
Alternative: variant on event log
Variant column denormalized onto each event row. Faster reads since no join. Backfills become catastrophic when assignments are corrected. Cannot reason about pre-assignment behavior. Late-arriving data is hard to attribute correctly.

Common follow-up questions