SaaS API Connector with Incremental Sync

A medium 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

medium

Seniority

L5

Problem

A third-party CRM holds about 40 million records behind an API whose schema changes without notice. The warehouse needs a full historical copy of them plus ongoing incremental updates. Design the pipeline that keeps the warehouse in sync with this API.

The API has rate limits. You have deadlines.

Business requirements

• Analytics needs vendor data fresher than the nightly dump can deliver; one tracked dashboard requires updates within hours.
• The vendor caps the API at a fixed rate and the current dump fails on rate limits about once a week.
• When a record is deleted in the vendor system, the API simply stops returning it; the warehouse can't keep showing it as if it still exists.

Worked solution and explanation

What this problem really is

This is a state-management problem dressed up as an API connector. Anyone can page through an endpoint and load a table. What separates candidates is who owns the state between runs: the cursor, the rate-limit budget, and the memory of which ids existed last time. Skip that and the warehouse drifts from the CRM. Deleted records stay forever, because an API that stops returning a row never sends you an event about it.

Step 1: Sync incrementally inside the freshness window

The orchestrator triggers api_extractor on an hourly cadence. The extractor reads the last cursor from incremental_tracker, pulls only rows changed since then, and writes the new cursor back. The first run has an empty cursor, so the historical backfill and the ongoing sync are the same code path. A nightly dump misses the hours-fresh dashboard and repeats the full rate-limit cost every night.

Step 2: Pace under the cap and back off on 429s

api_extractor runs as a single task that throttles itself below the vendor's fixed rate, and on a 429 it retries with exponential backoff. Because the cursor only advances after a page lands, a retry resumes instead of restarting. The weekly failure comes from hammering the cap and then redoing everything.

Step 3: Land raw, then normalize

api_extractor writes the payload untouched into raw_landing. schema_normalizer maps it into warehouse columns. The vendor changes schema without notice, so a rename should break only the mapping, never the fetch.

Step 4: Reconcile the id set to catch deletions

On a slower cadence, reconciliation_job pulls only the current ids from the API and merges the result into warehouse_target. Any id the vendor no longer returns is marked deleted. Cursor sync cannot do this, since it only sees rows that changed.

The reference design

Nightly full dump

Pages the whole API every night, so one 429 mid-run means starting again. Data is a day stale, and deleted rows stay in the warehouse because nothing removes them.

Cursor plus reconciliation

Pulls only changed rows within the freshness window and resumes after a backoff. A slower reconciliation_job removes deleted rows. The cost is extra state and a second, smaller API budget.

Common follow-up questions

• The historical backfill will take days under the rate limit. How do you keep it from delaying the incremental sync? (Tests splitting the rate budget between a backfill window and the live cursor, and relying on the warehouse upsert to absorb overlap.)
• The `reconciliation_job` and the incremental run both hit the same capped API. How do you stop them starving each other? (Tests whether scheduling and a shared request budget live in the orchestrator instead of two jobs racing.)