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.
- Run 1: INSERT ordersNetwork socket drops before acknowledgment
- Retry 1: INSERT ordersScheduler retries identical task
- 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.
| Word | What it means |
|---|---|
| Idempotent | Running it twice leaves the same result as once |
| Append | Add rows to the end of a table |
| Overwrite | Replace what was there |
| Partition | One slice of a table, such as one day |
| Merge | Update rows that exist and insert the new ones |
| Retry | Running 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.
- 45,000 rows committedDatabase writes records successfully
- Socket timeout (300s)Airflow marks task as failed
- 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.
| Strategy | SQL / Implementation Pattern | When to Choose It |
|---|---|---|
| Atomic Partition Overwrite | INSERT OVERWRITE orders PARTITION (order_date = '2026-10-10') | Daily or hourly partitioned tables where the entire time slice is recomputed. |
| Staging Table + Swap | CREATE OR REPLACE TABLE orders AS SELECT ... FROM staging | Full-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 View | QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1 | Append-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.
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.
-- 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.
| Dimension | Full Refresh (Rebuild from scratch) | Incremental Load (High-watermark / CDC) |
|---|---|---|
| Compute & Cloud Cost | High. Scans and processes full historical volume on every run. | Low. Processes only changed records since last execution. |
| Pipeline Complexity | Low. No state tracking, no lookback buffers, no tombstone deletes. | Higher. Requires watermark state storage, deduplication, and merge keys. |
| Late Data & Deletes | Self-healing. Rebuilding automatically captures historical updates and deletes. | Requires explicit tombstone handling (soft deletes) and lookback buffers. |
| Schema Evolution | Simple. 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.
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 Iteration | Incoming Records | Processed Rows | Final Distinct Orders in Table |
|---|---|---|---|
| First Execution | 2 records (id 101, 102) | 2 inserts | 2 rows in table |
| Automatic Retry | 2 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.
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.
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.
- Never run blind INSERT INTO queries on target tables that can experience retries.
- Use INSERT OVERWRITE for partitioned batch slices and MERGE for mutable entity tables.
- Add a lookback buffer to high-watermark queries so late-committing rows are not lost.
- Pair overlapping lookback extracts with upserts to avoid duplicate rows.
- 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.
Load: Night: 45,000 orders are extracted and written to the warehouse with a plain INSERT. The write succeeds.
- INSERT 45,000
- committed
Scheduler: The network drops the acknowledgement. After 300 seconds Airflow marks the task failed and retries it.
- no ack
- timeout
- retry
Result: The retry inserts the same 45,000 rows again. The table now holds 90,000 and revenue is doubled.
- 90,000 rows
- GMV x2
Fix: An idempotent write fixes it: overwrite the day's partition, or MERGE on the order key, so running twice leaves one copy.
- overwrite partition
- or MERGE on key
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.
- watermark
- lookback 2h
Python: The sink class stores rows by order_id, so loading the same batch twice still gives 2 rows, not four.
- keyed store
- 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.
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.
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 statusstatus 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.
- Order DAG tasks so every dependency runs firstProduction ticket: A scheduler can't just run tasks in the order they were declared - it has to respect the dependency graph.Studiointermediatepython15 minLocked
- Distinct departmentsInterview-style drill: List unique department names.Studiobeginnersql8 min
- Deduplicate events with ROW_NUMBERInterview-style drill: Keep the first event_id for duplicate user/type/time rows.Studiointermediatesql12 min