Pulling a million records through a client's paginated API without losing a row

Do the arithmetic before writing any code. A system holds 1,000,000 records and you fetch 100 rows per page, so you need 10,000 requests. Forget to set limit on an API that defaults to 10 rows, as Stripe does, and that becomes 100,000.

If the API paginates by offset, the last pages force the server to scan nearly a million rows and throw them away, just to return the 100 you asked for.

Picture your first task at a client: pull tickets, transactions or messages from their system to build a pipeline or feed an agent. Everything downstream depends on this API read loop.

If it silently duplicates 5 rows or drops 5 rows, every number in the demo is wrong.

This guide shows how to write a Python data puller for the three common pagination styles, with filters, batching, rate-limit handling and checkpoints. You need Python 3, the requests library and a test API key. The code uses a hypothetical API with simplified field names. In real work, substitute the field names from the client’s documentation.

Step 1: Read the docs to identify the API’s style

Before writing the loop, answer two questions: how does the API move to the next page, and how do you know the data has run out? The three public APIs below represent the approaches you will meet most often.

API	How it advances	Page size	Stop signal
Confluence (Atlassian)	Offset with start and limit	Atlassian advises always setting limit explicitly	Response has no next link
Stripe	Cursor by object ID, starting_after / ending_before	limit from 1 to 100, default 10	has_more is false
Slack	Server-issued cursor	Slack recommends 100 to 200 per call	next_cursor is empty, null or absent

What all three share is that the server decides when to stop, not the client. That is the first rule: never work out in advance that “there are 50 pages” and loop 50 times.

By the end of this step you should be able to state three things about the client’s API: the name of the parameter that advances the page, the maximum limit, and the field that signals the data is exhausted.

Step 2: An offset loop with an explicit limit

For a Confluence-style API, the loop follows the next link rather than incrementing start itself. The snippet below is simplified: links.next is a hypothetical field name, so check it against a real response.

import requests

def iter_offset(session, url, limit=100):
    params = {"start": 0, "limit": limit}  # always set limit explicitly
    while url:
        resp = session.get(url, params=params)
        resp.raise_for_status()
        page = resp.json()
        yield from page["results"]
        url = page.get("links", {}).get("next")  # no next link means no more data
        params = None  # the next link usually already contains start/limit

Check: print the row count for each page. If every page has exactly 25 rows even though you asked for 100, the server is applying its own cap. That is why Atlassian advises setting limit so you can be sure how many results each page returns.

Why does offset break when data is changing?

Offset has two weaknesses. The obvious one is speed: the larger the offset, the more rows the database has to scan and discard. The more dangerous one is called page drift: new records arrive in the table while you are paging through it.

Suppose you read tickets newest first. Page 1 returns rows 1 to 100. Meanwhile 5 new tickets are created, and because they are newest they jump to the top of the list, pushing every older row down 5 places.

When you request page 2 with offset 100, the server returns what used to be positions 96 to 195, so 5 rows are repeated. With deletions instead of inserts, the reverse happens: 5 rows are skipped.

Step 3: A cursor loop that stops on the server’s signal

A cursor avoids page drift because it marks “after this record”, not “after position N”. Stripe uses the ID of the last object on the page as starting_after. The code below follows the Stripe style, with items as a hypothetical field name.

def iter_cursor(session, url, limit=100):
    params = {"limit": limit}  # Stripe allows 1-100
    while True:
        resp = session.get(url, params=params)
        resp.raise_for_status()
        page = resp.json()
        items = page["items"]
        yield from items
        if not page["has_more"] or not items:
            break
        params["starting_after"] = items[-1]["id"]

For a Slack-style API, change the stop condition: check whether next_cursor is empty, null or missing, and handle all three cases. Stripe also offers client libraries with auto-pagination. If the client allows it, use them, but understand the loop underneath so you can debug when something goes wrong.

Cursors carry their own risk. With seek/keyset-style pagination, if the record used as the marker is deleted, that ID may no longer be valid and the next request will fail. So whenever you hit an error, log the last cursor, not just the HTTP status code.

Step 4: Use filters to narrow what you pull

Do not pull a million rows if you only need last quarter’s data. Filtering by time or status cuts the scope from the very first request.

If what remains is still large, you can split it into several time windows yourself, each running its own cursor loop. That is a way of organising work on the client side, not a pagination style.

Keyset pagination is different: it uses the filter values from the previous page to define the next one. If the client’s API issues no cursor but offers filtering and sorting, build a keyset yourself by taking the created_at and id of the last row on the page as the condition for the next request.

