A nightly pipeline failed halfway and the retry produced duplicate rows. Walk me through fixing this properly.

The immediate problem

The task appends rather than replacing its window, so a partial run followed by a retry writes some rows twice. The data is now wrong in a way that is hard to detect, because duplicates look like legitimate rows.

The fix, in order

1. Clean the current damage. Identify the affected window and remove duplicates — ideally by deleting the whole window's output and re-running once the task is fixed, rather than by trying to deduplicate in place, which is error-prone.

2. Make the task idempotent per partition. The single change that matters:

• Write by overwrite-partition, not append. Delete the target window, then insert — in one transaction where the engine allows it. In a lakehouse, an atomic partition overwrite does this natively.
• Or upsert on a deterministic key, so a repeated row updates rather than duplicates.

3. Derive every input from the execution window, not from now(). A task containing CURRENT_DATE cannot be backfilled correctly and its retries are not reproducible. This is the second most common cause and it is silent.

4. Verify it. Run the task twice for the same window and diff the output. If they differ, it is still not idempotent. This check takes a minute and should be part of the definition of done for any pipeline task.

What else this reveals

No detection. Duplicates were found by someone noticing, which means there is no reconciliation. Add a check on each load — row count against source, or a total against the system of record — that alerts on divergence. Grain and duplication errors are systematic and silent; a reconciliation catches the class.

Possibly no partial-failure semantics. If the task writes incrementally as it goes, a failure leaves a partially-written window that looks complete. Writing to a staging location and swapping atomically on success removes that whole category.

Backfill was probably unsafe too. If retries duplicate, so does any historical re-run — so the pipeline could not have been corrected for a past period even before this failure.

What a strong answer adds

Framing idempotency as the enabling property rather than a defensive one: it is what makes retries safe, backfills routine, and a failed run something you re-execute rather than investigate. A pipeline that has it is operationally cheap; one that does not requires a human to reason about state after every failure, forever.