Section 5: One Owner per Fact - Data Modeling

Executive Summary: Five datasets sit behind Section 4's IDs, each with one source of truth. The campaign hierarchy lives in a ~250 GB OLTP store sharded by advertiser_id. The serving index document is a denormalized copy of it (2 KB per ad, ~200 GB per copy, 16 shards), fed by change data capture into a snapshot, a delta layer and a kill list. The user context record (5 KB) is read once per request. Spend and pacing is a per-campaign aggregate refreshed every 10 s. The event logs are append-only. The rule: nothing on the request path is a source of truth, so nothing on the request path waits on a write.

The interviewer points at ad_id, campaign_id and tracking_token in the Section 4 sketch. "Those are just IDs. What's behind them, where does each one live, and who's allowed to change it?"

You: "Access patterns first, then shapes, then stores. The request path only ever reads: an index lookup, one user record, a throttle value. The writes come from two places that run on different clocks: advertisers editing campaigns, and users generating events. So I'll give every fact one owner and make everything the request path touches a derived copy."

The Source of Truth: the Campaign Hierarchy

Advertisers think in four levels, and the model follows them:

advertiser  adv-88120  "Trailhead Running Co."  billing_account, currency USD, status active
campaign    cmp-5512   objective: traffic   daily_budget: $5,000   schedule: always-on   status: active
ad set      ads-90431  targeting: interest=running AND age 25-40 AND region=US-TX
                       bid_type: CPC   bid: $1.20   frequency_cap: 3 impressions / user / rolling 24 h
ad          ad-7c21e   creative_id: cr-4410   policy_status: approved

Budget sits on the campaign, targeting and bid on the ad set, creative and policy verdict on the ad. At 10M advertisers, 20M campaigns and 50M live ads, the full records come to ~250 GB. That's a small OLTP database, sharded by advertiser_id. Every edit an advertiser makes lands on one shard, so "pause this campaign and raise that bid" is a single-shard transaction. The serving path never reads this database; it consumes its change stream.

Quick check before moving on: