Pagination, Rate Limits & Retries

Milestone 2's pipeline pulls a thousand-row sample and proves every record accounted for. Now Maria wants September — all 323,044 rows of it — the vendor throttles you mid-run, and Tom gets the stern email. This is the lesson where extraction grows up: read every page exactly once, take "slow down" gracefully, and make reruns harmless by construction.

Assumes: Stage 2, especially Post 6 (the retry helper) and Post 7 (the scripted-fake testing pattern). Installs: pip install requests. Every number below was computed against the real 311 feed or measured from a scripted fake — labeled either way, never asserted.

Before you code: clarify the ask

Input: the 311 Socrata feed, September 2026
Output: 323,044 distinct rows in SQLite, plus one reconciliation line Maria can read
Deadline: the ops review, Friday

Notice the shape of the ask: it's not "fetch the data." It's "fetch all of it, prove it's all of it, don't anger the vendor, and survive a 2 AM death." Four requirements, and each one is a section of this lesson.

The minimal concept

Three ideas, and the second one is the whole post:

Pagination: the API is a book — read every page exactly once. The feed won't hand you 323,044 rows in one response; it deals them in pages ($limit / $offset, or a cursor). Fetching is the easy part. The job is accounting: every page read once, none skipped, none read twice. A loop without a proof is a hope with a for-loop around it.

Rate limits: the API's bouncer, not its enemy. A 429 Too Many Requests means "slow down," and the Retry-After header tells you exactly how long. The polite response has two parts: backoff — wait longer after each refusal — and jitter — randomize the wait so five throttled clients don't all wake up in the same millisecond and re-form the mob. The vendor's limit is part of your contract; code like it.

Idempotency keys: make "oops, ran it twice" a no-op. Put a unique key on every write — a PRIMARY KEY in the database, an Idempotency-Key header on a POST — so a rerun after a 2 AM crash inserts zero new rows instead of doubling your month. Milestone 2 used the dedupe key for exactly this; now it's doing heavier lifting.

Build it, part 1: read every page exactly once

The extraction loop has four moves, in this order: count first, order explicitly, walk the pages, reconcile. Counting first is Post 5's boundary instinct applied to the whole job — you can't prove completeness against a total you never asked for:

import requests

BASE = "https://data.cityofnewyork.us/resource/erm2-nwe9.json"

