Idempotent Data Pipelines: Why They Matter and How to Build Them

Your ETL pipeline runs nightly at 2 AM. One night the network connection to the source database drops midway through, the job fails, your orchestrator retries at 3 AM, and the job succeeds. What is in your destination table? If the answer is "it depends," you have a reliability problem. If the answer is "exactly the same rows as if the job had succeeded the first time," you have an idempotent pipeline.

Idempotency is the single most important property of a reliable data pipeline. It is also the property most commonly skipped when teams are building fast.

What Idempotency Means in Data Pipelines

An idempotent pipeline produces the same result whether it runs once or multiple times against the same input. Run it twice and you get the same data. Retry a failed run and you get the same data as if it had never failed.

This matters because pipelines fail. Networks drop, APIs timeout, warehouses throttle, disks fill up. Retries are not an edge case — they are routine. A pipeline that produces correct data only when it runs exactly once is not a reliable pipeline.

Why Non-Idempotent Pipelines Break in Predictable Ways

The most common non-idempotent anti-pattern is unconditional INSERT. Every run appends new rows to the destination table, regardless of whether those rows already exist. A retry after a partial failure appends a second copy of the rows that were written before the failure.

The result: duplicate rows in your destination table that downstream queries will double-count unless they are explicitly deduplicated. And they rarely are, because the pipeline was supposed to be idempotent.

Other common failure modes:

• Aggregations that accumulate. A pipeline that increments a running total without checking whether the current period has already been counted.
• Log tables without deduplication. Event logs that record each processing run as a new entry, even for the same source events.
• Multi-step pipelines with partial retries. Step 1 transforms data, step 2 loads it. If step 2 fails and the pipeline retries from step 1, the transformation may produce different results if step 1 mutates a shared state.

Patterns for Building Idempotent Pipelines

Upsert (Merge) Instead of Insert

Replace INSERT with an UPSERT (also called MERGE in SQL). On each run, insert new rows and update existing rows based on a primary key. Running the same upsert twice produces the same result as running it once.

Most warehouses support merge syntax. Snowflake, BigQuery, and Redshift all have MERGE or equivalent. In dbt, the incremental materialization with a unique key handles this.

Partition-Based Delete-Then-Insert

For time-series data, partition your destination table by date. On each run:

1. Delete all rows for the current processing partition (e.g., today's date).
2. Insert the full set of rows for that partition from the source.

If the run fails and retries, step 1 removes any partially written rows, and step 2 rewrites them completely. The result is always the same regardless of how many times the pipeline runs.

This pattern is safe because the partition is the unit of atomicity. Rows outside the current partition are never touched.

Write to a Staging Table, Then Swap

Instead of writing directly to the production table:

1. Write output to a staging table.
2. Validate the staging table.
3. Atomically swap the staging table into production (rename or replace).

If any step fails, the production table is unchanged. The next retry starts fresh by overwriting the staging table. No partial writes reach production.

Idempotent State Tracking

Track pipeline run state in a metadata table: which partitions have been successfully processed, what the last high-water mark was, which runs are in-progress versus complete. On each run, check state first to determine what to process and where to write.

This is more complex to implement but supports more sophisticated recovery logic, including the ability to re-run specific historical partitions without affecting others.

The High-Water Mark Pattern

For incremental pipelines, idempotency requires careful management of the high-water mark — the maximum value of the incremental key (usually a timestamp or ID) from the last successful run.

Wrong approach: Update the high-water mark at the start of the run with the current timestamp. If the run fails, the high-water mark has advanced past the data that was not loaded, and that data is silently skipped on the next run.

Correct approach:

1. Record the high-water mark that will be used for this run before the run starts.
2. Run the pipeline using that high-water mark.
3. Only update the stored high-water mark after the run completes successfully.

This ensures that a failed run does not advance the high-water mark, so the next run reprocesses the same window.

Idempotency and Streaming Pipelines

Idempotency is harder but equally important in streaming pipelines. Common approaches:

• Exactly-once delivery at the message broker level (Kafka supports this with transactional producers and consumers).
• Deduplication at the consumer level — store a record of processed message IDs and skip duplicates on retry.
• Idempotent write operations at the sink — use upserts rather than appends when writing to the destination.

Most real-world streaming systems implement at-least-once delivery and handle deduplication at the consumer or destination layer.

Testing for Idempotency

Idempotency is easy to test and rarely is. Add a test to your pipeline's test suite:

1. Run the pipeline once against a test dataset.
2. Capture the state of the destination table.
3. Run the pipeline again against the same test dataset.
4. Assert that the destination table state is identical after both runs.

This test catches most idempotency bugs before they reach production. It is particularly valuable to run this test against a staging environment with production-like data.

The Cost of Getting This Wrong

Non-idempotent pipelines accumulate errors silently. Duplicate data builds up over weeks or months. By the time the issue is discovered, auditing and cleaning the affected tables requires significant engineering effort, and downstream reports and dashboards may need to be recalculated.

Building idempotency in from the start is an investment of a few hours. Retrofitting it into a production pipeline with months of accumulated duplicate data is a much larger project.

If you are building data infrastructure and want to get the reliability fundamentals right from day one, Clixo builds production data pipelines for product and engineering teams.