Who Saw the Ad Twice

A hard Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

hard

Seniority

L6

Problem

Our TV and digital ad measurement pipelines are completely separate: TV exposure arrives as second-level logs from set-top boxes, and digital arrives as impression logs from connected apps. Design a pipeline that combines them into cross-platform audience metrics for campaigns that ran on both broadcast TV and connected apps.

TV and digital. Same viewer, two measurement worlds.

Business requirements

• Advertisers want one number for how many people saw their campaign across TV and digital, not two separate numbers that double-count households.
• We can't fail a CCPA audit; raw personal identifiers can't move between datasets the way they do today.
• The ad server checks frequency caps on every impression and the lookup has to feel instant or the cap doesn't apply.
• Advertisers expect their measurement report by morning the day after; if it's late, that's a contract breach.

Worked solution and explanation

What this really is

This is a household-level set union dressed up as ad measurement, with a latency split hiding inside it. Anyone can count distinct viewers. What separates candidates is seeing that TV and digital carry different identifiers for the same household, so the union only works through an identity graph keyed on hashed ids. The second tell is that frequency capping is a different pipeline: the ad server asks on every impression and cannot wait for a warehouse. Miss the graph and you double-count every household that saw both. Miss the hashing and raw PII sits in one dataset when the CCPA assessor looks.

Walk the requirements

Step 1: Count households, never sum platforms

A set-top box id and a mobile device id never match on equality. identity_graph maps both hashed ids to one household key, and cross_platform_reach counts distinct on that key. Summing TV reach and digital reach is exactly the double count the advertiser is paying to remove.

Step 2: Hash in place, move only hashes

tv_hasher and digital_hasher salt and hash identifiers before anything crosses a dataset boundary. Only hashed ids, the graph mapping and aggregates move. Centralising raw emails and device ids to make the join easy is the design that fails the audit.

Step 3: Give the ad server its own store

frequency_stream tails digital_impressions and updates per-household counters in cap_store, a Redis key-value store. The ad server reads it on every request in milliseconds. Pointing it at the warehouse costs hundreds of milliseconds, the cap arrives late, and campaigns over-deliver.

Step 4: Order the night and alert early

The orchestrator runs identity refresh, then measurement, then the clean-room publish. Its late-vs-SLA alert fires at 2am with hours to recover, not at 9am when the contract is already breached.

The whiteboard answer

Copy TV logs into the digital warehouse, join on email, add the two reach counts, and let the ad server query the same warehouse for caps.

The design that survives

Hash per source, count distinct on the household key from identity_graph, publish to a clean room, and serve caps from cap_store on a streaming path.

Common follow-up questions

• A third measurement partner joins next quarter. What in this design extends and what does not? (The partner becomes one more hashed source feeding `identity_graph`; measurement inherits it.)
• The Redis cap counts start disagreeing with the morning report. Where does the gap come from? (The cap store is approximate and fast; the report is exact. Reconcile at the day boundary instead of chasing perfect consistency.)