Databricks
Idempotent Databricks jobs: re-run safely without duplicates
How to make Databricks jobs safe to re-run: replaceWhere for date slices, MERGE for keyed changes, Auto Loader checkpoints for files and txnAppId for foreachBatch, plus the parameter and control-table habits that make backfills easy.

Hemant Walchale
· 4 min read
Every pipeline eventually fails halfway through. The question is what happens when you press “repair run”. If the answer is “we check for duplicates first”, the job isn’t idempotent: running it twice gives a different result from running it once.
On Databricks, there’s a clear pattern for each kind of load. The mistake is using one pattern everywhere.
Pick the pattern by the kind of load
| Kind of load | Pattern | Why it’s safe to re-run |
|---|---|---|
| Daily or hourly batch for a date or partition | Recompute the slice, overwrite with replaceWhere |
The re-run replaces the same slice |
| Changes keyed by a business key (CDC, updates) | MERGE on the key, source deduplicated |
Matching rows are updated, not duplicated |
| Files landing in cloud storage | Auto Loader with a checkpoint | Each file is processed once |
Custom writes in foreachBatch |
txnAppId and txnVersion write options |
Delta skips a batch it has already committed |
| Full refresh of a small table | CREATE OR REPLACE TABLE ... AS SELECT |
Each run rebuilds the whole table |
1. Date slices: replaceWhere
When a job computes one day of data from source, write that day with replaceWhere. A re-run overwrites the same day, and anything outside the predicate is untouched.
(df.write.format("delta")
.mode("overwrite")
.option("replaceWhere", f"event_date = '{run_date}'")
.saveAsTable("sales.daily_orders"))
The SQL equivalent is INSERT INTO ... REPLACE WHERE event_date = '...' SELECT .... Delta checks that every written row matches the predicate, so a bug that produces rows for the wrong day fails instead of quietly writing them. Backfills become a loop over dates, and a failed backfill can restart from any date.
2. Keyed changes: MERGE, with the source deduplicated
For CDC feeds and update streams, MERGE on the business key. Plain INSERT is never safe here: a re-run appends the same changes again.
MERGE INTO silver.customers AS t
USING (
SELECT * FROM bronze.customer_changes
WHERE batch_date = :run_date
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY changed_at DESC) = 1
) AS s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.changed_at >= t.changed_at THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;
Two details matter:
- Deduplicate the source. If two source rows match one target row, Delta fails the
MERGE. Keep the latest row per key. - Guard against older changes. The
changed_at >= t.changed_atcondition stops a re-run of an old batch from overwriting newer data.
3. Files: Auto Loader checkpoints
Auto Loader records which files it has processed in the stream’s checkpoint, so a restarted stream picks up where it stopped, and each file is ingested once. Two rules keep this true:
- One checkpoint location per stream, never shared, never deleted casually. Deleting it means reprocessing every file.
- Use
trigger(availableNow=True)for scheduled batch-style runs, so the job processes everything new and stops.
4. foreachBatch: txnAppId and txnVersion
Streaming writes through foreachBatch can run the same micro-batch twice after a failure. Delta handles this if you pass a stable application ID and the batch ID:
def write_batch(batch_df, batch_id):
(batch_df.write.format("delta")
.option("txnAppId", "orders_to_silver") # stable per stream
.option("txnVersion", batch_id)
.mode("append")
.saveAsTable("silver.orders"))
Delta records the highest version committed for that application ID and skips a batch it has already seen. Use a different txnAppId for each table you write inside the same function.
Two habits that matter as much as the pattern
Pass the run date in. Use a job parameter for the logical date, such as {{job.start_time.iso_date}} for scheduled runs, and override it for backfills. Never call current_date() inside the transformation: a re-run the next morning would process the wrong day.
Keep a small control table. One row per job, date and status, with row counts in and out. It tells you which slices finished and which need re-running, and it’s where reconciliation checks can look.
How to test it
Run the job for one date. Record row counts and a sum of a key amount. Run it again for the same date without changing anything. If any number changes, the job isn’t idempotent yet.
The short version
Choose the pattern by load type: replaceWhere for slices, MERGE with a deduplicated source for keyed changes, Auto Loader checkpoints for files, and txnAppId for foreachBatch. Pass the run date as a parameter, keep a control table, and test by running everything twice. Related: what is change data capture, what is medallion architecture and Auto Loader schema evolution.
Sources
- Selectively overwrite data with Delta Lake, Databricks documentation
- Upsert into a Delta Lake table using merge, Databricks documentation
- Use foreachBatch to write to arbitrary data sinks (idempotent table writes), Databricks documentation
- What is Auto Loader?, Databricks documentation
- What is a dynamic value reference?, Databricks documentation
Not sure where your own platform stands? Take the free 3-minute data platform scorecard, or see how we work on Databricks. Need people to do this alongside your team? See senior data engineers for hire. Unfamiliar terms are explained in the data engineering glossary.