Lesson 2 of 3 in this module

8/14 in track

Idempotent loads

python14 min

Overview

Run the load twice, get the same gold table. A key you have already written is a skip, not a second insert.

On this page8 sections

What happened

At 07:40 on Tuesday, the finance lead opens the daily dashboard and sees yesterday's revenue is exactly double the expected amount. Nobody launched an extra job or altered numbers. The nightly load simply ran twice.

The first run succeeded in writing its records to the warehouse, but the network connection dropped before Airflow received the success acknowledgement. The scheduler assumed the task failed and triggered an automated retry, which inserted the same 45,000 orders a second time.

Idempotency in data pipelines

An operation is idempotent if executing it multiple times produces the exact same system state as executing it once.

An idempotent task leaves the target table in the identical state whether it runs once or 5 times.

If a nightly job encounters a network timeout after loading rows, the orchestrator retries the task. Idempotent sinks ensure that repeated runs never duplicate data.

Why blind appends fail on retry

Most beginner pipelines fail because they rely on blind append operations like INSERT INTO orders. Every retry or backfill inserts another copy of every transaction.

  1. Run 1: INSERT ordersNetwork socket drops before acknowledgment
  2. Retry 1: INSERT ordersScheduler retries identical task
  3. Result: Double rowsDaily metrics and revenue double in dashboards

Fixing duplicate rows in large warehouses requires halting downstream analytics while engineers run expensive deduplication scripts.

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
IdempotentRunning it twice leaves the same result as once
AppendAdd rows to the end of a table
OverwriteReplace what was there
PartitionOne slice of a table, such as one day
MergeUpdate rows that exist and insert the new ones
RetryRunning a failed task again

Why it broke (or worked)

A timeout that doubled revenue

Suppose a nightly Airflow DAG extracts orders from PostgreSQL and loads them into Snowflake with a plain INSERT. The numbers in this example are assumptions chosen to make the arithmetic easy.

A lost acknowledgement can turn one load into two.

If the INSERT succeeds but the acknowledgement is lost, Airflow waits until its timeout (assume 300 seconds), marks the task failed, and retries it.

The cost of missing write boundaries

Because the pipeline used simple INSERT statements, the retry inserts the same 45,000 rows again, and gross merchandise value for that load is doubled.

  1. 45,000 rows committedDatabase writes records successfully
  2. Socket timeout (300s)Airflow marks task as failed
  3. Retry appends 45,000 rows90,000 rows now in analytical table

Someone then has to find the duplicate keys and rebuild the daily totals, which is the cost of a load that was not safe to retry.

The pattern to steal

Four idempotent write strategies

Senior data engineers use four common patterns to make loads safe to retry across batch and streaming pipelines. Each pattern still needs a stable key, a clear update rule, and a destination that enforces the rule.

Architectural strategies for implementing idempotent data loads in production pipelines.

StrategySQL / Implementation PatternWhen to Choose It
Atomic Partition OverwriteINSERT OVERWRITE orders PARTITION (order_date = '2026-10-10')Daily or hourly partitioned tables where the entire time slice is recomputed.
Staging Table + SwapCREATE OR REPLACE TABLE orders AS SELECT ... FROM stagingFull-table refreshes or batch dimensional tables where table recreation is fast.
ACID Upsert (MERGE)MERGE INTO target USING staging ON target.id = source.id ...Incremental loads with updates to existing records (such as order status changes).
Deterministic Dedup ViewQUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1Append-only bronze lakes where deduplication is resolved at read time via silver views.

Atomic partition overwrites and MERGE statements ensure that duplicate executions replace existing rows instead of stacking copies.

Partition overwrite and MERGE patterns

Whether a task runs once or 5 times, writing to a partition or running a key-based merge leaves the table in the exact same state.

Idempotent write patterns: Partition overwrite and MERGE
Extract raw deltabatchStage in temporarytableExecute atomic MERGEor OVERWRITEVerifieddeterministic state

Whether a task runs once or 5 times, the target partition reflects the exact same final state.

Staging the incoming batch first lets you validate schema before committing changes to the production target table.

High-watermark extracts and lookback buffers

In high-volume systems, full table extracts are too slow. Pipelines track a high-watermark timestamp to extract only new records since the last run.

Delayed transactions can commit with earlier timestamps after the watermark query runs. Adding a lookback buffer recaptures those delayed rows.

SQLIncremental high-watermark extraction with a 2-hour safety lookback buffer (illustration, not run here)
-- Production incremental extract with lookback buffer
SELECT order_id, customer_id, order_total, order_status, updated_at
FROM source_orders
WHERE updated_at >= (:last_high_watermark - INTERVAL '2 hours')
  AND updated_at < :current_run_start_time;

