API Ingestion: The Bug Magnet

An API pull looks like a database pull with a web request in place of a query, and the differences are what break it. A vendor's endpoint answers 1 page at a time, against data that changes between pages, behind a limit on how often you may ask, maintained by a team that does not know your job exists.

Pagination: offsets drift, cursors do not

A vendor with 23,000 campaigns returns 100 per request, newest first. Offset pagination asks for offset=0, then offset=100, and so on for 230 requests. A campaign created while you are on page 40 lands on page 1 and pushes every later record down 1 position, so the last record of page 40 becomes the first of page 41 and you load it twice. A deletion above your page shifts records up and you skip 1. With an oldest-first sort, a new campaign lands at the end and repeats nothing.

Cursor pagination returns an opaque token with each page that means continue after this record, so inserts and deletes elsewhere cannot move your position. When a vendor offers both, use the cursor, and store it after each page's write, so a job that dies on page 180 resumes there.

Rate limits: the vendor's 429

Vendors commonly cap requests, say 600 per minute per account. The 601st gets status 429 and often a Retry-After header saying how many seconds to wait. A 429 is a transient failure: retry with exponential backoff, and when the header is present use its number instead of your own. Hammering a vendor that has told you to wait can get API keys suspended.

import time


def fetch_all(client, state):
    cursor = state.read_cursor("campaigns")
    delay = 1
    while True:
        response = client.get("/campaigns", params={"limit": 100, "after": cursor})
        if response.status == 429:
            wait = int(response.headers.get("Retry-After", delay))
            time.sleep(wait)
            delay = min(delay * 2, 60)
            continue
        response.raise_for_status()
        page = response.json()
        if not page.get("ok", True):
            raise RuntimeError(f"vendor returned 200 with error: {page.get('error')}")
        write_replace_by_key(page["data"], table="raw.campaigns", key="campaign_id")
        delay = 1
        cursor = page.get("next_cursor")
        if cursor is None:
            state.write_cursor("campaigns", None)
            return
        state.write_cursor("campaigns", cursor)

Four things keep the loop honest. The 429 branch sleeps for the vendor's number when present and doubles its own otherwise, capped at 60 seconds. The ok check reads the body, because some vendors return 200 with an error inside. The cursor is written only after the page's rows land, so a crash resumes on the failed page. On the last page the stored cursor is cleared, because it exists to resume one crawl: left in place, it would start the next hourly run at the final page, and the other 22,900 campaigns would never be re-read.

The failure modes vendors create

What vendors change underneath you

• A page cap: the endpoint returns an empty page for any offset of 10,000 or more, so a 23,000-record pull stops at record 10,000 with no error.
• A field that turns nullable: budget_cents was always present, then 1 campaign returns null and the integer cast fails on page 77.
• A timeout on page 230 with no cursor stored, so the retry re-crawls from page 1 and spends the rate limit on pages it already loaded.

Do

• Pull by updated_since when offered, with a few minutes of overlap
• Honor Retry-After before applying your own backoff

Avoid

• Retry a 401 or 403: the credential is wrong and waiting cannot fix it
• Parse timestamps without confirming the vendor's timezone in writing

Practice: the drifting count

Your hourly campaign pull uses offset pagination over a newest-first list and appends rows to raw.campaigns. Row counts run 2 to 5 higher than the vendor's dashboard most hours, never lower. What explains it?

• The rate limit makes retried pages load twice. Honor Retry-After before backing off and the counts will match.
• Campaigns created during the pull push later records down 1, so a boundary record repeats, and the append keeps both copies.
• Deleted campaigns shift records the other way, so each deletion causes a skipped record and the count drifts upward.
• The high-water mark advanced before the write, so each run re-pulls the previous hour and loads it twice.

Capstone: the webhook outage

A payments vendor offers webhooks and an events API that returns the same events under the same event_id, with cursor pagination and an updated_since filter. Your webhook endpoint, which enqueues to a queue, was down for 40 minutes on Monday from 14:00, and the vendor's retries for that window are exhausted. Which approach restores Monday's data?

• Ask the vendor to replay the webhooks from 14:00 onward, since it keeps a per-customer offset and can resend from any position.
• Rerun the queue consumer from the offset it held at 14:00, because the queue retained every event from the outage.
• Pull the events API with updated_since set to 13:50 and write with replace by event_id, so lost events arrive and landed ones are overwritten with identical copies.
• Wait for the vendor's nightly settlement file and load it over the webhook table, since it holds every event of the day.

Capstone: 4 sources, 4 markers

Four sources feed a new raw layer: the orders database, a payment event queue, a bank's nightly files, and an ads vendor's cursor-paginated API. Which set of progress markers is right for all 4?

• Orders: high-water mark on updated_at. Queue: the committed offset. Files: checksums of each loaded batch. API: the cursor written after each page's rows land.
• Orders: the committed offset. Queue: high-water mark on updated_at. Files: the last cursor returned. API: checksums of each loaded batch.
• Orders: high-water mark on updated_at. Queue: high-water mark on the event timestamp. Files: the presence of the sentinel. API: the page number last fetched.
• Orders: a hash of the whole table. Queue: the committed offset. Files: checksums of each loaded batch. API: the offset value last fetched.

About this lesson

This section is part of the Ingestion Patterns: 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.