A Stream All Day and a File at Midnight

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

Transactions arrive as a continuous stream all day, and a daily reconciliation file lands at midnight that corrects the prior day's activity. Both feeds have to land in one warehouse that the analysts query. Design the pipeline that carries both.

Real-time and batch. Same pipeline. No compromises.

Business requirements

• Operations watches the day's activity in real time and reads provisional figures; finance signs off only on reconciled figures.
• The midnight reconciliation file is the authoritative number for the prior day; finance's view of that day has to reflect it.
• Today the dashboard shows numbers without telling the analyst whether they have been reconciled or are still provisional; that's been the source of confusion.
• Amended files for prior periods sometimes arrive days late; an amendment for last Tuesday has to update last Tuesday's figures, not today's.

Worked solution and explanation

This is a supersede-by-key problem dressed up as a streaming-versus-batch debate. Anyone can draw a Kafka box and a file box that both point at a warehouse. The real question is what happens to yesterday's rows when the midnight file lands. If the answer is 'both sets sit there', operations and finance get two different numbers for the same day, and nobody can say which one is right.

The trap is two tables, one per path. It looks tidy, but the figures drift because they were computed independently. Then an amended file for last Tuesday arrives, the loader keys on arrival time, and last Tuesday's corrections inflate today's totals.

Walking the requirements

Step 1: Give each path its own cadence

The stream is real-time and writes rows with status = 'provisional'. The loader runs once a day on the file. If you push everything through one path, either operations waits a day or the reconciliation file pays for streaming compute it never needed.

Step 2: Land both in one warehouse table

Both paths write the same table, so there is one place a number can live. Operations reads the latest rows. Finance filters to 'reconciled'. Two filters on one source of truth, never two tables to compare.

Step 3: Overwrite the partition the file covers

The loader uses partition_overwrite on the date the file reconciles. The provisional rows for that date are replaced in one transaction instead of sitting beside the reconciled ones. Re-running the same file gives the same end state.

Step 4: Key amendments on event_date, not arrival

An amended file carries the period it corrects. The loader overwrites that partition and sets status = 'amended', so last Tuesday's file fixes last Tuesday and today's totals stay untouched.

Two tables, one per path

Yesterday has two answers, a join is needed to compare them, and a late file has no obvious home. Analysts cannot tell which figure is canonical.

One table with a status

Yesterday has one answer at any moment. The status column says whether it is provisional, reconciled or amended, and the dashboard can show that badge.

Common follow-up questions

• A dashboard queries yesterday at 11pm, before the file arrives. What does it return, and how does the analyst know? (Tests whether the candidate points at the `status` column showing provisional rows instead of hiding the gap.)
• An amended file for last Tuesday changes a single row. What does the warehouse look like afterwards? (Tests partition overwrite by `event_date`: the whole partition is rebuilt from the file and flagged amended.)