A batch job failed halfway and was rerun, and now the target table has duplicate rows. How do you make the job safe to rerun?

Make every write idempotent: running the job twice for the same input must leave the table exactly as running it once. The job was written as a plain append, so the rerun added again the rows the failed run had already committed. Fix the data first, then change how the job writes: replace a defined slice of the table, merge on a key, or tag the write so the table rejects a repeat.

1. Clean up what is there

• Find the duplicates by business key and load date, and confirm the count matches what the failed run wrote.
• On Delta Lake, you can see the two commits in the table history (DESCRIBE HISTORY). Remove the extra rows with a DELETE or a rewrite of the affected slice, inside one transaction.
• Tell the owners of downstream tables: anything that read the table in between read duplicates too.

2. Pick an idempotent write pattern

Replace a slice

(daily.write.format("delta")
   .mode("overwrite")
   .option("replaceWhere", f"event_date = '{run_date}'")
   .save(path))

The rows matching the condition are replaced atomically, and the rest of the table is untouched. A rerun for the same date produces the same result. For a plain Parquet table, the nearest equivalent is dynamic partition overwrite (partitionOverwriteMode set to dynamic). Without it, an overwrite in Spark's default static mode deletes every partition matching the write's specification, which with no specification means the whole table. On Delta, dynamic partition overwrite is now marked legacy, so prefer replaceWhere.

Merge on a key

Use when the input can repeat rows or update them. Deduplicate the batch on the key first: a MERGE can fail when several source rows match the same target row, because it is unclear which one should update it.

Tag the transaction

(batch.write.format("delta").mode("append")
   .option("txnAppId", "orders_loader")
   .option("txnVersion", batch_number)
   .save(path))

Delta records the pair. A later write with the same app id and a version that is not higher than the last one recorded is ignored, so a retried append is not applied twice. The version must be the same on every retry of the same data and higher than the last one written: a batch number works, and so does the time of the first attempt if the job saves it and reuses it on retry. A version taken fresh from the clock on each attempt defeats the check.

3. Make reruns part of the design

• Parameterise the job by the slice it processes (run_date), never by "now". A rerun on Tuesday for Monday must produce Monday's data.
• Do the whole write in one transaction. Several separate writes to one table can leave half a run behind.
• Test it: run the job twice for the same date in a test environment and compare row counts and checksums.

What interviewers want: the word idempotent, a concrete pattern for each case (replace a slice, merge, transaction tags), and the habit of parameterising by date so a rerun does exactly the same work.