API and File-Based Data Ingestion

Data Engineering & Modern Data Platforms · Lesson 15 · Data Ingestion, ETL and Orchestration

Lesson Overview

The landing job in Building Batch Data Pipelines with Python assumes a closed file. Two TechLearningPro sources do not start that way. The content catalog is an HTTP API the studio owns. Instructor operations also posts a webhook when a lesson is published, and once a night a course-outline export arrives as CSV on a private prefix. A well-meaning puller numbered pages from zero, requested page size 500, and stored each response. During the hour the studio unpublished a lesson, page 3 and page 4 both contained the same later lessons and omitted one that had shifted into the gap. The lesson dimension gained duplicates and lost a terminal checkpoint. The completion SQL in ETL and ELT Architecture then dropped events whose lesson_id no longer matched the dimension, which looked like a learner problem.

API ingestion and file ingestion are how you build the closed file the batch job already knows how to publish. The API side needs a pagination contract that does not skip or repeat when the collection changes, a retry policy that honors the server's wait, and a manifest written last so a consumer never reads a partial set of pages. The file side needs a header contract, an encoding, a checksum, and the same publish-last rule. The webhook side needs a signature over the raw body and an idempotency key, because the studio will retry. None of that is a vendor SDK. This lesson uses the platform's own catalog contract, the Python standard library, and a directory that stands in for an object prefix. It does not invent a cloud client's methods. When you point the same steps at a real object store, you use that store's documented upload and checksum, and you keep the order: parts, then manifest.

Data Integration Patterns already chose API pull for the lesson dimension and file drop for seats. This lesson is the mechanics. Change feeds from PostgreSQL are the next lesson, not a substitute for a catalog that will not give you a table.

Planning assumptions, labeled: about eight thousand lessons, a page size of a few hundred, an hourly dimension bound, a webhook that must be safe to receive twice, and a nightly outline CSV of modest size. The fifty-million-event progress file is not pulled through this API. Do not "simplify" the platform by putting progress events behind offset pagination.

Learning Objectives

By the end of this lesson you will be able to:

1. Explain why offset pagination skips or repeats rows when the source collection changes, and how a keyset cursor avoids that class of gap.
2. Land API pages as parts plus a manifest written last, so a reader never treats a partial pull as the dimension.
3. Verify a webhook signature over the raw body and ignore an identical redelivery.
4. Reject a CSV whose header carries a prohibited field or whose encoding does not match the contract.
5. Apply a retry delay that respects Retry-After and backs off otherwise, without counting a failed page as complete.
6. State what the lesson dimension is allowed to be used for, and what a webhook notification is not allowed to replace.

Prerequisites

• Lesson 12's file-drop and API-pull patterns, including the rule that a manifest is the completeness marker.
• Lesson 13's zones: landing is private, raw is projected, curated is what SQL joins.
• Lesson 14's atomic replace and checkpoint-last habit. Pages are just the files that job expected someone else to close.
• Lesson 11's CSV and JSON limits: no reliable types in CSV, one JSON object per line once you land pages as JSONL.
• Python 3.11, including hmac, hashlib, csv, and pathlib. /learn/python. You do not need an HTTP framework. /learn/sql is the consumer of the dimension, not the puller.

Detailed Core Concepts

The catalog contract is explicit

The studio's lesson catalog, for this course, is a documented HTTP resource the platform owns:

GET /v1/lessons?page_size={n}&cursor={opaque}

The response body is JSON: items, a list of lesson objects, and next_cursor, a string or null. Each item has lesson_id, course_id, title, and terminal_checkpoint. The cursor is the lesson_id of the last item returned, and the server returns items with lesson_id strictly greater than that cursor, ordered by lesson_id. The first call omits cursor. page_size is a maximum, not a promise that the page is full. A null next_cursor ends the scan.

That paragraph is the contract. A client that assumes "cursor is a page number" will be wrong. A client that sorts again by title will not match the server's continuation. The analytical puller does not invent query parameters the studio did not publish. If the studio later offers updated_since, that is a new contract and a new watermark, reviewed as an incremental pattern. Until then the hourly job is a full keyset scan of a few thousand lessons, which the planning assumption says is cheap, and which still needs a manifest because "cheap" is not "atomic."

Offset pages move under you

Offset pagination, ?offset=0&limit=500 then offset=500, numbers positions in a list that is not stable. If a lesson is deleted near the start after you have read the first page, every later lesson shifts up. The next offset skips the lesson that slid into the gap you already counted. If a lesson is inserted near the start, the next offset repeats a lesson you have seen and may never finish if you stop at a row count you computed up front. The duplicates and the gaps are not a flaky network. They are the meaning of offset on a mutable list.

Keyset pagination continues from the last key you saw. A deletion of a key you have not reached yet is either included or not based on whether it still exists when the server reads that key range. You do not skip an unrelated later key because an earlier row disappeared. You can still miss a lesson inserted behind your cursor, with a smaller id, after you passed it. A full hourly scan accepts that the dimension is "as of the scan," not "every intermediate edit." State that on the product. The cohort of lessons is small enough to rescan. Progress events are not.

