Case Study 2: The Page That Slid Away

"We lost twelve records an hour for five months. The duplicates it also produced were absorbed by an idempotent write, which removed the only symptom anyone would have noticed."

Executive Summary

Kestrel's carrier tracking ingester used offset pagination, sorted newest-first, running hourly. Every run silently lost about twelve records and read about twelve twice.

Over five months that is roughly 43,000 lost tracking events out of 14.5 million — 0.3%. Small enough to be invisible to every check the team had, and large enough that delivery-time analysis was wrong in a way that mattered: the missing events were disproportionately the most recent ones, which skewed the "time to delivery" distribution.

The fix was one URL parameter.

This case study is §16.2's ⚠️ callout at full length. It is included because the mechanism is arithmetic rather than a bug, because idempotency — a practice this book recommends everywhere — hid half the symptom, and because the bias in which records were lost is the part that made an invisible loss consequential.

Skills applied: offset pagination (§16.2); idempotency hiding symptoms (Chapter 4 §4.5); reconciliation (Chapter 13 §13.5); sampling bias (Chapter 11, Case Study 1).

Background

The endpoint. GET /tracking?limit=100&offset=N, returning tracking events sorted newest first — which is the API's default and the natural thing for a human-facing view.

The ingester, hourly:

offset, all_events = 0, []
while True:
    page = api.get("/tracking", params={"limit": 100, "offset": offset,
                                        "since": last_run_iso})
    if not page["items"]:
        break
    all_events.extend(page["items"])
    offset += 100
upsert_to_bronze(all_events)     # idempotent on tracking_event_id

Forty pages per run, about 4,000 events, taking roughly 90 seconds.

The carrier receives about 8 events per second across all its customers, of which Kestrel sees roughly 0.13 per second — but the collection being paginated is Kestrel's shipments only, and Kestrel receives about 12 new tracking events during the 90-second extraction.

The Problem

t=0s    page 1: offset 0-99      → events ranked 1..100 (newest first)
        ...
t=45s   page 20: offset 1900-1999
        ← 6 new events have arrived. Every existing event's rank has
          increased by 6.
t=45s   page 21: offset 2000-2099
        → the events now at ranks 2000-2099 include six that were at
          1994-1999 when page 20 was read.  READ TWICE.
        → the six events that WERE at 2094-2099 have moved to 2100-2105.
          They will be read on page 22, and so will six more that shifted.
          The shift accumulates.

The arithmetic. With $k$ new arrivals during the extraction and pages of size $p$, records slide forward by $k$ positions. Records that were at the boundary of a page already read slide into the region already covered and are read twice; records at the far end slide past the last page and are never read at all.

At $k = 12$ per run, about 12 records are missed and about 12 are duplicated, every hour.

$$12 \times 24 \times 150 \text{ days} \approx 43{,}200 \text{ lost events}$$

⚠️ Failure Mode — Idempotency hid half the symptom

The duplicates were absorbed by the idempotent upsert on tracking_event_id. That is the correct design and this book has recommended it since Chapter 4.

And it removed the only symptom anyone would have noticed.

Without the idempotent write, duplicate tracking events would have appeared in bronze, inflating counts, and someone would have investigated within days. The investigation would have found the pagination bug, and the losses would have been fixed at the same time.

Idempotency made the pipeline correct in one direction and silent in the other. It cannot help with the missing records — nothing can recover a record you never fetched — but it removed the evidence that would have led you to them.

The general shape: a defensive mechanism can suppress the symptom of a different defect. Two other instances in this book:

  • Chapter 13's overlap window converts loss into duplicates, which idempotency then absorbs — deliberately, and the same suppression applies if the loss has a different cause.
  • Chapter 15's DLQ converts a crash into a quiet divert, deliberately, and removes the symptom of an upstream schema change.

In all three cases the answer is the same: if a mechanism suppresses a symptom, you owe a measurement in its place. Idempotency owes you a duplicate-rate metric. An overlap window owes you a reconciliation. A DLQ owes you a rate alert.

