Skip to content
METRIC OAK

New: the 3-week Data Platform Assessment: fixed fee, priced roadmap. See what's included →

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

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_at condition 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

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.

Frequently asked questions

What does idempotent mean for a data pipeline?

Running the same job twice for the same input leaves the table in the same state as running it once. A failed run can simply be re-run, and a backfill is just a loop over dates, with no duplicates and no manual clean-up.

Should I use replaceWhere or MERGE?

Use replaceWhere when the job recomputes a whole slice, such as one day or one partition, from source. Use MERGE when changes arrive keyed by a business key and only some rows change. Plain INSERT or append is safe only when something else, such as an Auto Loader checkpoint, guarantees each input is processed once.

Why does my MERGE fail with multiple source rows matching the same target row?

Your source has duplicate keys. Deduplicate it first, keeping the latest row per key, for example with ROW_NUMBER() over the key ordered by the change timestamp descending, and filter to row 1.

Why should the run date be a job parameter?

If the job calls current_date() itself, re-running yesterday's failed run this morning processes today instead. Pass the logical date in as a parameter so a re-run or backfill always processes the date it was given.

Have 30 minutes? Get a senior architect’s view of your data platform.

No sales deck. You talk to the engineer who would lead the work.

Book an architecture review
Hemant WalchaleYou talk to Hemant Walchale, founder145 published guidesProfiles: LinkedIn · Clutch · GoodFirms

Free, takes 20 seconds

Get your starter data platform roadmap

Pick your platform and your main goal. You get a 90-day plan written by senior data engineers: what to do first, next and after.

You’ll also get our monthly email (confirm it in your inbox). Unsubscribe any time. Privacy policy.