A pull is complete when the manifest says so

Each page is stored as a part object: lesson-dim/dt=2026-03-02/run=01/parts/page-0001.jsonl. The part's bytes are hashed. After the last page, the job writes manifest.json in the run directory, listing each part name, byte length, SHA-256, and row count. Readers of the dimension are allowed to read a run directory only when the manifest exists and every hash matches. A crash while page 4 is uploading leaves no manifest. The previous run's manifest remains the dimension. That is the same consumer promise as lesson 14's unpublished file.

Do not use a directory listing as the manifest. Listings include partial parts and temp names. Do not write the manifest first with a planned list and "fill it in." A reader that races you will trust an empty plan. Manifest last, after the parts are durable and hashed from the bytes you wrote, not from the bytes you intended to write.

Retries, rate limits, and partial pages

The catalog may respond with 429 or 503 and a Retry-After value in seconds. The client waits that long, then repeats the same cursor. It does not advance. A timeout with no response also repeats the same cursor. Backoff without a Retry-After header doubles a base delay up to a cap, for a bounded number of attempts, then fails the run. Failing the run leaves the previous manifest in place.

Counting a short page as the end of the catalog is a second bug. The end is next_cursor null, not "fewer items than page_size," if the contract says the server may return a short page in the middle. This course's contract uses null to end, and allows a short final page. A short middle page with a non-null cursor is continued. Implement the contract you wrote down. Do not import a different API's habits.

Idempotency of the pull is the manifest's hash of parts. Re-pulling the hour replaces the run id. It does not append pages to yesterday's run. Two writers of the same run id are forbidden, the same single-flight rule as lesson 14.

Webhooks are at least once

The studio webhook posts the published lesson as JSON and signs the raw body with HMAC-SHA256 using a shared secret. The header in this course's contract is X-TLP-Signature, hex-encoded. Verification computes the HMAC over the exact bytes received and compares with hmac.compare_digest. Re-serializing the JSON and signing that will fail legitimate deliveries whose whitespace differs, and it will also let a proxy "pretty-print" you into rejecting the world. Compare in constant time so the failure does not become a timing oracle for the secret.

The body carries event_id and the lesson fields. That is state transfer, not a notification that forces a callback. Lesson 12's rule applies. A duplicate event_id with the same body is a retry and is ignored. A duplicate event_id with a different body is a breach and is not applied. The webhook updates a current-state "latest publish" feed for instructor tooling. It does not, by itself, rebuild the hourly dimension. A lost webhook is repaired by the next full catalog scan. If you skip the scan because "we have webhooks," a missed callback becomes a permanent hole. The scan is the completeness story. The webhook is the freshness optimization inside the hour.

CSV is a contract about headers and bytes

The nightly outline export is CSV. The contract names the encoding (UTF-8), the header row, the required columns (course_id, lesson_id, position, title), and the prohibited headers (email, name, payment_token). csv.DictReader uses the header row. A producer that swaps two columns without renaming them will silently assign titles to positions if you index by position instead of by name. Named columns catch a missing header. They do not catch a swapped meaning under the same header. A checksum in a sibling manifest catches a torn upload. A row count in the manifest catches a truncated file only if the producer knew the count. Use both: the producer's count and the hash of the object you actually stored.

Embedded commas and quotes are why you use a CSV parser and not line.split(","). A title with a comma, quoted correctly, is one column. Newlines inside quotes are one record. A file that is still being uploaded is not a CSV yet. The manifest-last rule is the same as for API pages. Lesson 11's warning stands: CSV has no types. position is text until you parse an integer and quarantine the row that is not one.

Object storage, described without a fictional client

A prefix plus a key is the layout: the run id is in the key, parts are immutable once the manifest exists, and the pointer "current" is a small object updated after the manifest verifies. Updating "current" last is what readers follow. The local directory in the walkthrough is that layout on a filesystem. A cloud object store adds its own rules for when a PUT becomes visible and how a multipart upload is completed. Those rules are in that provider's documentation. This lesson's invariant is the one you test: a reader that follows the current pointer never sees a missing part, a mismatched hash, or a prohibited header.

Architecture and Design Diagrams

The hourly dimension and the webhook feed are different products. The CSV outline joins the same lesson ids and does not get to bypass the header check.

mermaidCopy
flowchart LR
    subgraph sources [Studio sources]
        api[Catalog keyset API]
        hook[Publish webhook]
        csv[Outline CSV]
    end

    subgraph landing [Private landing]
        parts[Page parts]
        manifest[Manifest written last]
        signed[Verified webhook log]
        file[CSV plus checksum]
    end

    subgraph serve [What consumers read]
        dim[Lesson dimension as of scan]
        latest[Latest publish feed]
    end

    api --> parts --> manifest --> dim
    hook --> signed --> latest
    csv --> file --> dim
    latest -.->|must not replace the hourly scan| dim

