How I stopped fearing the 3 AM pager by forcing idempotency everywhere
If your pipeline isn't idempotent, it isn't production-ready; it’s just a fragile script waiting to ruin your weekend. Most engineers treat "idempotency" as an academic concept for distributed systems papers, but in the trenches of fintech and healthcare, it’s the difference between a minor blip and a regulatory filing. If you can’t run your job five times in a row with the exact same input and get the exact same state, you aren't doing data engineering—you're doing data gambling. I’ve spent six years cleaning up the messes left by "append-only" thinking. I’ve seen millions of dollars in duplicate ACH transactions and patient records corrupted by "just one more retry" logic. This guide covers the patterns I use to make sure that when the scheduler kicks off at 3 AM, I can sleep through the alarm because the system knows how to fix itself. 1. Stop relying on "Append" mode The biggest sin in data engineering is assuming that your destination table is a clean slate. When a job fails halfway through, you don't want a partial load sitting in your production warehouse. Never use INSERT INTO blindly. If you are using BigQuery, Snowflake, or Databricks, use MERGE or overwrite-on-partition. If you are using SQL-based ELT, write your transformations to stage data in a transient table before swapping it into production. Never push directly to the target. -- The wrong way: INSERT INTO target_table SELECT * FROM staging -- The right way: Use an atomic swap or a MERGE statement MERGE INTO production . transactions AS T USING staging . transactions AS S ON T . transaction_id = S . transaction_id WHEN MATCHED THEN UPDATE SET T . amount = S . amount , T . status = S . status WHEN NOT MATCHED THEN INSERT ( transaction_id , amount , status ) VALUES ( S . transaction_id , S . amount , S . status ); Photo by 🇻🇪 Jose G. Ortega Castro 🇲🇽 on Unsplash 2. Partitioning is your safety net If your pipeline runs daily, your data must be partitioned by that day. If you are loading data without a