Idempotent data pipelines on Databricks, plus CDC with no watermark

An idempotent data pipeline produces the same table state no matter how many times it runs. On a Databricks lakehouse, that takes three things: Delta MERGE on a deduplicated key, hash-based change detection for sources with no watermark, and an append-only archive for any column you can’t rebuild from source.

The hard part is that a bad rerun rarely looks bad. No job fails. A table quietly changes, and history that nobody can rebuild disappears. Gartner puts the average cost of poor data quality at $12.9 million a year per organization. Reruns that silently rewrite tables are one of the easiest of those leaks to close.

How does a routine rerun wipe eight years of history?

We hit this on a service-desk pipeline. One routine rerun wiped eight years of resolution-time history.

The cause was simple. The resolution times were derived columns. They lived only in the working tables, never in the source system. When the backfill overwrote those tables, nothing upstream could rebuild them.

Nobody made a dramatic mistake. The job did exactly what it was written to do. It just wasn’t written to run twice.

Why do stateful loads break when they run twice?

Most pipelines assume they will run once, in order, on schedule. Retries, backfills and scheduler hiccups break that assumption every week. Three patterns do most of the damage.

Blind appends

The job dumps the latest batch into the target table. If Tuesday’s job runs twice, Tuesday’s rows land twice. Cleanup scripts patch the symptom. The write itself has to refuse duplicates.

Sources with no watermark

Legacy systems and ERPs, service desks included, often update rows in place without touching a timestamp. With no updated_at column, the extract has no way to ask “what changed since last time?” Teams fall back to full daily extracts and hope the downstream math holds.

Derived columns with no home of their own

Metrics calculated in flight and stored only in silver are one rerun away from deletion. Rebuild silver from bronze and any history that existed only in silver is gone. That is exactly what happened to our resolution times.

What does idempotent mean for MERGE, overwrite and append on Databricks?

Databricks and first principles thinking start in the same place. Before you write a job, state what its second run does to the table. Each of the three write patterns gives a different answer.

Overwrite is idempotent. Run it ten times with the same input and you get the same table. The risk is destruction, not duplication. Overwriting a whole table to refresh one day erases everything the source no longer holds. Scope it instead: Delta’s replaceWhere option overwrites only the rows that match a predicate, such as one load date, and leaves the rest alone.

Append is not idempotent by default. Every rerun adds the same rows again. In a Structured Streaming foreachBatch, Delta’s txnAppId and txnVersion options let the table skip a batch it already committed. Outside that pattern, deduplicate first or switch to MERGE.

MERGE is the workhorse, with two conditions. First, the source needs one row per key. Delta raises an error when several source rows match the same target row, so deduplicate before you merge. Second, the update must be deterministic. A SET clause that stamps current_timestamp() changes every matched row on every run. Update only when the row hash differs, and the hundredth run leaves the table exactly as the first one did.

How do you run CDC when the source has no watermark?

You work out what changed from the data itself. The method that holds up is the hash diff.

Land a full snapshot of the source in bronze. For each row, compute sha2 over every non-key column. Coalesce nulls to a sentinel value first, because concat_ws skips nulls and two different rows can produce the same string. Then MERGE the snapshot into silver on the primary key:

• Key missing from the target: insert it.
• Key present but the hash differs: update it.
• Key in the target but missing from the snapshot: delete it with WHEN NOT MATCHED BY SOURCE.

Guard that delete branch. Run it only when the snapshot is complete, and check the row count against the last load before you allow deletes. Otherwise a half-finished extract looks like a mass deletion.

If you already run Databricks Lakeflow pipelines, including the ones that prepare AI-ready data for models, AUTO CDC gives you automated change data capture for this case. Its snapshot mode does the comparison for you and can keep SCD Type 2 history. We cover the pipeline side in our guide to Delta Live Tables.

The trade-off with hash diffs is cost. You read the whole source on every run. That work moves off the source database and onto lakehouse compute, which is usually where you want it. On a large legacy table, though, a nightly full snapshot can grow into one of the bigger lines on the Databricks bill.

How do you keep hash-diff compute costs under control?

• Store the row hash as a column in silver, so each run hashes only the incoming snapshot and never recomputes the target.
• Extract only the key and the columns you actually track. Every extra column is read, hashed and compared on every run.
• Cluster silver on the primary key with liquid clustering, so matched rows sit together and MERGE rewrites fewer files.
• Keep deletion vectors on, so MERGE marks changed rows instead of rewriting whole Parquet files.
• Match the snapshot cadence to how often the source really changes. A reference table that changes monthly doesn’t need a nightly full extract.

How do you protect derived columns with immutable archive copies?

Treat any column you can’t rebuild from source as a first-class asset. In a medallion architecture, it doesn’t belong only in a silver working table.

