REST & GraphQL API Ingestion: Pagination, Rate Limits, Incremental Cursors & Retry/Backoff

API ingestion is the deceptively simple task of pulling records out of someone else's REST or GraphQL endpoint and landing them in your warehouse — and it is where more data pipelines silently lose, duplicate, or stall on rows than any other stage, because the endpoint was designed to serve a web app, not to be drained by a nightly connector. Every third-party integration your business depends on — the payments API, the CRM, the ticketing system, the ad platform — hands you data one page at a time, behind a rate limiting quota, with no change data capture and no transactional guarantees, and expects you to walk the entire result set without skipping a record when a new one is inserted mid-scan, without tripping a 429 that gets your key throttled, and without re-pulling the full history every run.

This guide is the senior-data-engineering walkthrough for building a connector that survives all of that. It covers pagination (offset/limit, keyset/cursor, opaque page tokens, and GraphQL edges/pageInfo connections), the rate limiting posture that keeps you under quota (token buckets, honoring 429 and Retry-After, capping concurrency), the incremental cursor that pulls only what changed since the last run (a durable updated_since watermark with an overlap window and idempotent upserts), and the retry backoff machinery that lets a flaky upstream fail without corrupting your data (exponential backoff with full jitter, a retry budget, a circuit breaker, and a dead-letter queue). Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the ETL practice library →, rehearse the query fundamentals on the SQL practice library →, and sharpen the delivery axis with the streaming practice library →.

---

On this page

• Why API ingestion is the pick-one design that binds every connector
• Pagination — offset, keyset, page-token, GraphQL connections
• Rate limits & throttling — token bucket, 429, Retry-After
• Incremental cursors & idempotency
• Retry & backoff — exponential backoff, jitter, dead-letter
• Cheat sheet — API ingestion recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why API ingestion is the pick-one design that binds every connector

Four axes, four ways to lose data — the choices bind you for the life of the connector

The one-sentence invariant: API ingestion is the exercise of draining a result set that lives behind someone else's HTTP endpoint by picking a pagination model to walk every record, a rate-limit posture to stay under quota, an incremental strategy to pull only what changed, and a failure-handling policy to survive transient errors — and each of those four choices trades correctness against throughput against upstream friendliness in a way that every downstream table inherits. Unlike a database you own, you cannot tail a write-ahead log, add a trigger, or ask the DBA for a replication slot. You get a paginated, rate-limited, cursor-less HTTP surface, and the entire correctness story of the pipeline lives in how carefully your connector walks it.

The four axes interviewers actually probe.

• Pagination model. How do you walk the whole set without skipping or double-counting? Offset/limit is trivial but drifts when rows are inserted mid-scan; keyset/cursor pagination anchors on a stable sort key and resumes cleanly; opaque page tokens hand you a black-box next string; GraphQL returns edges plus a pageInfo.endCursor. Interviewers open here because "just add ?page=2" is the answer that skips rows in production.
• Rate-limit posture. How do you stay under the quota without a human watching? A naive loop hammers the API, trips a 429, and gets your key throttled or banned. The senior answer shapes traffic client-side with a token-bucket limiter, honors the server's Retry-After header, and caps concurrency. Getting this wrong turns one slow connector into a platform-wide incident when the API provider rate-limits your whole account.
• Incremental strategy. How do you avoid re-pulling the full history every run? Most APIs expose an updated_since / modified_after filter and a monotonic id or updated_at. The connector persists a cursor, pulls the delta, and advances the cursor — with an overlap window to catch rows that committed late and an idempotent upsert so replays don't duplicate. Interviewers probe this because a full-refresh connector that "works" in dev quietly becomes a 6-hour job that blows the quota in prod.
• Failure handling. How do you survive a flaky upstream without corrupting data? Networks blip, APIs return 500s and 503s, and gateways time out. The senior answer classifies errors into retryable vs permanent, retries the retryable ones with exponential backoff plus jitter under a retry budget, trips a circuit breaker when the whole API is down, and dead-letters the poison records instead of blocking the pipeline forever.

The 2026 reality — REST and GraphQL, same four problems.

• REST list endpoints dominate: GET /v1/customers?limit=100&updated_since=... with either offset, a cursor query param, or a Link: <...>; rel="next" header (the GitHub/Stripe style). Every axis applies.
• GraphQL APIs hand you a cursor connection (edges { node cursor } pageInfo { endCursor hasNextPage }) and often bill by query cost points rather than request count — the rate-limit axis changes shape but does not disappear.
• Webhook + poll hybrids are common: the API pushes a webhook "something changed," and the connector then pulls the delta via an incremental cursor. The pull side is still ordinary API ingestion.
• Managed connectors (Fivetran, Airbyte, Meltano/Singer taps) implement exactly these four axes under the hood. Knowing what they do lets you debug them and lets you hand-roll the connector the managed tool doesn't have.

What interviewers listen for.

• Do you name all four axes — pagination, rate limits, incremental, retries — without prompting? — senior signal.
• Do you say "offset pagination drifts under concurrent inserts, so I use keyset" the moment pagination comes up? — required answer.
• Do you shape traffic client-side with a token bucket rather than "just catch the 429 and retry"? — senior signal.
• Do you make the incremental pull idempotent with an upsert and an overlap window, not "assume the API's updated_since is exact"? — senior signal.
• Do you describe retries as "exponential backoff with jitter under a budget, then dead-letter" rather than "retry three times"? — required answer.

Worked example — the four-axis connector comparison

Detailed explanation. The most useful artifact for an API-ingestion interview is a four-row table that, for a given endpoint, names the choice on each axis. Every serious connector discussion converges on this table; having it in your head turns a rambling answer into a crisp one. Walk through building it for a hypothetical GET /v1/orders endpoint on a payments API that must feed a Snowflake warehouse.

• The endpoint. GET /v1/orders?limit=100 — returns up to 100 orders, newest first, with a has_more flag and a next_cursor string.
• The quota. 100 requests per second per key, 429 with Retry-After on breach.
• The change signal. Each order has an immutable id and a mutable updated_at; the endpoint accepts updated_since.
• The downstream. Snowflake RAW.orders, full history, deduplicated by id.

Question. Fill the four-axis table for the /v1/orders endpoint and name the choice on each axis.

Input.

Axis	Options	Choice for /v1/orders
Pagination	offset / keyset / page-token / GraphQL	opaque next_cursor page-token
Rate limits	none / retry-only / token bucket	client token bucket @ 90 req/s + honor 429
Incremental	full refresh / updated_since cursor	updated_since cursor on updated_at
Failure handling	none / fixed retry / backoff+jitter	exponential backoff + jitter + DLQ

Code.

# The connector config that encodes all four axis choices
from dataclasses import dataclass

@dataclass(frozen=True)
class IngestConfig:
    # Axis 1 — pagination
    page_size: int = 100
    pagination: str = "page_token"      # opaque next_cursor from the API

    # Axis 2 — rate limits
    requests_per_second: float = 90.0   # stay under the 100/s quota
    max_in_flight: int = 5              # concurrency cap

    # Axis 3 — incremental
    cursor_field: str = "updated_at"    # what we watermark on
    overlap_seconds: int = 300          # 5-min safety window

    # Axis 4 — failure handling
    max_retries: int = 5
    base_backoff_seconds: float = 1.0
    dead_letter_after: int = 5          # DLQ a record after N failed attempts

CONFIG = IngestConfig()

Step-by-step explanation.

1. Pagination is chosen by what the API gives you, not by preference. /v1/orders returns an opaque next_cursor, so the connector must loop on it — offset would drift and there is no way to keyset because the sort key is not exposed as a filter. Read the API docs first; the pagination axis is dictated, not designed.
2. The rate-limit posture is set below the published quota — 90 req/s against a 100/s limit leaves headroom for clock skew and burst. The max_in_flight cap bounds concurrency so a burst of parallel workers cannot collectively exceed the bucket.
3. The incremental axis picks updated_at as the cursor field because it advances on every mutation; the 5-minute overlap_seconds re-scans a small window each run so rows that committed with an earlier updated_at than the last high-watermark are not missed.
4. The failure axis is a policy, not a reflex: at most 5 retries with exponential backoff from a 1-second base, and any record still failing after 5 attempts goes to a dead-letter queue instead of blocking the run.
5. Encoding all four axes in one frozen config object is the senior move — it makes the connector's behaviour auditable and testable, and it is the artifact a reviewer reads to understand the whole ingestion contract at a glance.

Output.

Axis	Decision	Consequence downstream
Pagination	page-token loop on next_cursor	every order walked, no drift
Rate limits	90 req/s token bucket + 429 honor	key never throttled
Incremental	updated_since=cursor + 5-min overlap	only the delta pulled; no missed late rows
Failure handling	backoff + jitter + DLQ after 5	flaky upstream never corrupts RAW.orders

Rule of thumb. Before writing a single request, fill the four-axis table from the API docs. The pagination and rate-limit axes are dictated by the endpoint; the incremental and failure axes are designed by you. Pin all four in a config object so the whole ingestion contract lives in one place.

Worked example — what interviewers actually probe

Detailed explanation. The senior API-ingestion interview has a predictable arc: an ambiguous opener ("how would you pull all our Stripe charges into the warehouse?"), then progressive narrowing to test whether you know the four axes. Candidates who name pagination, rate limits, incremental, and retries as distinct problems score highest; candidates who say "I'd write a loop with requests.get" score lowest. Walk through the grading rubric.

• Ambiguous opener. "Pull all orders from this API into Snowflake." — invites the four-axis framing.
• Follow-up 1. "There are 40 million orders — how do you page through them?" — probes pagination.
• Follow-up 2. "The API allows 100 requests a second — how do you not get throttled?" — probes rate limits.
• Follow-up 3. "The job runs hourly — how do you avoid re-pulling everything?" — probes incremental.
• Follow-up 4. "The API returned a 503 mid-run — now what?" — probes failure handling.

Question. Draft a five-minute senior answer that covers all four axes before the follow-ups are even asked.

Input.

Interview signal	Weak answer	Senior answer
Pagination	"increment ?page"	"keyset/cursor so inserts don't shift the window"
Rate limits	"sleep 1 second between calls"	"token-bucket limiter under quota + honor Retry-After"
Incremental	"pull everything, dedupe later"	"updated_since cursor + overlap window + upsert"
Failure	"wrap it in try/except and retry"	"classify errors, backoff+jitter under a budget, DLQ"
Idempotency	"hope it doesn't run twice"	"upsert on natural key so replays are safe"

Code.

Senior API-ingestion answer template (5 minutes)
================================================

Minute 1 — name the four axes up front
  "Four problems: how I page the whole set, how I stay under the
   rate limit, how I pull only the delta, and how I survive transient
   failures. I'll design each."

Minute 2 — pagination
  "I prefer keyset/cursor pagination anchored on a stable sort key,
   or the API's opaque page token, so concurrent inserts never shift
   my window and skip a row — which is exactly what offset does."

Minute 3 — rate limits
  "I shape traffic client-side with a token-bucket limiter set below
   the published quota, cap concurrency, and on a 429 I sleep for the
   server's Retry-After before resuming. I never just retry into a
   throttle."

Minute 4 — incremental + idempotency
  "I persist a durable cursor on updated_at, request updated_since =
   cursor minus a small overlap window to catch late rows, and land
   everything through an idempotent UPSERT on the natural key so a
   replay or an overlap never duplicates."

Minute 5 — failure handling
  "Transient errors (429, 500, 503, timeouts) retry with exponential
   backoff plus full jitter under a retry budget; permanent errors
   (400, 401, 404) don't retry. A record still failing after the
   budget goes to a dead-letter queue, and a sustained failure rate
   trips a circuit breaker so I stop hammering a dead API."

Step-by-step explanation.