def extract_month(session, start, end, page_size=10000):
    where = (f"created_date >= '{start}T00:00:00' "
             f"AND created_date < '{end}T00:00:00'")
    # 1. count first: the number the reconciliation is checked against
    total = int(session.get(
        BASE, params={"$select": "count(*)", "$where": where},
        timeout=(5, 30)).json()[0]["count"]

    # 2-3. explicit order, then walk the pages
    seen, dupes, offset, page = {}, 0, 0, 0
    while True:
        page += 1
        try:
            rows = fetch_page(session, BASE, params={
                "$select": "unique_key,created_date,agency,complaint_type",
                "$where": where,
                "$order": "created_date,unique_key,:id",
                "$limit": str(page_size), "$offset": str(offset)},
                timeout=(5, 60))
        except RuntimeError as e:
            # catch only to add context: WHICH page died, then re-raise
            raise RuntimeError(
                f"extract failed at page {page} (offset {offset})") from e
        for row in rows:
            if row["unique_key"] in seen:
                dupes += 1
            seen[row["unique_key"]] = row
        offset += len(rows)
        if len(rows) < page_size:
            break
    # 4. reconcile: the Milestone 2 arithmetic, now at full-month scale
    return {"total": total, "distinct": len(seen),
            "dupes": dupes, "pages": page}

Two details worth naming. The $order is explicit because page boundaries are only stable under a total order — you'll see what happens without one in the Break section, and it's the best bug in this post. And the try/except follows the exception rule: it catches only to attach the page number and offset, then re-raises. A dead page must be loud and locatable, never silent.

The real run, September 2026, ten thousand rows a page:

TOTAL via $select=count(*): 323044
page 1: offset=0 rows=10000 in 0.9s
page 2: offset=10000 rows=10000 in 3.8s
page 3: offset=20000 rows=10000 in 1.2s
...
page 33: offset=320000 rows=3044 in 1.7s
SUMMARY: total=323044 | distinct_keys=322997 | pages=33 | dupes=47
complete: False
elapsed: 64.0s

Read that summary twice. 323,044 rows arrived across 33 pages — but only 322,997 distinct keys. Forty-seven rows were served twice, which means forty-seven rows were never served at all. The loop ran; the proof failed. That failure is the most valuable output of the whole run, because without the reconciliation you'd have shipped a September missing 47 rows and never known. Why it happened — and the fix — is Break 1.

Build it, part 2: the polite 429

Post 6's retry helper knew about 503s and connection errors. The vendor's email adds the case it was missing: 429, with a Retry-After header, handled with backoff and jitter. One design decision first: Retry-After is honored exactly — it's the vendor telling you what they want — and jitter applies only to your own exponential guesswork. Waiting less than the vendor asked is how you get the second, sterner email:

import random, time

def fetch_page(session, url, params=None, tries=4, base=0.2,
               sleep=time.sleep, rand=random.random):
    """GET that respects 429s. Retry-After is honored as-is;
    jitter applies only to our own exponential guess."""
    for attempt in range(1, tries + 1):
        resp = session.get(url, params=params, timeout=(5, 30))
        if resp.status_code == 200:
            return resp.json()
        if resp.status_code == 429 and attempt < tries:
            retry_after = resp.headers.get("Retry-After")
            if retry_after is not None:
                delay = float(retry_after)            # vendor said so: honor it
            else:
                delay = base * (2 ** (attempt - 1)) * (0.5 + rand())
            sleep(delay)
            continue
        raise RuntimeError(f"page failed: HTTP {resp.status_code}")

Proving the curve with Post 7's scripted-fake pattern — a fake session plays [429 + Retry-After: 1, 429, 429, 200], and the sleeps are measured, not asserted (SIMULATED network, MEASURED timings):

measured sleeps (s): [1.0, 0.211, 1.137]
calls made: 4, result rows: 1

The vendor's requested second is honored to the millisecond; your own backoff guesses come out jittered — 0.21s and 1.14s instead of the naked 0.4s and 0.8s. And jitter is doing real work across runs. Same script, five seeds, base 0.5 (un-jittered would be [0.5, 1.0] every time):

seed 0: sleeps=[0.672, 1.258]
seed 1: sleeps=[0.317, 1.347]
seed 2: sleeps=[0.728, 1.448]
seed 3: sleeps=[0.369, 1.044]
seed 4: sleeps=[0.368, 0.603]

No two clients wait the same intervals. That's the point — and Break 2 shows what happens without it.

Build it, part 3: writes that survive reruns

Tom's 2 AM scenario: the run dies on page 19, you rerun, pages 1–18 arrive again. The write side has to absorb that without blinking. The idempotency key is the PRIMARY KEY — INSERT OR IGNORE turns the rerun into a no-op at the database level, no application logic required (Post 3's SQL, now load-bearing):

con.execute("CREATE TABLE september(unique_key TEXT PRIMARY KEY, ...)")
con.executemany("INSERT OR IGNORE INTO september VALUES (?,?,?,?)", rows)
con.commit()

Measured, against the real September keys:

IDEMPOTENCY: pk_table first_load_new=323026 rerun_new=0 final_count=323026

First load: 323,026 new rows. Full rerun: 0 new rows. And the counterfactual — the same rerun against a table with no key:

no_key_table after 'rerun': 646052 rows  (expected 646052 = double-counted)

646,052 — exactly double. That's the ops review presenting September with every request counted twice, because the write side had no key. The PRIMARY KEY is a one-line insurance policy against the 2 AM rerun; the table without one is a double-billing machine.

(Sharp-eyed: 323,026, not 323,044 — the rerun demo ran on the :id-tiebreaker extract, which still showed 18 boundary dupes. The dedupe key absorbed them silently, which is precisely its job. The reconciliation in part 1 is what detects the gap; the key is what contains it.)

Break it, three ways

1. The moving page boundary. You already watched this one happen — the part 1 run's complete: False. Forty-seven unique keys arrived twice across five boundary crossings (pages 15→16, 20→21, 24→25, 29→30, and a 35-row cluster at 31→32):

DUPLICATE unique_key 70408707 (first seen page 15, now page 16)
DUPLICATE unique_key 70409120 (first seen page 15, now page 16)
DUPLICATE unique_key 70416141 (first seen page 15, now page 16)
... 47 total across five boundary crossings ...

First hypothesis: the keys themselves repeat in the feed. Disproven in one query — each of those keys exists exactly once:

unique_key=70408707: 1 row(s) in the feed
   :id=row-cfas~zfiu-pknn created_date=2026-09-15T00:33:32.000 agency=NYPD

So the same row was served on two different pages. Root cause: ties in the sort key. Hundreds of rows share a created_date down to the second, and within a tie group the API is free to order rows however it likes — differently on every request. A tie group straddling a page boundary gets split differently each time, and a row slides across. The fix is a tiebreaker that can't tie — Socrata's internal :id:

"$order": "created_date,unique_key,:id"   # was: "created_date,unique_key"

