Idempotency
The property that running a pipeline (or rebuilding a model) multiple times produces the same result as running it once — no duplicates, no drift. A core requirement for reliable data.
On this page · 6 sections
- Category
- Data pipelines
- Goes deeper in
- dbt for Analytics Engineers
A pipeline is idempotent if re-running it doesn't change the outcome. Reload yesterday's data twice and you should get one copy, not two. This matters because in the real world jobs fail halfway, get retried, and backfill overlapping windows — and none of that should corrupt your tables.
The usual enemy of idempotency is naive `INSERT` logic that appends every run. The fixes: build full-refresh tables from a deterministic SELECT, or use merge/upsert on a unique key for incremental models so re-processing a window replaces rather than duplicates.
Designing for idempotency is what lets you sleep through a 3 a.m. retry. It's also a favorite interview question because it separates people who've run production pipelines from people who haven't.
Production pipelines fail and retry constantly. Idempotency makes those retries safe instead of a source of silent duplication and drift.
Explaining retry-safe writes, overlapping backfills, and late-arriving data demonstrates practical understanding of pipeline reliability.
- Append-only INSERT logic that duplicates rows on every re-run or backfill.
- Non-deterministic transformations (e.g. relying on current_timestamp or random ordering) that produce different output each run.
- How do I make a data pipeline idempotent?
- Build from deterministic SELECTs and use merge/upsert on a unique key (rather than blind INSERT), so re-processing the same window replaces rows instead of duplicating them.