The read path for a run is a sequence. Anything before the current pointer moves is invisible to the completion SQL.

mermaidCopy
sequenceDiagram
    participant Job
    participant API as Catalog API
    participant Parts as Part prefix
    participant Reader as Dimension reader
    loop until next_cursor is null
        Job->>API: GET page for cursor
        API-->>Job: items and next_cursor
        Job->>Parts: Put part, then hash the stored bytes
    end
    Job->>Parts: Put manifest
    Job->>Parts: Move current pointer
    Reader->>Parts: Read current only
    Note over Job,Reader: A crash before the pointer move keeps the previous current

Offset versus keyset, for the interview you will actually get:

mermaidCopy
flowchart TD
    mut[Source collection changes during the scan]
    off[Offset: later pages shift]
    skip[Skipped or repeated rows]
    key[Keyset: continue after last lesson_id]
    bound[Misses only keys inserted behind the cursor]
    mut --> off --> skip
    mut --> key --> bound

Technical Explanation

Cursor opacity and stability

Treat the cursor as opaque even though this contract admits it is a lesson_id. Store it. Do not increment it, decode it, or trim it. If the studio switches to a composite key, an opaque client keeps working and a client that did lesson_id + 1 does not. The walkthrough's server uses the id because the teaching implementation has to be true. The client function only passes the string back.

A cursor that does not advance is a stuck loop. If next_cursor equals the cursor you sent, abort. If you have followed more pages than ceil(expected_lessons / 1) + a margin, abort. Eight thousand lessons at page size 200 is forty pages. A guard at a few hundred pages fails a bug. It should not be the completeness check. The null cursor is.

Hash the bytes you stored

Compute SHA-256 from the file you will ask the reader to open, after the write, not from the buffer you meant to write if those can differ. On a local replace they should not differ. On an object store, use the checksum the store documents, and still keep your own hash in the manifest if you need a vendor-neutral verify. A manifest that records the hash of the HTTP response and a part that was re-encoded on write will fail verify forever or, worse, will be "fixed" by skipping verify.

Signature scope

HMAC-SHA256(secret, raw body) as hex is the contract here. It is not a claim about any particular vendor's header. Production additions that this lesson leaves named but not implemented: a timestamp in the signed payload so a captured body cannot be replayed next week, and a key id so you can rotate the secret. Without a timestamp, your idempotency key stops accidental retries and does not stop a stolen body from being posted again after you have forgotten that event_id. Rotate secrets by accepting two secrets during the overlap. Reject when neither matches. Do not log the secret or the signature next to the body.

CSV and the prohibited header

Check headers before parsing every row into the warehouse types. If email is present, refuse the file, the way lesson 14 refuses a JSONL file. Do not load the column and drop it in a second pass that already wrote landing into a shared place. Private landing, refuse, previous dimension remains current.

Integer parsing for position quarantines the row or fails the file according to the cap you set with the product owner. A non-integer position in an outline will mis-order a course. For a file of outline rows, failing the file is reasonable. For a file of forty thousand seat rows, a cap plus a quarantine may be the contract. Write it down per file type. Do not inherit the progress-event cap without looking.

What the dimension guarantees

The lesson dimension produced by a verified manifest is "catalog as of this successful scan," at grain lesson_id. It includes course_id and terminal_checkpoint because lesson 13's completion SQL joins them. It is not enrollment. It is not a learner attribute. Titles may contain text the studio typed. They are not a place to hide a personal email "in case support needs it." The webhook feed is "last signed publish event per lesson" and can be newer than the dimension. Completion SQL reads the dimension. Using the webhook feed as the dimension will diverge the moment a callback is lost. The diagram's refusal is the product boundary.

Practical Implementation or Design Walkthrough

Problem

Pull the catalog with a keyset cursor, show the offset failure mode, land parts and a manifest, verify a webhook body, and refuse a CSV that contains an email header. All of this runs in the standard library against in-memory data and a local directory.

pythonCopy
from __future__ import annotations

import csv
import hashlib
import hmac
import io
import json
import os
import tempfile
from dataclasses import dataclass
from pathlib import Path


@dataclass(frozen=True)
class Lesson:
    lesson_id: str
    course_id: str
    title: str
    terminal_checkpoint: int


@dataclass(frozen=True)
class Page:
    items: tuple[Lesson, ...]
    next_cursor: str | None


def fetch_keyset(
    lessons: tuple[Lesson, ...],
    cursor: str | None,
    page_size: int,
) -> Page:
    """Server side of the catalog contract. Ordered by lesson_id, exclusive cursor."""
    if page_size < 1:
        raise ValueError("page_size must be positive")
    ordered = tuple(sorted(lessons, key=lambda item: item.lesson_id))
    if cursor is None:
        start = 0
    else:
        start = None
        for index, item in enumerate(ordered):
            if item.lesson_id == cursor:
                start = index + 1
                break
        if start is None:
            raise ValueError("cursor is not a lesson_id this server returned")
    chunk = ordered[start : start + page_size]
    more = start + len(chunk) < len(ordered)
    nxt = chunk[-1].lesson_id if chunk and more else None
    return Page(items=chunk, next_cursor=nxt)