The Analysis

Detection at five months, and not by monitoring.

A data scientist building a delivery-time model noticed the distribution had a shorter right tail than the carrier's own published statistics. Kestrel's data said 94.1% of shipments were delivered within four days; the carrier's monthly summary said 91.8%.

A 2.3-point discrepancy in the direction of Kestrel's data looking better — which is the direction that gets less scrutiny.

Why the loss was biased. This is the part that made an invisible 0.3% consequential.

Records slide forward in a newest-first ordering, so the records that slide off the end are the oldest ones in the queried window — and in a since-filtered hourly extraction, those are the events from the start of the hour.

But there is a second, stronger bias. Tracking events for a shipment arrive as a sequence: picked_up, in_transit, out_for_delivery, delivered. A shipment generating events during the extraction window has its later events arriving as new records — pushing its earlier events toward the boundary.

So the lost events were disproportionately the early events of shipments that were actively moving — which are exactly the shipments whose delivery times you most want to measure.

The result: Kestrel's data over-represented shipments with complete, quiet event sequences and under-represented ones still in flight, biasing delivery-time estimates optimistic.

🔎 Read the Plan — Compare against the provider's own aggregate

Kestrel had no way to detect this internally. Every internal check — row counts, freshness, null rates — passed, because 0.3% is inside any reasonable tolerance and the missing rows left no trace.

The only detection available was external: the carrier published a monthly summary with total event counts and delivery-rate statistics, and it had been available the whole time.

sql -- The check that would have caught it in month one. SELECT c.month, c.carrier_reported_events, k.our_events, c.carrier_reported_events - k.our_events AS missing, ROUND(100.0 * (c.carrier_reported_events - k.our_events) / c.carrier_reported_events, 3) AS pct_missing FROM carrier_monthly_summary c JOIN (SELECT date_trunc('month', event_ts) AS month, COUNT(*) AS our_events FROM bronze.carrier_events GROUP BY 1) k USING (month); -- Expect pct_missing ≈ 0. It was 0.3, every month, in the same direction.

And note the sign. Chapter 4's Case Study 2 and Chapter 13's second both make the same point: timing noise varies in sign; loss does not. Five consecutive months of a same-signed 0.3% gap is conclusive even though no single month's figure is alarming.

Whenever a provider publishes an aggregate — a settlement total, an event count, a monthly summary — reconcile against it. It is the only check that can see what your own systems cannot, because it comes from outside them.

The Decision

Three changes, and the first is one line.

1. Paginate over something that does not move.

# WAS: newest-first offset pagination over a growing collection.
#      Records slide forward as new ones arrive; boundary records are read
#      twice and end-of-window records are never read at all.
#
# NOW: ascending order on an immutable field, with a closed range.
#      New records arrive at the END, past the region already read.
#      Nothing shifts.
params = {
    "limit": 100,
    "sort": "created_at",
    "order": "asc",
    "created_after": window_lo.isoformat(),
    "created_before": window_hi.isoformat(),    # CLOSED range: the result set
}                                               # cannot change while we read it

Two properties, and both are needed. Ascending order on an immutable field means new records append rather than insert. A closed upper bound means the result set is fixed for the duration of the extraction — nothing arriving during the 90 seconds is inside the range at all.

2. The monthly reconciliation against the carrier's published summary, with a sign-persistence check as well as a magnitude threshold (Chapter 4's Case Study 2's control, applied here).

3. A duplicate-rate metric. The idempotent write now counts how many upserts were no-ops:

result = upsert_to_bronze(events)
metrics.gauge("upsert_noop_rate",
              result.unchanged / max(result.total, 1),
              tags={"table": "carrier_events"})

This is the measurement that idempotency owed. A no-op rate of ~0.3% would have been visible from day one and is a direct fingerprint of pagination overlap. It costs one counter.

📐 Design Decision — A closed range, or just ascending order?

Ascending order alone fixes most of the problem: new records append past the read region, so nothing shifts underneath you.

