Ads Platform Data Model (OLTP and Reporting)

0) Problem Restatement

Design the data model for an advertising platform (asked at Netflix several times). Advertisers create campaigns with budgets and dates. Campaigns contain line items (or ad groups) with targeting and bids, which show creatives (the actual video or image ads). The platform records impressions and clicks. Two different needs:

• The transactional side (OLTP): the campaign manager UI creates and edits these objects, which needs correctness and history.
• The reporting side (OLAP): advertisers and finance ask "impressions, spend and reach per campaign per day", over billions of events.

1) Requirements

• Model advertisers, campaigns, budgets, line items, creatives, targeting, and delivery events.
• Keep an edit history (who changed the budget, and when).
• Support reports by time, campaign, creative, device and country.
• Handle late events and corrections.

2) Transactional Model (OLTP, e.g., Postgres)

CREATE TABLE advertisers (advertiser_id BIGINT PRIMARY KEY, name TEXT, billing_account_id BIGINT, status TEXT);

CREATE TABLE campaigns (
  campaign_id BIGINT PRIMARY KEY, advertiser_id BIGINT REFERENCES advertisers,
  name TEXT, objective TEXT,                         -- awareness, reach, ...
  budget_cents BIGINT, budget_type TEXT,             -- total or daily
  start_at TIMESTAMP, end_at TIMESTAMP, status TEXT, version INT
);

CREATE TABLE line_items (
  line_item_id BIGINT PRIMARY KEY, campaign_id BIGINT REFERENCES campaigns,
  bid_type TEXT, bid_cents BIGINT, pacing TEXT, frequency_cap JSONB,
  start_at TIMESTAMP, end_at TIMESTAMP, status TEXT, version INT
);

CREATE TABLE creatives (
  creative_id BIGINT PRIMARY KEY, advertiser_id BIGINT, type TEXT,  -- video, image
  asset_url TEXT, duration_sec INT, review_status TEXT
);
CREATE TABLE line_item_creatives (line_item_id BIGINT, creative_id BIGINT, weight INT,
  PRIMARY KEY (line_item_id, creative_id));

CREATE TABLE targeting_rules (
  line_item_id BIGINT, dimension TEXT,     -- country, device, genre, audience_segment
  operator TEXT,                           -- include / exclude
  values TEXT[]
);

CREATE TABLE change_log (                  -- edit history for every entity
  entity_type TEXT, entity_id BIGINT, version INT, changed_by BIGINT,
  changed_at TIMESTAMP, before JSONB, after JSONB
);

Notes:

• Many-to-many between line items and creatives (one creative can run in several line items).
• Targeting as rows (dimension, include/exclude, values) is flexible: new dimensions don't need schema changes.
• Version + change_log: every edit bumps the version and writes before/after, which gives audit history and supports "undo".

3) Reporting Model (OLAP / Warehouse)

Use a star schema: a big fact table of events or daily aggregates, surrounded by dimension tables that describe them.

flowchart LR
    F[("fact_ad_delivery_daily")] --- D1["dim_date"]
    F --- D2["dim_campaign - SCD2"]
    F --- D3["dim_line_item - SCD2"]
    F --- D4["dim_creative"]
    F --- D5["dim_device"]
    F --- D6["dim_geo"]
    F --- D7["dim_advertiser"]

fact_ad_delivery_daily:
  date_key, campaign_key, line_item_key, creative_key, device_key, geo_key,
  impressions, clicks, completed_views, spend_cents, unique_reach_sketch (HLL)

• Grain (what one row means): one row per day × campaign × line item × creative × device × country. Say the grain out loud in the interview.
• Raw event facts (fact_impression) exist too, for deep dives, but most reports read the daily aggregate.

3.1 Slowly changing dimensions (SCD)

A campaign's name or budget changes over time. For reports to show what was true at that time, use SCD Type 2: each change creates a new dimension row with valid_from / valid_to and a new surrogate key. Facts point to the key that was valid when the event happened.

4) Pipeline (ELT)

1. Delivery events (impressions, clicks) stream via Kafka into the data lake (raw, partitioned by hour).
2. Hourly jobs deduplicate, join with dimensions and build fact_ad_delivery_hourly, then roll up to daily.
3. OLTP changes flow via CDC into dimension tables (building SCD2 history).
4. Late events: reprocess the last 3 days each run (restatement), so late data is included. Final billing numbers are locked after the restatement window.

5) Trade-offs & Alternatives

6) Common Follow-up Questions

• "Unique reach across days?" You can't add daily uniques together. Store HyperLogLog sketches in facts and merge them for any range.
• "Budget vs spend?" Spend comes from facts, the budget from the campaign dimension. Pacing dashboards join them.
• "Multi-currency?" Store spend in the advertiser's billing currency plus a normalized USD column, using the day's FX rate.

7) Wrap-Up

Model the transactional side as normalized tables for advertisers, campaigns (budget and dates), line items (bids, pacing, caps), creatives, a line-item-to-creative link table, and flexible targeting rules, with versions and a change log for history. Model reporting as a star schema with a clearly stated grain, daily aggregate facts with HLL reach sketches, and SCD Type 2 dimensions, fed by an ELT pipeline that restates recent days to absorb late events.