1. Minute 1 is the framing that scores. Naming the four axes immediately signals you see API ingestion as a design with independent decisions, not a script. Weak candidates jump straight to requests.get and get narrowed to death by the follow-ups.
2. Minute 2 pre-empts the pagination follow-up. Saying "offset drifts under concurrent inserts" without being asked is the single most reliable senior signal in this interview.
3. Minute 3 shows you protect the provider, not just yourself. Client-side traffic shaping plus honoring Retry-After is the difference between a good API citizen and the connector that gets the whole account banned.
4. Minute 4 couples incremental with idempotency in one breath. The overlap window and the upsert are what make "pull only the delta" correct rather than merely fast.
5. Minute 5 splits errors into retryable and permanent and bounds the retries. "Retry three times" loses the offer; "backoff plus jitter under a budget, then dead-letter" wins it.

Output.

Grading criterion	Weak score	Senior score
Names all four axes in minute 1	rare	mandatory
Says offset drifts, uses keyset/token	occasional	required
Shapes traffic client-side	rare	senior signal
Couples incremental with idempotent upsert	rare	senior signal
Backoff+jitter under a budget + DLQ	rare	senior signal

Rule of thumb. The senior API-ingestion answer is a five-minute monologue that closes all four axes before the interviewer can ask a follow-up. Rehearse it once; deploy it every time an "ingest this API" question appears.

Worked example — the "pick the strategy" decision tree

Detailed explanation. Given a new endpoint, the senior engineer runs a short decision tree in their head to fix the pagination and incremental strategy. Codifying the tree makes the answer reproducible: hand it any endpoint and it produces a plan. Walk the tree for three canonical endpoints — a keyset-friendly REST list, an opaque-token REST list, and a GraphQL connection.

• Q1. Does the endpoint accept a filter on a stable sort key (id > X, created_after)? → yes = keyset; no = go to Q2.
• Q2. Does it return an opaque next cursor / page token? → yes = page-token loop; no = go to Q3.
• Q3. Is it GraphQL with pageInfo? → yes = cursor-connection walk; no = fall back to offset (and accept the drift risk with a reconcile).
• Q4 (incremental branch). Does it accept updated_since / modified_after? → yes = incremental cursor; no = full refresh + change-detection on merge.

Question. Walk the tree for the three endpoints and record the pagination + incremental plan for each.

Input.

Endpoint	Stable key filter?	Opaque token?	GraphQL?	updated_since?
REST /users?since_id=	yes	—	no	yes
REST /orders?cursor=	no	yes	no	yes
GraphQL issues(after:)	no	yes (cursor)	yes	yes

Code.

def pick_strategy(has_key_filter: bool,
                  has_page_token: bool,
                  is_graphql: bool,
                  has_updated_since: bool) -> dict[str, str]:
    """Return the pagination + incremental plan for an endpoint."""
    if has_key_filter:
        pagination = "keyset"
    elif is_graphql:
        pagination = "graphql_connection"
    elif has_page_token:
        pagination = "page_token"
    else:
        pagination = "offset (+ reconcile; drifts under inserts)"

    incremental = "updated_since cursor" if has_updated_since else "full refresh + merge"
    return {"pagination": pagination, "incremental": incremental}


print(pick_strategy(True,  False, False, True))
# → {'pagination': 'keyset', 'incremental': 'updated_since cursor'}

print(pick_strategy(False, True,  False, True))
# → {'pagination': 'page_token', 'incremental': 'updated_since cursor'}

print(pick_strategy(False, True,  True,  True))
# → {'pagination': 'graphql_connection', 'incremental': 'updated_since cursor'}

Step-by-step explanation.

