Module 1: Ingest anything · Lesson 1.6 · about 40 min
APIs and source databases
Pull paginated APIs with retries, backoff and rate limits, re-pull late-changing days with a look-back window, and choose full, incremental or CDC extracts.
Module 1 · about 40 minutes · read first: 1.2, 1.4. You’ll be able to write an API client that pages through results, retries politely, respects rate limits and re-pulls recent days that are still changing. You’ll also know the three ways to extract from a source database and when a library like dlt saves you work.
1. Why this matters
Ad platforms, cloud till systems, booking engines, CRMs and payment providers all offer REST APIs: web addresses that return data (usually JSON) when you ask. Marketing data is the hardest teacher, because the numbers for “yesterday” keep changing for days as late sales are credited to ads. And sometimes the source is a company database you are allowed to read.
2. A well-behaved API client
import requests
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry
def session():
s = requests.Session()
retry = Retry(total=5, backoff_factor=1.5, status_forcelist=[429, 500, 502, 503, 504],
respect_retry_after_header=True, allowed_methods=["GET"])
s.mount("https://", HTTPAdapter(max_retries=retry))
s.headers["Authorization"] = f"Bearer {settings.ads_token}"
return s
def fetch_day(s, day: str):
url, params, pages = f"{BASE}/reports/daily", {"date": day, "page": 1}, []
while True:
r = s.get(url, params=params, timeout=30)
r.raise_for_status()
body = r.json()
pages.append(body) # land every page raw, unchanged
if not body.get("next_page"):
return pages
params["page"] = body["next_page"]
Pagination (splitting results into pages) comes in three shapes: page number, offset and limit, and cursor (the server hands you a token or link for the next page). Cursors are the safest when data changes while you read. The exact backoff timing of the Retry helper depends on your urllib3 version, so check its docs if the waits matter.
Watch a call that keeps failing, then succeeds:
Retry-After: 20. The rule would say 12 seconds, but the server asked for 20, and the server wins.Maths and principle
Principle: exponential backoff spaces out retries so a struggling server can recover. A little random jitter stops thousands of clients from retrying at the same instant.
Equation: the wait before retry is
where is the base wait and a random extra between 0 and . With s: 1.5, 3, 6, 12, 24 s. Without jitter the total over retries is .
Analogy: calling a busy friend: you wait a minute, then two, then four, instead of redialling every second. Where it breaks: a 429 with a Retry-After header overrides your formula. Obey it.
Try it: s and 4 retries. Longest total wait without jitter? (2 + 4 + 8 + 16 = 30 s.)
3. Data that changes after the fact
Ad conversions, hotel bookings (changed, cancelled), till refunds: yesterday’s numbers are not final. The pattern is a look-back window: every run re-pulls the last days and replaces them.
Choose from the data, not by guessing: measure how much a day’s total still changes after 1, 3, 7 and 14 days. If changes after day 7 are under 0.1% of spend, is a defensible choice, and the report footer says “last 7 days provisional”.
4. When the source is a database
| Method | How | Load on the source | Catches deletes? |
|---|---|---|---|
| Full extract | SELECT * every night |
High, and it grows forever | Yes, by comparing with the last copy |
| Incremental | WHERE updated_at > :watermark |
Low | No, unless rows are soft-deleted (flagged, not removed) |
| CDC (change data capture) | Read the database’s transaction log: SQL Server CDC or Change Tracking, Postgres logical replication, Debezium | Lowest | Yes |
Always read from a replica or a read-only login, during agreed hours, with a query timeout. A heavy query on a restaurant’s till database at lunchtime is how you meet the operations director.
- Pagination
- Getting results a page at a time: by page number, by offset, or by a cursor the server gives you.
- Exponential backoff
- Waiting twice as long after each failure before trying again.
- Rate limit
- The most calls an API allows per minute (or hour). Going over earns a 429.
- Look-back window
- Re-pulling and replacing the last N days on every run, because they may still change.
- CDC
- Reading every insert, update and delete from the database's own log of changes.
- Physical meaning
- An ad agency's client asks why yesterday's cost per sale fell overnight. Without a look-back window, the dashboard froze day one's half-counted conversions forever; with it, the number settles within a week and the report says which days are still provisional. Polite retries keep the agency's key from being blocked on the busiest morning of the month.
“Incremental loads by updated_at give me an exact copy of the table.” They miss every row that was hard-deleted, and every update made by a process that forgot to touch updated_at. Use CDC, or compare a periodic full extract, if deletes matter (they usually do for refunds and cancellations).
5. dlt: the same job in a few lines
dlt (data load tool) is an open-source Python library that handles pagination, incremental state, schema inference and changes, and loading into DuckDB, Postgres, SQL Server and Azure SQL, Snowflake, BigQuery and more (check its docs for the current list).
import dlt
from datetime import date, timedelta
def days_between(start: str, end: date):
d = date.fromisoformat(start)
while d <= end:
yield d.isoformat()
d += timedelta(days=1)
@dlt.resource(write_disposition="merge", primary_key=["date", "campaign_id"])
def ad_stats(cursor=dlt.sources.incremental("date", initial_value="2026-09-01")):
s = session()
start = (date.fromisoformat(cursor.last_value) - timedelta(days=7)).isoformat() # 7-day look-back
for d in days_between(start, date.today() - timedelta(days=1)):
for page in fetch_day(s, d):
yield page["data"] # dlt infers the schema, keeps state, merges on the key
pipeline = dlt.pipeline("ads", destination="duckdb", dataset_name="raw_ads")
print(pipeline.run(ad_stats())) # change the destination later without changing the resource
Know both levels. Tools like dlt and Airbyte are excellent, and you would use them in a real low-cost stack (Module 7). But incidents and interviews test whether you understand what they do underneath: pagination, state, merge keys, schema changes. Build it by hand once, then adopt the tool and write down why. “I wrote the client, then replaced 300 lines with dlt and kept my tests” is a strong story.
6. Try it
-
Serve the course ad JSON as a fake API. Save this as
fake_api.pyand run it withuv run fastapi dev fake_api.py(installfastapi[standard]first):import json from pathlib import Path from fastapi import FastAPI, HTTPException app, DIR = FastAPI(), Path("data/landing/api/ads") @app.get("/reports/daily") def daily(date: str, page: int = 1): f = DIR / f"{date}.json" if not f.exists(): raise HTTPException(404) return json.loads(f.read_text())["pages"][page - 1] -
Write
sources/ads_api.pywith retries, pagination and a 7-day look-back. Land each page raw as JSON lines with afetched_atfield. -
Make the fake API return a
429on 10% of calls and check your retries cope; log the retry counts. -
Notice
spend_micros(spend × 1,000,000) and the occasionalcurrency: USD. Do not fix them yet; note them innotes/sources.mdfor Module 2. -
Rebuild the same extract with dlt into DuckDB and compare row counts with your hand-built version. Write five lines in the README on what dlt gave you for free.
Commit: feat(ingest): ads API client with backoff and look-back; dlt comparison.
What to check: with the random 429s switched on, a full run still lands every page, and the row counts from your client and from dlt match.
7. Practice
- A cursor-paginated API returns
next: null. What does your loop do? - Your job re-pulls 7 days every morning, and Sunday’s total changed on Wednesday. Is that a bug?
- Why read from a replica rather than the main till database?
Answers
- Stop: there are no more pages. Land what you have.
- No. That is exactly why the look-back exists. Sunday is still inside the window and marked provisional.
- A heavy read on the main database slows the tills for customers. A replica (or agreed quiet hours with a timeout) keeps your extract from hurting the business.
New words: REST API, pagination, cursor, exponential backoff, jitter, rate limit, look-back window, incremental extract, CDC, dlt.
- Page through APIs (cursors are safest), land every page raw, and set timeouts.
- Retry with exponential backoff and jitter, and always obey a Retry-After header.
- Data that changes after the fact needs a look-back window sized from measurements, with provisional days labelled.
- Full, incremental and CDC extracts differ in load and in whether they see deletes; read from a replica.
Check yourself
Sources
- Fielding, R., Nottingham, M. and Reschke, J. (2022), RFC 9110: HTTP Semantics, Section 10.2.3 Retry-After https://www.rfc-editor.org/rfc/rfc9110
- Nottingham, M. and Fielding, R. (2012), RFC 6585: Additional HTTP Status Codes (429 Too Many Requests) https://www.rfc-editor.org/rfc/rfc6585
- dlt (data load tool) documentation https://dlthub.com/docs/
- Kleppmann, M. (2017), Designing Data-Intensive Applications, O'Reilly Media, Chapter 11 (change data capture)
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.