Architecture · data platform

How a row gets in

The ingest path end to end, the two places it loses data, and the one queue that has no dead letter. Written after the 14 August incident.

Owner data platform Current as of 26 August 2026 Throughput 4.2M rows/day

The path

Left to right. The moving dot is one row.

one row, in flight we own it managed, we configure only failure path

Stage by stage

Collector

Accepts a webhook or reads the nightly drop, checks the payload against the schema, stamps it with a receipt id and a wall-clock time, and enqueues. It does not transform anything. That was deliberate: a schema change should not be able to take ingestion down.

p99 34ms retry 3x, exponential on failure dead letter

Queue

At-least-once delivery, which means the transform stage has to be idempotent — and is, by way of the receipt id. Visibility timeout is sixty seconds; the slowest transform we have measured is nine, so there is room.

depth usually under 400 timeout 60s dead letter none

Transform and load

Twelve workers, each pulling a batch of up to five hundred. Normalises currency and timezone, resolves the account id, writes to the warehouse in one transaction per batch. A batch lands whole or not at all.

batch up to 500 workers 12 write one txn per batch

The queue has no dead letter, and that is what bit us on 14 August

A malformed account id made the transform throw. At-least-once redelivered it, forever, and the retries crowded out real work for about forty minutes. The collector has a dead letter store; the queue does not. Adding one is roughly an afternoon, and it is the highest-value change on this page.

Where it loses data

Failure	What happens	Severity
poison message	Redelivered indefinitely. Blocks a worker and, at enough volume, the queue. No alert fires, because depth recovers between retries.	P1
missed sftp drop	The nightly export is pulled once at 02:00 with no retry. If the vendor is late we have no data for that day, and nothing says so until someone reads a dashboard.	P1
schema drift	Caught at the collector and dead-lettered, which is correct. But nothing reads that store on a schedule, so it is only correct in the sense that the data still exists.	P2
duplicate receipt	Handled. The transform upserts on receipt id, so redelivery is a no-op.	Fine

The idempotency key, in full

This is the whole reason at-least-once is survivable. It is four lines.

-- one row per receipt; a replay overwrites rather than duplicates
INSERT INTO events (receipt_id, account_id, occurred_at, payload)
VALUES ($1, $2, $3, $4)
ON CONFLICT (receipt_id) DO UPDATE SET
  payload = EXCLUDED.payload, loaded_at = now();

What I would change, in order

Give the queue a dead letter after five attempts

Closes the P1 that already cost us forty minutes. An afternoon, no migration.

Alert when the nightly drop does not arrive

A missing file currently looks exactly like a quiet day. One scheduled check.

Put a weekly read on the dead letter store

Dead-lettering only counts as a save if somebody looks, and nobody is looking.