Can You Re-run This Safely?

Picture the orchestrator retrying a task at 02:14. The first attempt wrote 6 million of the day's 20 million rows into silver.orders and then lost its connection. The retry starts from the beginning, reads all 20 million rows from bronze, and appends them. The table now holds 26 million rows for 20 October, 6 million of them twice. Nothing errored on the second attempt, so nothing paged. A pipeline is idempotent when running it 5 times leaves the table in the same state as running it once, and that property is what makes retry safe to turn on.

Pattern 1: overwrite the partition

The most direct form of idempotency is to make the unit of work a whole partition and replace it. The run deletes everything it owns, which is the one day it is processing, and writes that day again from the source. Run it twice and the second run deletes the first run's 20 million rows before writing its own 20 million.

DELETE FROM silver.orders
WHERE dt = DATE '2026-10-20';

INSERT INTO silver.orders
SELECT
  order_id,
  customer_id,
  order_total,
  currency,
  updated_at,
  dt
FROM bronze.orders
WHERE dt = DATE '2026-10-20'

Two details decide whether this holds. First, the filter must use the run's logical date, which the orchestrator passes in, rather than CURRENT_DATE. A retry at 00:30 the next morning with CURRENT_DATE would delete and rewrite the wrong day. Second, there is a window between the DELETE and the INSERT where a reader sees an empty partition. If consumers query during the load window, write into a staging table and swap it in as the final step, which section 4 builds on.

Pattern 2: MERGE on the business key

Partition overwrite struggles when an update arrives for a row that lives in a partition from 3 weeks ago. Rewriting every partition in that range to apply 200,000 changed rows is wasteful, and change data capture feeds produce exactly that shape of input. MERGE matches incoming rows to existing rows on a key, updates the ones that exist, and inserts the ones that do not. A replay matches every row the first run wrote, and the updated_at guard below skips them, so they stay unchanged. The source has to hold one row per key first: if staging.orders_changes carries 2 versions of one order_id, which change feeds deliver routinely, the MERGE fails on the double match, so run the ROW_NUMBER deduplication from section 2 before merging.

MERGE INTO silver.orders AS target
USING staging.orders_changes AS source
  ON target.order_id = source.order_id
WHEN MATCHED AND source.updated_at > target.updated_at THEN
  UPDATE SET
    order_total = source.order_total,
    currency = source.currency,
    updated_at = source.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, order_total, currency, updated_at, dt)
  VALUES (source.order_id, source.customer_id, source.order_total, source.currency, source.updated_at, source.dt)

The updated_at guard matters for replays out of order. If the feed redelivers Monday's change after Tuesday's already applied, the guard keeps Tuesday's values. Without it, MERGE is idempotent for a single batch but not for the sequence of batches.

The numbers point at the decision. When changes arrive as whole days and consumers tolerate a staged swap, overwrite the partition and skip the key requirement. When changes arrive as individual rows spread across history, MERGE on a stable key and partition the target so the merge prunes to the partitions the batch touches rather than joining against 2 billion rows.

The test to say out loud

Interviewers want to hear how you would prove it. Run the pipeline for 20 October, snapshot the row count and a checksum of the partition, run it again, compare. The diff comes back empty, and that sentence is your evidence. The pattern's advanced tier spends a whole lesson on this proof, so at this depth, naming the test and the expected outcome is the mark to hit.

• 0 rows

About this lesson

This section is part of the Make It Reliable: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.