The DLQ as a Quality Signal
Consumers notice bad data when a dashboard number looks wrong, often hours after the cause. The DLQ sees the cause within seconds, because every record a producer change breaks lands there the moment it is processed. With the right metrics, the DLQ becomes the earliest warning the pipeline has about the systems that feed it.
What a DLQ row has to carry
A metric can slice only by a field the DLQ row stores. A DLQ holding the payload and a raw error message answers how many records failed. It can't say which producer release broke which field, and that is the question that finds a regression.
- ops.orders_dlq: dlq_id bigint PK, order_id bigint, event_seq bigint, payload varchar, producer_version varchar, failure_class varchar, error_signature varchar, error_message varchar, attempt_count integer, first_failed_at timestamp, status varchar, ticket varchar
- ops.orders_throughput: hour_start timestamp PK, producer_version varchar PK, records_in bigint, records_written bigint, records_dlq bigint, records_filtered bigint
The error_signature column is the raw message with its values stripped out. cannot cast 'abc' to decimal at tip_amount and cannot cast '12,50' to decimal at tip_amount both become cannot cast <v> to decimal at tip_amount. Grouping by raw message produces a group per bad value; grouping by signature produces a group per bug.
Rate by slice, against a baseline
The order pipeline's normal DLQ rate is 0.01%, about 720 rows an hour. At 10:00 the order service starts a canary of release v4.12, a trial rollout on 5% of its hosts. The release sends tip_amount as a string whenever a tip was edited after checkout, which happens on 15% of orders. The next hour looks like this:
| producer_version | records_in | dlq_rows | dlq_rate |
|---|---|---|---|
| v4.11 | 6,840,000 | 684 | 0.01% |
| v4.12 | 360,000 | 54,000 | 15.00% |
| All versions | 7,200,000 | 54,684 | 0.76% |
Consider which alerts that hour trips. An alert on 100,000 DLQ rows per hour stays silent. A pipeline-wide rate alert at 1% stays silent too, since 0.76% sits under it. Sliced by producer version, v4.12 fails at 15%, a figure no healthy release produces. That alert fires during the canary and the rollout stops at 5% of hosts. At full rollout, the same bug would send 1.08 million orders an hour to the DLQ.
- 1,500x
WITH dlq AS (
SELECT
producer_version,
count(*) AS dlq_rows
FROM ops.orders_dlq
WHERE first_failed_at >= date_trunc('hour', current_timestamp) - INTERVAL '1' HOUR
AND first_failed_at < date_trunc('hour', current_timestamp)
GROUP BY producer_version
),
traffic AS (
SELECT
producer_version,
sum(records_in) AS records_in
FROM ops.orders_throughput
WHERE hour_start = date_trunc('hour', current_timestamp) - INTERVAL '1' HOUR
GROUP BY producer_version
)
SELECT
t.producer_version,
t.records_in,
coalesce(d.dlq_rows, 0) AS dlq_rows,
round(coalesce(d.dlq_rows, 0) * 100.0 / nullif(t.records_in, 0), 2) AS dlq_pct
FROM traffic AS t
LEFT JOIN dlq AS d
ON d.producer_version = t.producer_version
ORDER BY dlq_pct DESCThe 4 DLQ metrics worth alerting on
- Rate by slice: dlq_rows / records_in per producer version and failure class. Catches a regression while a canary still touches a small share of traffic.
- New signature: an error_signature absent for the past 14 days. A new bug shows up as a new group even at low volume.
- Oldest untriaged row: the age of the oldest row with status = 'new'. A DLQ nobody reads loses data slowly, and this age says how slowly.
- Conservation: records_in = records_written + records_dlq + records_filtered. A gap means records vanished without reaching the DLQ.
The conservation check exists because a quiet DLQ proves nothing. If a code change wraps the parse step in a try block that logs the error and moves on, failures stop reaching the DLQ and its rate falls to zero, which looks like an improvement. Conservation exposes it: 7,200,000 records in against 7,180,000 written, 0 in the DLQ and 0 filtered leaves 20,000 orders unaccounted for.
Right after a pipeline deploy, the order pipeline's DLQ rate drops from 0.01% to 0.00%, while records_in holds steady at 7.2 million per hour. What should an engineer check first?
- Nothing: the deploy fixed the last parse bugs, so the DLQ thresholds can be tightened
- The producer release history, because producers must have cleaned up their payloads
- Conservation: whether records written plus DLQ plus filtered still adds up to records_in
- The DLQ table's storage quota, since a full table would stop accepting new rows
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.