skip to content

A file loader crashed mid-batch, re-ran, and now every row from that drop is duplicated. How do you make file ingestion idempotent?

level: seniorimportance: must knowfreq 66%

answer

  1. the second attempt is inevitable
  2. two things must agree and cannot commit together
  3. make rerunning converge instead of accumulate
  4. name alone is a weak identity for a file

basics

~20 s

File ingestion is at-least-once by nature, so make replay harmless: track processed objects by key plus content hash in the same transaction as the load, or make the load replace a partition rather than append to it, or upsert on a deterministic row key.

solid answer

~50 s

There is always a window between committing rows and recording that you committed them, so a crash inside it guarantees a re-read. Three mechanisms, best combined. **Replace, don't append**: make one prefix the unit of load, write into a staging table and swap or overwrite the target partition, so rerunning converges on the same state. **A processed-file ledger**: a table keyed on object key plus ETag or version id, with a unique constraint, updated in the same transaction as the rows where the target supports it — the ETag matters because the same content re-dropped under a new name is invisible to a name-only ledger. **Deterministic row keys plus MERGE**: derive a stable key from the source primary key, or from object key plus row number when there is none, and upsert. Moving processed files to an archive prefix looks tidy but is copy-then-delete, so it is not atomic either.

code

sql · 14 lines
sql
-- claim the object before loading; the unique key makes a replay a no-op
INSERT INTO ingest_ledger (object_key, etag, claimed_at)
VALUES (:object_key, :etag, now())
ON CONFLICT (object_key, etag) DO NOTHING
RETURNING object_key;
-- zero rows returned => already claimed or loaded, skip the file

-- load into staging, then replace the partition instead of appending
BEGIN;
DELETE FROM orders WHERE ingest_date = DATE '2026-08-20';
INSERT INTO orders SELECT * FROM orders_staging;
UPDATE ingest_ledger SET completed_at = now()
 WHERE object_key = :object_key AND etag = :etag;
COMMIT;

go deeper

for a junior

Know that a loader can and will see the same file twice, and that appending blindly is what turns a retry into duplicated rows.

for a middle

Explain the crash window between committing rows and recording the file as done, and describe a ledger keyed on object key plus content identity as the standard guard.

for a senior

Design the whole path: replace a partition rather than append, decide where the ledger commits relative to the data, handle stale claims, and explain why an archive move is copy-then-delete and therefore racy.

for a principal

Set the standard so every pipeline is replayable by construction — raw zone preserved, load unit equals prefix, backfill and nightly run sharing one code path — because per-team ad-hoc dedup is what produces the 3am reconciliation call.