Because the lookback window pulls overlapping rows already processed yesterday, the target write must be an idempotent MERGE or OVERWRITE.

Full refreshes versus incremental loads

Deciding between a full refresh and an incremental load requires balancing cloud compute costs against pipeline operational complexity.

Engineering trade-off matrix: Full Refresh versus Incremental data loads.

DimensionFull Refresh (Rebuild from scratch)Incremental Load (High-watermark / CDC)
Compute & Cloud CostHigh. Scans and processes full historical volume on every run.Low. Processes only changed records since last execution.
Pipeline ComplexityLow. No state tracking, no lookback buffers, no tombstone deletes.Higher. Requires watermark state storage, deduplication, and merge keys.
Late Data & DeletesSelf-healing. Rebuilding automatically captures historical updates and deletes.Requires explicit tombstone handling (soft deletes) and lookback buffers.
Schema EvolutionSimple. Rebuilding the table applies new columns immediately.Requires careful migration scripts to avoid breaking existing target tables.

Keep dimension tables under 100,000 rows as full refreshes. Switch fact tables to incremental loads when full scans exceed your schedule window.

How you write it

Implementing an idempotent sink in Python

The Python class below demonstrates an idempotent sink. It stores records in a dictionary keyed by primary ID, overwriting duplicates cleanly.

PythonIdempotent sink implementation demonstrating identical state across retries
class IdempotentWarehouseSink:
    def __init__(self):
        self.storage = {}

    def load_batch(self, records):
        """Loads a batch of records idempotently using primary key upsert logic."""
        upserted_count = 0
        for record in records:
            key = record["order_id"]
            self.storage[key] = record
            upserted_count += 1
        return upserted_count

    def get_row_count(self):
        return len(self.storage)

sink = IdempotentWarehouseSink()
batch = [
    {"order_id": 101, "customer_id": 12, "status": "pending"},
    {"order_id": 102, "customer_id": 34, "status": "paid"},
]

# Run 1: Initial load
sink.load_batch(batch)
print("Row count after first load:", sink.get_row_count())

# Run 2: Simulated network retry of the exact same batch
sink.load_batch(batch)
print("Row count after retried load:", sink.get_row_count())
Row count after first load: 2
Row count after retried load: 2

Running load_batch a second time updates existing dictionary keys rather than appending new items, keeping row counts stable.

State consistency across retries

Trace how the target storage handles initial loads versus automated retries below.

Trace table demonstrating idempotent state consistency across automated retries.

Run IterationIncoming RecordsProcessed RowsFinal Distinct Orders in Table
First Execution2 records (id 101, 102)2 inserts2 rows in table
Automatic Retry2 records (id 101, 102)2 updates (overwrites)2 rows in table (zero duplicates)

The second run processes 2 records as updates, leaving final distinct orders unchanged at two.

Watch outAvoid non-deterministic generated keys

If your pipeline generates surrogate primary keys using UUID.uuid4() on every run, an upsert cannot match previous records because the UUID changes every time. Use deterministic hashing (such as MD5 or SHA256 of business keys) so retries generate identical identifiers.

RememberAlways include updated_at tiebreakers in MERGE

When merging incoming records that might arrive out of order, ensure your MERGE statement updates existing records only when source.updated_at >= target.updated_at to prevent older historical data from overwriting newer updates.

Idempotent load checklist

Review the lifecycle of an idempotent load before deploying retries or incremental extraction jobs.

  1. Never run blind INSERT INTO queries on target tables that can experience retries.
  2. Use INSERT OVERWRITE for partitioned batch slices and MERGE for mutable entity tables.
  3. Add a lookback buffer to high-watermark queries so late-committing rows are not lost.
  4. Pair overlapping lookback extracts with upserts to avoid duplicate rows.
  5. Verify retry safety by running the load step twice in testing and checking row counts.

Common beginner questions

Does INSERT OVERWRITE work in cloud warehouses like Snowflake and BigQuery?

Yes. In Snowflake, INSERT OVERWRITE INTO target SELECT ... atomically replaces the table or specific partition. In BigQuery, write_disposition='WRITE_TRUNCATE' achieves identical atomic replacement.

How do incremental pipelines handle hard deletes in source databases?

High-watermark timestamp filters cannot detect hard-deleted rows because deleted rows no longer exist in the source table. To track deletions, use Change Data Capture (CDC) with Debezium, or enforce soft deletes (setting is_deleted = TRUE in the source).

