The Dashboard and the Attribution Model

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

Design one architecture on Azure Databricks that feeds both a real-time campaign performance dashboard and a daily attribution report from a stream of ad impression and conversion events peaking near 200,000 per second. Today separate streaming and batch pipelines produce the two, and their numbers have drifted apart.

Streaming and batch. One pipeline to rule them.

Business requirements

• The daily attribution report is what we bill against and the live dashboard has to show the same numbers for the same period; today they diverge.
• Mobile impressions arrive hours late; both the dashboard and the daily report have to attribute each event to the time it actually happened.
• Attribution credits the last impression seen within the lookback window before a conversion; the daily report has to read the relevant historical window for each day's conversions.
• Today the dashboard's approximate dedup misses duplicates that arrive late while the batch report removes them exactly; the unified view can't keep that gap.

Worked solution and explanation

The trap

This is a consistency problem wearing a streaming-vs-batch costume. Anyone can draw a fast path for the dashboard and a slow path for the report. That is exactly the architecture that is drifting today. The real skill is making the daily report a closed-window read of the same rollups the dashboard reads, so the two views have nothing to disagree about. Keep two computations and every rule gets written twice: dedup, lateness, the attribution lookback. The invoice you bill against then stops matching the number the client watched all day.

Build it in four decisions

Step 1: Dedup once, upstream of both views

Drop duplicates on a stable event id in dedup_stream, before anything aggregates. Today the streaming side dedups approximately and the batch side dedups exactly, and that gap is where the dashboard overcounts. A duplicate that arrives late is still caught, because dedup keys on the id and not on arrival order.

Step 2: Window on event time, with a lateness allowance

event_time_attribution buckets by when the impression happened, not when it arrived. A mobile event that shows up three hours late updates its own hour through the upsert into the warehouse, and because both views read that bucket, both restate together.

Step 3: Give attribution the history it needs

Last-touch credit needs the impressions from before today. event_archive holds the deduped history, and event_time_attribution reads the lookback window from it for each conversion. A same-day-only lookup silently drops credit for yesterday's impression.

Step 4: Serve both views from one warehouse table

Rollups land in rollup_warehouse. The dashboard reads the open periods and the report reads yesterday's closed window. Same rows, two queries, no reconciliation job.

The reference design

Two synchronized pipelines

Stream dedups approximately, batch exactly. Stream buckets by arrival, batch by event time. Each owns its own attribution window. Numbers diverge, and someone writes a reconciliation job.

One computation, two reads

dedup_stream and event_time_attribution run once. The report is a filter on rollup_warehouse, so any disagreement would have to come from a query, not a pipeline.

Common follow-up questions

• An impression arrives a week late, past the lateness allowance. What do the dashboard and the report show? (Tests a replay path from `event_archive` that recomputes the affected day once, so both views restate the same period.)
• The attribution lookback grows from 30 to 90 days. What gets expensive? (Tests whether the candidate sees that attribution state and archive reads scale with the window, and how partitioning by event date keeps the read bounded.)