Dead-Letter Queues and Error Recovery in Clinical ETL

Every clinical ingestion pipeline eventually meets a record it cannot process, and the way it handles that record is a data-integrity decision, not an operational footnote. This page is part of Automated EDC Ingestion & Sync Pipelines, and it focuses on a single discipline: how to catch a failed Electronic Data Capture (EDC) record, decide why it failed, and hold it in a durable, auditable place until it can be corrected — never dropping it on the floor. For clinical data managers, biotech and pharma Python ETL engineers, and regulatory reviewers, a silently discarded record is one of the most serious defects an inspector can find, because it breaks the ALCOA+ Complete principle at the source: the analysis dataset no longer reflects everything the EDC captured, and nobody can prove what went missing. The patterns below split failures into two escrow paths — a quarantine for records that failed schema or validation and need clinical review, and a dead-letter queue (DLQ) for records whose infrastructure retries were exhausted — so that every un-ingested record remains attributable, reconstructable, and recoverable under 21 CFR Part 11 and ICH E6(R3).

A Record’s Error Lifecycle at a Glance #

A record enters, is validated and fetched, and takes exactly one of three routes: a clean load, a bounded transient-retry loop that dead-letters on exhaustion, or a schema-conflict path that quarantines for clinical review before it is corrected and re-queued. The teal transitions are the ones that write an audit entry.

Concepts and Prerequisites #

The single most important idea on this page is that “the pipeline failed” is never a sufficient description of what happened — you must record why, because the reason dictates the recovery path and the regulatory obligation. Two failure families call for two entirely different escrow stores.

A quarantine holds records that reached your service intact but failed a content rule: a required SDTM variable is absent, a coded term is not in the controlled terminology, a cross-form check contradicts an earlier visit, or the ODM payload does not conform to the expected schema. These are not infrastructure problems and retrying them a hundred times will never fix them; they need a human — usually a clinical data manager (CDM) — to decide whether the source is wrong, the mapping is wrong, or the schema needs to change. The quarantine is therefore adjacent to discrepancy management: the same records frequently drive Automated Clinical Query Generation.

A dead-letter queue holds records whose processing failed for infrastructure reasons and whose bounded retries were exhausted: an EDC gateway stayed down past the retry cap, a downstream warehouse rejected a write, a message could not be deserialized at all. The record itself may be perfectly valid; the environment simply refused to let it through. Recovery here is a replay after the infrastructure recovers, described in detail in the companion guide on Replaying Dead-Letter Queue Records in Clinical ETL. Collapsing these two stores into one bucket is the most common design mistake in the field, because it either sends validation problems to an on-call engineer who cannot fix them or buries genuine infrastructure outages in a review queue no engineer watches.

Both stores build on the retry primitives documented in Building Retry Logic for EDC API Timeouts: a record only reaches the DLQ after the transient-retry policy has given up, so the classification below assumes that layer already sits upstream. Records that survive both escrow paths flow on into the transformation stage covered in Pandas DataFrames for Clinical Data Cleaning.

The reference implementation assumes a pinned, version-controlled dependency set so the error-handling behavior is reproducible across IQ/OQ/PQ environments:

One environment assumption is non-negotiable: the escrow store is durable and independent of the worker. If the DLQ lives only in a worker’s memory or on its local disk, a pod restart destroys the very records you were obligated to preserve. Use a transactional database or a broker with a native dead-letter topic so that writing the failure and acknowledging the source message happen atomically.

Classifying Transient Versus Permanent Failures #

Recovery begins with a deterministic classifier that maps every failure to exactly one error_class. The classification is the audit-traceable reason a record was routed the way it was, so it must be a pure function of the failure — never a guess, and never “route everything to the DLQ and sort it out later.”

The classification maps cleanly onto the HTTP and processing taxonomy the ingestion tier already speaks:

# 21 CFR Part 11 relevance: the error_class is the deterministic, logged reason a
# record was routed — an inspector re-derives the routing decision from this alone.
from enum import Enum


class ErrorClass(str, Enum):
    TRANSIENT_INFRA = "transient_infra"    # retry, then DLQ if exhausted
    POISON = "poison"                      # cannot even be parsed -> DLQ now
    SCHEMA_INVALID = "schema_invalid"      # ODM/SDTM shape wrong -> quarantine
    VALIDATION_INVALID = "validation_invalid"  # content rule failed -> quarantine
    AUTH = "auth"                          # credential failure -> hard stop


class Route(str, Enum):
    RETRY = "retry"
    DEAD_LETTER = "dead_letter"
    QUARANTINE = "quarantine"
    HARD_STOP = "hard_stop"