def pull_keyset(
    lessons: tuple[Lesson, ...],
    page_size: int,
    page_guard: int = 10_000,
) -> tuple[Lesson, ...]:
    """Client. Follow next_cursor until null. Abort if the cursor stalls."""
    collected: list[Lesson] = []
    cursor: str | None = None
    for _ in range(page_guard):
        page = fetch_keyset(lessons, cursor, page_size)
        collected.extend(page.items)
        if page.next_cursor is None:
            return tuple(collected)
        if page.next_cursor == cursor:
            raise ValueError("cursor did not advance")
        cursor = page.next_cursor
    raise ValueError("page guard exceeded")


def pull_offset_while_mutated(page_size: int = 2) -> tuple[str, ...]:
    """Show an offset scan skipping a row when an earlier row disappears."""
    first = [
        Lesson("les_01", "c1", "Intro", 3),
        Lesson("les_02", "c1", "Models", 4),
        Lesson("les_03", "c1", "SQL", 5),
        Lesson("les_04", "c1", "Files", 2),
    ]
    seen: list[str] = []
    offset = 0
    page = first[offset : offset + page_size]
    seen.extend(item.lesson_id for item in page)
    offset += page_size
    # The studio unpublishes les_01 before the client requests the next offset.
    shifted = [item for item in first if item.lesson_id != "les_01"]
    page = shifted[offset : offset + page_size]
    seen.extend(item.lesson_id for item in page)
    return tuple(seen)


def retry_delay_seconds(
    attempt: int,
    retry_after: float | None,
    base_seconds: float = 1.0,
    cap_seconds: float = 30.0,
) -> float:
    """Same cursor is retried. Retry-After wins. Otherwise exponential backoff."""
    if attempt < 1:
        raise ValueError("attempt starts at 1")
    if retry_after is not None:
        if retry_after < 0:
            raise ValueError("Retry-After must be non-negative")
        return min(retry_after, cap_seconds)
    delay = base_seconds * (2 ** (attempt - 1))
    return min(delay, cap_seconds)


def signature_hex(secret: bytes, body: bytes) -> str:
    return hmac.new(secret, body, hashlib.sha256).hexdigest()


def accept_webhook(
    secret: bytes,
    body: bytes,
    presented_signature: str,
    seen_ids: set[str],
) -> str:
    """Return 'applied' or 'duplicate'. Raise if the signature or the body is bad."""
    expected = signature_hex(secret, body)
    if not hmac.compare_digest(expected, presented_signature):
        raise ValueError("signature mismatch")
    payload = json.loads(body)
    if not isinstance(payload, dict):
        raise ValueError("body must be an object")
    event_id = payload.get("event_id")
    if not isinstance(event_id, str) or not event_id:
        raise ValueError("event_id is required")
    if event_id in seen_ids:
        return "duplicate"
    for field in ("lesson_id", "course_id", "title"):
        if not isinstance(payload.get(field), str):
            raise ValueError(f"{field} is required")
    seen_ids.add(event_id)
    return "applied"


def _sha256(path: Path) -> str:
    digest = hashlib.sha256()
    with path.open("rb") as handle:
        for chunk in iter(lambda: handle.read(1024 * 1024), b""):
            digest.update(chunk)
    return digest.hexdigest()


def _atomic_bytes(path: Path, data: bytes) -> None:
    path.parent.mkdir(parents=True, exist_ok=True)
    temporary = path.with_suffix(path.suffix + ".tmp")
    temporary.write_bytes(data)
    os.replace(temporary, path)


def land_pages(run_dir: Path, pages: tuple[tuple[Lesson, ...], ...]) -> Path:
    """Write parts, then manifest.json. Readers use the manifest, not a listing."""
    parts_meta: list[dict[str, object]] = []
    parts_dir = run_dir / "parts"
    for index, page in enumerate(pages, start=1):
        name = f"page-{index:04d}.jsonl"
        lines = [
            json.dumps(
                {
                    "lesson_id": item.lesson_id,
                    "course_id": item.course_id,
                    "title": item.title,
                    "terminal_checkpoint": item.terminal_checkpoint,
                },
                sort_keys=True,
            )
            for item in page
        ]
        body = ("\n".join(lines) + ("\n" if lines else "")).encode("utf-8")
        path = parts_dir / name
        _atomic_bytes(path, body)
        parts_meta.append(
            {"name": name, "sha256": _sha256(path), "bytes": path.stat().st_size, "rows": len(page)}
        )
    manifest_path = run_dir / "manifest.json"
    _atomic_bytes(
        manifest_path,
        (json.dumps({"parts": parts_meta}, indent=2, sort_keys=True) + "\n").encode("utf-8"),
    )
    return manifest_path