## Accept the premise first Any file poller is at-least-once. Rows land in the target and the fact of having landed them is recorded somewhere else — a ledger row, a checkpoint, a moved file — and no matter how you order those two steps there is a window in which a crash leaves them disagreeing. Order them one way and you double-load; order them the other and you silently skip a file, which is worse. So the goal is not to eliminate the second attempt but to make the second attempt harmless. That reframing is the answer interviewers are listening for. Candidates who chase "exactly-once file processing" as a delivery property are solving the wrong problem. ## Mechanism 1: make the load a replacement The strongest and simplest option. Define the unit of work as one landing prefix — one day, one hour, one drop — and make loading it *replace* the corresponding target partition rather than add to it. Load into a staging table, then swap or overwrite the partition in one operation. Rerunning now converges. Whether the previous attempt got zero rows in or ninety percent of them, the outcome after the rerun is identical: exactly what the prefix contains. This also gives you free backfill semantics — "reload 14 August" is the same code path as the nightly run — and it is why the dated-prefix layout and idempotency are the same design decision viewed from two angles. Its requirement is that the prefix be a *complete* statement of that partition. If a day's data arrives in several independent drops, overwrite must consider all of them, or you must fall back to upserts. ## Mechanism 2: a processed-file ledger A table recording each object you have consumed. Key it on the object key **plus a content identity** — the ETag, or the version id, or a hash you compute — not the name alone. Two failure modes motivate that: - A partner re-uploads a corrected file under the same name. Name-only, you skip it; you wanted to reload it. - A partner re-sends identical content under a new name (`orders_20260820_v2.csv`). Name-only, you load it again; content identity catches the duplicate. Beware ETag semantics: for a simple upload it is the MD5 of the object, but for a multipart upload it is a composite of the part checksums and a dash-suffixed part count, so it is *not* a content hash across differently-chunked uploads of identical bytes. Treat it as an object-version identity rather than a portable content fingerprint, and compute your own hash if you need to compare across sources. Write the ledger row in the **same transaction** as the data where the target allows it. Where it does not — many warehouse bulk loaders commit independently — the fallback is a claim-then-load protocol: insert the claim first with a unique constraint so concurrent workers cannot both take the file, load, then mark it complete. A crash between claim and complete leaves a claimed-but-unfinished row, which a reaper must detect by age and reset. Design that reaper deliberately: it is where the subtle bugs live. Some warehouse bulk loaders keep their own record of which files they have already ingested. That is a convenience with its own retention horizon and its own edge cases, and it does not survive a re-drop under a different name — treat it as a backstop, not as your idempotency strategy. ## Mechanism 3: deterministic keys and MERGE Give every row an identity that is stable across replays and upsert on it. If the source has a primary key, use it. If it does not, `hash(object_key || row_number)` is deterministic as long as the file's bytes are unchanged, which they are — objects are immutable once written. Then a replay updates rows in place rather than appending them. The caution is that a row key derived from position collapses if the producer re-emits the same logical rows in a different order under a new file name. Then only a business key or a content hash of the row will save you. ## What does not work well **Moving processed files to an `archive/` prefix.** There is no rename in object storage; it is copy then delete, two operations with a crash window between them. Crash after copy and the file exists in both places — your loader now sees it in the landing prefix on the next run and loads it again, and you have merely moved the race rather than removed it. It is still useful for keeping the working prefix small; it is not a correctness mechanism. **Timestamp-based filters** — "load everything modified since the last run". Clock skew, retroactive re-uploads and equal-timestamp boundaries all leak. If you use a modified-time filter, use it as a *narrowing* device on top of a ledger, never as the sole guard. **Trusting the marker.** A completeness marker tells you the drop is finished, not that it is new. The two mechanisms are orthogonal and you need both. ## Detecting the failure you already have When duplicates are already in the table, do not just deduplicate and move on. Establish the blast radius — group by the natural key and count, and check whether the duplicate rows are byte-identical (a replay) or differ (two genuine versions, which is a different bug). Then fix the loader, then repair the data by replaying the affected prefixes through the now-idempotent path, which is exactly why keeping the raw landing zone intact matters.

  • Why is moving a processed file to an archive prefix not a reliable exactly-once guard?
    Object stores have no atomic rename — a move is a copy followed by a delete. A crash between them leaves the object in both prefixes, so the next run finds it in the landing zone and loads it again. It keeps the working prefix small, which is worth doing, but the correctness must come from a ledger or a replace-style load.
  • Your ledger keys on object name only. What slips through?
    Both directions. A corrected file re-uploaded under the same name is skipped when you wanted it reloaded, and identical content re-sent as _v2 is loaded twice. Keying on name plus ETag or a content hash distinguishes new content from a re-send, which is what you actually want to decide.
  • The target cannot commit rows and the ledger in one transaction. What protocol do you use?
    Claim, load, complete. Insert a claim row with a unique constraint so no second worker takes the file, run the load, then mark complete. A crash leaves a stale claim, so a reaper resets claims older than a threshold — and because the reset can race a slow-but-alive load, the load itself should still be a replace or an upsert.
  • Duplicates are already in the fact table. How do you repair without making it worse?
    Measure first: group by the natural key and check whether duplicates are byte-identical replays or genuinely different versions, since those are different bugs. Fix the loader, then replay the affected prefixes through the idempotent path rather than hand-deleting rows — which is only possible because the raw landing zone still holds the originals.

Idempotency here is a light switch, not a button: pressing it again should leave the room in the same state, not turn on a second light.

saying these in an interview costs you the question

  • Aims for exactly-once delivery instead of making replay harmless
  • Keys the processed-file ledger on filename alone
  • Treats moving files to an archive prefix as atomic
  • Relies on last-modified timestamps to decide what is new
  • Assumes the completeness marker also prevents double loading

context

resolving links…