How do I build a reliable data pipeline that doesn't create duplicates on retry?

Question

My nightly job loads yesterday's transactions from an API into our warehouse. When it fails halfway and I rerun it, I get duplicate rows.

Accepted Answer

Make every load **idempotent**: running it twice must produce the same result as running it once.

1. **Upsert (MERGE) on a natural key** instead of blind `INSERT`:

```sql
MERGE INTO fact_transactions t
USING staging_transactions s
  ON t.transaction_id = s.transaction_id
WHEN MATCHED THEN UPDATE SET amount = s.amount, status = s.status
WHEN NOT MATCHED THEN INSERT (transaction_id, amount, status, tx_date)
  VALUES (s.transaction_id, s.amount, s.status, s.tx_date);
```

2. **Or delete-then-insert per partition**, in one transaction: `DELETE WHERE tx_date = '2026-09-25'`, then insert that day's data.
3. **Load to staging first**, validate (row counts, nulls, sums), then merge in a single transaction. A half-finished run never touches the final table.
4. **Parameterize by date** (`--date 2026-09-25`) instead of "yesterday", so backfills and reruns are explicit.
5. Record each run in a `load_log` table (date, rows, status) for monitoring.

1 Answer