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

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.

Scheduled pull with a cursor checkpoint, per-page retry with backoff, immutable raw landing, dead-letter for stuck pages, dedup on the stable vendor id, a quality gate that is the only thing feeding the finance warehouse, and monitoring on the run and the dead-letter store.
  • 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