Can a pipeline be idempotent if it sends email notifications?

Side effects like sending emails or firing webhooks require separate idempotency keys. Store sent notification IDs in an audit table, and verify that notification_id does not exist before dispatching alerts.

2 runs, one table
  1. Load: Night: 45,000 orders are extracted and written to the warehouse with a plain INSERT. The write succeeds.

    1. INSERT 45,000
    2. committed
  2. Scheduler: The network drops the acknowledgement. After 300 seconds Airflow marks the task failed and retries it.

    1. no ack
    2. timeout
    3. retry
  3. Result: The retry inserts the same 45,000 rows again. The table now holds 90,000 and revenue is doubled.

    1. 90,000 rows
    2. GMV x2
  4. Fix: An idempotent write fixes it: overwrite the day's partition, or MERGE on the order key, so running twice leaves one copy.

    1. overwrite partition
    2. or MERGE on key
  5. Extract: Incremental extracts keep a high-watermark and look back 2 hours to catch late rows. That overlap is safe only because the write is idempotent.

    1. watermark
    2. lookback 2h
  6. Python: The sink class stores rows by order_id, so loading the same batch twice still gives 2 rows, not four.

    1. keyed store
    2. same state

What comes next

Now that you understand idempotent loads, lookback buffers, and incremental strategies, the next lesson explores deduplication before load: how to clean up dirty upstream batches using hash sets and row number ranking.

Practice

Implement IdempotentSink.load(key, row) so that an existing key returns 'skip' and a new key inserts and returns 'insert'. Load ORD-1 twice, then ORD-2 once.

Going further: a failure halfway through the load

A retry is only safe if a half-finished first attempt leaves nothing behind that the second attempt trips over. Appending is the usual cause of trouble. Overwriting the target in a transaction is the usual cure.

The load below writes 3 rows and crashes after 2. Predict the table after a retry with each method.

PythonCrash halfway, then retry
rows = ["r1", "r2", "r3"]

def append_load(table, crash_after=None):
    for i, row in enumerate(rows):
        if crash_after is not None and i == crash_after:
            raise RuntimeError("crash")
        table.append(row)

def replace_load(table, crash_after=None):
    staging = []
    for i, row in enumerate(rows):
        if crash_after is not None and i == crash_after:
            raise RuntimeError("crash")                 # staging is thrown away
        staging.append(row)
    table[:] = staging                                  # swap in all at once

for name, load in [("append ", append_load), ("replace", replace_load)]:
    table = []
    try:
        load(table, crash_after=2)
    except RuntimeError:
        pass
    load(table)                                          # the retry
    print(name, table)
append  ['r1', 'r2', 'r1', 'r2', 'r3']
replace ['r1', 'r2', 'r3']

The append version holds 5 rows: the 2 written before the crash, plus all 3 again. The replace version holds exactly 3. It builds the result in a staging area and swaps it in at the end, so a crash before the swap changes nothing, and running it again produces the same result.

Databases give you the same idea as a transaction: the delete and the insert either both happen or neither does.

SQLDelete and insert in one transaction
BEGIN;
DELETE FROM raw_orders WHERE status = 'paid';
INSERT INTO raw_orders VALUES (1,'paid',100),(2,'paid',250);
COMMIT;
SELECT status, COUNT(*) AS rows FROM raw_orders GROUP BY status ORDER BY status
status    rows
paid      2
refunded  1

Running that block again leaves the same 2 paid rows and 1 refunded row. The refunded row was never touched, because the delete only covers the slice this run owns. Defining the slice (a date, a batch id) is the design decision that makes idempotency possible.

Try a new scenario

Your daily load appends yesterday's orders to a table. It failed on Tuesday after loading some rows, and someone clicked retry. How do you find out if rows are now duplicated, and how do you fix the design?

Work it out, then open the answer

Group by the order key and look for keys that appear more than once, and compare the day's row count with the source. To fix the design, load into a staging table, then replace Tuesday's partition (delete that day and insert it in one transaction), or merge on the order key. Then a retry cannot add a second copy.

Interview: how would you make an append-only load idempotent?

Give each row a stable key and merge on it, or load into a staging table and replace the whole slice (a date partition) in one transaction. Either way, running the same load twice leaves the same table.

Close the lesson and explain it

Without scrolling up, explain why an append is not safe to retry, and what the staging-and-swap approach guarantees.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Practice this

Same ideas as interview drills. These challenges open in the studio with a dataset and tests already set up.

Rate:
Was this useful?