A pipeline step is idempotent when running it twice with the same input leaves the same result as running it once. Idempotency makes retries safe: a failed job can be re-run without duplicating rows, double counting or sending the same record twice. It usually comes from replacing instead of appending, merging on a key, or tracking what was already processed.
For example, a job loads yesterday's sales into a daily table by appending rows. It fails after writing half the rows, and the scheduler retries it. With an append, the table now holds the first half twice. If the job instead deletes yesterday's partition and writes it again in one transaction, or merges on the order id, the retry leaves the table exactly as one successful run would have. The second design is idempotent; the first is not.
Idempotency matters most at the edges of a pipeline. Loads from a source can be repeated when a file is delivered twice. Writes to other systems, such as an API that creates support tickets or sends emails, cannot be rolled back, so they need a key the receiving system uses to recognize a repeat, often called an idempotency key, or a record of what was already sent. Steps that depend on the current time, random numbers or the order of rows are common sources of results that differ between runs.
A simple test is to run a step twice on the same input and compare the result with one run. If the two differ, retries, backfills and recovery after failures all produce surprises. See also deduplication, watermark and incremental load.
On data platforms, the usual tools for idempotent writes are a merge on a key, replacing a whole partition, or writing to a new table and swapping it in. Each makes the result of a step depend only on its input, not on how many times it ran, which is the property that makes automatic retries safe. See also partitioning.
Emolysis's golden tests check it directly, for example by pushing the same Reverse ETL batch twice, or by running a backfill twice.