The Backfill That Doubled the Revenue Table

Setting catchup=True causes the scheduler to create DAG runs for every interval between start_date and now - roughly 90 runs - and clearing task instances marks them for re-execution. Because the load task is a plain INSERT with no key and no delete, each re-execution appends another copy of that date's revenue. The table therefore gains one extra copy per re-run per date.

The duplication is uneven because the number of re-runs per date is uneven:

• Some dates were re-run once by the backfill and already had one copy → two copies.
• Dates whose task instances were cleared more than once, or that were also picked up by the normal schedule while the backfill was running, got three.
• The in-progress run that was cleared was killed mid-write; depending on transaction handling it either left a partial batch committed or rolled back, so some dates have partial or missing rows rather than a clean multiple.
• catchup=False originally meant only the most recent interval ran each day; re-enabling catchup created runs for intervals that had never run, so some dates went from one copy to two for a different reason than others.

depends_on_past=False and no retries are not causes, but they remove two brakes: nothing serializes the backfill, and nothing retries a failed run cleanly. The root cause is that the task is not idempotent, so re-execution is destructive. Backfill is only safe when re-running a logical date produces the same table state as running it once.

Remediation of the data:

1. Stop the bleeding: pause the DAG so no further runs append while you work.
2. Identify duplicates by the business key (date) with a GROUP BY date HAVING COUNT(*) > 1 and compare against the source of truth to decide which row is correct - do not assume the first or last is right, since the killed mid-write run may have committed a partial batch.
3. Rebuild the affected partitions from source rather than deleting 'extra' rows blindly: for each date, delete all rows for that date and re-insert from the source, or recompute into a staging table and swap. Verify row counts per date against the source.
4. For the killed run, check whether a partial batch was committed (e.g. rows for a date that do not match the source totals) and rebuild that date explicitly.

Changes before any future backfill:

• Make the write idempotent: DELETE FROM revenue_daily WHERE date = '{{ ds }}' followed by the INSERT in the same transaction, or a MERGE/upsert on date. Then re-running a date is harmless.
• Add a unique constraint on date so a duplicate fails loudly instead of silently doubling the table. This is the cheapest permanent guard.
• Add retries with a delay so transient failures recover without manual clearing.
• Consider max_active_runs to throttle a backfill and avoid overwhelming the warehouse.
• Backfill in a controlled way (CLI dags backfill with explicit start/end, or a limited clear) rather than clearing everything in the UI, and check counts afterwards.

A green task means the process exited successfully, not that the output was correct. This DAG's task succeeds whenever the INSERT completes, regardless of whether it inserted one copy or three. Airflow has no knowledge of the table's contents, so it cannot detect duplication. The 90 green squares are evidence that the job ran, not that it was right.

The deeper point is that the DAG was latently non-idempotent for 90 days. It happened to be correct only because it was never re-run. The first backfill, retry, or manual clear would expose the flaw. This is why idempotency is a design requirement rather than a nicety: the scheduler will eventually re-run a task, whether through retries, clearing, or catchup.

Checks that would have caught it earlier:

• A unique constraint or primary key on date in the warehouse - the second insert fails immediately.
• A data-quality task after the load asserting COUNT(*) = COUNT(DISTINCT date) for the affected range, which fails the DAG run rather than the table.
• A row-count comparison against the source for the logical date, as part of the DAG.
• A test that runs the load task twice against a scratch schema and asserts the row count is unchanged - the direct test of idempotency.

Any of these converts a silent data corruption discovered weeks later into a red task on the day it happens, which is exactly what orchestration is for.