What is an idempotent ETL job and why does it matter?

Short answer

An idempotent ETL run produces the same logical target state when repeated on unchanged inputs, rather than appending duplicate records or reapplying corrections.

Why

Retries are inevitable when a transfer times out or a worker crashes after writing only part of a table. Design ingestion around stable source keys, deterministic transforms, validated staging outputs and an atomic publish or upsert step. Replaying the same run should not create duplicate sample rows or multiply a unit conversion.

Track input hashes and workflow run IDs so the system can distinguish a true new version from a retry. Test repeated execution, interruption at different stages and late-arriving corrections. Be careful that upstream data changes may legitimately produce a different result.

Idempotence applies to a specified input snapshot and transformation configuration. Logging a job as successful while leaving partially published files is not an idempotent design.

Common mistakes

Rerunning an append-only ingestion after failure and silently creating duplicate scientific observations

The interviewer might ask next

How would you test retry safety if a pipeline fails after writing 70% of its rows?