Chapter 1 · Practice 1 of 4
Make every task produce the same result when it runs again
Why it matters
Airflow's best practices say to treat tasks like database transactions: a re-run must give the same final result. The most common way this breaks is an INSERT that adds the same rows again on a retry.
| Pattern | On re-run |
|---|---|
INSERT new rows | Duplicates |
UPSERT or MERGE on a key | Same result |
| Delete the partition, then insert | Same result |
| Overwrite the output file or partition | Same result |
Design each task so its output is fully determined by its inputs and its time window, and so it replaces its own output rather than appending to it.
Do
- Use MERGE, upsert or partition overwrite for every load
Avoid
- Plain INSERT into a table that a retry can load again
Example
-- Load one day idempotently: replace the day's rows in one transaction
BEGIN;
DELETE FROM fact_orders WHERE order_date = :run_date;
INSERT INTO fact_orders
SELECT * FROM staging_orders WHERE order_date = :run_date;
COMMIT;