A batch import script that crashes halfway through a run is not unusual. What decides whether that crash is a minor inconvenience or a data quality incident is whether the script's writes are safe to repeat. This guide, informed by patterns 137Foundry uses on its own client data engineering work, walks through a concrete pattern for adding idempotency to a Python batch import, so a re-run after a partial failure never produces duplicate or corrupted rows.
What Idempotency Actually Buys You
An idempotent operation produces the same result no matter how many times it runs with the same input. For a batch import, that means re-running the same file, the same chunk, or the same row twice should leave the destination in exactly the state it would be in after running once. The formal definition, and why it matters across distributed systems generally, is covered well in the Wikipedia article on idempotence, worth a read if your team has never designed for it deliberately.
Without this property, every retry after a crash becomes a judgment call: did the crash happen before or after the write committed? If you cannot answer that cleanly, you either risk duplicating data by retrying blindly, or risk losing data by skipping a chunk that might not have actually completed.
The Core Pattern: Upsert on a Natural Key
The simplest way to make a Python batch import idempotent is to replace blind inserts with an upsert keyed on a natural, stable identifier from the source data, rather than relying on an auto-generated destination-side ID. If the source record has a stable external ID, an order number, a customer ID, a source-system primary key, use that as the conflict target.
In PostgreSQL, this looks like an INSERT ... ON CONFLICT (natural_key) DO UPDATE statement, which the PostgreSQL documentation covers in detail under its upsert support. Most modern relational databases have an equivalent construct, and most Python ORMs expose it through a merge or upsert helper rather than requiring raw SQL.
def upsert_batch(connection, rows):
query = """
INSERT INTO imported_records (external_id, payload, updated_at)
VALUES (%(external_id)s, %(payload)s, now())
ON CONFLICT (external_id)
DO UPDATE SET payload = EXCLUDED.payload, updated_at = now()
"""
with connection.cursor() as cur:
cur.executemany(query, rows)
connection.commit()
This single pattern handles the most common failure case: a chunk that partially wrote and gets retried in full. Rows already written simply get overwritten with the same values, rows not yet written get inserted, and nothing gets duplicated.
Tracking Chunk Completion Separately From Row-Level Upserts
Upserting handles duplicate rows, but it does not tell you which chunks are safe to skip entirely on a retry, which matters for performance on a large import where re-running an already-completed chunk wastes time even if it does no harm. A small tracking table solves this cleanly.
def chunk_already_done(connection, chunk_id):
with connection.cursor() as cur:
cur.execute(
"SELECT 1 FROM import_chunks WHERE chunk_id = %s AND status = 'done'",
(chunk_id,),
)
return cur.fetchone() is not None
def mark_chunk_done(connection, chunk_id):
with connection.cursor() as cur:
cur.execute(
"INSERT INTO import_chunks (chunk_id, status, completed_at) "
"VALUES (%s, 'done', now()) "
"ON CONFLICT (chunk_id) DO UPDATE SET status = 'done', completed_at = now()",
(chunk_id,),
)
connection.commit()
Check chunk_already_done before processing each chunk, and call mark_chunk_done only after every row in the chunk has committed successfully. This gives you a fast skip path for completed work on retry, on top of the safety net the upsert already provides for chunks that partially completed.
Handling Non-Idempotent Side Effects
Database writes are the easy case. Many batch imports also trigger side effects that are not naturally idempotent, sending a notification email, calling a third-party API that creates a resource, incrementing an external counter. These need their own idempotency handling, separate from the database layer.
The common pattern is to generate a stable idempotency key per logical operation, often the same natural key used for the database upsert, and pass it to the downstream system if it supports idempotency keys natively, which many payment and notification APIs do. If the downstream system does not support this, track "side effect already fired" state in your own database the same way you track chunk completion, and check it before firing the side effect on a retry.
Testing Idempotency Directly, Not Just Hoping It Works
The best way to verify a batch import is actually idempotent is to run it twice against the same input and diff the resulting table state. Row counts should match exactly between the two runs, and a checksum or hash of the full row set should be identical.
def verify_idempotent(connection, import_fn, rows):
import_fn(connection, rows)
state_after_first = row_checksum(connection)
import_fn(connection, rows)
state_after_second = row_checksum(connection)
assert state_after_first == state_after_second, "import is not idempotent"
The standard library's Python documentation covers the unittest and hashlib modules used to wire a check like this into an existing test suite without adding a new dependency. Run this check against a small test dataset as part of your normal test suite, not just as a one-off manual check before a big import. Idempotency is exactly the kind of property that quietly breaks when someone adds a new field to the import without thinking about the upsert conflict target, and a test catches that before production does.
Making the Upsert Fast at Larger Row Counts
The upsert pattern shown above works fine for chunks in the range of a few thousand rows. At higher volumes, calling executemany with a large row set can become the bottleneck, since most database drivers execute it as many individual statements under the hood rather than a true bulk operation. Batching writes through a dedicated bulk-load path, or using a driver-specific bulk upsert helper where available, can cut import time substantially once you are moving millions of rows rather than thousands.
Profile before optimizing this, though. For most batch imports under a few hundred thousand rows, a straightforward executemany with a reasonable chunk size is fast enough that the extra complexity of a specialized bulk path is not worth the maintenance cost. Save that optimization for the imports where you have actually measured it mattering.
Handling Schema Evolution Without Breaking Old Idempotency Keys
A batch import script tends to live longer than the schema it was originally written against. Columns get added, the natural key used for the conflict target sometimes changes, and a script that assumed one shape of the source data can silently start producing wrong upserts if the key definition drifts without the code catching up.
Guard against this by validating the shape of incoming rows against an explicit schema check before the upsert runs, rather than trusting that the source format never changes. A lightweight validation library, or even a simple set of assertions on expected keys and types, catches a source format change immediately instead of letting it silently corrupt the conflict-target logic that idempotency depends on.
Applying This to a Real Backfill
This pattern applies directly to the broader problem of backfilling historical data into a live pipeline, where idempotency is one of several concerns alongside chunking strategy and throttling write volume against downstream capacity. 137Foundry covers that fuller picture, including how idempotency fits alongside chunking and monitoring, in a longer guide worth reading if you are planning a larger backfill than a single import script covers.
Getting the upsert pattern right on a single script is a good first step. Getting the surrounding chunk tracking and side-effect handling right is what makes the whole import trustworthy enough to re-run without a second thought.
Top comments (0)