---
title: "Idempotency"
description: "Idempotency is the property of an operation that produces the same end result no matter how many times it's applied, which makes retries after a failure safe rather than risky."
canonical: "https://motherduck.com/glossary/idempotency/"
related:
  - title: "Data loading patterns | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/loading-data-into-motherduck/loading-patterns/"
  - title: "Code that runs is not code that's correct: 4 ways to trust an AI-written data pipeline"
    url: "https://motherduck.com/blog/robust-data-pipelines-with-ai/"
  - title: "DuckLake Architecture Deep Dive: Catalog, Storage, Compute | MotherDuck"
    url: "https://motherduck.com/videos/ducklake-architecture-deep-dive/"
---

# Idempotency

> Idempotency is the property of an operation that produces the same end result no matter how many times it's applied, which makes retries after a failure safe rather than risky.

## Overview

An operation is idempotent if running it once has the same effect as running it many times. `SET x = 5` is idempotent—no matter how many times you run it, `x` ends up as 5. `x = x + 1` is not—each execution changes the result. Idempotency matters enormously in data engineering because pipelines fail and get retried constantly: a network blip, a timeout, or an orchestrator restart can cause a task to run twice, and if that task isn't idempotent, the second run silently corrupts the data (duplicate rows, double-counted revenue, doubled inventory decrements).

## Why it matters in pipelines

Distributed systems can rarely guarantee exactly-once delivery of messages or exactly-once execution of tasks; "at-least-once" is the realistic guarantee in most retry and message-queue systems. Idempotency is how engineers get exactly-once effective behavior on top of at-least-once delivery: if a task might run twice, make sure running it twice is harmless.

Common idempotent patterns in pipelines:

- **Upserts instead of inserts** — write logic that overwrites-by-key rather than always appending, so re-running a load doesn't create duplicates.
- **Deterministic partition overwrites** — a batch job that rebuilds "all data for 2026-07-06" by fully replacing that partition, rather than appending to it, is idempotent regardless of how many times it runs.
- **Deduplication keys** — attaching a unique event ID or idempotency key to each write so a downstream system can detect and discard duplicates.

## Example: idempotent writes in DuckDB

Plain `INSERT` is not idempotent—running the same insert twice produces duplicate rows. DuckDB supports `INSERT OR REPLACE INTO`, which relies on a primary key or unique constraint to overwrite existing rows instead of duplicating them:

```sql
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    amount DECIMAL(10,2),
    updated_at TIMESTAMP
);

INSERT OR REPLACE INTO orders
VALUES (1001, 42, 59.99, '2026-07-06 10:00:00');

-- running this exact statement again leaves the table unchanged
INSERT OR REPLACE INTO orders
VALUES (1001, 42, 59.99, '2026-07-06 10:00:00');
```

The same idea works with `ON CONFLICT`, which gives more control over what gets updated on a duplicate key:

```sql
INSERT INTO orders VALUES (1001, 42, 64.99, '2026-07-06 11:00:00')
ON CONFLICT (order_id) DO UPDATE SET
    amount = excluded.amount,
    updated_at = excluded.updated_at;
```

For batch pipelines, a common idempotent pattern is a full partition overwrite rather than a row-by-row upsert:

```sql
DELETE FROM orders WHERE order_date = '2026-07-06';
INSERT INTO orders
SELECT * FROM read_parquet('s3://bucket/orders/dt=2026-07-06/*.parquet');
```

Re-running this for the same date always leaves the table in the same state, so a retry after a mid-run failure is safe.