The Panel and the Set-Top Boxes

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

An on-call engineer saw one late operator file and one crashed show projection delay every network's 8am early ratings and every client's 10am finals. We weight a 40,000-home panel against set-top data for 45 million devices that each operator sends overnight on its own schedule. Design the pipeline, keeping every report exactly as a client received it.

Set-top boxes tell you who watched. Projection tells you how many.

Business requirements

• Networks make same-day programming decisions on the early ratings and final ratings are due to all clients by mid-morning; missing the deadline triggers contractual penalties.
• Cable operators deliver on different schedules and one late operator can't block national ratings for everyone else.
• When a cable operator delivers late or revises a prior period, ratings have to be re-run and clients told about the revision; silent changes are a regulatory and contractual problem.
• When the projection job for one show fails, the other shows still have to deliver; one giant job for everything has caused mass delivery delays.

Worked solution and explanation

What this really is

This is a deadline-bound batch DAG in a TV-ratings costume. Weighting 40,000 panel homes against 45 million set-top boxes is the easy part. The hard part is the shape of the work. The trap is one nightly job keyed on the calendar day. It waits for the slowest cable operator, so every network misses its early read. It dies on one broken show, so every client misses 10am. When an operator restates last Tuesday, it quietly overwrites numbers clients have already traded on.

Build it in order

Step 1: Land and validate each operator separately

Each operator's file lands into viewing_events_raw as its own task, behind a validation gate that checks row counts and device coverage against that operator's history. A late operator is a sensor that times out and pages with the operator's name. The national run proceeds without it and flags the affected markets.

Step 2: Schedule against two deadlines, not one

Networks make same-day programming calls on an early read, so the orchestrator runs projection twice. The 8am preliminary uses whatever operators have arrived. The 10am final adds the stragglers. Both deadlines are SLAs the orchestrator monitors, so a miss alerts before the client notices.

Step 3: Give every show its own projection task

Apply panel_weights per show and write projected_ratings through a staging table, then swap it in. Each task retries on its own, and a rerun replaces its partition instead of appending duplicates. A model blowup on one sitcom stays one red task while the other shows deliver.

Step 4: Turn late or corrected data into a versioned revision

Late or restated operator files flow from ingest into the revision processor. It re-projects only the affected shows and periods, then writes a new version keyed by period, show and revision number into the archive next to the original. It also emits a revision notice to the affected clients and logs both in client_delivery_log.

One nightly job

Starts when the last operator lands. One bad show fails the whole run. A restatement overwrites the table in place, and clients find out from their own reconciliations.

Per-operator, per-show DAG

Early read ships on what arrived. A failed show retries alone. A restatement becomes revision 2 in the archive, revision 1 stays as shipped, and a notice goes out.

Common follow-up questions

• An operator restates a period from last week. Walk the data from file to client inbox. (Tests the full revision path: ingest, validation, scoped re-projection, a versioned archive write beside the original, and a notice to only the affected clients.)
• The 8am preliminary shipped without one operator. How do clients know which markets to trust? (Tests whether flagged markets travel with the preliminary rather than living only in an internal alert.)
• A show needs a different weighting model. What changes in the DAG? (Tests per-show tasks as the extension point: a new task, with shared ingest, warehouse and archive untouched.)