A production pipeline will run twice — a retry, a redeploy, an impatient human. Idempotency means running twice changes nothing.
The workhorse pattern is delete-then-insert by partition:
SQL
DELETE FROM daily_revenue WHERE day = '2026-08-15';
INSERT INTO daily_revenue
SELECT order_date, SUM(amount)
FROM orders WHERE order_date = '2026-08-15'
GROUP BY order_date;
Each run owns its slice of data completely. Append-only pipelines double-count on retry; idempotent ones just converge.