How I stopped fearing the 3 AM pager by forcing idempotency everywhere

In the world of data engineering, idempotency is a crucial concept that ensures a system can handle repeated runs of a job without causing unintended side effects. If a pipeline is not idempotent, it may not be ready for production use—it's essentially a fragile script prone to causing problems. In high-stakes industries like fintech and healthcare, idempotency can mean the difference between a minor glitch and a regulatory violation.

The author spent six years correcting the mistakes caused by "append-only" thinking. They witnessed millions of dollars in duplicate ACH transactions and patient records corrupted by excessive retries. To prevent such issues, they cover several patterns to promote idempotency in their pipelines.

First, they advise against relying on "append mode." Each job should be considered as a new event, not an addition to an already existing dataset. When using big data platforms like BigQuery, Snowflake, or Databricks, they recommend using MERGE or overwrite-on-partition instead of blindly inserting data. For SQL-based ELT workflows, they suggest writing transformations to a temporary table before swapping it into production. Never push directly to the target table.

Second, partitioning is essential for ensuring data integrity. If a pipeline runs daily, data should be partitioned by that day. Partition overwrites allow for simple atomic operations, turning complex delete and re-insert logic into a straightforward process. In Spark, this involves writing the data with an overwrite mode and specifying the partition key. This ensures that a failed job can simply be re-run, guaranteeing consistent state.

Third, deterministic execution IDs are vital for debugging and tracing. Each row should have a job_run_id and an ingestion_timestamp. By generating these IDs using a hash of the natural keys, you ensure that re-running the job updates the row rather than creating duplicates. The author provides a Python function to generate such IDs.

Fourth, adhering to the "No Side Effects" rule means that a pipeline job should only move data from one place to another. Any additional actions, such as sending alerts or updating caches, should be performed downstream using a state machine pattern. This allows for safe re-execution if needed. For instance, in a Postgres or Snowflake environment, you can wrap the deletion and insertion processes in a transaction block. If the insert fails, the delete statement is rolled back, leaving the system in its original state.

Fifth, when MERGE operations are impractical, the "Delete-Before-Insert" pattern can be used. This involves deleting the target data for the specific time range before inserting the new batch. In a PostgreSQL or Snowflake environment, this process can be wrapped in a transaction to ensure data integrity. If the insert fails, the delete statement will automatically roll back, restoring the system to its previous state.

Sixth, when fetching data from APIs, it's essential to make the process idempotent. Instead of simply dumping the raw response, use ETag or Last-Modified headers to check if the data needs to be fetched again. For local caching, Redis can be used to store IDs of processed records, preventing duplicate processing if the pipeline restarts.

Lastly, treating configuration as code, rather than relying on manual adjustments, is crucial. All pipeline configurations—source paths, target tables, look-back windows, etc.—should be stored in version-controlled config files like YAML. This allows for reproducibility and clarity when debugging or re-running jobs. By checking out the repository at a specific commit hash, you can review the configuration and understand precisely what the job was executing at the time of failure.