Whichever route you take, the sort order must be stable. Sorting by created_at alone is not enough: two records with the same timestamp can swap places between requests, and if they sit on a page boundary, one row is duplicated and another is missed. Add id as a secondary key so that no two rows ever tie.

Two more details routinely cost an afternoon. Arrays are passed in queries inconsistently: according to the OpenAPI guidance, the form style can be ?color=blue,green,red or ?color=blue&color=green, so check which form the client’s API expects. Also, many APIs use a - prefix for descending order, for example sort=-created_at.

params = {
    "limit": 100,
    "status": "closed,resolved",   # or repeat the parameter, depending on the API
    "sort": "created_at,id",       # ascending, id breaks ties (multi-field syntax is hypothetical)
}

Filters and sorting are only fast when the database has an index behind them. If a filter makes each request markedly slower, ask the client’s engineering team whether that field is indexed before concluding the API is “slow”.

Step 5: Batching, 429s and checkpoints

Pull enough data and you will hit a rate limit sooner or later. A well-behaved API returns HTTP 429 Too Many Requests, and your script must treat that as a signal to wait, not to give up. The snippet below is a simplified approach with increasing wait times; if the client’s documentation specifies its own retry policy, follow the documentation.

import time, json, os

def get_with_retry(session, url, params, max_tries=6):
    for attempt in range(max_tries):
        resp = session.get(url, params=params)
        if resp.status_code != 429:
            resp.raise_for_status()
            return resp.json()
        time.sleep(2 ** attempt)  # wait 1, 2, 4, 8... seconds
    raise RuntimeError("Still getting 429 after several attempts")

def save_checkpoint(cursor, path="checkpoint.json"):
    with open(path, "w") as f:
        json.dump({"cursor": cursor}, f)

def load_checkpoint(path="checkpoint.json"):
    if not os.path.exists(path):
        return None
    with open(path) as f:
        return json.load(f).get("cursor")

These two functions are only useful once wired into the cursor loop. The most important principle: save the cursor only after the batch is on disk. If you save the cursor first and the script dies while writing the file, the next run will start after rows that were never written.

def flush(batch, out_dir):
    last_id = batch[-1]["id"]
    with open(f"{out_dir}/batch_{last_id}.jsonl", "w") as f:
        for row in batch:
            f.write(json.dumps(row) + "\n")
    save_checkpoint(last_id)  # save the cursor AFTER the data is fully written

def pull_all(session, url, out_dir, limit=100, batch_size=1000):
    params = {"limit": limit}
    cursor = load_checkpoint()
    if cursor:
        params["starting_after"] = cursor  # resume from the saved position
    batch = []
    while True:
        page = get_with_retry(session, url, params)
        items = page["items"]
        batch.extend(items)
        if len(batch) >= batch_size:
            flush(batch, out_dir)
            batch = []
        if not page["has_more"] or not items:
            break
        params["starting_after"] = items[-1]["id"]
    if batch:
        flush(batch, out_dir)

Suppose the script dies at request 7,000 of 10,000. The cursor in the file is the last ID of the most recent fully written batch, so the next run re-fetches the few pages that had not yet been written and carries on. It neither starts over nor misses a row.

Naming files by the batch’s last ID also means a rerun does not overwrite earlier data.

For recurring syncs, check whether the API supports caching via ETag or Last-Modified, so you do not re-pull what has not changed.

Final check: compare the number of unique IDs with the total number of rows written. The two must match. If they do not, you have page drift, an unstable sort order or retries writing duplicates, and you must fix it before handing the data to anyone.

How does this skill show up at a client?

Clients will not ask whether you know cursor pagination. They will ask why the dashboard is missing 312 tickets compared with the source system. The person who can answer that within an hour, because they logged cursors, counted unique IDs and know what page drift is, is the one trusted with the next piece of work.

When reading FDE job descriptions, look for requirements about integrating with customer systems or building data pipelines from third-party systems: that is where this skill is used every day.

On your CV, instead of writing “API integration”, be specific: how many records you pulled, through which pagination style, how you handled rate limits and how you verified data integrity.

A small GitHub project that pulls data from Slack or a Stripe sandbox, with checkpoints and a row-count reconciliation step, is more convincing than a generic one-line description.

A script that runs on your laptop is only the starting point. What the client actually checks is whether the row counts match the source system, including after the script was interrupted and rerun at midnight.