Reprocessing From the DLQ

Concepts covered: Dead Letter Queues

A DLQ that only collects records loses them more slowly. Recovery needs a path back: find the broken records, write down what is known about them, fix the cause, and send them through the pipeline again without starving live traffic or corrupting the curated table.

A lifecycle for every DLQ row

statusSet byMeaning
newThe pipelineFailed, and nobody has looked yet
triagedOn-call engineerThe signature has an owner and a ticket
fix_deployedOwnerThe fix is live; rows are safe to replay
replayedReplay workerWritten to curated.orders
refailedReplay workerFailed again; attempt_count raised
discardedOwner, with a reasonNever to be replayed, such as test traffic

Annotation works a signature at a time. A single update marks all 54,000 tip_amount rows from the v4.12 canary as triaged with a ticket number. The status column lets replay select only rows whose cause is fixed, and it ends loops: a row that reaches refailed a third time stays put until a human looks.

Replay through a separate, throttled path

The quickest-looking replay copies DLQ rows back onto the pipeline's input log. Those old records then queue behind live ones and draw on the same tax capacity live orders need. A dedicated replay worker runs the same transform code under its own rate limit, so replay cannot take capacity from live orders as long as that limit fits the dependency's current headroom.

Set that limit from the dependency's headroom. Live traffic uses 2,000 of the tax service's 3,000 requests per second. Holding 400 back for bursts leaves replay 600 per second. At that rate the 54,000 canary rows replay in 90 seconds, and a backlog of 2.1 million rows takes roughly 58 minutes.

Live and replay paths run the same transform code and share the tax service. Replay reads fixed rows from the DLQ under its own limit of 600 requests per second, upserts them into curated.orders, and writes rows that fail again back to the DLQ.
  • Order log (source, Durable log, 72 h retention)
  • Live transform (transform, Tax enrichment); parallelism: 32 consumers; on error: classify, then retry, break or DLQ
  • Tax service (custom, 3,000 req/s capacity)
  • orders_dlq (storage, DLQ table); alerting: DLQ rate by producer_version
  • Replay worker (transform, Same transform code); parallelism: 4 workers; idempotency: event_seq-guarded upsert
  • curated.orders (silver, Keyed on order_id); idempotency: upsert on order_id
  • Order log -> Live transform
  • Live transform -> Tax service
  • Live transform -> curated.orders
  • Live transform -> orders_dlq
  • orders_dlq -> Replay worker
  • Replay worker -> Tax service
  • Replay worker -> curated.orders
  • Replay worker -> orders_dlq

Idempotent writes and the stale replay

Replay has to be safe to run twice, because the replay worker can crash halfway like any process. An upsert keyed on order_id covers that: a second run rewrites the same row. Replay also meets a hazard live traffic rarely does, a DLQ row older than what the table already holds. Order 88123 failed at event_seq 4, when its tip was edited. Its seq 5, a cancellation, succeeded an hour later. A plain upsert of seq 4 would un-cancel the order.

The guard is a condition on the write: apply a replayed row only when its event_seq is greater than the stored one. Running the same condition as a read-only dry run shows what replay will do before it touches the table.

SELECT
  d.order_id,
  d.event_seq AS replay_seq,
  o.event_seq AS current_seq,
  CASE
    WHEN o.order_id IS NULL THEN 'insert'
    WHEN d.event_seq > o.event_seq THEN 'update'
    ELSE 'skip_stale'
  END AS replay_action
FROM ops.orders_dlq AS d
LEFT JOIN curated.orders AS o
  ON o.order_id = d.order_id
WHERE d.status = 'fix_deployed'
  AND d.error_signature = 'cannot cast <v> to decimal at tip_amount'
ORDER BY d.order_id
order_idreplay_seqcurrent_seqreplay_action
881211NULLinsert
8812242update
8812345skip_stale

A dry run with 2% of rows in skip_stale is normal: those orders moved on while their failed event waited. A dry run with 60% in skip_stale means the selection is wrong, often rows a crashed replay wrote without updating their status. The guard turns that second pass into skips instead of overwrites.

A replay of 2.1 million DLQ rows is planned for peak hours. The tax service serves 3,000 requests per second, live traffic uses 2,500, and 400 stay in reserve for bursts. About how long will replay take?

About this lesson

This section is part of the Failure Handling: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.