# Only these classes are ever eligible for an automatic transient retry.
_TRANSIENT = frozenset({ErrorClass.TRANSIENT_INFRA})
_QUARANTINE = frozenset({ErrorClass.SCHEMA_INVALID, ErrorClass.VALIDATION_INVALID})


def route_for(error_class: ErrorClass, attempts_remaining: int) -> Route:
    """Deterministic routing: reason + retry budget in, single route out."""
    if error_class is ErrorClass.AUTH:
        return Route.HARD_STOP                 # never dead-letter a bad credential
    if error_class in _QUARANTINE:
        return Route.QUARANTINE                # a human, not a retry, fixes these
    if error_class is ErrorClass.POISON:
        return Route.DEAD_LETTER               # unparseable: no point retrying
    if error_class in _TRANSIENT and attempts_remaining > 0:
        return Route.RETRY
    return Route.DEAD_LETTER                    # transient budget exhausted

Two properties make this defensible. A SCHEMA_INVALID record is never retried, because a hundred re-reads of a payload with a missing USUBJID produce a hundred identical failures and a hundred wasted quota units. And an AUTH failure is never dead-lettered, because a revoked credential invalidates the whole run — dead-lettering it would let thousands of otherwise-good records pour into escrow while the real fix is a five-minute secret rotation.

The distinction the classifier encodes is ultimately a question of who can fix the failure and when. A transient infrastructure failure resolves itself: the gateway comes back, the deadlock clears, and an automated replay drives the record through unchanged. Nobody needs to look at the data. A schema or validation failure, by contrast, is a statement about the data itself — the payload as it stands cannot become correct without a human decision, whether that is a corrected source value, an amended mapping, or a controlled schema change. Routing on that axis means the on-call engineer’s DLQ contains only work an engineer can act on, and the CDM’s quarantine contains only work a data manager can act on. When the two are mixed, both queues become untriageable: engineers page on validation problems they cannot resolve, and genuine outages disappear into a review backlog. The error_class is what keeps the two audiences, and their two very different service-level expectations, cleanly separated.

Routing to Quarantine and the Dead-Letter Queue #

Once a record is classified, the routing step writes it to the correct durable store with enough context that recovery never has to reconstruct anything from logs. The DLQ record schema is the heart of this — get the fields wrong and a record that failed at 02:00 can never be safely replayed at 09:00.

The essential fields, and why each one is load-bearing:

# ALCOA+ requirement: Complete + Original — no failed record is dropped, and the
# exact source bytes are preserved with an integrity hash before any transform.
import hashlib
from datetime import datetime, timezone

import sqlalchemy as sa
import structlog

log = structlog.get_logger("clinical_etl.error_recovery")
metadata = sa.MetaData()


def _utcnow() -> datetime:
    return datetime.now(timezone.utc)


def dead_letter(engine: sa.Engine, table: sa.Table, *, raw: bytes,
                error_class: ErrorClass, detail: str, attempt: int,
                source_offset: str, run_id: str) -> None:
    """Write (or coalesce) a failed record into the durable DLQ, idempotently."""
    digest = hashlib.sha256(raw).hexdigest()      # bind stored bytes to a hash
    now = _utcnow()
    stmt = sa.dialects.postgresql.insert(table).values(
        payload_sha256=digest,
        raw_payload=raw,                          # verbatim ORIGINAL bytes
        error_class=error_class.value,
        error_detail=detail[:2000],               # bounded, PII-safe
        attempt_count=attempt,
        first_seen=now,
        last_seen=now,
        source_offset=source_offset,
        run_id=run_id,
        status="open",
    )
    # Same logical record failing twice must NOT create two rows: coalesce on the
    # payload hash, bump the attempt count, and move last_seen forward.
    stmt = stmt.on_conflict_do_update(
        index_elements=["payload_sha256"],
        set_={
            "attempt_count": table.c.attempt_count + 1,
            "last_seen": now,
            "error_detail": detail[:2000],
        },
    )
    with engine.begin() as conn:                  # atomic with source ack upstream
        conn.execute(stmt)
    log.warning("dead_lettered", payload_sha256=digest, error_class=error_class.value,
                attempt=attempt, run_id=run_id, source_offset=source_offset)

Poison messages — payloads that cannot be parsed at all — deserve special handling. They can never succeed on replay, so they must be capped to prevent a single corrupt record from being redelivered forever. A broker that re-queues an unacknowledged message will otherwise spin the same poison record through your worker endlessly, a failure mode that looks like a healthy-but-busy pipeline while zero real progress is made.

