Designing resilient ETL pipelines: retries, idempotency and dead-letter queues

ETL pipelines fail in production — that's a fact. Network timeouts, schema drift, upstream API rate limits and dirty data all conspire to break your perfectly planned job. The question isn't how to avoid failures, but how to handle them gracefully.

Retry with exponential backoff

Every network call in your pipeline should be wrapped in a retry mechanism with exponential backoff and jitter. This prevents thundering-herd problems when an external service recovers.

import time
import random

def retry(fn, max_attempts=5, base_delay=1.0):
    for attempt in range(max_attempts):
        try:
            return fn()
        except Exception as e:
            if attempt == max_attempts - 1:
                raise
            delay = base_delay * (2 ** attempt) + random.uniform(0, 1)
            time.sleep(delay)

Idempotency is non-negotiable

If your pipeline can be re-run safely after a failure, you've eliminated an entire class of data-quality bugs. Design every transformation so that running it twice produces the same result as running it once.

Key strategies:

Use upserts (MERGE) instead of inserts for dimensional data.
Include a batch_id and processed_at timestamp on every row.
Partition by date so re-running a day only overwrites that partition.

Dead-letter queues for poison messages

Not every record can be processed. Instead of failing the entire batch, route unprocessable records to a dead-letter table for later investigation.