Design a database schema for an ad A/B test
Tests separating high-volume events from slow-changing experiment metadata. Strong answer: distinct tables for variants, impressions, and clicks; clicks link to impressions; a user-assignment table avoids duplicating variant data per event.
What's really being asked
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.
The full answer
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.
The mistakes people make
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.
What usually comes next
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?
A 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.
Interview question
What is the main advantage of storing variant assignments in a separate user_allocations table instead of adding variant metadata to every impression row?
- a.It allows impression rows to be updated in place when a user converts to a click
- b.It guarantees that each user sees only one variant for the entire experiment duration
- c.It reduces storage costs and prevents data inconsistency when experiment metadata changesCorrect
- d.It eliminates the need for joins when calculating click-through rates by variant
Why? this is the answer
Storing allocation once avoids duplicating slow-changing metadata across billions of high-volume events, saving storage and preventing inconsistency if a headline is edited. Option D is tempting because denormalization often speeds up queries, but the schema prioritizes immutable, normalized events to preserve analytical accuracy and reduce cost.
Just read this? Test yourself on what you have been reading.
Read the original → mitzu.io
- #data modeling
- #a/b testing
- #sql schema
- #event tracking
- #interview prep
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on data modeling — each one lists the topics its interview covers.
See open roles