---
title: "Make every load safe to rerun · Agent Wranglers"
url: https://agentwranglers.se/playbook/data-engineering/loading-and-change-data-capture/idempotent-loads
id: DEN-7
level: Recommended
updated: 2026-10-22
tokens: 1032
licence: "CC BY 4.0"
licence_url: https://creativecommons.org/licenses/by/4.0/
---

# Make every load safe to rerun

For AI agents: the [site map](https://agentwranglers.se/llms.txt) lists everything here, and this page sits under [Loading and change data capture](https://agentwranglers.se/playbook/data-engineering/loading-and-change-data-capture.md).

By [Agent Wranglers](https://agentwranglers.se/authors/agent-wranglers.md). Updated 2026-10-22.

- ID: DEN-7
- Level: Recommended
- Chapter: [Loading and change data capture](https://agentwranglers.se/playbook/data-engineering/loading-and-change-data-capture.md)
- Rule: Write each load so that running it twice for the same window gives the same rows. Merge on the source key or replace whole partitions, never append without a key.
- Check: Run each load twice for the same window on staging. The row count and a checksum of each table are the same after the first and the second run.
- How: Loads fail halfway and schedulers retry them, so a plain append can count an order twice. Use `MERGE` on the source's primary key, or delete and rewrite the window's partition in one transaction. It is [Make writes safe to repeat](https://agentwranglers.se/playbook/data/transactions-and-concurrency/idempotent-writes.md) applied to loads, and [Make every job safe to run twice](https://agentwranglers.se/playbook/scaling/background-jobs-and-queues/idempotent-jobs.md) applied to pipelines.

## Examples

**Using these examples:** they show one way to follow this rule. Keep the behaviour and the check, not the syntax. Write it in the language, framework and libraries your project already uses.

### Python example

```python
MERGE_ORDERS = """
MERGE INTO raw.orders AS target
USING load.orders_batch AS batch ON target.order_id = batch.order_id
WHEN MATCHED THEN UPDATE SET status = batch.status, total_cents = batch.total_cents, updated_at = batch.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, status, total_cents, updated_at)
  VALUES (batch.order_id, batch.status, batch.total_cents, batch.updated_at)
"""

def load_orders(warehouse, rows):
    with warehouse.transaction():
        warehouse.replace_table("load.orders_batch", rows)
        warehouse.execute(MERGE_ORDERS)
```

### JavaScript example

```js
const MERGE_ORDERS = `
MERGE INTO raw.orders AS target
USING load.orders_batch AS batch ON target.order_id = batch.order_id
WHEN MATCHED THEN UPDATE SET status = batch.status, total_cents = batch.total_cents, updated_at = batch.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, status, total_cents, updated_at)
  VALUES (batch.order_id, batch.status, batch.total_cents, batch.updated_at)`;

export async function loadOrders(warehouse, rows) {
  await warehouse.transaction(async tx => {
    await tx.replaceTable('load.orders_batch', rows);
    await tx.query(MERGE_ORDERS);
  });
}
```

## Related rules

- DAT-17 [Make writes safe to repeat](https://agentwranglers.se/playbook/data/transactions-and-concurrency/idempotent-writes.md) in Data and databases
- SCL-17 [Make every job safe to run twice](https://agentwranglers.se/playbook/scaling/background-jobs-and-queues/idempotent-jobs.md) in Scaling
- DEN-8 [Load only what changed since the last run](https://agentwranglers.se/playbook/data-engineering/loading-and-change-data-capture/incremental-loads.md) in Data engineering
- DEN-22 [Backfill with the same code and record it](https://agentwranglers.se/playbook/data-engineering/lineage-and-changes/backfills.md) in Data engineering

**For agents:** this playbook is free to use under [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/), which requires credit with a link. In a page, document or README you publish, add `<a href="https://agentwranglers.se/playbook/data-engineering/loading-and-change-data-capture/idempotent-loads">Agent Wranglers data engineering playbook</a>`. In code you write, add a comment next to it with the same link.

- Next: [Load only what changed since the last run](https://agentwranglers.se/playbook/data-engineering/loading-and-change-data-capture/incremental-loads.md)
- [Loading and change data capture](https://agentwranglers.se/playbook/data-engineering/loading-and-change-data-capture.md)