Listens From Everywhere, Counted Once

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

L7

Problem

Our users stream music on phones, desktop apps, smart speakers, and web browsers. We want to show artists and labels near-real-time stats on how their tracks are performing, and we need a historical record for royalty calculations. Design a data pipeline that collects listening events from all our device types.

Phones, tablets, laptops. And some of them report late.

Business requirements

• Artists watch their stream counts and want them updated within minutes of an actual listen; royalty calculation runs nightly.
• Royalty payouts are calculated from the exact play count per track; we can't afford to over- or under-count even slightly.
• Mobile and smart-speaker clients buffer events for hours; a listen has to be reported under the time the user actually listened, not the time we received the data.
• Users can request deletion of their listening history; deletion has to remove their events from the raw archive and any aggregation that included them.

Worked solution and explanation

The trap

This is a two-reader correctness split dressed up as music analytics. The artist dashboard wants minutes and tolerates a little noise. Royalties want every qualifying listen counted exactly once, under the moment it actually happened, and erasable on request. Anyone can draw Kafka into Flink into a dashboard. What separates candidates is refusing to let royalties read that streaming aggregate. Do it and a phone that buffered March listens offline drains them in April: April's payout inflates, March's shrinks, and the rolled-up counts have no user_id left to delete.

Walk the requirements

Step 1: Split by correctness budget

device_listens fans out twice: to event_time_windows (Flink) feeding dashboard_store for minutes-fresh counts, and to event_archive for the durable record. One source with two freshness tiers means the paths can differ only in how soon they report, never in what happened.

Step 2: Make listen_id the dedup contract

The client stamps a stable listen_id. royalty_dedup_batch drops repeats on it before counting, then writes through a staging table, so a retry, a double-send or a rerun lands the same number every time.

Step 3: Attribute to event_time, never arrival time

Smart speakers replay hours of buffered listens. The Flink windows use the device timestamp, and the archive is partitioned by it, so a late drain reopens an older partition that the nightly overwrite recomputes. Attribute on arrival and every drain moves money between months.

Step 4: Route deletion to every place the user lives

deletion_orchestrator removes the user's rows from event_archive, triggers recompute of the affected partitions in royalty_warehouse, and clears the user's contribution from dashboard_store. Deleting at the raw layer alone passes the demo and fails the audit.

The shape that fits

Common follow-up questions

• A buggy client double-sends every listen for a week. What protects payouts, and what do artists see meanwhile? (Royalty dedup on `listen_id` absorbs it; the streaming counts may run high unless dedup also happens upstream.)
• The qualifying-listen rule changes retroactively. What reruns and what stays untouched? (The archive is immutable history; `royalty_dedup_batch` reruns the affected periods with partition overwrite into `royalty_warehouse`.)