1. Endpoint 1 exposes since_id, a filter on the monotonic id, so keyset wins — the connector asks for since_id = last_max_id and never re-reads a page. This is the most robust REST pagination and should be preferred whenever the API allows it.
2. Endpoint 2 gives no key filter but returns an opaque cursor, so the connector loops on the token until has_more is false. It is stable against inserts (the server manages the cursor) but the token cannot be reused across runs, so incremental relies on updated_since, not on the cursor.
3. Endpoint 3 is GraphQL: the plan is a cursor-connection walk (after: endCursor until hasNextPage is false), which is page-token pagination with a schema-standard shape.
4. All three endpoints accept updated_since, so all three use an incremental cursor on updated_at. If one had not, the fallback would be a full refresh with change detection at merge time — more expensive but always correct.
5. The tree separates the pagination decision (dictated by the endpoint's capabilities) from the incremental decision (dictated by whether a change filter exists). Keeping them independent is what lets you reuse one paginator across many endpoints.

Output.

Endpoint	Pagination plan	Incremental plan
REST /users?since_id=	keyset	updated_since cursor
REST /orders?cursor=	page-token loop	updated_since cursor
GraphQL issues(after:)	cursor-connection walk	updated_since cursor

Rule of thumb. Run the decision tree per endpoint: keyset if a stable-key filter exists, else GraphQL connection or opaque token, else offset with a reconcile. Decide incremental separately on whether an updated_since filter exists. Two independent decisions, one reusable connector.

Senior interview question on API ingestion design

A senior interviewer often opens with: "You need to ingest a third-party REST API with roughly 40 million records into Snowflake, refreshed hourly. The API allows 100 requests per second, paginates with an opaque cursor, exposes an updated_since filter, and occasionally returns 429s and 503s. Design the connector end to end — pagination, rate limiting, incremental cursor, and failure handling — and explain how a mid-run crash resumes without losing or duplicating a record."

Solution Using a checkpointed connector that composes all four axes

# connector.py — a durable API-ingestion connector composing all four axes
import time
import json
import random
import requests
from pathlib import Path
from datetime import datetime, timedelta, timezone

CHECKPOINT = Path("/state/orders_cursor.json")
BASE_URL   = "https://api.example.com/v1/orders"

def load_cursor() -> datetime:
    if CHECKPOINT.exists():
        return datetime.fromisoformat(json.loads(CHECKPOINT.read_text())["cursor"])
    return datetime(1970, 1, 1, tzinfo=timezone.utc)   # bootstrap

def save_cursor(ts: datetime) -> None:
    tmp = CHECKPOINT.with_suffix(".tmp")
    tmp.write_text(json.dumps({"cursor": ts.isoformat()}))
    tmp.replace(CHECKPOINT)                             # atomic checkpoint write

def run() -> int:
    prev   = load_cursor()
    # Axis 3 — incremental with a 5-minute overlap window
    since  = (prev - timedelta(minutes=5)).isoformat()
    params = {"limit": 100, "updated_since": since}
    max_seen = prev
    n = 0

    while True:
        # Axis 4 — retry with backoff + jitter (see get_with_retry below)
        resp = get_with_retry(BASE_URL, params)
        page = resp.json()

        for order in page["data"]:
            upsert(order)                              # idempotent on order["id"]
            ts = datetime.fromisoformat(order["updated_at"])
            max_seen = max(max_seen, ts)
            n += 1

        if not page.get("has_more"):
            break
        params["cursor"] = page["next_cursor"]         # Axis 1 — page-token loop

    # Advance the cursor to the max observed, not to "now"
    if max_seen > prev:
        save_cursor(max_seen)
    return n

# Axis 2 + Axis 4 — rate-limited, retried GET
class TokenBucket:
    def __init__(self, rate: float, capacity: int):
        self.rate, self.capacity = rate, capacity
        self.tokens, self.ts = capacity, time.monotonic()

    def take(self) -> None:
        while True:
            now = time.monotonic()
            self.tokens = min(self.capacity, self.tokens + (now - self.ts) * self.rate)
            self.ts = now
            if self.tokens >= 1:
                self.tokens -= 1
                return
            time.sleep((1 - self.tokens) / self.rate)

BUCKET = TokenBucket(rate=90, capacity=90)             # 90 req/s under the 100/s quota

def get_with_retry(url: str, params: dict, budget: int = 5) -> requests.Response:
    for attempt in range(budget):
        BUCKET.take()                                  # Axis 2 — stay under quota
        resp = requests.get(url, params=params, timeout=30)
        if resp.status_code == 429:                    # honour Retry-After exactly
            time.sleep(float(resp.headers.get("Retry-After", "2")))
            continue
        if resp.status_code in (500, 502, 503, 504):   # transient → backoff + jitter
            sleep = min(60, (2 ** attempt)) * (0.5 + random.random() / 2)
            time.sleep(sleep)
            continue
        resp.raise_for_status()                        # 4xx (except 429) → permanent
        return resp
    raise RuntimeError(f"exhausted retry budget for {url}")

Step-by-step trace.

Step	Mechanism	Result
Load cursor	read orders_cursor.json (or epoch)	resume point survives crashes
Overlap	updated_since = cursor - 5 min	late-committing rows re-scanned
Page loop	follow next_cursor until has_more=false	full delta walked, no drift
Rate limit	TokenBucket(90) before every GET	never exceeds 100/s quota
429	sleep Retry-After, retry	throttle respected, not fought
5xx / timeout	exponential backoff + full jitter, budget 5	transient errors ride out
Upsert	MERGE on id	replays and overlap never duplicate
Advance cursor	max(updated_at) observed, atomic write	next run starts exactly here

After deployment, the hourly run pulls only orders changed since the last cursor (minus the overlap), pages through them under 90 req/s, honors every 429, retries transient 5xx with jittered backoff, and upserts each order by id. A mid-run crash loses nothing: the cursor is only advanced after the run completes, so the next run re-pulls the in-flight window and the idempotent upsert absorbs the duplicates.

Output:

Metric	Full-refresh connector	Four-axis connector
Records pulled per run	40,000,000	~50,000 (delta only)
Requests per run	400,000	~500
Throttle incidents	frequent (hammered API)	zero (token bucket)
Duplicate rows on retry	yes	none (idempotent upsert)
Crash recovery	restart from zero	resume from cursor

Why this works — concept by concept:

• Page-token loop — following the API's opaque next_cursor until has_more is false walks the entire delta without offset drift, because the server owns the cursor and inserts cannot shift it.
• Token-bucket limiter — refilling at 90 tokens/second and taking one per request shapes traffic below the published quota, so the connector never trips the provider's throttle in the first place.
• Overlap window + idempotent upsert — requesting updated_since = cursor - 5 min re-scans a small trailing window to catch late-committing rows, and the MERGE on id makes those overlapping (and any replayed) rows harmless.
• Cursor advanced to observed max, atomically, after the run — writing the checkpoint only when the run finishes, and only to the maximum updated_at actually seen, guarantees a crash resumes from a correct point rather than skipping the in-flight window.
• Cost — one small checkpoint file, ~500 requests/run instead of 400,000, one token-bucket sleep amortized to zero under normal load, and one upsert per record. Net O(delta) per run versus O(all records) for full refresh, with zero throttle incidents and exactly-once effect despite at-least-once delivery.

ETL
Topic — etl
ETL problems on API ingestion connectors

Practice →

Data Processing Topic — data-processing Data processing problems on paginated sources

Practice →

---

2. Pagination — offset, keyset, page-token, GraphQL connections

pagination is how you walk a result set that never fits in one response — and the model you pick decides whether concurrent inserts silently skip your rows

The mental model in one line: pagination is the pattern for draining a large result set one bounded page at a time, and the four models — offset/limit, keyset (seek), opaque page-token, and GraphQL cursor connections — trade simplicity against stability under concurrent writes, where offset silently skips or repeats rows when the underlying data shifts mid-scan and keyset/cursor/token models anchor on a stable position that inserts cannot disturb. Every connector engineer has shipped an offset paginator that "worked" until the source got busy; the senior move is to reach for keyset or the API's cursor from the start.

The four pagination models.

• Offset / limit. ?limit=100&offset=200 — "skip 200, take 100." Trivial to write and the only option some APIs give. Its fatal flaw: if rows are inserted or deleted before the offset while you page, the window shifts — you skip rows or read duplicates. Also O(offset) on the server for large offsets. Acceptable only for small, static result sets.
• Keyset / seek. ?limit=100&since_id=1042 (or created_after=...) — "give me the next 100 rows after this stable key." Anchored on a monotonic sort key, so inserts never move your position. Resumes cleanly across runs (persist the last key). The default for any REST API that exposes a key filter.
• Opaque page-token. The response carries a next_cursor / next_page_token string you pass back verbatim. The server encodes the position; you treat it as a black box. Stable within a scan, but usually not reusable across runs, so incremental relies on a separate updated_since filter.
• GraphQL cursor connection. The Relay-style shape: edges { node cursor } plus pageInfo { endCursor hasNextPage }. You pass after: endCursor until hasNextPage is false. It is page-token pagination with a schema-standardized contract.

Why offset drifts — the one diagram to memorise.

• The scan. You read offset=0..99, then offset=100..199, etc.
• The insert. Between page 1 and page 2, a new row lands at the top (newest-first ordering).
• The bug. Every row shifts down one position, so the row that was at offset 100 is now at offset 101 — your offset=100 page re-reads the row that was at offset 99, and one real row is skipped forever.
• The fix. Keyset — "after id 1042" — is immune, because the anchor is the data's own key, not a positional count.

The Link header and has_more conventions.

• Link: <url>; rel="next". GitHub/Stripe-style: the response's Link header carries the fully-formed next-page URL. Follow it until there is no rel="next".
• has_more + next_cursor. Stripe-style JSON body flags: loop while has_more is true, passing next_cursor back.
• Bare next URL in the body. Many APIs put a next (or null) field in the JSON envelope. Same loop, different field name.

Common interview probes on pagination.

• "Why not just use offset?" — required answer: it drifts under concurrent inserts/deletes and is O(offset) at scale.
• "How do you resume pagination after a crash?" — persist the keyset (or rely on updated_since; opaque tokens usually don't survive a run).
• "How does GraphQL paginate?" — edges/pageInfo, after: endCursor until hasNextPage is false.
• "How do you paginate a fast-changing feed without gaps?" — keyset on a monotonic id, and sort by that id, not by a mutable field.

Worked example — keyset pagination over a REST list

Detailed explanation. The canonical stable REST paginator: sort by a monotonic id, ask for since_id = last_id_seen, and loop until a short page (fewer than limit) signals the end. It resumes across runs by persisting the last id and is immune to concurrent inserts. Build it against a /v1/users endpoint.

• Endpoint. GET /v1/users?limit=100&since_id=<id> — returns users with id > since_id, ascending, up to limit.
• Terminal condition. A page with fewer than limit rows means we've reached the tail.
• Resume. Persist max(id) after each page.

Question. Implement a keyset paginator that walks all users and can resume from a persisted id.

Input.

Parameter	Value
Endpoint	GET /v1/users
Sort key	id (monotonic)
Page size	100
Filter	since_id
Resume state	last_id

Code.

import requests

def paginate_users(base_url: str, start_after_id: int = 0, limit: int = 100):
    """Yield every user via keyset pagination; stable under concurrent inserts."""
    since_id = start_after_id
    while True:
        resp = requests.get(
            f"{base_url}/v1/users",
            params={"limit": limit, "since_id": since_id, "order": "id.asc"},
            timeout=30,
        )
        resp.raise_for_status()
        users = resp.json()["data"]

        if not users:
            return                     # empty page → done

        for user in users:
            yield user

        since_id = users[-1]["id"]     # advance the keyset to the last id seen

        if len(users) < limit:
            return                     # short page → tail reached

Step-by-step explanation.

1. The request always filters since_id and sorts ascending by id. Because id is monotonic and immutable, "everything after id X" is a stable window — a row inserted with a higher id will simply appear on a later page, and one inserted with a lower id was already read.
2. since_id starts at 0 (bootstrap) or at a persisted value (resume). This is the entire resume story: keyset pagination is stateful in exactly one integer, which you can checkpoint anywhere.
3. After yielding a page, the connector advances since_id to users[-1]["id"] — the largest id on the page, guaranteed to be the last because the server sorted ascending. The next request continues strictly after it, so no row is read twice.
4. The loop terminates on either an empty page or a short page (len(users) < limit). A short page means the server had no more rows after the last id, which is the tail. Relying on a short page avoids one wasted final request in most cases and an empty page covers the boundary where the last real page was exactly limit rows.
5. There is no offset anywhere, so there is no drift: concurrent inserts and deletes cannot shift the window. This is why keyset is the default recommendation for any REST list that exposes a key filter.

Output.

Request	since_id	Rows returned	New since_id
1	0	100	100
2	100	100	213 (ids not contiguous)
3	213	100	350
4	350	42 (short page)	— (done)

Rule of thumb. Prefer keyset pagination whenever the API exposes a filter on a monotonic key. Sort by that key ascending, advance to the last id on each page, and terminate on a short page. One integer of state, zero drift, trivial resume.

Worked example — the opaque page-token loop

Detailed explanation. When the API gives no key filter but returns an opaque next_cursor, the connector loops on the token until the API says there are no more pages. The token is a black box — never parse it, never construct it, just pass it back. Build the loop against a Stripe-style has_more + next_cursor envelope.

• Envelope. { "data": [...], "has_more": true, "next_cursor": "cus_abc..." }.
• Loop. Pass cursor=next_cursor back until has_more is false.
• Resume caveat. Opaque tokens usually expire; do not persist them across runs — use updated_since for incremental.

Question. Implement a page-token paginator that walks a Stripe-style list endpoint to completion.

Input.

Parameter	Value
Envelope	data / has_more / next_cursor
Loop control	has_more
Token param	cursor
Cross-run resume	via updated_since, not the token

Code.

import requests

def paginate_by_token(base_url: str, path: str, params: dict | None = None):
    """Walk an opaque page-token endpoint until has_more is false."""
    params = dict(params or {})
    params.setdefault("limit", 100)
    cursor = None

    while True:
        if cursor:
            params["cursor"] = cursor      # opaque token, passed back verbatim

        resp = requests.get(f"{base_url}{path}", params=params, timeout=30)
        resp.raise_for_status()
        body = resp.json()

        for item in body["data"]:
            yield item

        if not body.get("has_more"):
            return                          # server says: no more pages
        cursor = body["next_cursor"]        # never parse or build this string

Step-by-step explanation.

1. The first request sends no cursor — the API returns the first page and, if more exist, a next_cursor. Subsequent requests attach the cursor. This "cursor is absent on page one" convention is near-universal for token pagination.
2. The token is opaque by contract. The server may encode an offset, a keyset, a timestamp, or an encrypted blob inside it; your code must treat it as a black box, because the encoding can change without notice and any attempt to parse it is a future outage.
3. The loop yields each item as it streams pages, so memory stays flat regardless of total size — the connector never materializes the whole result set. This generator shape is the right default for any paginator.
4. Termination is driven entirely by the server's has_more flag, not by counting rows. The server is authoritative about when the scan is complete; trusting has_more avoids the off-by-one bugs that row-counting introduces.
5. The token is not persisted across runs, because opaque cursors typically expire and are not resumable. Incremental fetching is layered on top with an updated_since filter in params, which is durable. Keeping "walk this scan" separate from "resume next run" is the key design split.

Output.

Request	cursor sent	has_more	next_cursor
1	(none)	true	cus_0aa
2	cus_0aa	true	cus_0bb
3	cus_0bb	true	cus_0cc
4	cus_0cc	false	(none) → done

Rule of thumb. Treat the page token as a black box — never parse or construct it — and let the server's has_more/next field drive termination. Keep the intra-scan token separate from the cross-run updated_since cursor; the token walks this scan, the updated_since resumes the next run.

Worked example — walking a GraphQL cursor connection

Detailed explanation. GraphQL standardizes pagination as a Relay-style connection: the query asks for first: N, after: $cursor, and the response returns edges { node cursor } plus pageInfo { endCursor hasNextPage }. You loop, feeding endCursor back into after, until hasNextPage is false. Build the walk against a GitHub-style issues connection.

• Query. issues(first: 100, after: $cursor) { edges { node { ... } cursor } pageInfo { endCursor hasNextPage } }.
• Loop. after = pageInfo.endCursor while pageInfo.hasNextPage.
• Node. The actual record lives in edges[].node.

Question. Implement a GraphQL connection walk that pulls every issue.

Input.

Parameter	Value
Transport	POST /graphql
Page arg	first: 100, after: $cursor
Records	edges[].node
Loop control	pageInfo.hasNextPage
Next cursor	pageInfo.endCursor

Code.

import requests

QUERY = """
query($cursor: String) {
  repository(owner: "acme", name: "app") {
    issues(first: 100, after: $cursor, orderBy: {field: UPDATED_AT, direction: ASC}) {
      edges { node { id title updatedAt } cursor }
      pageInfo { endCursor hasNextPage }
    }
  }
}
"""

def paginate_graphql(endpoint: str, token: str):
    """Walk a Relay-style GraphQL connection to completion."""
    cursor = None
    headers = {"Authorization": f"Bearer {token}"}

    while True:
        resp = requests.post(
            endpoint,
            json={"query": QUERY, "variables": {"cursor": cursor}},
            headers=headers,
            timeout=30,
        )
        resp.raise_for_status()
        conn = resp.json()["data"]["repository"]["issues"]

        for edge in conn["edges"]:
            yield edge["node"]                     # the record is the node

        page = conn["pageInfo"]
        if not page["hasNextPage"]:
            return
        cursor = page["endCursor"]                 # feed endCursor into `after`

Step-by-step explanation.

1. The query takes $cursor as a variable and passes it to after. On the first request cursor is None, which GraphQL treats as "from the beginning." Ordering explicitly by UPDATED_AT ASC makes the walk deterministic and pairs naturally with an incremental cursor.
2. Each page's records live in edges[].node; the sibling edges[].cursor is the per-edge position (rarely needed directly). Yielding node gives the caller clean records without the connection envelope.
3. pageInfo.endCursor is the position after the last edge on this page — exactly what after wants next. Feeding it back is the entire loop; there is no offset and no drift, just like an opaque REST token.
4. Termination is pageInfo.hasNextPage == false. This is the GraphQL contract's authoritative "no more pages" signal, analogous to REST's has_more.
5. Because GraphQL lets you request only the fields you need (id title updatedAt), the payload is smaller than a REST endpoint that returns the whole object — a real bandwidth and rate-cost win, which matters when the API bills by query cost (covered in the next section).

Output.

Request	after cursor	edges	hasNextPage	endCursor
1	null	100	true	Y3Vyc29yOjEwMA==
2	Y3Vyc29yOjEwMA==	100	true	Y3Vyc29yOjIwMA==
3	Y3Vyc29yOjIwMA==	57	false	(walk complete)

Rule of thumb. For GraphQL, always drive the loop off pageInfo — after: endCursor while hasNextPage. Request only the fields you need and order by updatedAt so the connection walk composes with an incremental cursor. The node is your record; the envelope is plumbing.

Senior interview question on pagination

A senior interviewer might ask: "A REST endpoint returns events newest-first and only supports ?page=&per_page= offset pagination. It receives thousands of new events per minute while you page through 5 million historical events. Your connector keeps missing events. Explain why, and redesign the pagination so no event is lost — even though the API gives you only offset."

Solution Using a stable-sort keyset over the offset API with a reconcile pass

# The bug: offset over a newest-first, actively-growing feed skips rows.
# The fix: page by a STABLE ascending key (event id or created_at) so
# concurrent inserts land AFTER the window instead of shifting it.

import requests

def paginate_events_stable(base_url: str, since_id: int = 0, per_page: int = 200):
    """
    Even though the API 'supports offset', we page by a stable ascending id.
    If the API cannot filter by id, we still sort ascending by created_at and
    treat the last seen (created_at, id) as the resume key — never a raw offset.
    """
    resume_key = since_id
    while True:
        resp = requests.get(
            f"{base_url}/events",
            # ask the server to sort ascending by a stable key; filter by it
            params={"per_page": per_page, "since_id": resume_key, "sort": "id", "order": "asc"},
            timeout=30,
        )
        resp.raise_for_status()
        events = resp.json()["data"]
        if not events:
            return
        for ev in events:
            yield ev
        resume_key = events[-1]["id"]          # keyset, not offset
        if len(events) < per_page:
            return

# If the endpoint truly cannot filter by id (offset-only), page ascending by
# created_at, dedupe by id, and run a nightly reconcile to catch any gaps.
def paginate_offset_with_reconcile(base_url: str, per_page: int = 200):
    seen: set[int] = set()
    page = 1
    while True:
        resp = requests.get(
            f"{base_url}/events",
            params={"per_page": per_page, "page": page, "sort": "created_at", "order": "asc"},
            timeout=30,
        )
        resp.raise_for_status()
        events = resp.json()["data"]
        if not events:
            return
        for ev in events:
            if ev["id"] not in seen:           # dedupe: offset can repeat rows
                seen.add(ev["id"])
                yield ev
        page += 1

Step-by-step trace.

Concern	Offset (broken)	Stable keyset (fixed)
Sort order	newest-first (mutable head)	ascending by stable id
Effect of an insert	every row shifts down → skip	new row appends after window
Resume state	page number (meaningless after inserts)	last id seen
Duplicates	yes (rows re-appear)	none
Server cost	O(offset) deep scans	O(limit) index seek
Safety net	none	dedupe set + nightly reconcile

After the redesign, the connector pages ascending by a stable id (or created_at with an id dedupe), so events inserted during the scan land after the current position instead of shifting it under the window. The offset-only fallback dedupes by id and leans on a nightly reconcile that diffs source count against warehouse count to catch any residual gap.

Output:

Metric	Offset newest-first	Stable keyset
Events skipped during a busy scan	~0.5–3%	0
Duplicate events ingested	possible	none (keyset) / deduped (fallback)
Deep-page server latency	grows with offset	flat (index seek)
Resume after crash	unreliable	exact (last id)

Why this works — concept by concept:

• Stable ascending sort key — paging by a monotonic id (or created_at) instead of a positional offset means a concurrent insert appends after the scan window rather than shifting every row down, which is the exact mechanism by which offset skips events.
• Keyset resume — the resume state is the last id seen, a single stable value, so a crash or a next run continues strictly after it with no dependence on a page number that inserts have invalidated.
• Id dedupe on the offset fallback — when the API truly offers only offset, sorting ascending narrows the drift and a seen set on id removes the duplicates offset can produce; the scan trades memory for correctness.
• Nightly reconcile — a count/PK diff between source and warehouse is the backstop that turns "probably no gaps" into "provably no gaps," catching anything the offset fallback still misses.
• Cost — keyset is an O(limit) index seek per page versus O(offset) deep scans, so it is both correct and faster; the dedupe fallback costs O(rows) memory for the id set, bounded per run, which is the price of using an API that should never have shipped offset-only.

SQL
Topic — sql
SQL keyset and pagination query problems

Practice →

ETL Topic — etl ETL problems on paginated API extraction

Practice →

---

3. Rate limits & throttling — token bucket, 429, Retry-After

rate limiting is the quota that keeps you from draining the API — shape traffic client-side, obey 429, and never fight a throttle

The mental model in one line: rate limiting is the provider's defense against your connector, expressed as a quota (requests per second, requests per window, or GraphQL cost points) enforced by returning HTTP 429 with a Retry-After header, and the senior response is to never reach the 429 by shaping traffic client-side with a token-bucket limiter set below the quota, capping concurrency, and — when a 429 does slip through — sleeping for exactly the server's Retry-After instead of retrying immediately into the throttle. Every connector that gets an account banned did the same thing: it treated the 429 as a retry signal rather than a "you were supposed to slow down" signal.

The quota models you'll meet.

• Fixed window. "1000 requests per minute," reset on the minute boundary. Simple but bursty — you can spend the whole budget in the first second and starve for 59.
• Sliding window. The same limit measured over a rolling window, smoothing the burst edge. Harder to game; the server tracks your recent request timestamps.
• Token bucket. A bucket of N tokens refilling at R per second; each request spends one; empty bucket = wait. This is also the model you should implement client-side, because it naturally allows small bursts while enforcing an average rate.
• GraphQL cost points. Instead of counting requests, the server assigns each query a cost based on the fields and connection sizes requested, and bills against a points budget (e.g. GitHub's 5000 points/hour). A cheap query costs 1; a deep nested connection costs hundreds.

The 429 contract — what the server is telling you.

• Status 429 Too Many Requests. You exceeded the quota. This is not a transient error to retry immediately; it is an instruction to slow down.
• Retry-After header. Either a number of seconds (Retry-After: 2) or an HTTP date. Sleep for exactly this long before the next request. Honoring it is the difference between a brief pause and an escalating ban.
• X-RateLimit-Remaining / X-RateLimit-Reset. Many APIs expose your remaining budget and the reset time on every response, so you can pre-emptively slow down before hitting zero rather than reacting to a 429.
• The escalation. Ignore repeated 429s and providers escalate: longer cooldowns, temporary key suspension, or a permanent ban. The connector's job is to make 429s rare and to back off hard when they happen.

Client-side traffic shaping.

• Token-bucket limiter. Set the rate below the published quota (e.g. 90% of it) to leave headroom for clock skew and measurement error. Every request calls take() and blocks until a token is available.
• Concurrency cap. Even with a rate limiter, N parallel workers can momentarily exceed the instantaneous rate. A semaphore capping max_in_flight bounds the burst.
• Adaptive backoff on remaining budget. Read X-RateLimit-Remaining; when it drops low, widen the inter-request delay so you glide into the reset instead of slamming into a 429.

Common interview probes on rate limiting.

• "How do you avoid getting throttled?" — required answer: token-bucket limiter below the quota, not "sleep and hope."
• "What do you do on a 429?" — sleep for Retry-After, then resume; never retry immediately.
• "How is GraphQL rate limiting different?" — cost points per query, not request count; budget by query complexity.
• "How do you use X-RateLimit-Remaining?" — pre-emptively slow down before the budget hits zero.

Worked example — a token-bucket client limiter

Detailed explanation. The core traffic-shaping primitive: a token bucket that refills at a fixed rate and blocks callers when empty. Set the rate below the quota and every request through the connector calls take() first. Build a thread-safe bucket and wire it into a fetch loop.

• Bucket. Capacity C, refill rate R tokens/second. Tokens accrue continuously; each request spends one.
• Blocking. If no token is available, take() sleeps just long enough for one to accrue.
• Setting. Rate = 90% of the published quota for headroom.

Question. Implement a thread-safe token-bucket limiter and use it to cap a request loop at 90 requests/second.

Input.

Parameter	Value
Published quota	100 req/s
Bucket rate	90 tokens/s
Bucket capacity	90 (allows a 1-second burst)
Concurrency	shared across worker threads

Code.

import time
import threading

class TokenBucket:
    """Thread-safe token bucket: refills at `rate`/s, blocks when empty."""
    def __init__(self, rate: float, capacity: float):
        self.rate = rate
        self.capacity = capacity
        self._tokens = capacity
        self._last = time.monotonic()
        self._lock = threading.Lock()

    def take(self, n: float = 1.0) -> None:
        while True:
            with self._lock:
                now = time.monotonic()
                # accrue tokens for the elapsed time, capped at capacity
                self._tokens = min(self.capacity,
                                   self._tokens + (now - self._last) * self.rate)
                self._last = now
                if self._tokens >= n:
                    self._tokens -= n
                    return
                deficit = n - self._tokens
            time.sleep(deficit / self.rate)     # sleep outside the lock

# 90 req/s under a 100/s quota, allowing a short 90-token burst
bucket = TokenBucket(rate=90.0, capacity=90.0)

def fetch(url: str):
    bucket.take()                               # blocks until a token is free
    return requests.get(url, timeout=30)

Step-by-step explanation.

1. The bucket tracks a floating-point token count and the timestamp of the last refill. On each take(), it computes how many tokens have accrued since _last (elapsed * rate), adds them, and caps at capacity. This lazy refill avoids a background thread.
2. If at least n tokens are available, it spends them and returns immediately — this is the common fast path under normal load, so the limiter adds essentially zero latency when you're under quota.
3. If the bucket is short, it computes the deficit and sleeps exactly deficit / rate seconds — the minimum wait for enough tokens to accrue — outside the lock so other threads aren't blocked while it waits.
4. The lock makes the bucket safe to share across worker threads, so a pool of parallel fetchers collectively respects one global rate. A per-thread bucket would let N threads each do rate req/s and blow the quota N times over.
5. Setting rate = 90 against a 100/s quota leaves 10% headroom. This matters because the server measures on its clock and its window edges; a client running exactly at the limit will trip 429s from measurement skew alone.

Output.

Elapsed	Requests issued	Effective rate	Under quota?
0–1 s	90 (burst)	90/s	yes
1–2 s	90	90/s	yes
10 s	900	90/s avg	yes
any 1 s window	≤ 90	≤ 90/s	yes (10% headroom)

Rule of thumb. Shape traffic with a shared token bucket set to ~90% of the published quota, refilling continuously so short bursts are allowed but the average rate holds. A shared, thread-safe bucket is mandatory the moment you have more than one worker.

Worked example — honoring 429 and Retry-After

Detailed explanation. Even a well-tuned limiter occasionally trips a 429 (another job shares the key, the server's window differs, a burst slipped through). The correct reaction is to sleep for exactly the server's Retry-After and then resume — not to retry immediately, and not to apply your generic exponential backoff, because the server has told you precisely how long to wait. Build a fetch wrapper that distinguishes 429 handling from generic retries.

• On 429. Read Retry-After (seconds or HTTP date), sleep that long, retry.
• On 5xx / timeout. Generic exponential backoff (next section).
• On 4xx (except 429). Permanent; do not retry.

Question. Write a fetch function that honors Retry-After on 429 and pre-emptively slows down using X-RateLimit-Remaining.

Input.

Signal	Meaning	Action
429 + Retry-After: N	throttled	sleep N seconds, retry
429 + Retry-After:	throttled	sleep until date, retry
X-RateLimit-Remaining low	near budget	widen delay pre-emptively
200	ok	proceed

Code.

import time
import email.utils
import requests

def parse_retry_after(value: str) -> float:
    """Retry-After is either delta-seconds or an HTTP-date."""
    try:
        return float(value)
    except ValueError:
        dt = email.utils.parsedate_to_datetime(value)
        return max(0.0, dt.timestamp() - time.time())

def fetch_respecting_limits(url: str, params: dict, bucket, max_429: int = 10):
    for attempt in range(max_429):
        bucket.take()
        resp = requests.get(url, params=params, timeout=30)

        if resp.status_code == 429:
            wait = parse_retry_after(resp.headers.get("Retry-After", "1"))
            time.sleep(wait)                       # obey the server exactly
            continue

        # Pre-emptive slowdown: if the budget is nearly spent, glide to reset
        remaining = int(resp.headers.get("X-RateLimit-Remaining", "9999"))
        reset_in  = float(resp.headers.get("X-RateLimit-Reset-After", "0"))
        if remaining <= 5 and reset_in > 0:
            time.sleep(reset_in / max(remaining, 1))

        resp.raise_for_status()
        return resp
    raise RuntimeError("too many 429s; provider is throttling hard")

Step-by-step explanation.

1. parse_retry_after handles both Retry-After formats: a plain number of seconds, or an HTTP date (some APIs, especially behind CDNs, send a date). Converting the date to a delta gives one uniform "sleep this long" value.
2. On 429, the connector sleeps for exactly Retry-After and retries. It does not apply exponential backoff here — the server named the wait, so second-guessing it either wastes time (waiting longer) or re-trips the throttle (waiting shorter).
3. After a successful response, the connector reads X-RateLimit-Remaining. When the remaining budget is low, it inserts a pre-emptive delay sized to spread the remaining requests across the time until reset — gliding into the window boundary instead of slamming a 429.
4. raise_for_status() turns any other 4xx into an exception (handled as permanent by the caller) and 5xx into an exception the generic retry layer catches. Keeping 429 handling separate from 5xx handling is the key structural point.
5. A bounded max_429 loop prevents an infinite spin if the provider is throttling extremely hard; after too many 429s the connector surfaces an error so a human (or the circuit breaker) can intervene rather than sleeping forever.

Output.

Attempt	Status	Header	Action
1	429	Retry-After: 2	sleep 2s
2	200	X-RateLimit-Remaining: 3	pre-emptive small sleep
3	200	X-RateLimit-Remaining: 50	proceed normally
4	200	—	return

Rule of thumb. On a 429, sleep for exactly Retry-After — never your own backoff. Watch X-RateLimit-Remaining and slow down before it hits zero. The goal is to make 429s rare and, when they happen, to obey the server to the second.

Senior interview question on rate limiting

A senior interviewer might ask: "You run 8 parallel workers ingesting a GraphQL API that bills by query-cost points — 5000 points per hour, and each query you send costs between 1 and 200 points depending on how many nested connections you request. The naive design keeps getting throttled. Design a client-side limiter that respects the cost budget (not a request count), coordinates across all 8 workers, and degrades gracefully when the budget runs low."

Solution Using a shared cost-aware token bucket keyed on GraphQL query cost

# A cost-aware, shared token bucket: tokens ARE points, and each query
# spends its estimated cost before sending. Refill = 5000 points / 3600 s.
import time
import threading
import requests

class CostBucket:
    """Token bucket where tokens are GraphQL cost points, shared across workers."""
    def __init__(self, points_per_hour: int):
        self.rate = points_per_hour / 3600.0     # points per second
        self.capacity = float(points_per_hour)   # allow a burst up to one hour
        self._tokens = self.capacity
        self._last = time.monotonic()
        self._lock = threading.Lock()

    def spend(self, cost: float) -> None:
        while True:
            with self._lock:
                now = time.monotonic()
                self._tokens = min(self.capacity,
                                   self._tokens + (now - self._last) * self.rate)
                self._last = now
                if self._tokens >= cost:
                    self._tokens -= cost
                    return
                deficit = cost - self._tokens
            time.sleep(deficit / self.rate)

BUDGET = CostBucket(points_per_hour=5000)         # shared by all 8 workers

def estimate_cost(first: int, nested: int) -> int:
    """Estimate query cost the way the API scores it: page size × nesting."""
    return max(1, first * max(1, nested) // 100)

def graphql_fetch(endpoint: str, query: str, variables: dict, token: str):
    cost = estimate_cost(variables.get("first", 100), variables.get("nested", 1))
    BUDGET.spend(cost)                            # reserve points BEFORE sending
    resp = requests.post(
        endpoint,
        json={"query": query, "variables": variables},
        headers={"Authorization": f"Bearer {token}"},
        timeout=30,
    )
    if resp.status_code == 429:
        time.sleep(float(resp.headers.get("Retry-After", "5")))
        BUDGET.spend(cost)                         # re-reserve and retry once
        resp = requests.post(endpoint, json={"query": query, "variables": variables},
                             headers={"Authorization": f"Bearer {token}"}, timeout=30)
    resp.raise_for_status()

    # Reconcile with the server's actual cost accounting when exposed
    body = resp.json()
    actual = body.get("extensions", {}).get("cost", {}).get("actual")
    if actual and actual > cost:
        BUDGET.spend(actual - cost)               # true-up the difference
    return body

Step-by-step trace.

Concern	Naive design	Cost-aware bucket
Budget unit	request count	cost points
Coordination	per-worker	one shared bucket, 8 workers
Reservation	after send (too late)	before send
Under-estimate	trips 429	trued-up from extensions.cost.actual
Low budget	slams into throttle	workers block until points refill

After the redesign, all 8 workers share one CostBucket; each estimates a query's cost, reserves those points before sending, and blocks if the shared budget is short. When the server reports the actual cost in extensions.cost, the connector trues up the difference so a systematic under-estimate cannot drift the budget. A 429 that still slips through is honored via Retry-After.

Output:

Metric	Naive (request-count)	Cost-aware bucket
Throttle (429) incidents/hour	20–40	~0
Points spent/hour	overshoots 5000	≤ 5000
Cross-worker coordination	none	one shared bucket
Behaviour at budget edge	hard throttle	graceful blocking

Why this works — concept by concept:

• Tokens are cost points, not requests — because the API bills by query cost, the bucket refills at points/hour ÷ 3600 and each query spends its estimated cost, so the limiter matches the real quota the server enforces instead of a request count the server ignores.
• Reserve before send — spending points before issuing the query means the connector never sends a query it cannot afford; reserving after the fact is how the naive design overshoots and trips 429s.
• One shared bucket across workers — a single lock-guarded bucket makes all 8 workers respect one global budget; per-worker budgets would collectively spend 8× the quota.
• True-up from extensions.cost.actual — when the server reports the real cost, correcting the difference prevents a persistent under-estimate from slowly draining the budget into a throttle.
• Cost — one shared lock (microseconds of contention), a cheap cost estimate per query, and occasional blocking when the budget is tight. Compared to the naive design's 20–40 throttles/hour, the cost-aware bucket trades a little throughput at the budget edge for zero throttling and predictable, quota-respecting ingestion.

ETL
Topic — etl
ETL problems on rate-limited extraction

Practice →

Optimization Topic — optimization Optimization problems on throughput and throttling

Practice →

---

4. Incremental cursors & idempotency

An incremental cursor pulls only what changed since last run — a durable watermark, an overlap window, and an idempotent upsert make it correct

The mental model in one line: an incremental cursor is a durable high-watermark (usually the maximum updated_at or id seen) that the connector persists after each run and passes as an updated_since filter on the next run, pulling only the delta — and making it correct rather than merely fast requires an overlap window that re-scans a small trailing slice to catch rows that committed with an earlier timestamp than the watermark, plus an idempotent upsert on the natural key so the overlap and any retry never duplicate a row. Every connector that "only pulls changes" and quietly misses rows got the overlap window wrong; every one that duplicates rows on retry skipped the upsert.

The cursor field — what you watermark on.

• updated_at timestamp. The most common cursor. Advances on every mutation, so it captures both inserts and updates. Vulnerable to clock skew and late commits — hence the overlap window.
• Monotonic id. Works for insert-only streams (events, logs) where rows are never updated. Immune to clock skew, but blind to updates, so wrong for mutable entities.
• Opaque server cursor. A few APIs (and all GraphQL streaming/subscription-ish endpoints) hand you a resumable position token that is durable across runs. When offered, prefer it — the server guarantees no gaps.
• Sequence / version number. Some APIs expose a monotonic version or seq per record that advances on every change — the ideal cursor field, combining update-awareness with clock-skew immunity.

The overlap window — why "since the exact watermark" misses rows.

• The problem. A transaction that started at 12:00:00 but committed at 12:00:10 may stamp updated_at = 12:00:01. If your last run advanced the watermark to 12:00:05 at 12:00:06, the next run's updated_since = 12:00:05 filter misses that row — it committed after you looked but has an earlier timestamp.
• The fix. Request updated_since = watermark - overlap (e.g. 5–15 minutes). You re-pull a small trailing window every run, guaranteeing late-committing rows are caught.
• The cost of the fix. You re-fetch a few rows you already have — which is free correctness-wise only because the upsert makes re-fetching harmless.

Idempotency — making replays and overlaps safe.

• Natural key. Every record has a stable identifier (id, or a composite like (account_id, external_id)). Land rows through an UPSERT/MERGE on that key, not a blind INSERT.
• Exactly-once effect. Delivery is at-least-once (overlap re-fetches, retries re-send), but the effect is exactly-once because the upsert overwrites rather than duplicates.
• Ordering. When two versions of the same row arrive, keep the one with the larger updated_at (a WHEN MATCHED AND source.updated_at > target.updated_at guard) so an out-of-order replay can't overwrite newer data with older.

Checkpoint durability — where the cursor lives.

• Durable store. A row in Postgres, a file in S3, Airflow XCom, a small DynamoDB item. Must survive worker crashes.
• Advance only after commit. Persist the new watermark only after the delta has landed durably downstream. Advancing early then crashing loses the un-landed rows.
• Advance to observed max, not now(). Set the watermark to the maximum cursor value actually seen in the data, never to wall-clock now() — advancing to now() can skip rows whose timestamp lies between the last observed value and the clock.

Common interview probes on incremental cursors.

• "How do you pull only what changed?" — persist a watermark, filter updated_since, advance to observed max.
• "How do you not miss late-arriving rows?" — overlap window (watermark - N minutes).
• "How do you not duplicate on retry?" — idempotent upsert on the natural key.
• "When do you advance the cursor?" — only after the delta is durably landed; to the max observed value.

Worked example — an updated_since incremental pull

Detailed explanation. The canonical incremental connector: load the persisted cursor, request updated_since = cursor - overlap, page through the delta, upsert each row, and advance the cursor to the max updated_at seen — persisted only after the load succeeds. Build it end to end.

• Cursor store. A JSON file (or a Postgres row) holding the last watermark.
• Overlap. 10 minutes, to absorb late commits.
• Advance. To max(updated_at) observed, written atomically after the upsert batch.

Question. Implement an incremental pull that fetches the delta since the last cursor and advances safely.

Input.

Parameter	Value
Cursor field	updated_at
Overlap	10 minutes
Filter param	updated_since
Advance to	max(updated_at) observed
Persist when	after the batch lands

Code.

import json
from pathlib import Path
from datetime import datetime, timedelta, timezone

CURSOR_FILE = Path("/state/customers.cursor")
OVERLAP = timedelta(minutes=10)

def load_cursor() -> datetime:
    if CURSOR_FILE.exists():
        return datetime.fromisoformat(json.loads(CURSOR_FILE.read_text())["updated_at"])
    return datetime(1970, 1, 1, tzinfo=timezone.utc)          # bootstrap = full pull

def save_cursor(ts: datetime) -> None:
    tmp = CURSOR_FILE.with_suffix(".tmp")
    tmp.write_text(json.dumps({"updated_at": ts.isoformat()}))
    tmp.replace(CURSOR_FILE)                                   # atomic rename

def incremental_pull(paginate, upsert_batch) -> int:
    prev = load_cursor()
    since = (prev - OVERLAP).isoformat()                      # overlap window
    max_seen = prev
    batch, n = [], 0

    for row in paginate(params={"updated_since": since, "limit": 100}):
        batch.append(row)
        ts = datetime.fromisoformat(row["updated_at"])
        max_seen = max(max_seen, ts)
        n += 1
        if len(batch) >= 1000:
            upsert_batch(batch)                               # idempotent on id
            batch.clear()

    if batch:
        upsert_batch(batch)

    if max_seen > prev:
        save_cursor(max_seen)                                 # advance AFTER landing
    return n

Step-by-step explanation.

1. load_cursor reads the persisted watermark, defaulting to epoch on the first run — which makes the bootstrap run a full pull. There is no separate "initial load" mode; the incremental logic degenerates to a full pull when the cursor is at epoch.
2. The request filter is updated_since = prev - OVERLAP, not prev. The 10-minute overlap re-scans a trailing window so any row that committed late (with an updated_at earlier than the last watermark) is still caught on this run.
3. Rows are buffered and upserted in batches of 1000. Batching amortizes the downstream write cost; the upsert (keyed on id) makes the overlapping and any replayed rows harmless — landing the same row twice just overwrites it.
4. max_seen tracks the maximum updated_at in the data actually returned, not the wall clock. The cursor advances to this observed max, so it never jumps past a row the connector didn't see.
5. save_cursor runs only after every batch has landed, and writes atomically via a temp-file rename. If the process crashes mid-run, the cursor still points at the previous watermark, so the next run re-pulls the in-flight delta and the upsert absorbs the duplicates — no data lost, none duplicated in effect.

Output.

Run	prev cursor	updated_since (prev − 10m)	max seen	new cursor	rows
1 (bootstrap)	1970-01-01	1969-12-31 23:50	2026-08-18 09:00	2026-08-18 09:00	480,000
2	09:00	08:50	09:59	09:59	1,240
3	09:59	09:49	(no new rows)	09:59	0
4	09:59	09:49	10:58	10:58	1,090

Rule of thumb. Filter updated_since = cursor - overlap, advance the cursor to the observed max updated_at, and persist it atomically only after the batch lands. The overlap catches late rows; the upsert makes the overlap free; advancing to observed-max keeps the watermark honest.

Worked example — idempotent upsert / dedup on merge

Detailed explanation. The overlap window and retries both re-deliver rows, so the landing step must be idempotent. The tool is an UPSERT/MERGE on the natural key with an ordering guard that keeps the newest version. Build the merge for a Snowflake target and show why the ordering guard matters.

• Key. id (or a composite natural key).
• Merge. WHEN MATCHED update, WHEN NOT MATCHED insert.
• Guard. Only overwrite when source.updated_at >= target.updated_at, so an out-of-order replay can't regress the row.

Question. Write an idempotent merge that dedupes re-delivered rows and never overwrites newer data with older.

Input.

Concern	Mechanism
Duplicate delivery	MERGE on id
Out-of-order replay	updated_at ordering guard
Insert vs update	WHEN NOT MATCHED / WHEN MATCHED
Staging	load delta to a staging table first

Code.

-- 1. Land the raw delta into a staging table (append-only, cheap)
CREATE TEMP TABLE staging_customers AS SELECT * FROM @raw_delta;   -- illustrative

-- 2. Deduplicate within the batch: keep the newest row per id
--    (the overlap window can deliver two versions of the same id in one batch)
CREATE TEMP TABLE staging_dedup AS
SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS rn
    FROM   staging_customers
) WHERE rn = 1;

-- 3. Idempotent MERGE with an ordering guard
MERGE INTO analytics.customers AS tgt
USING staging_dedup AS src
   ON tgt.id = src.id
WHEN MATCHED AND src.updated_at >= tgt.updated_at THEN UPDATE SET
       tgt.name       = src.name,
       tgt.email      = src.email,
       tgt.status     = src.status,
       tgt.updated_at = src.updated_at
WHEN NOT MATCHED THEN INSERT (id, name, email, status, updated_at)
       VALUES (src.id, src.name, src.email, src.status, src.updated_at);

Step-by-step explanation.

1. The delta lands first in a cheap append-only staging table. Merging directly from the API stream would interleave reads and writes on the target; staging separates "get the data down" from "reconcile it," which is more robust and restartable.
2. Step 2 deduplicates within the batch using ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC). The overlap window and paging can hand you two versions of the same id in one run; keeping only rn = 1 (the newest) ensures the merge sees one row per key.
3. The MERGE ... ON tgt.id = src.id makes the write idempotent: whether a row is new or a re-delivery, it resolves to the same final state. Re-running the whole batch produces the identical target — the definition of idempotent.
4. The WHEN MATCHED AND src.updated_at >= tgt.updated_at guard is the subtle, senior part: without it, a delayed replay carrying an older version could overwrite a newer value already landed by a later run. The guard makes the merge safe under out-of-order delivery.
5. WHEN NOT MATCHED THEN INSERT handles genuinely new rows. Together the two branches mean the merge covers inserts and updates uniformly; deletes, if the API exposes them, would add a WHEN MATCHED AND src.deleted THEN DELETE branch or a soft-delete column.

Output.

Scenario	Without guard	With MERGE + guard
Same row delivered twice (overlap)	duplicated	one row
Retry re-sends a batch	duplicated	idempotent (no change)
Older version arrives after newer	overwrites (data regresses)	ignored (guard blocks)
Genuinely new row	inserted	inserted

Rule of thumb. Never blind-INSERT an incremental delta. Dedupe within the batch by ROW_NUMBER() on the key, then MERGE with an updated_at ordering guard. This turns at-least-once delivery into exactly-once effect and makes replays, overlaps, and out-of-order rows all harmless.

Worked example — backfill vs incremental and the reconcile

Detailed explanation. A mature connector has two modes — a one-time backfill (full history) and the recurring incremental pull — plus a periodic reconcile that catches anything the incremental cursor missed (deletes the API doesn't expose, rows with a botched updated_at). Walk through wiring the three together.

• Backfill. Bootstrap with cursor at epoch; page the entire history once; then hand off to incremental.
• Incremental. The recurring updated_since pull from the previous examples.
• Reconcile. Weekly, pull a full id list (cheap projection) and diff against the warehouse to catch missing or stale rows.

Question. Design the backfill → incremental handoff and a weekly reconcile that catches gaps.

Input.

Mode	Frequency	Purpose
Backfill	once	load full history
Incremental	hourly	pull the delta
Reconcile	weekly	catch missed/deleted rows

Code.

def backfill(paginate, upsert_batch, save_cursor):
    """One-time full pull; hands the max updated_at to the incremental cursor."""
    max_seen = None
    for row in paginate(params={"limit": 100}):          # no updated_since = all rows
        upsert_batch([row])
        ts = row["updated_at"]
        max_seen = ts if max_seen is None else max(max_seen, ts)
    if max_seen:
        save_cursor(max_seen)                            # incremental starts here

def reconcile(list_source_ids, list_warehouse_ids, refetch_and_upsert, delete_missing):
    """Weekly: diff source vs warehouse ids; refetch drift, remove tombstones."""
    src = set(list_source_ids())                         # cheap id-only projection
    whs = set(list_warehouse_ids())

    missing_in_warehouse = src - whs                     # incremental missed these
    for _id in missing_in_warehouse:
        refetch_and_upsert(_id)

    deleted_at_source = whs - src                        # gone upstream (hard delete)
    delete_missing(deleted_at_source)                    # soft-delete downstream

Step-by-step explanation.

1. backfill runs once with no updated_since, paging the entire history through the same idempotent upsert the incremental path uses. Sharing the upsert means backfill and incremental can even overlap safely — a row landed by both just merges.
2. The crucial handoff is save_cursor(max_seen): after the backfill, the cursor is set to the maximum updated_at in the full history, so the first incremental run picks up exactly where backfill stopped, with no gap and no full re-pull.
3. reconcile pulls only ids from the source (a cheap projection, often a dedicated lightweight endpoint) and diffs against the warehouse's ids. This is far cheaper than re-pulling full rows and catches the two failure modes incremental can't see.
4. missing_in_warehouse = src - whs are rows the incremental cursor skipped — perhaps a bad updated_at, perhaps a bug. Refetching and upserting them repairs the gap without a full backfill.
5. deleted_at_source = whs - src are rows hard-deleted upstream that the incremental cursor can never surface (a deleted row has no updated_at to filter on). The reconcile soft-deletes them downstream — the only reliable way to propagate deletes from an API that doesn't emit delete events.

Output.

Mode	Rows touched	Cost	Catches
Backfill	all (once)	O(history)	initial load
Incremental	delta (hourly)	O(delta)	inserts + updates
Reconcile	id diff (weekly)	O(ids)	missed rows + hard deletes

Rule of thumb. Ship all three modes: a one-time backfill that seeds the cursor, an hourly incremental pull, and a weekly id-diff reconcile. The reconcile is the only way to catch upstream hard-deletes and any row the updated_at cursor silently missed — treat it as mandatory, not optional.

Senior interview question on incremental cursors

A senior interviewer might ask: "You ingest a CRM's contacts API hourly with an updated_since cursor. Users report that some edits made right at the top of the hour never reach the warehouse, and that a re-run of a failed job doubles some contacts. Diagnose both bugs and redesign the incremental cursor so no edit is lost and re-runs never duplicate."

Solution Using an overlap window, observed-max advance, and an idempotent upsert

# Two bugs:
#   (1) missed edits  -> cursor advanced with no overlap; late commits slipped through
#   (2) doubled rows  -> failed run re-pulled and blind-INSERTed instead of upserting
# Fix: overlap window + advance-to-observed-max + idempotent MERGE.

import json
from pathlib import Path
from datetime import datetime, timedelta, timezone

CURSOR = Path("/state/contacts.cursor")
OVERLAP = timedelta(minutes=15)          # generous: CRM commits can lag

def pull_contacts(paginate, stage_rows, merge_stage) -> int:
    prev = _load()
    since = (prev - OVERLAP).isoformat()  # (1) re-scan a trailing window
    max_seen, rows = prev, 0

    staged = []
    for c in paginate(params={"updated_since": since, "limit": 100}):
        staged.append(c)
        max_seen = max(max_seen, datetime.fromisoformat(c["updated_at"]))
        rows += 1

    stage_rows(staged)                    # land raw delta to staging
    merge_stage()                         # (2) idempotent MERGE on contact id

    if max_seen > prev:
        _save(max_seen)                   # advance to observed max, after landing
    return rows

def _load() -> datetime:
    if CURSOR.exists():
        return datetime.fromisoformat(json.loads(CURSOR.read_text())["c"])
    return datetime(1970, 1, 1, tzinfo=timezone.utc)

def _save(ts: datetime) -> None:
    tmp = CURSOR.with_suffix(".tmp")
    tmp.write_text(json.dumps({"c": ts.isoformat()}))
    tmp.replace(CURSOR)

-- The idempotent MERGE that kills the duplicate-on-rerun bug
MERGE INTO crm.contacts AS tgt
USING (
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) rn
        FROM   crm.contacts_stage
    ) WHERE rn = 1                       -- dedupe overlap within the batch
) AS src
   ON tgt.id = src.id
WHEN MATCHED AND src.updated_at >= tgt.updated_at
     THEN UPDATE SET tgt.name = src.name, tgt.email = src.email,
                     tgt.updated_at = src.updated_at
WHEN NOT MATCHED
     THEN INSERT (id, name, email, updated_at)
          VALUES (src.id, src.name, src.email, src.updated_at);

Step-by-step trace.

Symptom	Root cause	Fix
Top-of-hour edits missing	cursor advanced with no overlap; late commit had earlier updated_at	updated_since = cursor - 15 min overlap
Re-run doubles contacts	failed run re-pulled + blind INSERT	MERGE on id (idempotent)
Two versions in one batch	overlap re-delivers the same id	ROW_NUMBER() dedupe, keep newest
Cursor jumps past rows	advanced to now()	advance to observed max(updated_at)
Cursor lost on crash	advanced before landing	persist only after MERGE

After the redesign, the 15-minute overlap re-scans the top-of-hour window so late-committing CRM edits are always caught; the MERGE on id makes the overlap (and any failed-run re-pull) idempotent; the within-batch ROW_NUMBER() dedupe collapses duplicate versions; and the cursor advances to the observed max only after the merge lands, so a crash re-pulls rather than skips.

Output:

Metric	Before	After
Missed top-of-hour edits	~1–2%	0 (overlap)
Duplicated contacts on re-run	yes	none (idempotent merge)
Out-of-order overwrite	possible	blocked (updated_at guard)
Crash recovery	may skip window	re-pulls window safely

Why this works — concept by concept:

• Overlap window — filtering updated_since = cursor - 15 min re-scans a trailing slice each run, so a transaction that committed late with an earlier updated_at than the last watermark is still captured instead of falling into the gap between "when you looked" and "when it committed."
• Idempotent MERGE on the natural key — landing through a MERGE on id rather than a blind INSERT means a re-pulled or overlapped row resolves to the same final state, converting at-least-once delivery into exactly-once effect.
• Within-batch dedupe — ROW_NUMBER() ... ORDER BY updated_at DESC collapses the multiple versions the overlap can deliver in one batch down to the newest, so the merge sees one row per key.
• Advance to observed max, after landing — setting the cursor to the maximum updated_at actually seen (not now()), and only after the merge commits, keeps the watermark honest and makes a mid-run crash re-pull the delta rather than skip it.
• Cost — the overlap re-fetches a small trailing window (a few hundred rows) each run — negligible because the merge makes it free — plus one dedupe window function and one merge per batch. Net O(delta + overlap) per run with zero missed edits and zero duplicates, versus the broken design's silent data loss.

ETL
Topic — etl
ETL problems on incremental loads and watermarks

Practice →

SQL Topic — sql SQL MERGE, upsert, and deduplication problems

Practice →

---

5. Retry & backoff — exponential backoff, jitter, dead-letter

retry backoff is how a flaky upstream fails without corrupting your data — classify errors, back off exponentially with jitter, budget the retries, and dead-letter the poison

The mental model in one line: retry backoff is the policy for surviving transient upstream failures by classifying each error as retryable (429, 500, 502, 503, 504, timeouts) or permanent (400, 401, 403, 404), retrying only the retryable ones with exponential backoff plus full jitter under a bounded retry budget, tripping a circuit breaker when the whole API is down so you stop hammering it, and routing records that exhaust their budget to a dead-letter queue so one poison record never blocks the pipeline forever. Every connector that "retries three times" either gives up too early on a recoverable blip or, worse, retries a permanent 400 forever.

Classifying errors — retry only what's transient.

• Retryable (transient). 429 (throttle — but honor Retry-After), 500/502/503/504 (server/gateway), connection resets, DNS blips, read timeouts. These may succeed on a later attempt.
• Permanent (do not retry). 400 (bad request — retrying sends the same broken request), 401/403 (auth — fix the token, don't retry), 404 (gone), 422 (validation). Retrying these wastes budget and never succeeds.
• The gray zone. 409 (conflict) and some 404s can be transient in eventually-consistent APIs; classify per-API from the docs, not by guessing.
• The rule. Default to not retrying unless you know an error is transient. A connector that retries everything hammers the API on permanent errors and masks real bugs.

Exponential backoff + jitter — why jitter is non-negotiable.

• Exponential backoff. Wait base * 2^attempt between retries (1s, 2s, 4s, 8s, …), capped at a maximum. This gives a struggling server room to recover instead of a retry storm.
• The thundering herd. If many workers all fail at once and all back off by the same schedule, they retry in sync — re-creating the exact load spike that caused the failure. Synchronized retries can keep a recovering API down.
• Full jitter. Instead of sleeping exactly base * 2^attempt, sleep a random value in [0, base * 2^attempt]. This de-synchronizes the herd so retries spread out. Full jitter is the AWS-recommended default and the correct choice for ingestion.
• The retry budget. Cap total attempts (e.g. 5) and total wall-clock retry time. Unbounded retries turn a dead API into an infinitely hung job.

Circuit breaker — stop hammering a dead API.

• Closed. Normal operation; requests flow, failures are counted.
• Open. After the failure rate crosses a threshold, the breaker opens — requests fail fast without even hitting the API, giving it time to recover and freeing your workers.
• Half-open. After a cooldown, let a few probe requests through; if they succeed, close the breaker; if they fail, re-open. This is how the connector automatically resumes when the API comes back.

Dead-letter queue — quarantine the poison.

• The poison record. A single record that always fails (malformed payload, an id the API 500s on) must not block the whole run. After it exhausts its retry budget, route it to a DLQ.
• The DLQ. A durable side channel (a table, an S3 prefix, a Kafka topic) holding failed records plus the error and attempt count, so a human can inspect and replay them.
• The alert. DLQ depth is a health metric — a sudden spike means the upstream schema changed or a whole class of records is now failing.

Common interview probes on retries.

• "How many times do you retry?" — required answer: bounded budget with exponential backoff + jitter, not a fixed count applied to everything.
• "Why jitter?" — de-synchronize the thundering herd so retries don't recreate the load spike.
• "What don't you retry?" — permanent 4xx (400/401/403/404); retrying them never succeeds.
• "What happens to a record that always fails?" — dead-letter it and alert, so it never blocks the pipeline.

Worked example — exponential backoff with full jitter

Detailed explanation. The core retry primitive: a wrapper that retries a callable on retryable errors, sleeping a random interval in [0, base * 2^attempt] (full jitter), capped at a max delay and a max attempt budget. Build it and show the jittered schedule.

• Backoff. base * 2^attempt, capped at max_delay.
• Jitter. Sleep uniform(0, backoff) — full jitter.
• Budget. Stop after max_attempts; re-raise the last error.

Question. Implement a retry decorator with exponential backoff and full jitter, and trace the sleep schedule.

Input.

Parameter	Value
base	1.0 s
factor	2^attempt
max_delay	60 s
max_attempts	6
jitter	full (uniform 0..backoff)

Code.

import time
import random
import functools

RETRYABLE = {429, 500, 502, 503, 504}

class RetryableError(Exception):
    pass

def with_backoff(max_attempts: int = 6, base: float = 1.0, max_delay: float = 60.0):
    """Retry on RetryableError with exponential backoff + full jitter."""
    def decorator(fn):
        @functools.wraps(fn)
        def wrapper(*args, **kwargs):
            last = None
            for attempt in range(max_attempts):
                try:
                    return fn(*args, **kwargs)
                except RetryableError as e:
                    last = e
                    if attempt == max_attempts - 1:
                        break                              # budget exhausted
                    ceiling = min(max_delay, base * (2 ** attempt))
                    sleep = random.uniform(0, ceiling)     # FULL jitter
                    time.sleep(sleep)
            raise last                                     # surface to caller / DLQ
        return wrapper
    return decorator

@with_backoff()
def fetch(url: str):
    resp = requests.get(url, timeout=30)
    if resp.status_code in RETRYABLE:
        raise RetryableError(f"{resp.status_code} on {url}")
    resp.raise_for_status()                                # permanent 4xx → no retry
    return resp

Step-by-step explanation.

1. The decorator loops up to max_attempts. Each iteration calls the wrapped function; a RetryableError triggers a backoff-and-retry, while any other exception (raised by raise_for_status on a permanent 4xx) propagates immediately — permanent errors are never retried.
2. The backoff ceiling is min(max_delay, base * 2^attempt) — 1, 2, 4, 8, 16, capped at 60. This is the classic exponential schedule, giving a struggling server geometrically more room on each attempt.
3. Full jitter replaces the fixed ceiling with random.uniform(0, ceiling). So attempt 3's wait is a random value in [0, 8], not exactly 8. This is the line that de-synchronizes concurrent workers and prevents the retry storm.
4. On the final attempt, the loop breaks and re-raises the last error rather than sleeping pointlessly after the last try. The caller catches this to route the record to the dead-letter queue.
5. fetch translates HTTP status into the retry taxonomy: a retryable status becomes a RetryableError (retried), while raise_for_status turns a permanent 4xx into a non-retryable exception (surfaced immediately). Keeping the classification inside fetch and the policy inside the decorator cleanly separates the two concerns.

Output.

Attempt	Ceiling (base·2^n, cap 60)	Actual sleep (full jitter)
0 → 1	1	uniform(0, 1) ≈ 0.4 s
1 → 2	2	uniform(0, 2) ≈ 1.3 s
2 → 3	4	uniform(0, 4) ≈ 2.1 s
3 → 4	8	uniform(0, 8) ≈ 5.6 s
4 → 5	16	uniform(0, 16) ≈ 9.9 s
5	—	budget exhausted → raise

Rule of thumb. Back off exponentially (base·2^attempt, capped), but always add full jitter — sleep a random value in [0, ceiling], not the ceiling itself. Bound the attempts. Fixed-schedule retries across many workers recreate the load spike that caused the failure; jitter is what breaks the herd.

Worked example — circuit breaker + dead-letter queue

Detailed explanation. Retries handle a single flaky request; a circuit breaker handles a whole API outage, and a DLQ handles a single poison record. Together they keep the pipeline alive: the breaker stops wasting attempts on a down API, and the DLQ quarantines records that will never succeed. Build both.

• Breaker. Count failures; open after a threshold; half-open after a cooldown; close on a successful probe.
• DLQ. After a record exhausts its retry budget, write it (plus error + attempts) to a durable side channel and continue.
• Alert. Page when DLQ depth spikes or the breaker stays open.

Question. Implement a circuit breaker guarding the fetch and a dead-letter path for records that exhaust their budget.

Input.

Component	Setting
Breaker open threshold	10 consecutive failures
Breaker cooldown	30 s before half-open
DLQ trigger	retry budget exhausted for a record
DLQ store	durable table / S3 / Kafka

Code.

import time

class CircuitBreaker:
    def __init__(self, threshold: int = 10, cooldown: float = 30.0):
        self.threshold, self.cooldown = threshold, cooldown
        self.failures = 0
        self.opened_at = 0.0
        self.state = "closed"

    def allow(self) -> bool:
        if self.state == "open":
            if time.monotonic() - self.opened_at >= self.cooldown:
                self.state = "half-open"          # let a probe through
                return True
            return False                          # fail fast, don't hit the API
        return True

    def record_success(self) -> None:
        self.failures = 0
        self.state = "closed"

    def record_failure(self) -> None:
        self.failures += 1
        if self.failures >= self.threshold:
            self.state = "open"
            self.opened_at = time.monotonic()

BREAKER = CircuitBreaker()

def ingest_record(record, fetch, dead_letter):
    if not BREAKER.allow():
        raise RuntimeError("circuit open; API considered down")
    try:
        result = fetch(record["url"])             # @with_backoff from previous example
        BREAKER.record_success()
        return result
    except RetryableError as e:
        BREAKER.record_failure()
        # budget already exhausted inside fetch → this record is poison
        dead_letter.put({"record": record, "error": str(e), "ts": time.time()})
        return None                               # continue the run; don't block

Step-by-step explanation.

1. The breaker starts closed — requests flow and failures are counted. Each success resets the failure count, so isolated blips never trip it; only a run of failures does.
2. After threshold consecutive failures the breaker opens and records the time. While open, allow() returns False and the connector fails fast without touching the API — this frees workers and, crucially, stops adding load to an already-struggling upstream.
3. After the cooldown, allow() transitions to half-open and lets a single probe request through. A success closes the breaker (normal service resumes); a failure re-opens it. This is the automatic-recovery mechanism — no human needed to flip it back.
4. When a record exhausts its own retry budget inside fetch (the @with_backoff wrapper re-raises), ingest_record routes it to the dead-letter queue with the error and timestamp, then returns None and lets the run continue. One poison record cannot stall the pipeline.
5. The DLQ is durable and inspectable, so an on-call engineer can see which records failed and why, fix the root cause (often an upstream schema change), and replay them. DLQ depth is monitored: a spike is an early warning that a whole class of records started failing.

Output.

Event	Breaker state	Record outcome
Steady success	closed	ingested
10 failures in a row	open	fail fast (API down)
30 s later, probe ok	closed	ingestion resumes
One record 500s forever	closed	dead-lettered, run continues
DLQ depth spikes	(any)	alert on-call

Rule of thumb. Layer three defenses: exponential-backoff-with-jitter for a flaky request, a circuit breaker for a down API, and a dead-letter queue for a poison record. The breaker stops you hammering a dead upstream; the DLQ stops one bad record blocking the run. Monitor breaker state and DLQ depth as first-class health signals.

Senior interview question on retry and backoff

A senior interviewer might ask: "Your ingestion job fans out to 20 workers hitting one API. When the API has a brief hiccup, all 20 workers fail, all retry on the same fixed schedule, and the synchronized retry storm keeps the API down — turning a 10-second blip into a 20-minute outage. Redesign the retry logic so a transient failure recovers quickly, the workers don't recreate the load spike, and a permanently-failing record doesn't hang the whole job."

Solution Using full-jitter backoff, a shared circuit breaker, and a dead-letter queue

# The failure: fixed-schedule retries across 20 workers = synchronized storm.
# The fix: full-jitter backoff (de-sync) + shared breaker (stop hammering)
#          + DLQ (quarantine poison) + a retry budget (bounded).
import time
import random

RETRYABLE = {429, 500, 502, 503, 504}
PERMANENT = {400, 401, 403, 404, 422}

def classify(status: int) -> str:
    if status in RETRYABLE:
        return "retry"
    if status in PERMANENT:
        return "permanent"
    return "permanent"                             # default deny: don't retry unknowns

def robust_fetch(url, breaker, dead_letter, record,
                 max_attempts=6, base=1.0, max_delay=60.0):
    if not breaker.allow():
        raise RuntimeError("circuit open")

    for attempt in range(max_attempts):
        resp = requests.get(url, timeout=30)
        kind = "ok" if resp.ok else classify(resp.status_code)

        if kind == "ok":
            breaker.record_success()
            return resp

        if kind == "permanent":
            breaker.record_success()               # server is up; this record is bad
            dead_letter.put({"record": record, "status": resp.status_code})
            return None

        # retryable
        breaker.record_failure()
        if resp.status_code == 429:                # honour the server's own timing
            time.sleep(float(resp.headers.get("Retry-After", "1")))
            continue
        if attempt == max_attempts - 1:
            break
        ceiling = min(max_delay, base * (2 ** attempt))
        time.sleep(random.uniform(0, ceiling))     # FULL jitter → de-sync the 20 workers

    dead_letter.put({"record": record, "error": "retry budget exhausted"})
    return None

Step-by-step trace.

Concern	Fixed-schedule (broken)	Full-jitter + breaker (fixed)
Retry timing	identical across 20 workers	random per worker (de-synced)
Load during recovery	20 simultaneous spikes	spread over the jitter window
Down-API behaviour	keep hammering	breaker opens, fail fast
Permanent 4xx	retried pointlessly	dead-lettered immediately
Poison record	hangs the run	dead-lettered, run continues
Bound	unbounded	attempt budget + max delay

After the redesign, each of the 20 workers backs off by an independent random interval, so their retries scatter across the jitter window instead of landing simultaneously — the API gets breathing room and recovers in seconds. The shared breaker opens if failures persist, so the workers stop hammering a genuinely-down API; permanent 4xx errors are dead-lettered without wasting a single retry; and any record exhausting its budget is quarantined so the run finishes.

Output:

Metric	Fixed schedule	Full jitter + breaker + DLQ
Outage length from a 10 s blip	~20 min (storm)	~15 s (recovers)
Retries during recovery	synchronized spikes	spread out
Wasted retries on 4xx	many	zero (classified)
Job hung by one bad record	yes	no (DLQ)

Why this works — concept by concept:

• Error classification — splitting statuses into retryable (429/5xx/timeouts) and permanent (4xx) means the connector only ever retries what can succeed, so a permanent 400 is dead-lettered on the first response instead of consuming the whole budget.
• Full jitter — sleeping uniform(0, ceiling) instead of a fixed ceiling de-synchronizes the 20 workers, scattering their retries across time so they stop recreating the exact load spike that caused the outage.
• Shared circuit breaker — a breaker that opens after sustained failures makes the workers fail fast and stop hitting a down API, then half-opens to probe for recovery — turning "keep hammering" into "back off collectively and auto-resume."
• Dead-letter queue + retry budget — bounding attempts and quarantining exhausted records means one poison record (or a class of them) never hangs the run; the DLQ preserves them for inspection and replay.
• Cost — a few random sleeps per failed request, one shared breaker (a couple of counters), and one DLQ write per poison record. Compared to the fixed-schedule storm that amplified a 10-second blip into a 20-minute outage, this is a near-free change that converts synchronized retries into a self-healing, bounded, quota-friendly recovery.

Streaming
Topic — streaming
Streaming retry, backoff, and delivery problems

Practice →

Event Processing
Topic — event-processing
Event processing problems on dead-letter and replay

Practice →

---

Cheat sheet — API ingestion recipes

• The four axes, one sentence. Every API connector picks a pagination model (walk the whole set), a rate-limit posture (stay under quota), an incremental strategy (pull only the delta), and a failure policy (survive transient errors). Pagination and rate limits are dictated by the endpoint; incremental and failure handling are designed by you. Pin all four in one config object.
• Pagination decision matrix. Keyset/seek (since_id=) if the API exposes a stable-key filter — stable under inserts, O(limit) seek, trivial resume. GraphQL cursor connection (after: endCursor while hasNextPage) for GraphQL. Opaque page-token loop (cursor=next_cursor while has_more) when only a token is offered. Offset/limit only for small static sets — it drifts under concurrent inserts and is O(offset) at depth.
• Keyset paginator template. Sort ascending by a monotonic id, request since_id = last_id, advance since_id = page[-1].id, terminate on a short page (len(page) < limit). One integer of state, zero drift, exact resume.
• Token-bucket limiter. tokens = min(cap, tokens + elapsed*rate); if tokens>=1: tokens-=1 else sleep((1-tokens)/rate). Set rate ≈ 0.9 × published_quota for headroom, share one thread-safe bucket across all workers, and cap max_in_flight with a semaphore.
• 429 / Retry-After handling. On 429, sleep for exactly Retry-After (parse both seconds and HTTP-date), then retry — never apply your own backoff to a 429. Watch X-RateLimit-Remaining/Reset and slow down before the budget hits zero. GraphQL bills by cost points, not requests — budget by query complexity.
• Incremental cursor + overlap. Persist a durable watermark (max updated_at seen). Next run request updated_since = watermark - overlap (5–15 min) to catch late commits. Advance to the observed max, never now(). Persist the cursor atomically, only after the delta lands.
• Idempotent upsert. Never blind-INSERT a delta. Dedupe within the batch (ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) = 1), then MERGE ON key with a WHEN MATCHED AND src.updated_at >= tgt.updated_at guard so out-of-order replays can't regress a row. At-least-once delivery, exactly-once effect.
• Backfill → incremental → reconcile. Backfill once (cursor at epoch), then hand max(updated_at) to the incremental cursor. Run incremental on the recurring schedule. Weekly, diff source ids vs warehouse ids (cheap projection) to catch missed rows and upstream hard-deletes the updated_at cursor can't see.
• Exponential backoff + full jitter. ceiling = min(max_delay, base * 2^attempt); sleep = uniform(0, ceiling). Full jitter is mandatory with many workers — fixed schedules resynchronize into a retry storm that recreates the load spike. Bound both attempts and total retry wall-clock.
• Error classification. Retry 429/500/502/503/504/timeouts; never retry 400/401/403/404/422. Default unknown statuses to permanent (deny). Retrying a permanent error wastes budget and masks bugs.
• Circuit breaker. Closed → count failures → open after N consecutive → fail fast during cooldown → half-open probe → close on success. Stops the connector hammering a down API and auto-resumes when it recovers. Monitor breaker state as a health metric.
• Dead-letter queue. After a record exhausts its retry budget, write it (record + error + attempts) to a durable side channel and continue the run. One poison record never blocks the pipeline. Alert on DLQ depth — a spike means an upstream schema change or a failing record class.
• Checkpoint durability. Store the cursor in a durable place (Postgres row, S3 object, XCom, DynamoDB). Write it atomically (temp-file rename or a transactional row). Advance only after the batch lands, so a crash re-pulls the in-flight delta and the idempotent upsert absorbs the duplicates.

Frequently asked questions

What is API ingestion in one sentence?

API ingestion is the practice of extracting records from a third-party REST or GraphQL HTTP endpoint and landing them durably in your own store, walking a paginated result set behind a rate limit without a database's replication log or transactional guarantees to lean on. The four load-bearing decisions are pagination (walk every record without skipping or duplicating under concurrent writes), rate limiting (stay under the provider's quota so your key isn't throttled), the incremental cursor (pull only what changed since the last run), and failure handling (retry transient errors with backoff and jitter, and dead-letter the poison). Every managed connector — Fivetran, Airbyte, a Singer tap — implements exactly these four axes internally, which is why senior data-engineering interviews probe them: they are the load-bearing pattern for feeding a warehouse from the dozens of SaaS APIs a modern business runs on.

Offset vs cursor pagination — which do I pick?

Prefer keyset/cursor pagination almost every time. Offset pagination (?limit=100&offset=200) is trivial but has two fatal flaws: it drifts — when rows are inserted or deleted before your offset while you page, the window shifts and you silently skip or duplicate rows — and it is O(offset) on the server, so deep pages get progressively slower. Keyset (also called seek) pagination anchors on a stable, monotonic sort key (?since_id=1042), so concurrent inserts land after your window instead of shifting it, deep pages stay fast (an index seek, not a deep scan), and resuming after a crash is a single stored integer. Use offset only for small, static result sets that never change mid-scan. For GraphQL, the equivalent of keyset is the cursor connection (after: endCursor until hasNextPage is false), and for REST APIs that only hand you an opaque next_cursor token, loop on the token — both are stable against inserts because the server owns the position.

How do you handle 429 rate limits?

The senior answer is to make 429s rare and, when they happen, obey the server exactly. Rare: shape traffic client-side with a token-bucket limiter set below the published quota (roughly 90% for headroom), share one thread-safe bucket across all workers, and cap concurrency with a semaphore so a burst of parallel requests can't collectively exceed the rate. When a 429 does slip through — another job shares the key, the server's window differs — read the Retry-After header and sleep for exactly that long (it's either delta-seconds or an HTTP date) before retrying; never apply your own exponential backoff to a 429, because the server has told you the precise wait. Pre-emptively watch X-RateLimit-Remaining and slow down before the budget hits zero. For GraphQL APIs that bill by query cost points rather than request count, budget against the points quota by estimating each query's cost and reserving it before sending. Ignoring repeated 429s escalates to key suspension or a permanent ban, so the connector must protect the provider, not just retry into the throttle.

What is an incremental cursor and how do you make it idempotent?

An incremental cursor is a durable high-watermark — usually the maximum updated_at (or a monotonic id / version) the connector has seen — that you persist after each run and pass as an updated_since filter on the next run, so you pull only the delta instead of the full history. Two things make it correct: an overlap window and an idempotent upsert. The overlap window requests updated_since = watermark - N minutes (5–15) so a transaction that committed late, with an updated_at earlier than the last watermark, is still caught rather than falling into the gap between when you looked and when it committed. The idempotent upsert lands every row through a MERGE on the natural key (with a WHEN MATCHED AND src.updated_at >= tgt.updated_at ordering guard), so the rows the overlap re-delivers, and any rows a retry re-sends, resolve to the same final state instead of duplicating — turning at-least-once delivery into exactly-once effect. Advance the cursor to the observed maximum value (never wall-clock now()), and persist it atomically only after the delta has landed, so a mid-run crash re-pulls the in-flight window rather than skipping it.

Exponential backoff vs fixed retry — why add jitter?

Fixed-schedule retries are dangerous at scale. Exponential backoff — waiting base * 2^attempt (1s, 2s, 4s, 8s…) between retries, capped at a maximum — gives a struggling server geometrically more room to recover than a fixed 1-second retry. But exponential backoff alone still fails when many workers fail together: if all of them back off on the same schedule, they retry in sync, recreating the exact load spike that caused the failure and keeping a recovering API down — the "thundering herd." Full jitter fixes this by sleeping a random value in [0, base * 2^attempt] instead of the fixed ceiling, so each worker's retries scatter across time and the herd de-synchronizes. This is the AWS-recommended default and the correct choice for ingestion. Pair it with a bounded retry budget (cap attempts and total retry wall-clock), error classification (retry only 429/5xx/timeouts, never permanent 4xx), a circuit breaker to stop hammering a down API, and a dead-letter queue for records that exhaust their budget.

REST vs GraphQL ingestion — what changes?

The four axes are identical; three of them change shape. Pagination: REST gives you offset, a keyset filter, an opaque page token, or a Link header; GraphQL standardizes on a Relay-style cursor connection (edges { node cursor } pageInfo { endCursor hasNextPage }), which is essentially page-token pagination with a schema-defined contract. Rate limiting: REST APIs usually cap request count per window; many GraphQL APIs instead bill by query cost points computed from the fields and connection sizes you request, so you budget by query complexity rather than request count — a single expensive nested query can cost hundreds of points. Payload shape: GraphQL lets you request exactly the fields you need, so payloads are smaller and you avoid over-fetching, which also reduces cost. Incremental and failure handling are unchanged: you still persist an updated_since cursor with an overlap window and an idempotent upsert, and you still retry transient errors with jittered backoff and dead-letter the poison. Learn the four axes once and you can ingest either transport; only the pagination and rate-cost mechanics differ.

Practice on PipeCode

• Drill the ETL practice library → for the pagination, incremental-load, watermark, and connector problems senior interviewers love.
• Rehearse on the SQL practice library → for the keyset pagination, MERGE/upsert, and deduplication patterns that make API ingestion idempotent.
• Sharpen the delivery axis with the streaming practice library → for retry, backoff, dead-letter, and at-least-once delivery scenarios.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the four-axis connector design against real graded inputs.

Lock in API ingestion muscle memory

Docs explain the endpoints. PipeCode drills explain the decision — when offset pagination silently skips rows, when a token bucket beats "sleep and retry," when the overlap window is the only thing catching a late commit, and when full jitter is the difference between a 10-second blip and a 20-minute outage. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice ETL problems →
Practice streaming problems →