What is an idempotent pipeline?

In one sentence: an idempotent pipeline gives the same result no matter how many times it reruns on the same input, so a retry after a crash never duplicates, corrupts, or drifts from what one clean run would have produced.

That property is what makes automated retries safe. A network blip, a timeout, an out-of-memory worker, a scheduler double-trigger: any of them can cause a step to run twice, and idempotency is what stops the second run from making a mess.

How it works generically

Idempotency in a pipeline usually comes from one of a few mechanisms: a stable identifier per row that a merge or upsert step checks before writing, an overwrite-not-append write pattern, or a watermark cursor that tracks exactly how far a run got so a retry resumes from the same point instead of duplicating already-written rows. None of these are automatic. Each has to be built into the write path on purpose.

A quick check

A pipeline write step is idempotent when it has all three:

• A stable key per row, something a merge or upsert can check before writing, not just a row's position in a batch.
• A write pattern that isn't blind append: upsert, merge, or a full overwrite of the affected partition or range.
• A cursor or watermark, so a retry knows how far the last run got instead of reprocessing or skipping.

Before (breaks on rerun, a second run doubles every row):

INSERT INTO orders (order_id, amount)
SELECT order_id, amount FROM staging_orders;

After (safe to rerun, a second run changes nothing):

MERGE INTO orders AS target
USING staging_orders AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET amount = source.amount
WHEN NOT MATCHED THEN INSERT (order_id, amount) VALUES (source.order_id, source.amount);

The difference is the key: the first query has no way to know a row already landed, so it appends again. The second checks order_id before writing, so running it twice leaves the table exactly where one run would have.

Why it matters

Without idempotency, a rerun after a partial failure leaves duplicate rows in a table and forces someone to manually clean up before retrying. With it, retrying is the fix, not a new problem to debug. That is the whole payoff: a scheduler can retry a failed step automatically, with no human deciding whether it is safe first.

How OptimaFlo handles this today

Bronze ingestion is watermark-based: each source tracks a stored cursor, an Iceberg table property, and only pulls rows newer than that cursor on each run, always appending, never overwriting. For REST sources, a client-side dedup filter checks incoming rows against what is already ingested by cursor field before writing. That filter is documented internally as a backstop, not the primary guarantee, and OptimaFlo's own code comments flag one ingestion mode as an incomplete case where duplicates can still occur. Warehouse-level write methods are explicit that they do not enforce source-key uniqueness themselves: the caller is responsible for pre-deduping the source. Idempotency in OptimaFlo today is watermark-driven and best-effort at the row level, not a blanket guarantee across every engine and mode.

Related terms

• Exactly-once delivery: The stronger guarantee idempotency is often confused with: no duplicates and no drops, not just safe-to-rerun.
• Incremental data loading: Idempotency is what makes incremental loads safe to retry without reprocessing everything from scratch.
• Data pipeline: The thing idempotency is a property of, not a pipeline type on its own.