Model a search-ads system end to end — a hundred advertisers compete in under a hundred milliseconds, one wins, and the conversion lands four days later on a different device. A complete working through: the causal chain across four facts, the one join key that threads it, append-only attribution you can re-run, loser sampling for the ranker, quality-score calibration, and the dashboard.
This prompt asks for "an ads model" and is in fact a causal chain stretched across four orders of magnitude and several days — where the hardest requirement is not volume but the ability to re-decide the past without rewriting it.
"Design a data model for search ads that captures the whole chain — auction clearing, impression, click, conversion — and supports advertiser billing, quality-score recomputation, ranker training, incrementality measurement, and re-runnable attribution windows. How do you scope it?"
The trap is to model the four stages as one event. They are not: they are four facts at violently different cardinalities and tempos. Two billion auctions a day clear in under a hundred milliseconds each; roughly a hundred ads compete in every one of them and exactly one wins, so the losing candidates outnumber the winners twentyfold. Impressions follow auctions, clicks follow impressions at a few percent, conversions follow clicks hours or days later — and the click can be invalidated by bot-detection that runs long after billing thought it was done. Collapse this into one table and the join becomes a timestamp-matching séance; keep it as four and you need one thing above all: a key that is born once and travels the whole way down.
A weak answer fuzzy-joins clicks to conversions on user and time and calls attribution a pipeline. A strong answer notices that attribution is not collected, it is computed — and that the moment a model changes, you must be able to recompute it over the same raw events without losing the old answer. So before any tables, the frame for the session:
Scope is the first scored dimension, so name it. In scope: the four-fact chain, the loser counterfactual, the append-only attribution layer with versioned models, and the analytics for billing, the ranker, and measurement. Out of scope, said explicitly: the real-time auction engine itself (the sub-100 ms serving path that runs the second-price auction — treated as the producer of fct_auctions), the bidding/budget pacing controller, creative storage, and spam/fraud beyond the invalid-click hook. The caveat: the model must not preclude privacy carve-outs, so a privacy_path is first-class from the start rather than retrofitted.
Then the envelope math, volunteered. Search-ads numbers at planetary scale:
| Quantity | Estimate | Consequence |
|---|---|---|
| Ad requests / day | 2,000,000,000 | ≈ 23 K auctions/s average, ~6× at peak |
| Candidates per auction | ~100 | Sets the raw counterfactual volume before sampling |
| Loser rows / day (raw) | ≈ 200 B | 20× winners — the row that forces sampling, not retention |
| Click-through rate | ~3% | Clicks are 1/30th of impressions; conversions rarer still |
| Conversion lag | minutes → days | Attribution windows must span 1–28 days; facts arrive late |
| Invalid-click detection lag | hours → days | Clicks are a slowly-changing fact; billing must be restatable |
| Attribution re-runs | on every model ship | Re-attribution must be a query, not a re-ingestion |
Read the table and the architecture is half-decided. The loser row dictates that the counterfactual is sampled and reweighted, never stored whole. The two lag rows — conversions arriving days late, invalid clicks landing later still — dictate that nothing downstream may assume a fact is final when it lands. And the re-run row dictates the central design move: attribution is a derived, append-only layer keyed by a run id, so the past can be re-decided without being overwritten. The rest of this article follows the key down the chain.
One key, four facts, two tempos. The serving path mints the decision and races on; the measurement path stitches the chain together hours later and re-runs attribution whenever a model ships — from the same immutable events.
Three properties of this picture do most of the interview's work. First, every fact on the serving path is immutable and carries auction_decision_id, so the chain is reconstructed by an equi-join on one column rather than by guessing which click caused which conversion. Second, attribution is a separate plane entirely — the stitcher pairs each conversion with its eligible touchpoints and an attribution run writes credit as new rows, so last-click and data-driven coexist and yesterday's answer is never destroyed. Third, the loser sampler is dashed off to the side because the counterfactual is a sampled tributary, not the main flow: 1% of losers reweighted is a balanced training set; 100% of losers is a bankrupt warehouse.
No fact is final when it lands; correctness is a function of when you ask. A conversion can arrive four days after its click, so attribution windows are evaluated at read time against the touchpoints that existed in-window. A click can be invalidated hours after it billed, so invalidation is an append — is_invalid = TRUE with an invalidation_ts — never an update, and billing is restated from the corrected view. The system is eventually consistent by design; its honesty lives in the run id and the timestamp, which let any number be reproduced exactly as it stood on the day it was reported.
The schema falls out of the chain. Four immutable facts threaded by auction_decision_id; a fifth fact, attribution, that is derived and append-only; a sampled sixth for the counterfactual; and versioned dimensions where a model or a creative has a history that billing must reproduce.
The auction is the head of the chain. It records the winner, the second-price clearance, and the privacy path — and it mints auction_decision_id, the single most important column in the model. The impression is what actually rendered, joined to the auction on that key, and it carries the ranker's own prediction (predicted_pctr, quality_score, ad_rank) so the ranker can later be graded against reality.
The click is the chain's one mutable-by-append fact. It is born clean, and bot-detection may later mark it invalid — but the model never overwrites it. is_invalid flips to TRUE with an invalidation_ts, so the billing history that charged the advertiser yesterday remains reproducible while today's credit is corrected. The conversion is the tail: an advertiser-reported event with a value, arriving hours or days after the click, owning no knowledge of which touchpoint earned it — that is attribution's job.
This is the table that makes the system senior. It is not collected — it is the materialized output of running a model over the chain. One row per (conversion × eligible-touchpoint × model × run), partitioned by attribution_run_id. The same conversion appears under MODEL_LAST_CLICK with credit 1.0 and under MODEL_DDA_v3 with a fractional credit, in the same run, never destroying the other. Re-attributing a quarter when the model upgrades is a new run id over the same immutable events — the old run is preserved, and an advertiser can compare the two side by side.
The dimensions are conformed and mostly SCD2, because the chain spans days and budgets drift hourly. dim_advertisers, dim_campaigns, dim_ad_groups, and dim_creatives all version their attributes so a quality-score regression can be debugged against the creative that was live at impression time, not the one live today. dim_keywords is SCD1 and high-cardinality — its history is not worth the storage. dim_attribution_models is SCD2 and is the keystone of re-attribution: the model is a versioned row, so "last-click vs data-driven" is a join, not an ETL fork. And dim_users is privacy-tokenized — never a raw user id, only a token and a privacy path.
The correctness of the whole model lives in two rules. The join rule: the chain is reconstructed only by auction_decision_id, never by timestamp proximity. The conservation rule: within a single run and model, the fractional credits for a conversion sum to one.
Attribution is dangerous because credit is real money divided among contenders. Give a conversion's full value to two touchpoints and you have double-paid; lose a fraction and an advertiser's ROAS is understated. The guard is arithmetic and per-conversion: across the eligible touchpoints for one conversion under one model in one run, fractional_credit sums to exactly 1.0 (or to 0.0 when the conversion falls in no window — unattributed, which is itself a valid, recorded answer). This is the auction's version of a ledger that must balance — RULE Nº 1, made executable.
Two edges branch off this spine. From CLICK, an INVALIDATED edge appends a verdict without rewinding billing. From CONVERSION, a re-run re-enters ATTRIBUTED under a new attribution_run_id — the only state that is allowed to happen more than once, because re-deciding the past is the entire point. Every other transition is immutable and append-once.
The second half of the invariant is the join discipline. Because every fact carries auction_decision_id, the chain is never reconstructed by matching a click's timestamp to a conversion's — a method that silently mis-attributes whenever two users share a device or a household IP. The key is deterministic; the séance is banned. When the chain cannot be completed — an iOS opt-out left user_token null, so the conversion cannot be tied to an impression — the model does not guess. It records the conversion as unattributed and routes the measurement to the aggregate path, where SKAN-style postbacks live. Honesty about what cannot be joined is part of the invariant.
Three programs carry the write path: the impression joiner that completes the head of the chain, the click-invalidation consumer that corrects without overwriting, and the reservoir sampler that keeps the counterfactual affordable. Each is small; the judgment is in what they refuse to do.
An impression is only useful if it carries its auction_decision_id; a beacon that lost the key on the wire is unjoinable and therefore worthless to the chain. The joiner's whole discipline is to refuse keyless impressions to a quarantine rather than fabricate a join. It never invents a decision id, and it never timestamp-matches an orphan impression to a nearby auction — a guess here poisons every downstream metric.
Bot-detection produces a stream of click ids it has judged invalid, sometimes days after the click billed. The consumer's one rule is that it corrects by appending a verdict, never by deleting the click — the row that charged the advertiser must remain to make billing reproducible. It is idempotent: a click already marked invalid is left exactly as it was, so a replayed detection batch cannot move the invalidation timestamp.
The losers are twenty times the winners and cannot be stored whole, but the ranker needs a balanced training set, so they are reservoir-sampled per auction and tagged with the inverse sampling rate. The sampler refuses to drop the population's shape: by stamping sample_weight = 1/rate on every retained row, downstream training reweights a 1% sample back to the full distribution without ever touching the discarded 99%.
One carve-out, always stated: the sampler never touches winners. Every impression is retained in full, because the winner is the billed, served reality and the positive class of the training set — only the unbilled, unserved counterfactual is thinned. Keeping the asymmetry explicit is what lets the ranker team reweight correctly instead of training on an accidentally balanced sample that lies about how rare a win is.
Two derived layers carry the slow loop. The attribution run materializes credit as an append-only batch tagged with a run id. Calibration grades the ranker by comparing the pCTR it predicted against the click-through it actually got — the feedback that keeps the auction's prices honest.
The attribution run is the slow loop's heart, and its defining property is that it is re-runnable. A run takes a model id, a window definition, and a slice of conversions; it pairs each conversion with its eligible in-window touchpoints, asks the model to distribute credit, and writes the result as new rows under a fresh attribution_run_id. Last-click is the trivial case — full credit to the last valid click in window. Data-driven splits credit across the path using the model's learned weights. The two run side by side over the identical events, and the latest run wins on read while every prior run remains queryable for audit. Re-attributing an entire quarter when MODEL_DDA_v2 becomes v3 is a batch job, not a re-ingestion — the raw facts never move.
Calibration is the other half — the loop that grades the ranker. The auction's prices are only as fair as its pCTR predictions: a creative whose realized click-through drifts far from its predicted pCTR is either over- or under-priced, and the gap is a retraining signal. The job buckets impressions by creative, computes the realized CTR from the click join, and flags any creative whose calibration error exceeds a threshold. It runs nightly because pCTR drift is slow and statistical — a single hour's clicks are too noisy to act on.
The facts are where the system explains itself — once you have the key. Three queries an interviewer loves, because each serves a different team and each carries a classic pattern on its back.
The advertiser's defining query, and the whole reason for auction_decision_id. Four facts collapse into one campaign narrative by joining on a single column, with the latest attribution run pinned and invalid clicks excluded. The pattern is the conformed-key fact join — the chain reconstructed deterministically, not guessed.
The measurement team's defining query. Because every model's output lives in the same table under the same run, comparing them is a GROUP BY model_id — last-click's full-credit world beside data-driven's distributed one. The pattern is dimensional comparison over a derived fact: the same conversions, two lenses, one scan.
The ranker team's defining query. Per creative, compare the average predicted pCTR against the realized click-through, and surface the creatives whose calibration error is large enough to demand retraining. The pattern is a FILTERed conditional rate against a predicted baseline — model accuracy, measured directly off the impression/click join.
A senior design ends with observability, because every clever re-run and carve-out above is invisible without it. The dashboard watches the chain from three seats — the auction, the funnel, and attribution — and one tile watches the thing that breaks silently: the join.
Read the amber tiles together and the dashboard narrates a privacy-driven shift from the operator's chair: identity is harder to join (match rate falling), so a growing share of conversions route to the aggregate path, and last-click flatters the final touch while data-driven tells the truer story at 42.0× — yet nothing leaked, because the invariant held. The single most important tile is the boring one: credit-sum errors at zero. Match rate is a coverage problem you manage; credit conservation is a law you do not break.
Strip the ads away and the question was testing five judgments, each of which generalizes far beyond search advertising: