Question

To ensure an ETL pipeline is idempotent, what specific design pattern must be applied to ensure that re-running a process after a mid-stream failure does not create duplicate entries in the target table?

Accepted Answer

To ensure an ETL pipeline is idempotent, you must apply the Upsert design pattern. An idempotent process is one that produces the same final state in the target system regardless of how many times it is executed. The Upsert pattern, a portmanteau of update and insert, achieves this by checking for the existence of a record before writing it. If a unique identifier for a record already exists in the target table, the process performs an update to the existing row with the new data. If the identifier does not exist, the process performs an insert to create a new row. To implement this, you must define a natural key or a surrogate key as a unique constraint on the target table. When the ETL process runs, it compares the incoming data against the existing keys in the destination. By using a merge operation or a staging table combined with a delete-and-insert strategy, you ensure that re-running the pipeline after a failure simply overwrites or reconciles the data rather than appending duplicates. For example, if a job fails halfway through, the subsequent re-run will recognize the previously processed records by their unique IDs and update them instead of creating new entries, leaving the target table in the correct, intended state.

Home → All Courses → Engineering and Technology Courses → Business Intelligence Engineering → Flashcard

To ensure an ETL pipeline is idempotent, what specific design pattern must be applied to ensure that re-running a process after a mid-stream failure does not create duplicate entries in the target table?