The API Drip Feed

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

Design the pipeline that syncs tasks, users, and projects from a third-party project management SaaS API into our analytics warehouse, where one tracked dashboard reads the task data.

The API gives you 100 records at a time. You need millions.

Business requirements

• The rate limit won't cover pulling every table fresh each hour, so only the one tracked dashboard's task updates can ride a within-the-hour path while the rest of the tables stay on a once-a-day refresh.
• The API only serves small rate-limited pages and bursts unevenly; ingestion has to absorb those bursts without dropping records.
• The vendor reshapes the API's fields every few weeks, so schema changes have to be caught before they reach the warehouse and surfaced to the team rather than silently corrupting the tables.

Worked solution and explanation

What this problem is really testing

This is change capture by polling a hostile API, dressed up as a project-management sync. Three stressors hit the warehouse at once: small rate-limited pages that burst unevenly, one dashboard that needs freshness the rest of the tables do not, and field names that drift every few weeks. Anyone can draw a straight line from the API to the warehouse. The separating move is refusing to let any one stressor reach the tables. Skip it and you drop records on a burst, miss the dashboard's hour, or load a renamed field as a column of nulls that nobody notices for a week.

Walk the requirements

Step 1: Buffer the API before anything reads it

A loader that reads the API directly must keep up with a burst or drop pages when it backs off on a 429. A queue (ingest_buffer) decouples fetch rate from load rate: the fetcher writes pages as they arrive, the loaders drain at their own pace, and throttling stalls a consumer without losing what is already buffered.

Step 2: Split the load into two cadences

The rate limit cannot cover pulling every table fresh each hour, and a daily-only design misses the dashboard's window. So hourly_task_loader pulls only task updates and daily_batch_loader refreshes the rest. An orchestrator paces both against the request budget, and cursor_state_store holds each loader's high-water mark so a run asks only for what changed.

Step 3: Validate the schema ahead of the warehouse

A vendor rename is silent. If loaders write what they receive, the old column fills with nulls and the table is quietly wrong. schema_validation_gate checks the shape of every batch from both loaders, passes clean ones to analytics_warehouse, and routes a mismatch to team_alerting, so the team fixes the mapping once instead of finding it in a broken report.

The shape that fits

Common follow-up questions

• The same connector now serves forty customer tenants, each with their own credentials and rate limit. What changes so one tenant's throttling does not stall the others? (Tests per-tenant runs with isolated cursors and rate-limit budgets instead of one shared pull.)
• The API never exposes hard deletes, but about two percent of tasks vanish each month. How do you keep `analytics_warehouse` from carrying ghosts? (Tests periodic full reconciliation against the live id set, since incremental polling cannot see a delete.)