Someone Else's Server

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

senior

Asked in

Whatnot

Problem

We run a live shopping marketplace and settle every order through an external payments vendor that exposes its records over a paginated REST API we do not control. Build the business-critical pipeline that pulls this data on a schedule and picks up from the last page it landed when a run dies partway, keeps ingesting when the vendor rate-limits or returns errors on some pages, and only lets clean, deduplicated records reach the finance warehouse that reconciliation and payouts read from. The vendor occasionally resends the same record, and its daily volume runs into the low millions.

Business requirements

• When the vendor rate-limits us or an instance dies halfway through a pull, I don't want to re-read everything or lose the middle. Pick up where we left off.
• The vendor resends the same transaction sometimes. If a payout counts it twice, that is real money out the door.
• I would rather hold a suspicious batch than let a null amount or a garbage currency flow into reconciliation.
• One bad page from the vendor should not blind finance for the whole day.
• If the data is late or the vendor is down, I need to know before reconciliation does.

Worked solution and explanation

Why this problem exists in real interviews

This looks like an ingestion problem but it is really a trust problem: you are building on a server you do not control, that rate-limits you, drops the occasional page, and resends the same settlement record more than once, and the output feeds payouts. The trap is the tidy daily pull: schedule a job, page through the vendor API, write to the warehouse, done. That design is correct only on the days the vendor behaves. The day it 429s in the middle of a run, your job either restarts from zero and hammers the vendor or resumes blindly and skips the middle, and the day it resends a batch, finance pays a seller twice. At the low-millions-a-day volume here, a duplicate rate of even a fraction of a percent is thousands of double payments.

So the real skill being probed is designing for at-least-once delivery from an unreliable external source: resumable ingestion, idempotent landing on a stable key, a quality gate before the money-critical consumer, and a dead-letter path so one bad page never blinds finance for the whole day. Every requirement traces back to the single fact that you do not own the other end of the wire.

Break down the requirements

Step 1: Pin the delivery contract before anything else

Ask how the vendor paginates, how it fails, and whether it delivers at least once. The answers here (cursor pagination, 429s and 5xx per page, duplicate ids, a stable vendor_txn_id) decide the entire design. If you skip this, you design for a clean feed that does not exist.

Step 2: Make the pull resumable, not restartable

The orchestrator owns a cursor or watermark that advances only on a page fully landed. When a run dies at page 400 of 900, the next run resumes at 400. Per-page retry with exponential backoff absorbs the transient 429s and 5xx; a page that still fails after N attempts goes to the dead-letter store so the run does not stall on it.

Step 3: Land raw, then dedup on the stable id

Write the raw vendor payload immutably first, so any downstream bug is replayable without touching the vendor again. Then upsert into the deduplicated table keyed on vendor_txn_id, latest updated_at wins. This one decision is what turns at-least-once delivery into effectively-once state, and it is where duplicate payouts are prevented.

Step 4: Gate on quality before the warehouse, not after

Amount non-null and non-negative, currency in the allowed set, row count within a band of the trailing average. Failing rows are quarantined and a batch that fails the volume check does not promote. The warehouse reads from the gate, never from the raw zone, so reconciliation only ever sees validated data.

The reference architecture

An orchestrator schedules the pull and owns the cursor. An ingestion worker pages the vendor API with per-page retry and backoff, writing raw responses to an immutable landing zone and shipping exhausted-retry pages to a dead-letter store. A dedup/upsert step keyed on vendor_txn_id produces the effectively-once table. A quality gate validates schema, nulls, and volume, quarantining failures, and only its output is promoted to the finance warehouse that reconciliation and payouts read. Observability watches freshness against the deadline, failure rate, and dead-letter depth, and alerts the team before finance notices.

Common follow-up questions

• The vendor announces they are deprecating the cursor API and switching to a webhook push next quarter. What changes and what stays? (Tests whether the candidate sees that the raw landing, dedup-on-id, quality gate, and warehouse are ingestion-mode-agnostic, and only the pull-and-cursor front end is replaced by a webhook receiver with the same idempotency contract.)
• Reconciliation finds a systematic error in how you parsed a field three weeks ago. How do you correct history without re-pulling from the vendor? (Tests whether the immutable raw landing zone is actually used for replay: reprocess raw through a fixed transform and re-upsert, rather than asking a vendor you do not control for three-week-old data.)