Handling Retries and Idempotency in ETL Jobs: A Practical Guide

In this article, let us look at how to handle retries and idempotency in ETL jobs. If you have built pipelines that process data at any reasonable scale, you have probably seen jobs fail for reasons that have nothing to do with your code — a network blip, an API timing out, a database that decided to restart mid-query. These are not bugs. They are facts of life in distributed systems. The question is not whether your pipeline will fail. The question is what it does when it fails.

We will cover the most common failure patterns, walk through retry strategies you can implement today, and then talk about idempotency — because a retry without idempotency is just a fancy way to duplicate your data.

Why ETL Jobs Fail in Production

Before we jump into retries, let us understand the kinds of failures we are dealing with. I have seen pipelines fail for all of these at some point:

• Transient network errors. The source database closes the connection after a long-running query. An API gateway returns a 503 because it is overloaded. These resolve on their own within seconds or minutes.
• Rate limiting. You are pulling data from an API and hit the quota. Waiting and trying again is the only fix.
• Resource contention. A Spark job runs out of memory because another job on the same cluster grabbed more than expected. Rerunning with the same resources often works.
• Stale credentials. An OAuth token expires mid-extract. You refresh it and retry.
• Partial writes. The extract succeeded but the load died halfway through. Your target table now has half the rows.

The key insight: most production failures are transient. A simple retry fixes them. But a retry without care creates duplicates, and duplicates in a data warehouse are a silent disaster — your dashboards are wrong and nobody knows why.

Retry Strategies That Actually Work

Let us walk through the options from simplest to most robust.

1. Fire-and-Retry (Naive)

The simplest approach: catch the exception and call the function again.

import time

def extract_with_retry(query, max_attempts=3):
    for attempt in range(max_attempts):
        try:
            return run_query(query)
        except TransientError as e:
            if attempt == max_attempts - 1:
                raise
            time.sleep(2)

This works for the quick hiccups. But it has problems. If the downstream system is genuinely overloaded, hammering it again after a fixed 2-second wait makes things worse. You want your retries to be a good citizen.

2. Exponential Backoff with Jitter

The standard approach in most production pipelines. After each failure, you wait longer — and you add some randomness so multiple retrying jobs do not synchronise and create thundering-herd problems.

import random
import time

def retry_with_backoff(func, max_attempts=5, base_delay=2):
    for attempt in range(max_attempts):
        try:
            return func()
        except TransientError:
            if attempt == max_attempts - 1:
                raise
            delay = base_delay * (2 ** attempt)
            jitter = random.uniform(0, delay * 0.5)
            time.sleep(delay + jitter)

With base_delay of 2 seconds, your waits look roughly like: 2s, 4s, 8s, 16s, 32s. The jitter spreads them out so 50 jobs do not all retry at exactly the same moment.

Most cloud SDKs (boto3, google-cloud, etc.) have built-in retry with backoff. In GCP, you can configure it when creating a client:

from google.cloud import bigquery
from google.api_core import retry

client = bigquery.Client()
job = client.query(query, retry=retry.Retry(deadline=120))

For AWS Glue jobs, if you are using boto3, you can set the retry mode:

import boto3
from botocore.config import Config

config = Config(retries={'max_attempts': 5, 'mode': 'adaptive'})
glue = boto3.client('glue', config=config)

3. Circuit Breaker

Exponential backoff helps. But if a downstream service is completely down, retrying for 2 minutes across 5 attempts is still wasting time and resources. A circuit breaker stops retrying entirely after a threshold of consecutive failures and checks back after a cooldown period.

This is more of an architectural pattern than a library call. In practice, you might implement it with a state store (Redis, DynamoDB) that tracks failure counts:

def call_with_circuit_breaker(service_name, func, failure_threshold=5, cooldown=300):
    state = get_circuit_state(service_name)  # from Redis/DDB
    if state == 'OPEN':
        last_failure = get_last_failure_time(service_name)
        if time.time() - last_failure < cooldown:
            raise CircuitBreakerOpenError(service_name)
        else:
            set_circuit_state(service_name, 'HALF_OPEN')

    try:
        result = func()
        reset_circuit(service_name)
        return result
    except TransientError:
        record_failure(service_name)
        raise

In practice, I reach for a circuit breaker only when the downstream is known to have extended outages. For most ETL pipelines, exponential backoff with a sensible deadline covers the majority of cases.

Strategy Comparison

Strategy	Best For	Complexity	Risk of Duplicates
Fixed-interval retry	Quick network blips	Low	High if not idempotent
Exponential backoff + jitter	Most ETL pipelines	Medium	Same as above — retry alone does not guarantee safety
Circuit breaker	Downstream with known outages	High	Low (it stops retrying) but adds latency
Dead-letter queue (DLQ)	Async, event-driven pipelines	Medium	None — failed records are isolated

Idempotency: The Other Half of the Puzzle

A retry strategy without idempotency is dangerous. If your extract-load job inserts rows and fails after inserting half, the retry inserts them again. Now you have duplicates. Here are the patterns I have used in production.

1. Delete-Write Pattern

The simplest pattern: before loading data for a given partition or batch ID, delete any existing rows for that batch.

DELETE FROM sales_facts WHERE batch_id = '2026-08-04-01';
INSERT INTO sales_facts SELECT * FROM staging_sales WHERE batch_id = '2026-08-04-01';

