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
On days the external payments vendor settling our marketplace orders rate-limits or returns errors on pages of its paginated REST API, the finance team's reconciliation comes up short. Design the scheduled pull that keeps ingesting through those failures and only lets clean, deduplicated records reach the finance warehouse, at a few million records a day.
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 a few million records a day, 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, deduplication 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 scheduler owns a cursor checkpoint 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 its retries 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 deduplicate 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
A scheduler runs the pull and owns the cursor checkpoint. An ingestion worker pages the vendor API with per-page retry and backoff, writing raw responses to an immutable landing zone in object storage and shipping exhausted-retry pages to a dead-letter store. A dedup 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 reaches the finance warehouse that reconciliation and payouts read. A monitoring stage watches the scheduled run against its deadline and the dead-letter depth, and alerts the team before finance notices.
- vendor_api (source, API)
- scheduler_cursor_checkpoint (transform, Airflow)
- ingestion_worker (transform); backoff: exponential
- raw_landing (storage, object storage)
- dead_letter_pages (storage, object storage)
- dedup_on_vendor_id (transform, Spark)
- validate_and_quarantine (quality gate, Great Expectations)
- finance_warehouse (storage, warehouse)
- reconciliation_and_payouts (consumer)
- pipeline_monitoring (consumer, alerting)
- scheduler_cursor_checkpoint -> ingestion_worker
- vendor_api -> ingestion_worker
- ingestion_worker -> raw_landing
- ingestion_worker -> dead_letter_pages
- raw_landing -> dedup_on_vendor_id
- dedup_on_vendor_id -> validate_and_quarantine
- validate_and_quarantine -> finance_warehouse
- finance_warehouse -> reconciliation_and_payouts
- scheduler_cursor_checkpoint -> pipeline_monitoring
- dead_letter_pages -> pipeline_monitoring
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 deduplication contract.)
- Reconciliation finds a systematic error in how you parsed a field 3 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-deduplicate, rather than asking a vendor you do not control for three-week-old data.)