def verify_manifest(run_dir: Path) -> int:
    manifest = json.loads((run_dir / "manifest.json").read_text(encoding="utf-8"))
    rows = 0
    for part in manifest["parts"]:
        path = run_dir / "parts" / part["name"]
        if _sha256(path) != part["sha256"] or path.stat().st_size != part["bytes"]:
            raise ValueError(f"part mismatch: {part['name']}")
        rows += int(part["rows"])
    return rows


PROHIBITED_HEADERS = frozenset({"email", "name", "payment_token"})
REQUIRED_HEADERS = frozenset({"course_id", "lesson_id", "position", "title"})


def screen_csv(text: str) -> tuple[dict[str, str], ...]:
    """Refuse prohibited or missing headers. Parse with the csv module, not split."""
    reader = csv.DictReader(io.StringIO(text))
    if reader.fieldnames is None:
        raise ValueError("csv has no header")
    headers = {name.strip() for name in reader.fieldnames}
    leaked = headers & PROHIBITED_HEADERS
    if leaked:
        raise ValueError(f"prohibited header {sorted(leaked)[0]}")
    missing = REQUIRED_HEADERS - headers
    if missing:
        raise ValueError(f"missing header {sorted(missing)[0]}")
    return tuple(dict(row) for row in reader)


def _demo() -> None:
    lessons = (
        Lesson("les_01", "c1", "Intro", 3),
        Lesson("les_02", "c1", "Models", 4),
        Lesson("les_03", "c1", "SQL", 5),
        Lesson("les_04", "c1", "Files", 2),
    )
    pulled = pull_keyset(lessons, page_size=2)
    assert [item.lesson_id for item in pulled] == ["les_01", "les_02", "les_03", "les_04"]
    skipped = pull_offset_while_mutated()
    assert skipped == ("les_01", "les_02", "les_04")
    assert "les_03" not in skipped

    pages: list[tuple[Lesson, ...]] = []
    cursor: str | None = None
    while True:
        page = fetch_keyset(lessons, cursor, 2)
        pages.append(page.items)
        if page.next_cursor is None:
            break
        cursor = page.next_cursor
    root = Path(tempfile.mkdtemp(prefix="catalog-land-"))
    run = root / "dt=2026-03-02" / "run=01"
    land_pages(run, tuple(pages))
    assert verify_manifest(run) == 4
    assert not (run / "parts" / "page-0001.jsonl.tmp").exists()

    assert retry_delay_seconds(1, retry_after=12) == 12
    assert retry_delay_seconds(3, retry_after=None) == 4

    secret = b"studio-secret"
    body = json.dumps(
        {
            "event_id": "pub_1",
            "lesson_id": "les_04",
            "course_id": "c1",
            "title": "Files",
        }
    ).encode("utf-8")
    seen: set[str] = set()
    assert accept_webhook(secret, body, signature_hex(secret, body), seen) == "applied"
    assert accept_webhook(secret, body, signature_hex(secret, body), seen) == "duplicate"
    try:
        accept_webhook(secret, body, "0" * 64, seen)
    except ValueError as exc:
        assert "signature" in str(exc)
    else:
        raise AssertionError("bad signature was accepted")

    good_csv = "course_id,lesson_id,position,title\nc1,les_01,1,\"Intro, part one\"\n"
    rows = screen_csv(good_csv)
    assert rows[0]["title"] == "Intro, part one"
    try:
        screen_csv("email,course_id,lesson_id,position,title\na@b.c,c1,les_01,1,Intro\n")
    except ValueError as exc:
        assert "prohibited" in str(exc)
    else:
        raise AssertionError("email header was accepted")
    print(f"pulled={len(pulled)} skipped_offset={skipped} rows={verify_manifest(run)} root={root}")


if __name__ == "__main__":
    _demo()

What the demo shows

The keyset pull returns four lessons in id order across pages of two. The offset demonstration, after les_01 disappears, collects les_01, les_02, and les_04, and never sees les_03. That is the skip. The manifest verifies four rows. A quoted comma survives csv.DictReader. An email header refuses the file. A second webhook with the same body is a duplicate. A wrong signature raises. Retry-After of 12 seconds is honored. Attempt 3 without that header waits 4 seconds. The demo does not sleep.

How you would run it

bashCopy
python3 catalog_ingest.py

The script prints counts and a temporary directory. No packages are installed. A real hourly job replaces fetch_keyset with an HTTP call that sends the cursor and reads next_cursor, and it replaces the local directory with the object prefix, using the provider's documented PUT. Keep pull_keyset, land_pages, verify_manifest, accept_webhook, and screen_csv as the behavior you test against fixtures when the network is absent.

What this does not do

