Building Idempotent CSV Imports That Are Safe to Run Twice

The test of a CSV import is not whether it works the first time. It is what happens the second time: after a retry, after the scheduler fires twice, after someone drops yesterday's file back into the folder, after a backfill overlaps a regular run. An import that doubles its rows on a rerun forces everyone who touches it to be careful, and people are not careful at six in the morning. An import that does nothing on a rerun is one you can stop worrying about. This article takes one tiny file, runs it twice through five designs, and shows what each leaves in the table. It is the idempotency chapter of automating CSV imports into a database.

The file and the experiment

The input is three orders:

order_id,customer,total
1,Acme,10.00
2,Beta,20.00
3,Cy,30.00

The experiment loads it twice into a fresh table with each design and counts the rows:

import csv, hashlib, io, sqlite3

DATA = "order_id,customer,total\n1,Acme,10.00\n2,Beta,20.00\n3,Cy,30.00\n"

def rows():
    return list(csv.DictReader(io.StringIO(DATA)))

def count(db):
    return db.execute("SELECT count(*) FROM orders").fetchone()[0]

# 1. Plain insert, run twice
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (order_id INTEGER, customer TEXT, total REAL)")
for _ in range(2):
    db.executemany("INSERT INTO orders VALUES (:order_id,:customer,:total)", rows())
print("plain insert, two runs:", count(db), "rows")

# 2. Primary key + upsert
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer TEXT, total REAL)")
for _ in range(2):
    db.executemany(
        """INSERT INTO orders VALUES (:order_id,:customer,:total)
           ON CONFLICT(order_id) DO UPDATE SET customer=excluded.customer, total=excluded.total""",
        rows(),
    )
print("upsert on key, two runs:", count(db), "rows")

# 3. No natural key: row hash with INSERT OR IGNORE
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE events (row_hash TEXT PRIMARY KEY, customer TEXT, total REAL)")
def h(r):
    return hashlib.sha256("|".join([r["order_id"], r["customer"], r["total"]]).encode()).hexdigest()
for _ in range(2):
    db.executemany("INSERT OR IGNORE INTO events VALUES (?,?,?)", [(h(r), r["customer"], r["total"]) for r in rows()])
print("row hash, two runs:", db.execute("SELECT count(*) FROM events").fetchone()[0], "rows")

# 4. Delete-and-insert by batch
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders (batch TEXT, order_id INTEGER, customer TEXT, total REAL)")
for _ in range(2):
    with db:
        db.execute("DELETE FROM orders WHERE batch = ?", ("2026-10-01",))
        db.executemany("INSERT INTO orders VALUES ('2026-10-01',:order_id,:customer,:total)", rows())
print("replace batch, two runs:", count(db), "rows")

plain insert, two runs: 6 rows
upsert on key, two runs: 3 rows
row hash, two runs: 3 rows
replace batch, two runs: 3 rows

The first design is what most first versions are, and it doubles the data. The other three each fix it in a different way, and each fits different data.

Upsert on a business key

When rows have a stable identity, such as an order ID, a SKU or a user ID, declare it as the primary key and use the database's upsert. SQLite and PostgreSQL write it as INSERT ... ON CONFLICT (key) DO UPDATE SET ..., and MySQL as INSERT ... ON DUPLICATE KEY UPDATE .... A rerun finds each key present and rewrites the same values. A corrected row with the same key updates in place, which is usually what you want.

Two things make an upsert dangerous. The key must be genuinely unique in the data. If two different rows in one file share a key, the second silently replaces the first, and PostgreSQL raises cannot affect row a second time if both are in one statement. The duplicate is a data problem that the upsert hides, so count distinct keys against rows before loading (finding duplicate keys in a CSV). And an upsert never deletes. A row that disappears from tomorrow's file stays in the table, so an upsert models "the file contains changes", not "the file is the truth". If the file is a full snapshot, you also need a step that removes keys no longer present.

A row hash when there is no key

Event data, such as log lines and clicks, often has no key. A hash of the row's content gives it one: store a SHA-256 of the fields, make it the primary key and insert with INSERT OR IGNORE (SQLite), INSERT ... ON CONFLICT DO NOTHING (PostgreSQL) or INSERT IGNORE (MySQL). An identical row on a rerun hashes to an existing key and is ignored.

The trade is that identity is the content. Two genuinely separate events with identical fields, say two clicks on the same button in the same second with the same user, collapse into one. If that matters, add something that tells them apart: a source line number, a sequence number from the producer. The other trade is that a corrected row is a different row: change a total from 10.00 to 12.00 and you get both versions, so a row hash suits immutable events and not mutable records.

Replace by batch

When each file represents a bounded unit, one day or one customer, the cleanest rule is: delete everything for that unit, then insert the file, in one transaction. A rerun removes what the first run wrote and writes it again. It handles corrections and removals together, since rows missing from the new file disappear, and it needs no key at all, only a batch label.

BEGIN;
DELETE FROM orders WHERE batch = '2026-10-01';
-- COPY or INSERT the file here, with batch = '2026-10-01'
COMMIT;

Its risk is the label. A wrong batch value deletes the wrong data, and a file that spans two batches needs two deletes. Put the label in the file name or the manifest, not in the data, and refuse to run when it cannot be determined. The pattern is also what makes partition swapping fast in large tables, where you load a staging partition and exchange it in.

A ledger of files

The three approaches above work on rows. A ledger works on files: a small table with the SHA-256 of every file loaded, checked before loading and written in the same transaction as the load. A file whose hash is present is skipped whole. It is cheap, it catches a copied or renamed file, and it composes with the row-level designs, which then only need to handle a file that is new but overlaps old data. The importer in automating CSV imports into a database combines a ledger with an upsert, and a Python script that loads a daily CSV into PostgreSQL does the same with COPY.

Key the ledger on content, not on the name. A file corrected and re-sent under the same name has a new hash and loads, and the same file under a new name has the old hash and is skipped. A name-keyed ledger gets both cases wrong.

Which one to pick

A full snapshot of a reference table is a fifth case: load into a new table, validate and swap names, so readers see the old one until the new one is complete.

When a rerun should not be a no-op

Idempotency is the right default and not a universal one. A file of payments that is meant to be applied again, because the amounts are deltas and not totals, must not be loaded twice and must not be silently skipped either; the right behavior is to fail loudly on the repeat, since someone is trying to do something unusual. A counter that increments per run, a job that sends an email for each new row, and any step with an outside effect need their own protection: the email is sent when the ledger row is first written, not on every pass.

Corrections are the other edge. If a provider re-sends a day with different numbers on purpose, you want the new numbers, which a ledger keyed on hash allows because the content differs, and a naive "already loaded for this date" check blocks. Decide which of those you mean, write it down, and test both. For the PostgreSQL behaviors mentioned, see the manual pages on INSERT, and for the SQLite forms the upsert documentation.