Case 5 — Idempotency Refactor

# Case 5 — Idempotency Refactor ## The brief The "daily customer summary" pipeline accumulates duplicates on retry. Every time Airflow retries the task, another row is added. Refactor to be idempotent. ## The setup Broken pipeline (provided): ```python @task def daily_customer_summary(): today = datetime.utcnow().date() rows = db.query(''' SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS revenue FROM orders WHERE order_date = %s GROUP BY customer_id ''', today) for row in rows: db.execute(''' INSERT INTO customer_summary (date, customer_id, orders, revenue) VALUES (%s, %s, %s, %s) ''', today, row.customer_id, row.orders, row.revenue) ``` Failure mode: task succeeds, but Airflow worker dies before marking it done. Airflow retries. Now `customer_summary` has duplicate rows for today. ## The task ### Step 1: Identify all sources of non-idempotency (15 min) Walk through the code and list: - The INSERT statement appends without dedup. - No DELETE for today's existing rows. - No transaction; partial inserts on failure leave inconsistent state. ### Step 2: Refactor for idempotency (20 min) Refactor with the DELETE-then-INSERT pattern: ```python @task def daily_customer_summary(execution_date): with db.transaction(): db.execute('DELETE FROM customer_summary WHERE date = %s', execution_date) db.execute(''' INSERT INTO customer_summary (date, customer_id, orders, revenue) SELECT %s, customer_id, COUNT(*), SUM(amount) FROM orders WHERE order_date = %s GROUP BY customer_id ''', execution_date, execution_date) ``` Use execution_date (not today), so backfills work. ### Step 3: Test idempotency (15 min) Run the task 5 times. After each run: - Count rows for today in customer_summary. - Sum revenue for today. Both should be identical across all 5 runs. If they grow, idempotency is broken. ### Step 4: Test backfill (10 min) Backfill the last 7 days: ```bash airflow dags backfill daily_summary --start-date 2026-05-19 --end-date 2026-05-25 ``` After backfill, verify: - Each day's row count is correct (matches source). - Re-running backfill produces same result (re-idempotent). ## Deliverables - Refactored task code. - A test script proving idempotency (runs the task N times; asserts results identical). - A document on the 3 most common idempotency patterns in your stack (DELETE+INSERT, MERGE, partition overwrite). ## What you'll learn - The DELETE-then-INSERT pattern (the workhorse). - Why transactions matter (partial failures otherwise). - Always use execution_date, not "today" or "now()". - How to verify idempotency (re-run; check identical output). - When MERGE is preferable to DELETE+INSERT (PK-based, no date partition).