It does not open a socket in the demo, because a lesson that depends on a port is a lesson that fails closed for the wrong reason. The HTTP mapping is the contract at the top of the lesson. It does not refresh OAuth tokens. It does not implement multipart upload. It does not treat the webhook store as the dimension the completion job joins.

Enterprise Real-World Scenarios

The hourly lesson dimension

Under the planning assumption of about eight thousand lessons, the hourly job scans the keyset API, writes a new run directory, verifies the manifest, and only then updates the current pointer the completion SQL is configured to read. A studio edit in the middle of the scan yields a dimension that is a single server's successive pages, not a transaction snapshot. The product text says "as of scan start to scan end," and the completion job uses the pointer that finished, not a page that arrived mid-scan. When the studio deleted a lesson during an old offset-based pull, les_03 disappeared and a later lesson was stored twice. The keyset pull plus the manifest made a rerun of that hour either complete or absent. The previous hour stayed current until verify passed.

terminal_checkpoint is on the dimension because completion-v3 needs it. The pull does not compute completions. A failed hour does not mean zero lessons. It means the pointer did not move. Instructor studio, which wants a publish to show within a minute, reads the webhook feed. Finance and the Monday mart do not.

The publish webhook and the outline CSV

The webhook carries the lesson fields and a stable event_id. The studio retries on timeout, so the second delivery returns duplicate and does not append a second "latest" row. A signature failure, including one caused by a middleware that rewrote whitespace and a verifier that re-serialized JSON, is how an early version dropped legitimate publishes. The repair was to sign and verify the raw body. The hourly scan still ran. A missed webhook was visible as webhook lag, not as a missing lesson in Monday's mart, because the mart never read the webhook table.

The outline CSV is the order of lessons inside a course, which the catalog items do not fully replace in this estate: position is the outline's fact. The file lands on a private prefix with a checksum manifest. A file whose header included email, added so an editor could "see who to ask," was refused. The previous outline stayed current. The title Intro, part one remained one column because the parser was the CSV module. A split on commas would have shifted position onto the word part and the completion join would have kept working, wrongly, because it does not read position. The outline product would have been nonsense. Parser choice is a product choice even when the mart you are worried about does not use every column.

Best Practices

• Write the pagination contract down: order, cursor meaning, and what ends a scan. Implement that, not a folklore page number.
• Prefer a keyset cursor for a collection that can change during the pull. Rescan on a schedule if you need inserts behind the cursor.
• Write parts, hash the stored bytes, write the manifest, then move the current pointer.
• Retry the same cursor. Advance only after a response you have stored.
• Honor Retry-After. Cap the wait. Fail the run after a bounded number of attempts and keep the previous pointer.
• Sign webhook bodies as bytes. Compare with hmac.compare_digest. Deduplicate on event_id.
• Let a full scan be the completeness path for any webhook freshness path.
• Parse CSV with a CSV parser. Refuse prohibited headers before load. Name the encoding.
• Keep landing private. Do not use a directory listing as a manifest.
• Map this layout to an object store with that store's documented complete-upload. Do not invent a client in the pipeline.
• Keep progress-event volume off this API. Hourly keyset is for the catalog-sized source.
• One writer per run id.

Common Mistakes and Anti-Patterns

Offset pagination on a mutable catalog. Deletes and inserts shift later pages. The dimension gains duplicates and holes that look like content bugs. The repair is a keyset cursor and a full rescan at the product's bound.

Ending the scan on a short page. A server that returns a short page with a continuation cursor is not finished. The client that stops early publishes a small catalog and the completion job drops real lessons. The repair is to stop on the contract's end marker, which here is a null cursor.

Manifest first, or no manifest. Readers observe a partial run, or they glob a prefix that includes a temp part. The repair is manifest last and a current pointer that moves after verify.

Re-serializing JSON before verifying the signature. Legitimate retries fail, or a transformed body verifies when it should not. The repair is HMAC over the raw bytes, and a test fixture that includes insignificant whitespace.

Webhook as the only dimension. One lost callback deletes a lesson from the join until someone notices completions fell. The repair is the hourly scan as the completeness source and the webhook as a faster side feed with a different consumer.

split(",") as a CSV parser. Quoted commas become extra columns and shift the fields you do not even use yet. The repair is csv.DictReader and a header contract.

Advancing the cursor on a timeout. You skip a page you never stored. The repair is to persist the page, then continue. A retry repeats the request for the cursor you still hold.

Putting the catalog token in the manifest. The manifest is readable by the dimension job and sometimes by more people than the secret deserves. The repair is a secret store, and a manifest that contains hashes and names only.

Troubleshooting and Design Review

Symptom: The lesson dimension is missing every k-th lesson, and the studio deleted drafts during the pull.

Likely cause: offset pagination. The gap matches the page boundary.

Investigation: log the cursor or offset you sent and the first and last id on each page. Reproduce with a deletion between two pages, as the offset demo does.

Corrective action: switch to the keyset contract. Rebuild the dimension from a finished scan. Do not patch missing ids by hand.

