Design a database schema for an ad A/B test

WHAT THIS TESTS: This question tests data modeling hygiene at scale. Even though the scenario is simple, the interviewer wants to see if you separate slowly-changing experiment configuration from high-volume immutable events, if you understand the storage cost of denormalization, and if you preserve analytical joinability without mutating event history. For a senior role, they also care whether you consider duplicate prevention and partitioning.

A GOOD ANSWER COVERS: Four core tables in order. First, an experiments table with experiment_id, variant_id, headline_text, start_time, and end_time to own the configuration. Second, a user_allocations table with user_id, experiment_id, variant_id, and allocated_at to record which bucket each user entered; this follows the best practice of storing allocation once rather than attaching it to every downstream event. Third, an impressions table with impression_id, user_id, ad_id, served_variant_id, timestamp, and context like device_type or geo; it should be append-only and partitioned by date. Fourth, a clicks table with click_id, impression_id, user_id, and timestamp that references the impression to enable exact CTR calculation. The candidate should also mention primary keys, timestamps on every table, and indexing on user_id and timestamp for join performance.

COMMON WRONG ANSWERS: Proposing a single events table that mixes impressions and clicks with a type column and nullable click columns; this destroys granularity and complicates CTR math. Suggesting that impression rows should be updated with a click flag when a conversion happens; event data should be immutable. Denormalizing headline text or variant metadata directly onto every impression row; this wastes terabytes at scale and risks inconsistency if a headline is edited. Omitting the user_allocations concept entirely and relying on the ad server to tag every impression with a variant; while possible, it misses the opportunity to show you understand normalized allocation tracking.

LIKELY FOLLOW-UPS: How would you calculate CTR per variant without double-counting users who see the ad multiple times? How would you handle a user who is assigned to a variant but never sees an impression? Would you store the allocation data on every event if the warehouse charges by scanned bytes rather than storage? How do you prevent the same user from appearing in both variants due to a client-side race condition?

ONE CONCRETE EXAMPLE: Imagine two headlines: Save 10 Percent Today versus Limited Time: 10 Percent Off. The experiments table holds both variant records under experiment_id 42. User 123 is assigned to variant A at 09:00 and that row lives in user_allocations. At 10:00 an impression row is created with user_id 123 and served_variant_id A. At 10:05 a click row is created pointing to that impression_id. A daily CTR query joins experiments to user_allocations to impressions to clicks, groups by variant_id, and computes count(clicks) divided by count(distinct impressions) without ever scanning headline text in the event tables.