Re-ran the full month: 47 dupes → 18. Better — not clean. And the same page fetched twice, seconds apart, differed in 107 of 10,000 rows. The feed moves under your feet: rows get corrected and re-served, so a boundary that was clean at 10:04 isn't clean at 10:05. Whether that's corrections landing or replicas disagreeing, the extractor can't tell and shouldn't care. The lesson isn't "find the perfect order" — it's that on a live feed, pages aren't promises. What makes the extract trustworthy is the triad: reconcile against the count (detects), dedupe on write (contains), re-pull the tail (repairs). The arithmetic from Milestone 2 caught a 47-row hole in a 323,044-row month. That's the machinery earning its keep.

2. The thundering herd. Five extractors get 429'd at the same instant. Each backs off exactly 1.0 second — no jitter. When does each retry fire? Measured from the same scheduling logic as the helper (SIMULATED clients, MEASURED timings):

jitter=off -> retries at [1.0, 1.0, 1.0, 1.0, 1.0]  spread=0.000s
jitter=on  -> retries at [0.572, 0.651, 0.824, 1.036, 1.151]  spread=0.579s

Without jitter, every client re-hammers the API in the same instant — the exact mob the rate limiter was trying to disperse, reassembled on schedule. The vendor's bouncer throws them out again, harder. Jitter isn't politeness theater; it's the difference between a retry storm and five independent, spread-out retries. This is why the helper randomizes your waits while honoring the vendor's explicit ones.

3. The naive hammer. The code that earned Tom's email — retry immediately, no backoff, tight loop (SIMULATED network, MEASURED loop time):

10 requests fired in 0.02ms — every one a 429.

Ten requests in twenty microseconds, each one a refusal, each refusal answered with another instant request. From the vendor's side this is indistinguishable from an attack — which is why the email said "sternly." The fix isn't just backoff; it's the realization that a retry loop without a wait is a load generator with a bug in it. Every retry policy you write from now on gets a delay, a cap on attempts, and — after the cap — a loud failure instead of an infinite loop.

Productionize: the extractor's contract

Five habits turn the demo into the thing that runs every month without you:

Checkpoint per page. Record the last completed offset (or better, the last key) after every page. The 2 AM death on page 19 resumes at page 19 — it doesn't re-walk eighteen pages. The checkpoint is a tiny file or table; the alternative is re-reading 180,000 rows because one request timed out.

Self-throttle; don't discover the limit. Sleep a beat between pages — the polite client paces itself instead of sprinting into the bouncer. Thirty-three requests took ~41–64 seconds here; adding a one-second pause between pages costs half a minute and buys you the vendor's goodwill. Rate limits you respect voluntarily are limits you never hit involuntarily.

Log every page with the run_id. Post 8's harness: one log line per page — page number, offset, rows, duration — tagged with the run's id. When the 6:20 rerun happens, its lines don't interleave ambiguously with the 6:00 attempt's, and "which pages landed?" is a grep, not a mystery.

Cap attempts, then fail loud. Post 6's rule, unchanged: after tries attempts, raise — with the page number and offset attached — and let the scheduler see a failure. A partial September that claims completeness is worse than a loud crash at 2 AM. The reconciliation line is the proof; a missing proof is itself the alert.

Never retry a 400. Still true, still worth repeating: a 429 is the vendor asking for patience; a 400 is the vendor telling you your request is wrong. Retrying a bad request with backoff is just failing slowly. Fix the params, don't pad the wait.

Explain it to the customer

Two notes go out. First, Maria's proof — the whole lesson in four numbers:

Then the vendor's API team, via Tom — short, professional, and specific about what changed:

Notice the difference between the two notes. Maria gets proof — numbers she can check. The vendor gets changed behavior — specifics they can verify from their side. Both are the same habit: don't assert trustworthiness, demonstrate it.

Where this lands in CityOps

The nightly pull graduates tonight. It no longer fetches a thousand-row sample — it walks the full window, page by page, with the reconciliation line Maria reads over coffee and the idempotent writes Tom's 2 AM rerun depends on. The backoff helper replaces Post 6's retry logic everywhere the pipeline touches a network, and the per-page logging rides Post 8's harness.

And there's a turn coming: in Milestone 3 you build the CityOps API, which means you become the vendor. Someone else's extractor will hammer your endpoints, ignore your 429s, and page your boundaries — and you'll be the one writing the stern email. Today's lesson is the client side of a contract you'll soon enforce from the server side. Polite clients finish; rude ones get cut off — and soon, you're the one holding the cutoff.

The signature line for this one: the polite client finishes; the rude one gets cut off. Milestone 2's principle was every record accounted for — this is the lesson that keeps that promise true at full-month scale, under a vendor's watchful eye.