Symptom: Completions fell for one course, and the dimension run directory has four parts and no manifest.

Likely cause: the job died after the parts and before the manifest, and a reader globbed the parts anyway. Or the current pointer moved early.

Investigation: compare the current pointer with the run that has a manifest. Confirm the SQL path.

Corrective action: point readers at current. Move current only after verify. Rerun the hour. Leave the partial run in place for forensics, then delete it under landing retention.

Symptom: Webhook deliveries are all signature failures after a proxy change.

Likely cause: the proxy or the verifier altered bytes. Someone parsed JSON and dumped it again before the HMAC.

Investigation: compare a stored raw body with what the verifier hashes. Check for charset translation.

Corrective action: hash the original bytes. Store the raw body in the private landing prefix if you need to re-verify. Do not log the secret while debugging.

Symptom: The outline positions are wrong only for titles that contain commas.

Likely cause: a split on commas. Quoted fields were not parsed.

Investigation: open the landing object and parse it with csv.DictReader. Compare position to the source for one quoted title.

Corrective action: replace the parser. Refuse the run that was produced by the split. Republish from the landing object if it is still inside retention and was the unmodified upload.

Symptom: The hourly job never finishes and the catalog logs show a tight loop.

Likely cause: the client increments an offset it thinks is a cursor, or next_cursor is ignored and the first page is requested forever. Alternatively a 429 is retried with no delay.

Investigation: log the cursor value per request, not the items. Check status codes.

Corrective action: follow next_cursor unchanged. Apply retry_delay_seconds. Keep the page guard as a backstop, not as the success condition.

A design review asks: What is the end of a scan? What does a mutation during the scan do? When does the current pointer move? What bytes are signed? What header fails a CSV? Which consumer reads the webhook, and which must not? Where is the secret? What remains served if the run fails? The review does not begin with a connector marketplace.

Interview Questions and Answers

Q1. Why does offset pagination skip rows when you only asked for the next page?

Offset addresses a position, not a key. If a row before that position is deleted after you read the first page, the rows after it shift into lower positions. The next offset jumps over the row that moved into the gap. An insert before the offset repeats a row and can stretch the scan. I use a keyset cursor: the server returns a stable order and a cursor that means "strictly after this key." I still rescan if I must see keys inserted behind me. I do not describe offset as acceptable "because the page size is large." A large page makes the skip larger.

Q2. How do you know an API pull is complete?

The contract's end condition has been reached, every page I accepted is stored, the hashes in a manifest match the stored bytes, and the pointer consumers read has moved to that manifest. A short page is complete only if the contract says a short page ends the scan. In this catalog, a null next_cursor ends it. A crashed job with parts and no manifest is not complete, and the previous pointer stays current. Directory listings are not manifests.

Q3. What is wrong with signing a webhook after you parse and re-serialize the JSON?

The signature no longer covers the bytes the sender signed. Key order, spaces, and Unicode escaping change the HMAC, so good deliveries fail. A party that can change bytes and then re-serialize can also make a bad body verify if your verifier signs the same way. I HMAC the raw body and compare with hmac.compare_digest. I deduplicate on event_id after the signature succeeds. I want a timestamp inside the signed body before I trust the request a week later. That timestamp is an addition to the contract, not a reason to skip the raw-byte check.

Q4. When is a webhook enough, and when do you still scan?

A webhook is enough for a consumer who can tolerate a miss until the next scan, or who only needs a hint to refresh. It is not enough as the only copy of a dimension the completion mart joins. Deliveries are at least once, and they are also at most the callbacks the sender managed to make. I keep the hourly keyset scan as the completeness path and let the webhook update a separate latest-publish feed. Lesson 12's state-transfer rule still applies: the body should carry the fields, not only an id, if the feed must be replayable without calling back.

Q5. How should a 429 change the client?

The client waits for Retry-After when it is present, capped so a mistaken header cannot pause the job for a day, and then repeats the same cursor. It does not mark that page successful. Without the header, it backs off exponentially, also capped, for a bounded number of attempts, then fails the run. The previous dimension remains served. I do not tight-loop a 429 to "catch up," because that is how the catalog falls over during enrollment week.

Q6. What belongs in a file manifest for a CSV drop?

The part names, byte sizes, checksums of the stored objects, the producer's row count if they have one, and the encoding. The consumer verifies hashes and refuses a prohibited header before any curated load. The manifest is written after the parts are durable. It does not contain credentials. A row-count match with a wrong checksum is still a failure. A checksum match with an email header is still a failure. They are different checks.

Q7. How do you test this without the studio's production API?

Against a fixture that implements the same contract: keyset order, null cursor at the end, a mutation case for offset if I am explaining that bug, a signature fixture with awkward whitespace, and a CSV with a quoted comma and a prohibited header. The production client is a thin HTTP mapping over those functions. I do not need the studio available to know the pointer must move last. I do need one contract test against the real API in a non-production studio before I trust cursor opacity, because a fixture can be more honest than the server. That test is a scheduled check, not a reason to skip the fixture.