This works well for batch pipelines where you can identify the batch. The downside is that between the DELETE and INSERT, the table is missing data. Use a transaction if your database supports it, or write to a staging table and swap it in atomically.

2. MERGE / UPSERT

If your source has a natural or composite key, use upserts. Most warehouses support this natively.

In BigQuery:

MERGE INTO target_table T
USING source_table S
ON T.order_id = S.order_id
WHEN MATCHED THEN
  UPDATE SET amount = S.amount, updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
  INSERT (order_id, amount, updated_at) VALUES (S.order_id, S.amount, CURRENT_TIMESTAMP())

In PostgreSQL or Redshift, you can use the ON CONFLICT clause:

INSERT INTO target_table (order_id, amount, updated_at)
SELECT order_id, amount, CURRENT_TIMESTAMP FROM source_table
ON CONFLICT (order_id) DO UPDATE SET amount = EXCLUDED.amount;

This is my go-to pattern for incremental loads where rows can arrive more than once.

3. Idempotency Keys

When calling external APIs that do not support upserts natively, pass an idempotency key. Many payment and SaaS APIs (Stripe, Salesforce, etc.) support this — you send a unique key, and the API ensures the same request is processed only once.

If the API does not support idempotency keys, you maintain a processed-keys table and check before each API call:

def safe_api_call(idempotency_key, payload):
    if is_already_processed(idempotency_key):
        logger.info(f'Skipping {idempotency_key}, already processed')
        return
    response = call_external_api(payload)
    mark_as_processed(idempotency_key, response)
    return response

Practical Example: A Resilient S3-to-BigQuery Load

Let us put this together. Here is a sketch of a job that extracts CSV files from S3, transforms them, and loads into BigQuery — with retries and idempotency built in.

import boto3
from google.cloud import bigquery
from google.api_core import retry
import hashlib

bq_client = bigquery.Client()

def load_file_to_bigquery(s3_key, table_id):
    # Generate an idempotency key from the file path and content hash
    content_hash = hashlib.md5(open(s3_key, 'rb').read()).hexdigest()
    idempotency_key = f"{s3_key}_{content_hash}"

    # Check if already loaded
    if already_loaded(idempotency_key):
        print(f"File {s3_key} already loaded, skipping")
        return

    # Load to staging with retry
    job_config = bigquery.LoadJobConfig(
        source_format=bigquery.SourceFormat.CSV,
        write_disposition=bigquery.WriteDisposition.WRITE_TRUNCATE,
    )

    staging_table = f"{table_id}_staging"
    load_job = bq_client.load_table_from_uri(
        f"gs://my-bucket/{s3_key}",
        staging_table,
        job_config=job_config,
        retry=retry.Retry(deadline=300),
    )
    load_job.result()

    # Merge staging into target (idempotent)
    merge_query = f"""
        MERGE INTO `{table_id}` T
        USING `{staging_table}` S
        ON T.order_id = S.order_id
        WHEN MATCHED THEN UPDATE SET amount = S.amount
        WHEN NOT MATCHED THEN INSERT (order_id, amount) VALUES (S.order_id, S.amount)
    """
    bq_client.query(merge_query, retry=retry.Retry(deadline=120)).result()

    # Record that we processed this file
    mark_as_loaded(idempotency_key)

This is production-style code — not perfect, but it handles the real failure modes: the load job might fail (retry kicks in), the load might partially complete (idempotency key prevents duplicate processing), and the merge itself is retryable.

Things to Watch Out For

Retry budgets. If you have a pipeline that calls 50 APIs and each one retries 5 times with exponential backoff, your total runtime can balloon. Set a deadline at the job level and let it fail fast if the total time exceeds your SLA.

Non-idempotent side effects. If your ETL job sends emails, fires webhooks, or writes to a log that another system reads from — those side effects survive retries. Wrap side-effect code in your idempotency-key check. Or better, move side effects out of the retry path entirely.

Exactly-once vs at-least-once. True exactly-once delivery is rare outside of a few systems (Kafka transactions, some stream processors). Most ETL pipelines should aim for at-least-once with idempotent writes. It is simpler to implement and reason about.

State management for circuit breakers. A circuit breaker needs shared state. If your job runs on ephemeral containers (Cloud Run, Kubernetes pods), you need an external store like Redis or DynamoDB. This adds a dependency and a failure mode. Weigh whether you really need it.

Idempotency key design. Your idempotency key needs to be deterministic — if the same logical payload generates a different key on retry, the whole thing is pointless. Use a hash of the payload content, not a timestamp or random UUID.

Production vs Demo

In a simple demo, you can get away with a try-except and a fixed sleep. In production, you want:

• Retries with exponential backoff and jitter on every external call (database queries, API calls, file reads)
• An idempotency mechanism (delete-write, merge, or idempotency keys)
• A way to track which batches or files have already been processed (a small state table in your warehouse works fine)
• Logging that clearly distinguishes first attempts from retries so you can debug without guessing
• A pipeline-level deadline so stuck jobs do not hold up downstream dependencies

Most of the cloud SDKs give you retries for free if you configure them. The idempotency part is what you need to build yourself — but it is usually a few lines of SQL and a lookup table.

We covered retry strategies, idempotency patterns, and how to put them together in a real pipeline. The core idea is simple: assume everything will fail occasionally, make sure retrying is safe, and make sure your data stays correct regardless. Get that right and your pipelines will survive the chaos that is production infrastructure.