An Airflow task failed after loading half its rows, was retried, and the table now has duplicates. Another task loads 'yesterday' with datetime.now(), and a rerun last week loaded the wrong day. How do you make these tasks safe to rerun?

Make every task idempotent: running it again for the same interval leaves the same result. 3 rules from Airflow's best practices cover both bugs. Never append with INSERT; write with an UPSERT (MERGE) on a key, or replace the partition the run owns. Read and write by the run's data interval (data_interval_start), never by now() or "the latest data", so a rerun next week still loads its own day. And treat the task like a database transaction: it commits all of its output or none of it, so a failure halfway leaves nothing to clean up.

1. Why the retry duplicated rows

The task inserted rows in batches. It failed after half of them, and the retry inserted all of them again. The first half is now in the table twice.

2. Why the rerun loaded the wrong day

# wrong: depends on when the task runs
day = (datetime.now() - timedelta(days=1)).date()

Rerun on 20 October for the run of 12 October, it loads 19 October.

3. The rewrite

from airflow.sdk import task
from airflow.providers.common.sql.operators.sql import SQLExecuteQueryOperator

load_orders = SQLExecuteQueryOperator(
    task_id="load_orders",
    conn_id="warehouse",
    sql='''
        BEGIN;
        DELETE FROM orders WHERE order_date = '{{ data_interval_start | ds }}';
        INSERT INTO orders
        SELECT * FROM staging_orders
        WHERE order_date = '{{ data_interval_start | ds }}';
        COMMIT;
    ''',
)

• The day comes from the interval, so the run of 12 October always loads 12 October, whenever it runs.
• Delete then insert inside one transaction replaces that day as a whole: a retry, a rerun or a backfill gives the same rows. A MERGE on the order id does the same for tables updated row by row.
• The transaction means a failure halfway rolls back, so there is never a half-loaded day.

4. The same rule for files

Write files to a path keyed by the interval, such as s3://lake/orders/date=2026-10-12/, and overwrite that path. Airflow's best practices give the same advice for S3 and HDFS.

5. A habit to check in review

What interviewers want: idempotency as the goal, UPSERT or partition overwrite instead of INSERT, the run's data interval instead of now(), all-or-nothing writes, the same rule for file paths, and why it makes retries and backfills safe.