Idempotency in data ingestion pipelines

WHAT THIS TESTS This probes whether you build pipelines that tolerate the inevitable duplicate processing caused by retries and at-least-once delivery, a core reliability principle.

A GOOD ANSWER COVERS Idempotency means an operation can be applied multiple times and leave the system in the same state as a single application. In a pipeline, that means reprocessing the same input, whether a message, file, or batch, does not create duplicate rows, double-counted metrics, or other drift. It matters because failures are normal: a consumer crashes after writing but before acknowledging, a network blip triggers a retry, or a queue guarantees only at-least-once delivery, so the same record will eventually be handled more than once. If each stage is idempotent, you can safely retry on failure, which is the foundation of reliable recovery. A concrete technique is to give each record a stable, deterministic key, a natural business key or a hash of the content, and write with an upsert or insert-if-not-exists so a repeat write overwrites or no-ops rather than appends. Alternatively, track which input batches or offsets have already been committed in a dedupe table and skip ones already seen, or write to a deterministic destination path so re-running overwrites the same output.

COMMON WRONG ANSWERS Assuming exactly-once delivery exists end to end and therefore ignoring duplicates. Using a blind append insert that doubles rows on retry. Generating a new random ID per attempt, defeating deduplication. Confusing idempotency with simply retrying.

LIKELY FOLLOW-UPS How does this enable exactly-once effects on top of at-least-once delivery. Where do you store dedupe state. How do upserts perform at scale.

ONE CONCRETE EXAMPLE An ingestion job derives a key from order_id and writes with an upsert into the target table. If the job retries after a partial failure and reprocesses the same file, the upsert overwrites the existing rows instead of inserting duplicates, so the final counts stay correct.