Q8. Why is the lesson dimension not allowed to be updated in place from a webhook?

The dimension's completeness is the scan. A webhook applies one lesson and says nothing about lessons I did not hear about, including deletions if the webhook is only "published" and not "withdrawn." In-place updates also race the hourly job. I give the webhook its own feed and its own consumer. The completion SQL names the scan pointer. Two writers into one table without a version will produce a dimension nobody can restate.

Q9. A partner offers only offset pagination. What do you do?

I ask for a keyset or an updated_since watermark. If the answer is no, I take a full extract at a time when the partner will freeze writes, or I accept a documented inconsistency window and reconcile against a row count or a checksum they provide. I do not pretend offset is keyset because I sort the pages afterward. Sorting does not restore a skipped id. For a small frozen file, I prefer they send the file.

Q10. Where does this lesson sit relative to the progress-event batch job?

The batch job publishes a file that is already closed. This lesson creates that closed artifact for the catalog and the outline: parts, manifest, current pointer. Progress events stay on the database extract or the closed JSONL path because of volume and because the player already writes PostgreSQL. I do not pull fifty million events through page_size=500. The checkpoint lesson and this lesson share publish-last. They do not share a source.

Q11. What do you do with an unknown field in a catalog item?

At the boundary I follow the same rule as the progress loader: unknown fields do not pass into the published dimension, and a field on the prohibited list fails the run. If the studio adds author_email, I want a failed hour and the previous dimension, not a new column in raw. A genuinely new instructional field is a contract change, reviewed, then added to the projection. Ignoring unknown fields feels tolerant and is how personal data arrives during a "minor" API revision.

Q12. How do you know the hourly dimension job is healthy after a month?

Every hour either moves the current pointer to a manifest that verifies or leaves the previous pointer and alerts. Row counts stay near the catalog size the studio reports, and a deliberate deletion between pages in a fixture does not skip keys under the keyset client. Webhook duplicates do not increase the latest-publish count. A CSV with a quoted comma lands one column. A prohibited header never appears in the published outline. The completion join's missing-lesson quarantine, from the next quality lesson, stays explainable. A job that exits zero because it wrote some pages is not healthy.

Hands-On Exercises

Exercise: Land a catalog scan and refuse a bad file

Problem statement. Implement a keyset pull, a manifest-last landing, a webhook verify, and a CSV header screen for a lesson catalog of your own size. Use fixtures. Do not call a third-party API you do not control.

Tasks.

1. Write the pagination contract in a paragraph: order, cursor, end condition, page size meaning.
2. Implement the client against an in-memory server. Include a guard if the cursor stalls.
3. Demonstrate an offset skip with a deletion between pages, and show that your keyset pull does not skip that way on a stable list.
4. Land pages and a manifest. Verify hashes. State when the current pointer moves.
5. Verify an HMAC over raw bytes, accept one duplicate event_id, and reject a bad signature.
6. Parse a CSV that contains a quoted comma, and refuse a prohibited header.
7. Define the retry rule for a 429 with and without Retry-After. You may assert delays without sleeping.
8. Name which consumer reads the scan and which may read the webhook. Draw one must-not arrow.

Expected deliverables. The program, the fixture assertions, the contract paragraph, and the pointer rule.

Evaluation rubric.

You pass when a reviewer can say which artifact the completion SQL is allowed to open, and what happened to the row your offset demo skipped.

Lesson Summary

API and file ingestion turn an unstable source into a closed, hashed artifact. Keyset cursors continue from a key so a deletion does not shift later pages off the scan. Offset positions do. A pull is complete when a manifest matches stored bytes and the current pointer moves. Until then the previous dimension stays served. Webhooks are signed over raw bytes, deduplicated on event_id, and forbidden from replacing the scan. CSV is parsed as CSV, and a prohibited header fails the file.

The lesson dimension TechLearningPro joins in completion-v3 comes from that pointer. The publish feed comes from the webhook. They are different products. Progress events stay on the batch landing from lesson 14.

Change Data Capture Fundamentals is the next source that is neither an API nor a file: the database log, for enrollment changes the cohort counter must see, including deletes. Kafka and Debezium remain Module 4.

Further Reading

• RFC 9110, HTTP Semantics — status codes and Retry-After. The catalog's cursor parameters are this course's contract, not a standard query language.
• Python hmac and hashlib — compare_digest and SHA-256 as used here. Primer: /learn/python.
• Python csv — DictReader and quoted fields. Pair with lesson 11 and RFC 4180 as a baseline, knowing exporters diverge.
• Python json — parsing webhook bodies after the signature check, not before.
• Gregor Hohpe and Bobby Woolf, Enterprise Integration Patterns — polling consumer and idempotent receiver, which are the webhook and the pull in messaging language.
• Continue this course: Change Data Capture Fundamentals, then Data Quality Engineering.