04 - Idempotent Pipeline Design

10 min read

Idempotent Pipeline Design

Quote

“An idempotent operation can be applied multiple times without changing the result beyond the initial application — this is the foundation of reliable data processing.”

— Tyler Akidau (Apache Beam tech lead)

Why Idempotent Pipeline Design Matters

Without idempotency, every pipeline failure becomes a crisis:

  • Re-running a failed load creates duplicate rows
  • Backfilling historical data corrupts existing records
  • Retrying after a timeout inserts partial + duplicate data
  • Testing in production is impossible without risking data corruption

With idempotency, you can re-run any step at any time with confidence.

Core Patterns

DELETE-INSERT (Partition Swap)

Delete all data for the target partition, then insert fresh data. The partition key (usually a date) scopes the delete. The bronze-layer-loading module uses this exact pattern to reload daily partitions safely.

-- Idempotent daily load: delete today's data, then re-insert
BEGIN TRANSACTION;
DELETE FROM silver.daily_prices WHERE trade_date = @trade_date;
INSERT INTO silver.daily_prices (trade_date, ticker, close_price, volume)
SELECT trade_date, ticker, close_price, volume
FROM bronze.raw_prices
WHERE trade_date = @trade_date;
COMMIT;

Why DELETE-INSERT over TRUNCATE

DELETE with a WHERE clause is partition-scoped — it only affects the target date. TRUNCATE removes ALL data and cannot be rolled back inside a transaction. Use TRUNCATE only for full-reload patterns on small tables.

DELETE-INSERT with Foreign Keys

If gold tables have foreign keys referencing silver, a DELETE-INSERT on silver cascades the DELETE to gold — destroying gold data. Either drop FKs before the load (re-create after), disable the FK constraint temporarily (ALTER TABLE NOCHECK CONSTRAINT), or use MERGE instead of DELETE-INSERT when FK relationships exist.

Use MERGE instead of DELETE-INSERT when FK relationships exist

MERGE INTO silver.table AS target USING source ON target.key = source.key ... updates and inserts in-place without deleting existing rows, so FK-referencing gold tables are never touched during the load. If DELETE-INSERT is required for correctness, disable the FK constraint with ALTER TABLE gold.table NOCHECK CONSTRAINT ALL and re-enable it after the load.

MERGE (Upsert)

Match on a business key. Update if exists, insert if new. See merge-and-upsert for the full T-SQL MERGE pattern. In dbt, the incremental materialization generates a MERGE statement under the hood, providing idempotency declaratively.

MERGE INTO silver.index_dim AS target
USING bronze.raw_index_data AS source
ON target.index_code = source.index_code
WHEN MATCHED THEN UPDATE SET target.display_name = source.display_name
WHEN NOT MATCHED THEN INSERT (index_code, display_name) VALUES (source.index_code, source.display_name);

MERGE Without a Partition Filter Can Full-Scan the Target Table

A MERGE INTO silver.index_dim without a WHERE clause on the source CTE scans every row in both the source and target. On a 100M-row table, this turns a 2-second incremental load into a 30-minute full scan. Always scope the MERGE source to the current partition (e.g., WHERE trade_date = @trade_date) and ensure the target has a matching index on the join key.

Scope the MERGE source CTE to the current partition and index the join key

Wrap the MERGE source in a CTE with WHERE trade_date = @trade_date. Ensure the target table has a covering index on (trade_date, index_code) — the join key columns. This reduces both source and target scans to a single date partition, keeping incremental loads fast regardless of table size.

Idempotency Is Not Exactly-Once

Idempotency means “safe to re-run.” Exactly-once means “processed exactly one time.” A MERGE upsert is idempotent (re-running produces the same result) but executes MORE than once on retry. In streaming systems (Pub/Sub, Kafka), exactly-once requires deduplication at the consumer — the message may be DELIVERED multiple times but must be PROCESSED only once. Idempotent writes make exactly-once achievable: if the write is idempotent, duplicate deliveries don’t corrupt state.

Use idempotency keys to achieve exactly-once semantics in streaming

Maintain a pipeline.processing_log table with an idempotency_key PRIMARY KEY. Before processing a message, check whether the key already exists with status = 'SUCCESS'. If it does, skip processing. If not, process and insert the key in the same transaction. Idempotent writes (MERGE) plus idempotency key tracking together guarantee that duplicate deliveries produce no duplicated side effects.

Staging Table Pattern

Load into a staging table first, then atomic swap into the target:

  1. TRUNCATE staging table
  2. Bulk load new data into staging
  3. BEGIN TRANSACTION
  4. DELETE target partition
  5. INSERT INTO target SELECT FROM staging
  6. COMMIT

This isolates the slow I/O (bulk load) from the fast atomic swap.

TRUNCATE Cannot Be Rolled Back Inside a Transaction in SQL Server

TRUNCATE TABLE is minimally logged and cannot be wrapped in an explicit transaction for rollback purposes in all isolation levels. If the INSERT after TRUNCATE fails, the staging table is empty with no recovery path. Use DELETE FROM staging (which is transactional) instead of TRUNCATE when the staging table participates in a multi-statement transaction, or accept that TRUNCATE on the staging table is safe because staging is always repopulated.

Use DELETE FROM staging instead of TRUNCATE when inside a multi-statement transaction

DELETE FROM staging_table (without a WHERE) is fully logged and rolls back cleanly if the subsequent INSERT fails. The table is empty either way when the transaction succeeds — the behavior is identical to TRUNCATE, but with transactional safety.

Idempotency Anti-Patterns

Anti-PatternProblemFix
INSERT without duplicate checkRe-run creates duplicate rowsUse MERGE or DELETE-INSERT
No transaction around multi-step loadPartial failure leaves inconsistent stateWrap in explicit transaction
Using IDENTITY columns as business keysCannot match records across re-runsUse natural business keys for matching
Appending timestamps without dedupSame data with different load timestampsDeduplicate on business key before insert

Related pattern

Without idempotency, concurrent pipeline runs can trigger race-conditions — two instances inserting the same partition simultaneously, producing duplicates or deadlocks. Idempotent designs eliminate this class of failure by making the outcome independent of execution order.

Graph View

Backlinks