Build an Idempotent Data Pipeline: Retries Without Duplicate Records

Show what happens when a pipeline runs twice

A data pipeline portfolio is more informative when it demonstrates failure handling as well as a successful run. A useful project question is simple: if the same input is processed again, will the destination still represent the intended records? Design an example that makes that behaviour visible.

For this project, define idempotency as repeated processing of the same logical input producing the same intended destination state. State the boundary clearly. A retry-safe database write does not automatically make notifications, external API calls or every other side effect safe.

Define identity before implementing the load

Choose a small synthetic dataset, such as order updates. Give each order a stable identifier and each change a timestamp or version. Document how you distinguish a duplicate event from a legitimate update, and decide what to do when two updates arrive out of order.

Avoid relying on ingestion time as the only definition of which record is newest. A delayed older event can arrive after a newer one. Your project should explain which source field establishes ordering, what assumptions it relies on and how unresolved conflicts are recorded.

Make writes and checkpoints agree

Use database constraints to enforce the identity rules you have defined. PostgreSQL supports conflict-handling options through INSERT ... ON CONFLICT. Choose the conflict action deliberately; ignoring a duplicate and applying a newer version are different requirements.

Plan the relationship between the destination write and the ingestion checkpoint. If the write succeeds but the checkpoint does not, the next attempt may replay data. If the checkpoint advances before the write is durable, a failure may skip work. Explain the transaction boundary and the recovery behaviour in your chosen design.

Test failures that reveal the design

Build tests for a repeated batch, a partial failure, an older update arriving late and an input with a missing identifier. Record both row counts and field values. A stable row count alone does not prove the correct version survived.

Keep the demonstration reproducible with a small setup script and clear sample inputs. Show the expected destination state before running the test, then compare it with the actual result. Include rejected records or a failure log so the reader can see how bad input is handled.

Explain the project in a data engineering interview

Present the requirement, identity rule, write strategy and failure tests before discussing tools. State what the project does not cover, such as distributed coordination or external side effects. This makes the limits of your implementation clear. Explore data roles with Huwell and connect the exercise to the responsibilities in each vacancy.