# ALCOA+ requirement: Available — a poison message is captured once and capped,
# so one corrupt payload cannot starve the pipeline via infinite redelivery.
MAX_POISON_REDELIVERIES = 3


def handle_poison(engine, table, msg, run_id: str) -> Route:
    """Cap redelivery of unparseable payloads; park them for engineer inspection."""
    if msg.delivery_count >= MAX_POISON_REDELIVERIES:
        # Preserve the raw bytes even though we cannot interpret them.
        dead_letter(engine, table, raw=msg.body, error_class=ErrorClass.POISON,
                    detail=f"undeserializable after {msg.delivery_count} deliveries",
                    attempt=msg.delivery_count, source_offset=str(msg.offset),
                    run_id=run_id)
        msg.ack()                                 # stop redelivery; it is now durable
        log.error("poison_capped", offset=msg.offset, deliveries=msg.delivery_count)
        return Route.DEAD_LETTER
    msg.nack(requeue=True)                         # give the broker a bounded retry
    return Route.RETRY

The ack() on a capped poison message is the counter-intuitive but correct move: once the raw bytes are safely persisted in the DLQ, acknowledging the source stops the redelivery loop without losing the record. The record is not gone — it has moved from a volatile broker to a durable, auditable store where an engineer can inspect it on their own schedule.

Configuration and Parameterization #

Routing thresholds — retry caps, poison caps, alert severities, replay ceilings — are policy, not code. They differ per vendor and per environment and every change to them is a change-controlled event, so externalize them into a validated configuration file and put the escrow tables under the same schema-migration discipline as the rest of the warehouse.

-- DDL is a controlled artifact: this table's migration hash is recorded in the
-- validation package. The DLQ and quarantine are separate tables by design.
CREATE TABLE etl_dead_letter (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload_sha256  CHAR(64)     NOT NULL UNIQUE,   -- integrity + dedup anchor
    raw_payload     BYTEA        NOT NULL,          -- verbatim ORIGINAL bytes
    error_class     TEXT         NOT NULL,
    error_detail    TEXT,
    attempt_count   INTEGER      NOT NULL DEFAULT 1,
    replay_count    INTEGER      NOT NULL DEFAULT 0, -- capped to stop poison loops
    first_seen      TIMESTAMPTZ  NOT NULL,
    last_seen       TIMESTAMPTZ  NOT NULL,
    source_offset   TEXT         NOT NULL,
    run_id          TEXT         NOT NULL,
    status          TEXT         NOT NULL DEFAULT 'open'
                     CHECK (status IN ('open','replaying','resolved'))
);
CREATE INDEX ix_dlq_open ON etl_dead_letter (error_class) WHERE status = 'open';

# config/error_routing.yaml — version-controlled; changes require a tracked CR.
# GxP: this file's git SHA is stamped into every run's audit header.
routing:
  transient_max_attempts: 5          # DLQ only after the retry policy gives up
  poison_max_redeliveries: 3         # cap unparseable payloads
  replay_cap: 3                      # max automatic replays per DLQ record
  quarantine_requires_signoff: true  # a CDM must approve before re-queue
alerting:
  dlq_open_warn: 25                  # page on-call when open DLQ rows exceed this
  dlq_open_page: 200                 # backpressure threshold: pause ingestion
  quarantine_age_hours_warn: 48      # aging validation failures need attention

Two rules keep this auditable. The DDL migration and the routing YAML are both controlled items whose git SHAs are stamped into each run’s audit header, so a reviewer can prove which policy governed a given failure. And the replay_cap in the config is enforced by the replay_count column in the DDL — a design where the guardrail lives in the durable store, not only in the worker that might be restarted mid-replay.

Testing and Validation #

An error-handling path that has only ever been exercised by accident is not validated — the failure routes are precisely the part regulators care about, so you must inject faults deterministically and assert the routing. Use fault injection to prove each class lands in the correct store and that no record is ever lost.

# OQ artifact: proves permanent faults quarantine, transient faults retry-then-DLQ,
# and that a failed record is always durable somewhere — never silently dropped.
import pytest


def test_permanent_schema_fault_quarantines(engine, quarantine_tbl):
    # Inject a payload missing the required USUBJID (a SCHEMA_INVALID fault).
    bad = b'{"STUDYID":"CDP-101","VISITNUM":1}'   # no USUBJID
    route = process_record(engine, bad, run_id="run-oq-01")
    assert route is Route.QUARANTINE               # human review, never retried
    rows = fetch_rows(engine, quarantine_tbl)
    assert len(rows) == 1 and rows[0]["error_class"] == "schema_invalid"


