Idempotent window replace

You meet it when#

A job fails at 03:12 having written half its output. You fix it at 09:00 and rerun. What is in the table now?

If the answer is "the correct data", you can go back to sleep next time. If it is "some rows twice", you are writing a repair script under pressure, and that script will have its own bug.

The version that looks right#

insert into daily_revenue
select order_date, sum(revenue_usd)
from orders
where order_date = '2026-03-14'
group by order_date;

Run it twice and Tuesday exists twice. Nothing errors.

The pattern#

begin;
delete from daily_revenue where order_date = '2026-03-14';
insert into daily_revenue
select order_date, sum(revenue_usd)
from orders
where order_date = '2026-03-14'
group by order_date;
commit;

Both statements bound to the same window, in one transaction. On a partitioned table, prefer replacing the whole partition — most engines make that an atomic metadata swap rather than a row-by-row delete.

The part that makes it work#

The window is a parameter, not a computation.

def run(window_start: date, window_end: date) -> None:
    """The only entry point. 'Yesterday' is a scheduling decision, not a code one."""
    ...

# The daily schedule and a 400-day backfill are the same code path.
for day in daterange(start, end):
    run(day, day + timedelta(days=1))

A job that internally computes "yesterday" cannot be backfilled, cannot be replayed, and cannot be tested. This one line of design is the difference between a pipeline you operate and one you babysit.

The other three idempotent shapes#

• Merge on a natural key when rows update rather than only arriving.
• Deterministic surrogate key so re-processing collides harmlessly instead of duplicating.
• Full refresh for small tables. Rebuild the whole thing every run. Trivially correct, and not embarrassing — incremental logic is where the bugs live.