Retries are only safe if the operation being retried is idempotent. Insert a row twice, get two rows. Send an email twice, send two emails. Charge a card twice, charge the customer twice. The retry layer that solves transient failures creates a duplicate-side-effect problem if the operations it retries are not idempotent.
The right fix is to make every mutating operation idempotent. The wrong fix is to refuse to retry. This piece walks through how to add idempotency to an existing pipeline without burning down what you have built and starting over.
What Idempotency Actually Means In Practice
An operation is idempotent if running it twice produces the same result as running it once. The cleanest examples come from REST: a PUT to a specific URL replaces the resource at that URL, so running the same PUT twice leaves the resource in the same final state. POST that creates a new resource is not idempotent by default; the second POST creates a second resource.
In data automation, the operations that need idempotency are the side-effect-producing ones: inserts into a database, messages onto a queue, calls to external mutating APIs, files written to storage. Read-only operations are trivially idempotent; you do not need to worry about them.
The pattern: each operation receives a key that uniquely identifies the logical work being done. If the operation is retried with the same key, the system recognizes it as a retry and does not re-do the side effect.
Pattern 1: Idempotency at the Database Layer
For database inserts, the simplest pattern is a unique constraint on a column that holds the idempotency key. The first insert with a given key succeeds. The retry inserts the same key and the database rejects it with a unique-constraint violation. The application catches the violation and treats it as success.
def insert_event(event):
key = compute_idempotency_key(event)
try:
db.execute("INSERT INTO events (idempotency_key, payload) VALUES (?, ?)", key, event.payload)
except UniqueConstraintViolation:
pass # already inserted, this is a retry
The idempotency key needs to be deterministic for the same logical operation. For an event from an upstream system, a hash of the event's natural identifiers (source ID, timestamp, event type) is the right shape. For a synthetic operation triggered by the pipeline itself, a UUID generated once at the top of the operation and threaded through retries is the right shape.
The schema change to add the column and unique constraint is the migration cost. For a table with millions of rows, adding a unique constraint can take meaningful downtime; consider an online schema change tool like pt-online-schema-change for PostgreSQL or gh-ost for MySQL.
Pattern 2: Idempotency at the External API Layer
For calls to external APIs that you do not control, the right pattern depends on whether the API supports idempotency natively. Stripe's API accepts an Idempotency-Key header on every mutating endpoint; the first call with a given key actually processes, and subsequent calls with the same key return the result of the first without reprocessing.
For APIs that support an idempotency header, pass the key on every retry. The API handles deduplication on its end. Your retry logic does not need to track any local state beyond the key itself.
For APIs that do not support idempotency natively, you have two options. The first is to track the request locally in your own database, recording when a request has been sent and what response came back. Before each call, check whether you have already sent the same logical request; if so, return the cached response without re-calling. The second is to live with the duplicate side effects and accept that retrying these specific APIs is unsafe; for these operations, your retry layer should be configured to not retry them at all.
The local-tracking approach is more work but extends idempotency to APIs that do not provide it natively. The no-retry approach is simpler but means transient failures on those APIs page humans, which is exactly what the retry layer was supposed to prevent.
Pattern 3: Idempotency at the Application Layer
For internal operations that touch multiple resources (a single logical action that inserts a row, publishes a message, and updates a counter), idempotency at the database layer per resource is not enough. The composite operation needs its own key.
The pattern: at the top of the operation, generate or accept an idempotency key. Wrap the entire operation in a transaction (or saga, if the resources do not share a transaction boundary). On the first execution, record the key as "in progress" before doing the work; on the retry, observe the key and either resume from where the first execution left off or skip the work entirely if it completed.
This is harder to implement correctly than the per-resource pattern. The transaction boundary issues are subtle, particularly when multiple resources span different storage systems. For most pipelines, the per-resource pattern is sufficient and is dramatically simpler to operate; reach for the composite-operation pattern only when the per-resource pattern is genuinely insufficient.
Migrating an Existing Pipeline
The migration sequence I recommend:
First, identify every operation in the pipeline that has a side effect. Categorize each as database insert, external API call, queue publish, or composite. The list is usually shorter than people expect; most pipeline code is data transformation, which is already idempotent.
Second, for each side-effect operation, decide which idempotency pattern fits. Database inserts almost always want the unique-constraint pattern. External API calls depend on the API. Queue publishes usually need application-layer keys.
Third, add the idempotency keys to the operations one at a time. Each operation can be migrated independently; you do not need a flag day. The retry layer remains disabled (or retries a smaller number of times) until all the relevant operations are idempotent, then you turn up the retry budget.
Fourth, after the migration, monitor for the unique-constraint violations and the API idempotency hits. These should be very low frequency under normal operation; if they spike, something has changed in the failure pattern of the underlying service.
Tools Worth Knowing
Tenacity handles the retry side of the equation; it does not handle idempotency directly but plays well with the patterns above. Python httpx makes the API client side clean. For the broader resilience patterns this fits into, Wikipedia's article on idempotency covers the underlying concept across CS and math contexts.
The longer walkthrough on how to build a self-healing retry strategy for data automation jobs covers how the retry layer integrates with idempotency, where the failure-classification fits, and how to bound retries safely. For the data integration work this pattern shows up in, https://137foundry.com/services/data-integration covers the broader pipeline-resilience surface.
What This Buys You
A pipeline with idempotent mutations is one where the retry layer can be aggressive without creating data quality problems. You can crank the retry budget up, absorb more transient failures, and reduce on-call noise.
A pipeline without idempotent mutations is one where every retry is a gamble. Either you retry and risk duplicate side effects, or you do not retry and pay the on-call cost for every transient failure. Neither is a good place to operate from long-term.
The migration is real engineering work but is bounded. Most pipelines I have helped migrate have somewhere between 5 and 25 distinct side-effect operations to make idempotent. The work is straightforward once the pattern is clear, and the result is a pipeline that can absorb transient failures reliably for the lifetime of the system.
The Habit Worth Building
For new pipeline code, write idempotent operations by default. The discipline is small (generate a key, pass it through, handle the constraint violation) and the payoff is large (retries are safe forever). The pattern compounds: every new operation that is idempotent from the start is one less migration to do later.
For existing pipelines without idempotency, the migration is worth doing before the retry layer needs to be relied upon during a real incident. The wrong moment to discover that your retries create duplicates is during a Friday afternoon outage with a backlog of failed messages already queued.
Top comments (0)