def test_transient_fault_retries_then_dead_letters(engine, dlq_tbl, monkeypatch):
    # Inject a persistent 503 so the retry budget is fully consumed.
    monkeypatch.setattr("mypipeline.io.write_warehouse", _always_503)
    route = process_record(engine, VALID_PAYLOAD, run_id="run-oq-02")
    assert route is Route.DEAD_LETTER              # only after retries exhaust
    row = fetch_rows(engine, dlq_tbl)[0]
    assert row["error_class"] == "transient_infra"
    assert row["attempt_count"] == 5               # matches transient_max_attempts
    assert row["payload_sha256"] == sha256_of(VALID_PAYLOAD)  # ORIGINAL preserved


def test_poison_message_is_capped_not_looped(engine, dlq_tbl):
    # A payload that cannot be parsed must not redeliver forever.
    msg = FakeMessage(body=b"\x00\x01not-json", delivery_count=3)
    assert handle_poison(engine, dlq_tbl, msg, run_id="run-oq-03") is Route.DEAD_LETTER
    assert msg.acked is True                        # redelivery loop is broken

The mock fixtures should cover, at minimum: a clean load, a schema-invalid record, a validation-invalid record, a transient fault that recovers within budget, a transient fault that exhausts budget and dead-letters, a poison message that caps, and an auth failure that hard-stops. Each test’s structured log output is retained as objective OQ evidence, and the suite runs as a compliance gate in CI so a regression in routing blocks deployment.

Production Gotchas and Failure Modes #

• Infinite replay loop. A record is dead-lettered, an automated job replays it, it fails identically, and it is dead-lettered again — forever, consuming quota and burying real failures. Remediation: enforce the replay_count cap in the durable store (not only in the worker), and after the cap park the record in a terminal needs_engineering state that only a human can release.
• DLQ growth and backpressure. During a prolonged vendor outage the DLQ fills faster than triage can drain it, and the escrow table itself becomes an operational risk. Remediation: alert on open-row count with two thresholds — a warn that pages on-call and a page that pauses ingestion entirely — so the pipeline applies backpressure rather than accumulating an unbounded, un-triageable backlog.
• Losing the original bytes. Storing a parsed, normalized, or JSON-re-serialized version of the payload instead of the raw bytes destroys the Original record; a replay then re-processes a lossy copy and the payload_sha256 no longer matches what the EDC actually sent. Remediation: persist raw_payload verbatim as BYTEA and hash it before any transform touches it.
• Replaying against a changed schema. A record dead-lettered last week is replayed today, but the target SDTM schema or controlled terminology has since changed; a blind replay silently writes a record that no longer conforms. Remediation: re-validate every DLQ record against the current schema at replay time, never against the schema captured when it failed — the mechanics are covered in Replaying Dead-Letter Queue Records in Clinical ETL.
• Quarantine that nobody watches. A validation failure routed to quarantine with no aging alert sits invisibly until a CDM stumbles on it during database lock, by which point the query window may have closed. Remediation: alert on quarantine age and feed the records into the standing query-generation workflow so they surface as trackable discrepancies.

Compliance Checklist #

• No failed record is ever silently dropped; every failure lands in a durable quarantine or DLQ (ALCOA+ Complete).
• The exact original bytes are stored verbatim and hashed (SHA-256) before any transform (Original).
• Every escrow record carries run_id and the extraction principal so the failure is Attributable.
• first_seen and last_seen timestamps are recorded on write, capturing when the failure occurred (Contemporaneous).
• Schema/validation failures route to quarantine and are never automatically retried.
• Transient infrastructure failures reach the DLQ only after the bounded retry policy is exhausted.
• Poison messages are capped and acknowledged so no corrupt record redelivers indefinitely.
• Automatic replays are bounded by a replay_count cap enforced in the durable store.
• The DDL migration and routing configuration are version-controlled and their git SHAs recorded in the run’s audit header (change-management trigger).
• DLQ open-row count is alerted with a backpressure threshold that pauses ingestion before the queue becomes un-triageable (Available).
• Quarantined records are age-alerted and surfaced as trackable discrepancies for CDM review.
• The routing classifier is covered by fault-injection OQ tests whose logs are retained as validation evidence.

Related #

• Building Retry Logic for EDC API Timeouts — the bounded retry layer that must exhaust before a record reaches the DLQ.
• Replaying Dead-Letter Queue Records in Clinical ETL — how to safely re-drive DLQ records after a fix without duplicates.
• Pandas DataFrames for Clinical Data Cleaning — the transformation stage that consumes records once they clear escrow.
• Automated Clinical Query Generation — where quarantined validation failures become trackable discrepancies.
• Parent: Automated EDC Ingestion & Sync Pipelines