Idempotency, backfills, and yesterday arriving twice

Here is the scenario, and it will happen to you. A job fails at 03:12. It has already written half its output. You fix the cause at 09:00 and rerun it. What is in the table now?

If the answer is "the correct data", your pipeline is idempotent and you can go back to sleep next time. If the answer is "some rows twice and one day missing", you are going to spend the morning writing a repair script under pressure, and the repair script will also have a bug, because everything written under pressure does.

Idempotency is the property that running an operation N times leaves the same state as running it once. It is the single highest-leverage property in data engineering.

Why naive appends are not idempotent#

-- Broken. Run this twice and Tuesday exists twice.
insert into daily_revenue
select order_date, sum(revenue_usd)
from orders
where order_date = '2026-03-14'
group by order_date;

Nothing about this errors on the second run. It just quietly doubles a day.

The four patterns that fix it#

1. Delete-then-insert on a partition. The workhorse. Bound both statements to the same window and run them in one transaction.

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;

Simple, obvious, and correct. On partitioned tables, prefer replacing the whole partition — most engines make that an atomic metadata swap rather than a row-by-row delete.

2. Merge / upsert on a natural key. When rows update rather than only arriving.

merge into customers as target
using staging_customers as source
  on target.customer_id = source.customer_id
when matched and source.updated_at > target.updated_at then update set
  email = source.email, plan = source.plan, updated_at = source.updated_at
when not matched then insert (customer_id, email, plan, updated_at)
  values (source.customer_id, source.email, source.plan, source.updated_at);

The and source.updated_at > target.updated_at clause matters more than it looks: without it, an out-of-order replay overwrites good data with stale data.

3. Deterministic surrogate keys. Derive the key from the content — a hash of the natural key columns — rather than an auto-increment. The same input row always produces the same key, so re-processing collides harmlessly instead of duplicating.

select md5(order_id || '|' || line_no) as order_line_key, ...

4. Full refresh. For small tables, rebuild the whole thing every run. Trivially idempotent. Do not be embarrassed by this — a dimension table with 40,000 rows does not need incremental logic, and the incremental logic is where the bugs live.

Backfills are a normal operation, not an emergency#

A backfill is reprocessing history: you fixed a bug, added a column, or discovered a source had been wrong for six weeks. If backfilling feels dangerous, your pipeline is not idempotent — because a backfill is nothing more than running the same job for a lot of past windows.

Things that make backfills survivable:

• Parameterise by window, always. The job takes a date; it does not compute "yesterday" internally. run(date) can be backfilled; run() cannot.
• Bound the concurrency. Firing 400 days in parallel is how you take down the warehouse and get an angry message from the platform team.
• Backfill into a shadow table first for anything large, verify row counts and a few aggregates against the live table, then swap. Cheap insurance.
• Know the cost before you start. A 400-day backfill of a job that scans 80GB per run scans 32TB. Someone will ask about that bill.

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

# Daily schedule and backfill are the same code path.
for day in daterange(date(2026, 1, 1), date(2026, 3, 14)):
    run(day, day + timedelta(days=1))

Late-arriving data, and the lookback window#

Data arrives late. A phone was offline, a vendor batched their export, a partner's job failed and reran. If you only ever process "yesterday", late rows land in a window you have already closed and are never picked up.

The standard fix is a lookback window: every night, reprocess the last N days rather than just one.

-- Reprocess a rolling 7-day window. Idempotency makes this free.
delete from daily_revenue where order_date >= current_date - 7;
insert into daily_revenue
select order_date, sum(revenue_usd) from orders
where order_date >= current_date - 7
group by order_date;

Pick N by measuring: look at the distribution of _loaded_at − event_time in your raw data and choose a value covering, say, 99% of it. Then publish that number. "Figures for the last seven days may be restated" is a completely acceptable thing to tell a business, and vastly better than numbers that silently change.

The checklist#

Before you call a pipeline done:

• Can I rerun any single window safely? (idempotency)
• Can I rerun 200 windows without taking anything down? (backfill)
• Do late rows get picked up? (lookback)
• If it writes zero rows, does anyone find out? (observability)
• Is the window a parameter rather than a hard-coded "yesterday"? (operability)

Five questions. Most incidents I have been paged for were a "no" to one of them.