Write derived output to its own Delta table and set the table property delta.appendOnly = true. Delta then rejects UPDATE and DELETE on that table. A bad rerun can add rows. It cannot erase history.

Lock it down in Unity Catalog too. Grant MODIFY on archive tables only to the pipeline’s service principal, and keep ownership with a platform group, so nobody quietly switches appendOnly off.

When the logic changes, append a new version of the metric with a calc_version tag. Leave the old values where they are. Gold views pick the version the business reports on.

Don’t count on time travel as your archive. RESTORE works only while the old files survive VACUUM, and the default retention is seven days. A rerun nobody notices for two weeks is past that window.

As a Lean Six Sigma Master Black Belt, I read a rerun that changes the numbers as a process defect, not bad luck. Databricks gives you the controls to remove the condition that causes it. That beats inspecting for damage after every backfill.

What does a bad rerun do to AI and LLM workloads?

The damage doesn’t stop at the dashboard. The same silver and gold tables usually feed feature tables, model training sets and the document stores behind a RAG pipeline. Whatever a rerun does to those tables, every model downstream inherits.

Duplicate rows overweight whatever they duplicate. Load Tuesday’s batch twice and a model trained on that table sees Tuesday twice as often. A retrieval index built from doubled or stale records hands an LLM conflicting passages, and the answer it writes can only be as good as what it retrieved.

Reproducibility goes too. If a training table changes every time someone reruns the load, you can’t rebuild the data a model learned from, and you can’t explain why this month’s model behaves differently from last month’s. Record the Delta table version each training run reads, and copy any training set you must keep long term into its own append-only table, because time travel won’t hold it past VACUUM.

That is why LLM data preparation starts with loads you can rerun safely, not with the model. Generative AI is only as reliable as the governed data foundation under it.

How do you test that a pipeline is safe to rerun?

Run it twice and compare. Check row counts, sums of key measures and a hash of the whole table. If anything moves on the second run, the pipeline is not idempotent.

Then run it out of order. Load Wednesday, then Monday, and confirm the final state matches a chronological run. With SEQUENCE BY in AUTO CDC, late-arriving rows don’t overwrite newer ones.

Kill a run halfway and restart it. The restart should land the same state as a clean run.

Streaming jobs need one more test. Structured Streaming records offsets and state in its checkpoint, so after a cluster failure the restarted query resumes from the last committed batch. The batch that was in flight can run again, though, so the sink write has to be idempotent: a MERGE on a deduplicated key, or the txnAppId and txnVersion options inside foreachBatch. Never share a checkpoint between two queries, and never delete one to unstick a stream unless you mean to reprocess. A fresh checkpoint reads the source again from its starting position.

Before any backfill, check Unity Catalog data lineage. Column-level lineage lands in system tables, so you can list every downstream table that reads a derived column. One caution: primary and foreign key constraints in Unity Catalog are informational, not enforced. Use CHECK and NOT NULL constraints, which Delta does enforce, and add a uniqueness test to the job.

Which pipelines should you make rerun-safe first?

Rank them by what a bad rerun would cost you:

• Tables holding derived columns that don’t exist in the source.
• Tables that feed training sets, feature tables or RAG indexes.
• Append jobs with automatic retries switched on.
• Loads from sources with no updated_at column.
• Any backfill someone plans to run this quarter.

Start with the first two groups. The first holds the losses time travel won’t cover a week later. The second carries a bad rerun into every model that reads it.

Secure. Solve. Scale. It starts with pipelines you can rerun without holding your breath. If a backfill on your lakehouse still makes your team nervous, talk to us about our data engineering work. We’ll map what each job does on its second run and fix the ones that fail the test.

Frequently Asked Questions (FAQs)

What is an idempotent data pipeline?

An idempotent data pipeline leaves the target in the same state however many times the same load runs. Retries and backfills don’t create duplicates or erase history.

Is a Delta MERGE always idempotent?

No. MERGE is idempotent when the source has one row per key and the update logic is deterministic. Duplicate source keys make Delta raise an error, and timestamps in the SET clause change rows on every run.

How do you run CDC when the source has no updated_at column?

Land a full snapshot, hash the non-key columns of each row and MERGE on the primary key. A new key is an insert. A changed hash is an update. A key missing from a complete snapshot is a delete.

Why are idempotent data pipelines critical for AI and LLM data preparation?

Models and RAG pipelines inherit whatever the tables hold. Duplicate rows overweight some records in training data, doubled or stale documents give an LLM conflicting context, and a training table that changes on rerun can’t be reproduced. Idempotent loads keep the data behind each model version stable and explainable.

How does Databricks Unity Catalog support idempotent CDC pipelines?

Unity Catalog doesn’t make a pipeline idempotent on its own, but it governs everything around it. Column-level lineage in system tables shows every table a backfill will touch, and privileges keep write access to archive tables with the pipeline alone. Its key constraints are informational, so pair them with an enforced uniqueness test.