The team added a closed upper bound as well, and the reasoning is worth following because it is not obviously necessary.

With ascending order and an open upper bound, the extraction ends when a page returns fewer than limit items. But records arriving during the extraction are inside the range, so the endpoint keeps having more to give — the extraction runs longer than expected, and its boundary with the next run's window is whatever moment it happened to stop.

That is not a loss; it is an ambiguity. Two consecutive runs might overlap or might not, and the watermark is derived from where the previous run happened to finish — which is Chapter 13 §13.4's property 1 violated in a new place: the range's bound is coming from the data read rather than from an authority.

A closed upper bound makes the run's scope a decision rather than an outcome. The run extracts exactly [lo, hi); anything arriving after hi belongs to the next run, unambiguously.

What it costs: a fixed lag equal to the window's settle time — Kestrel's runs extract up to five minutes ago, so data is at most an hour and five minutes old rather than an hour. Accepted.

What Happened

The 43,000 lost events were partially recovered. The carrier retains 90 days of tracking history, so the most recent three months were re-fetched; the older two months are permanently gone.

That recovery boundary is the same one as Chapter 4's Case Study 2 and Chapter 13's second: what you can recover is a property of the source's retention, not of your platform.

The delivery-time model was retrained on the corrected data and the four-day delivery rate moved from 94.1% to 91.9% — within 0.1 points of the carrier's published 91.8%.

Since then:

  • The monthly reconciliation runs on all three carriers. It found a second, unrelated discrepancy within two months: one carrier's summary counted an event type Kestrel was filtering out at ingest, which was correct behavior and had never been documented. The reconciliation forced the documentation.
  • The no-op rate metric sits at 0.02%, consistent with the deliberate five-minute overlap and nothing else.
  • The pagination pattern was audited across every API ingester. Two others used offset pagination on growing collections. Both were fixed the same week, and neither had been suspected.

That last point is Chapter 1's lesson recurring: when you find one instance, audit for the shape.

Lessons

  1. Offset pagination on a growing collection is systematically lossy. Records slide forward; boundary records are read twice and end-of-window records are never read.

  2. Paginate over something that does not move — ascending order on an immutable field, and a closed upper bound so the run's scope is a decision rather than an outcome.

  3. Idempotency hid half the symptom. It absorbed the duplicates, which were the only visible evidence, while doing nothing about the losses.

  4. If a mechanism suppresses a symptom, you owe a measurement in its place. Idempotency owes a duplicate-rate metric; an overlap window owes a reconciliation; a DLQ owes a rate alert.

  5. The bias in which records were lost mattered more than the rate. 0.3% lost uniformly is noise; 0.3% lost from actively-moving shipments biased every delivery-time estimate optimistic.

  6. Reconcile against the provider's own aggregate. It is the only check that can see what your own systems cannot, because it comes from outside them.

  7. Timing noise varies in sign; loss does not. Five consecutive same-signed months is conclusive even when no single month is alarming.

  8. What you can recover is a property of the source's retention. Three months came back; two are gone.

  9. When you find one instance, audit for the shape. Two other ingesters had the same bug and neither was suspected.

Questions for Discussion

  1. The discrepancy made Kestrel's data look better than the carrier's, which is the direction that gets less scrutiny. How would you counter that bias in a review process?

  2. Lesson 4 says a symptom-suppressing mechanism owes a measurement. Go through this book's idempotent writes, overlap windows, retries, and DLQs — which ones currently have their measurement and which do not?

  3. The closed upper bound costs five minutes of freshness to remove an ambiguity. When would you refuse that trade?

  4. The reconciliation found a second discrepancy that turned out to be correct behavior, and forced documentation. Is "the check found something that was fine" a success or a false positive?

  5. Two months of events are permanently gone. What would it have cost to have a design that survived this, and would you build it?

  6. The audit found two more ingesters with the same bug. Design the lint or review check that prevents a third — something that can be applied mechanically to new code.

  7. The data scientist found this by comparing against an external published figure. How many external figures could your platform be reconciling against and is not? Make the list.