A pipeline is idempotent when running it once or five times for the same input gives the same result. It is the single most useful property a batch job can have, because every job eventually gets rerun: a retry after a timeout, a backfill after a bug fix, someone clicking "Clear" in Airflow at 3 a.m.
If a rerun doubles your revenue numbers, the pipeline is broken even when every task is green.

The three patterns that get you there
1. Process a window, not "whatever is new"
Bad: WHERE loaded_at > (SELECT MAX(loaded_at) FROM target). The answer changes every time you run it.
Good: every run owns a fixed, explicit window, such as one day, passed in from the scheduler. In Airflow that is the run's data_interval_start and data_interval_end. Rerunning the 7 October run always processes 7 October.
2. Overwrite the window, don't append to it
Two safe ways to write:
Delete-then-insert inside one transaction
MERGE on a real business key
INSERT INTO ... SELECT with no delete or key is the classic source of duplicates. Avoid it for anything that can be rerun.
3. Deduplicate the source before you merge
MERGE fails (or behaves unpredictably) when the source has two rows for the same key. Keep only the latest:
A quick checklist
- Every run is parameterised by a date window from the scheduler, never by
CURRENT_DATEinside the SQL. - Writes are overwrite-by-partition or MERGE on a key, never blind appends.
- The source is deduplicated on that key first.
- Side effects (emails, API calls, file drops) live in their own task, so retrying the load doesn't resend them.
- You've actually tested it: run the same day twice and compare row counts.
Make this the default and backfills stop being scary.
Try it: break it, then fix it
Here's the whole idea in 25 lines of Python and SQLite. The script loads the same 1,000 orders twice, once with a blind append and once with delete-then-insert inside a single transaction.

Swap SQLite for Snowflake and the pattern is the same: DELETE ... WHERE order_date = :ds and INSERT inside one transaction, or a MERGE on the business key.