API Ingestion

'Our CRM vendor exposes a REST API. Pull every customer record into the warehouse once a day.' The follow-ups are predictable, and they arrive in the same order in almost every interview: how do you get all of them, what happens when the vendor slows you down, what happens when a call fails, and how do you avoid pulling 2 million records every day when 3,000 changed. An answer that covers those 4 before they're asked is the one that scores.

Following the pages

No API returns 2 million records in 1 response, so the loop has to page through them. Offset pagination asks for records 0 to 99, then 100 to 199, and breaks when a record is inserted between calls, because every later record shifts by 1 and 1 record is returned twice or skipped. Cursor pagination returns an opaque token pointing at the position after the last record, and the next call passes it back. Prefer the cursor when the API offers it, and say why: the vendor's data changes while you read it.

{
  "data": [
    {"id": "c_1001", "email": "ana@example.com", "updated_at": "2026-10-18T09:14:02Z"},
    {"id": "c_1002", "email": "raj@example.com", "updated_at": "2026-10-18T09:20:47Z"}
  ],
  "next_cursor": "eyJpZCI6ImNfMTAwMiJ9",
  "has_more": true
}

import time


class RetryableError(Exception):
    pass


def fetch_customers(client, updated_since):
    cursor = None
    while True:
        params = {"updated_since": updated_since, "limit": 100, "cursor": cursor}
        response = client.get("/v1/customers", params=params, timeout=30)
        if response.status_code == 429:
            time.sleep(int(response.headers.get("Retry-After", "10")))
            continue
        if response.status_code >= 500:
            raise RetryableError(response.status_code)
        response.raise_for_status()
        page = response.json()
        yield from page["data"]
        if not page["has_more"]:
            return
        cursor = page["next_cursor"]

What each response means

Pulling only what changed

A full pull of 2 million records at 100 per page is 20,000 calls. At a limit of 10 calls per second that is 33 minutes on a good day, and most vendors cap you well below that rate. The daily run should instead pass updated_since set to a few minutes before the previous successful run started, so the 3,000 records that changed come back in 30 calls. The overlap covers clock skew between the vendor's servers and yours, and a record stamped with an updated_at inside a long transaction that commits only after the previous run read past that time. The overlap also means the same record arrives in 2 consecutive runs, so the warehouse table is keyed on id, and the row with the newer updated_at wins.

Where API pulls break

The scored version

'I would page through /customers with the cursor the API returns, 100 records per call, passing updated_since set to 5 minutes before the last successful run started. Each raw page is stored as received, then parsed into a table keyed on id where the latest updated_at wins, which also makes a restarted run harmless. A 429 waits for Retry-After; a 5xx or a timeout retries with waits that double from 2 seconds up to 16, after which the run fails and alerts.' That is the production engineer's version, and all 4 of the standard follow-ups are already answered inside it.

About this lesson

This section is part of the How Data Moves: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.