All Articles

Idempotent SQL: Writing Safe, Re-runnable Scripts

Best Practices
#idempotent-sql#migrations#etl#upsert#data-engineering

What Idempotent Means, and Why It Matters

An idempotent operation produces the same end result no matter how many times it's run — run it once, or run it five times after a failed deploy and a retry, and the database ends up in the identical state either way. This matters enormously for migrations, seed scripts, and scheduled ETL jobs, all of which can fail partway through and need to be safely re-run without manual cleanup or, worse, silently duplicating data.

The Problem: Non-Idempotent Scripts

-- Running this script twice creates two identical rows, silently corrupting the data
INSERT INTO settings (key, value) VALUES ('max_upload_size', '10MB');

-- Running this migration twice fails on the second run with a "column already exists" error
ALTER TABLE users ADD COLUMN last_login TIMESTAMPTZ;

Both scripts work fine the first time — the danger only appears on a retry, which is exactly the scenario a deployment failure or a scheduler double-trigger produces. Idempotent SQL is written defensively, assuming a re-run is always possible.

Idempotent Inserts

-- PostgreSQL: ON CONFLICT DO NOTHING (requires a UNIQUE constraint on the conflicting column)
INSERT INTO settings (key, value) VALUES ('max_upload_size', '10MB')
ON CONFLICT (key) DO NOTHING;

-- PostgreSQL: ON CONFLICT DO UPDATE (an "upsert" — insert or update if it already exists)
INSERT INTO settings (key, value) VALUES ('max_upload_size', '10MB')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

-- MySQL: INSERT IGNORE, or ON DUPLICATE KEY UPDATE
INSERT IGNORE INTO settings (key, value) VALUES ('max_upload_size', '10MB');
INSERT INTO settings (key, value) VALUES ('max_upload_size', '10MB')
  ON DUPLICATE KEY UPDATE value = VALUES(value);

-- SQL Server: MERGE statement, the standard upsert pattern
MERGE settings AS target
USING (SELECT 'max_upload_size' AS key, '10MB' AS value) AS source
ON target.key = source.key
WHEN MATCHED THEN UPDATE SET value = source.value
WHEN NOT MATCHED THEN INSERT (key, value) VALUES (source.key, source.value);

Every one of these patterns requires a UNIQUE or PRIMARY KEY constraint on the conflicting column to work — see our constraints guide for how to set that up. Without a real constraint, the database has no way to detect "this row already exists."

Idempotent Schema Changes (Migrations)

-- PostgreSQL: IF NOT EXISTS / IF EXISTS guards on schema changes
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login TIMESTAMPTZ;
CREATE TABLE IF NOT EXISTS audit_log (id SERIAL PRIMARY KEY, event TEXT);
CREATE INDEX IF NOT EXISTS idx_users_email ON users (email);
DROP TABLE IF EXISTS temp_staging;

-- MySQL 8.0.29+: similar IF NOT EXISTS support for ADD COLUMN
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login DATETIME;

-- SQL Server: no native IF NOT EXISTS on ALTER TABLE — guard manually
IF NOT EXISTS (
  SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID('users') AND name = 'last_login'
)
BEGIN
  ALTER TABLE users ADD last_login DATETIME2;
END

Most migration frameworks (Flyway, Liquibase, Rails migrations, Django migrations) handle this idempotency concern for you automatically by tracking which migrations have already run — but understanding the raw SQL patterns matters when writing manual hotfix scripts, one-off data patches, or working outside a migration framework entirely.

Idempotent Updates and Deletes

Most UPDATE and DELETE statements are naturally idempotent already, as long as they're driven by a stable condition rather than a relative one:

-- Naturally idempotent: setting an absolute value based on a stable condition
UPDATE users SET status = 'inactive' WHERE last_login < NOW() - INTERVAL '1 year';
-- Running this 5 times in a row produces the identical end state every time

-- NOT idempotent: applies a relative change that compounds on every run
UPDATE products SET price = price * 1.1;
-- Running this twice increases the price by 21%, not 10% — a very common, dangerous mistake

-- Fix: compute against a stable base, or make the operation self-limiting
UPDATE products SET price = base_price * 1.1 WHERE price != base_price * 1.1;

This distinction — absolute vs. relative changes — is the single most important idempotency concept for UPDATE statements, and it's a very easy mistake to make when a script that's "supposed to run once" ends up re-triggered by a scheduler bug or a manual re-run during an incident.

Idempotent ETL Loads

-- Delete-and-reinsert pattern for a specific batch/date, wrapped in a transaction
BEGIN;
DELETE FROM daily_sales_summary WHERE report_date = '2026-01-15';
INSERT INTO daily_sales_summary (report_date, total_revenue)
SELECT '2026-01-15', SUM(amount) FROM orders WHERE DATE(order_date) = '2026-01-15';
COMMIT;
-- Re-running this for the same report_date produces the identical result every time,
-- because it always deletes any prior partial/full data for that date before reinserting

This "delete the target range, then reinsert" pattern is the standard idempotent load strategy for daily or batch-based ETL — it guarantees a clean, complete state for that specific partition regardless of how many times the job runs or partially fails.

Common Mistakes

  • Relative UPDATEs without a guard — price = price * 1.1 compounds on every re-run, one of the most dangerous and common idempotency bugs in production.
  • INSERT statements with no UNIQUE constraint to conflict against — ON CONFLICT/ON DUPLICATE KEY patterns silently do nothing useful without a real constraint backing them.
  • Multi-statement scripts without a wrapping transaction — if the script fails partway through, you're left in a partial, inconsistent state that's harder to safely re-run than either a full success or full failure would be.
  • Assuming a migration framework makes every script automatically idempotent — frameworks track which migrations already ran, but a poorly written migration's own logic (like a relative UPDATE) can still not be idempotent if manually re-run outside the framework's normal flow.

Common Interview Questions

  1. What does it mean for a SQL script to be idempotent? Running it multiple times produces the same end state as running it once — no duplicate rows, no compounding changes, no errors on re-run.
  2. How would you make an INSERT statement idempotent? ON CONFLICT DO NOTHING/UPDATE (PostgreSQL), INSERT IGNORE/ON DUPLICATE KEY UPDATE (MySQL), or MERGE (SQL Server) — all requiring a UNIQUE constraint.
  3. Why is UPDATE price = price * 1.1 dangerous in a script that might be re-run? It's a relative change that compounds with every execution, rather than converging to a stable, predictable end state.
  4. How would you design an idempotent daily ETL load? The delete-and-reinsert-for-the-specific-partition pattern, wrapped in a transaction, as shown above.

Frequently Asked Questions

Are all SELECT queries automatically idempotent?

Yes — a read-only SELECT never changes data, so running it any number of times has no side effects on the database state. Idempotency is only a concern for statements that write (INSERT, UPDATE, DELETE, and schema changes).

Is wrapping everything in a transaction enough to guarantee idempotency?

No — a transaction guarantees atomicity (all-or-nothing for that one run), but doesn't by itself prevent a second full run from duplicating data or compounding a relative change. Idempotency requires the statements themselves to be written defensively, as shown throughout this guide, in addition to transactional safety.

Practice Writing Safe, Re-Runnable SQL

Idempotency is one of the most valuable practical habits for anyone writing migrations, seed data, or ETL jobs in a real production environment. Try related patterns hands-on with our SQL practice questions, or work through realistic data engineering scenarios in our case studies. For the constraints that make conflict-based upserts possible, see our SQL constraints guide.

Back to All Articles