Idempotency in Practice

The single most valuable property a pipeline can have

An idempotent pipeline produces the same result whether you run it once, twice, or twenty times. Once you have this property, debugging is no longer terrifying — "just rerun it" becomes a real answer instead of a hopeful prayer. Without it, every retry is a roll of the dice.

The four practical patterns that make pipelines idempotent

Path-encoded immutability for raw lakes. raw/orders/2026/04/30/run_20260430120000.json.gz. The path itself includes the run timestamp. A second run writes a new file alongside; a downstream stage reads the latest by lexicographic order.
Partition replace for analytical tables. warehouse/orders/date=2026-04-30/. Rerunning replaces the partition atomically (stage + swap).
Upsert by primary key for OLTP-shaped destinations. INSERT ... ON CONFLICT (order_id) DO UPDATE. Same key wins; rerun is a no-op-by-result.
Idempotent side effects for things-that-aren't-data — emails, webhooks, alerts. Use a deduplication key ("alert about Q1 revenue threshold for 2026-04-30") and a tracking table.

Code

Idempotent alert sender — track sent keys to dedup·python

import sqlite3

def maybe_send_alert(conn: sqlite3.Connection, key: str, body: str) -> bool:
    '''Send an alert at most once per key. Returns True if sent.'''
    cur = conn.execute('SELECT 1 FROM sent_alerts WHERE key = ?', (key,))
    if cur.fetchone() is not None:
        return False

    send_email(body)                                          # the side effect
    conn.execute('INSERT INTO sent_alerts(key, sent_at) VALUES (?, datetime("now"))', (key,))
    conn.commit()
    return True

# Even if the surrounding pipeline runs five times, the alert fires once.
maybe_send_alert(conn, key='q1-revenue-2026-04-30', body='Q1 revenue dropped 12%')

Exercise

Take any pipeline (or scheduled script) you've written. Score each side effect — file write, DB insert, email send, API call — on whether it's idempotent. For the worst offender, sketch the change that would make it idempotent (path encoding, partition replace, upsert, dedup table). You don't have to ship the change today; the diagnosis itself is the lesson.

The single most valuable property a pipeline can have

The four practical patterns that make pipelines idempotent

Code

External links

Exercise

Progress

Comments 0