Example: Designing Idempotent Data Pipelines with a Coding Agent

Every pipeline gets re-run eventually: a backfill, a retry after a crash, an on-call engineer pressing the button twice. The question I took to ChatGPT, and then cross-examined with Grok, was simple: what does it take to make a re-run boring?

The short answer is idempotency: running the same job with the same inputs any number of times leaves the warehouse in exactly the same state.

The shape of a safe run

Both models converged on the same three-phase structure. Stage everything somewhere disposable, validate it, then publish atomically.

flowchart LR
  A[Source extract] --> B[(Staging table)]
  B --> C{Validate}
  C -- pass --> D[Atomic swap]
  C -- fail --> E[Alert + keep old data]
  D --> F[(Production table)]

The key insight: nothing a reader can see changes until the very last step, and that step is a single atomic operation.

Deterministic keys

The first thing the coding agent proposed was to derive every row’s primary key from its business content, so a duplicate run produces duplicate keys, not duplicate rows.

import hashlib
import json
from typing import Any


def row_key(record: dict[str, Any], fields: list[str]) -> str:
    """Stable, content-derived key: same input -> same key, every run."""
    payload = json.dumps({f: record.get(f) for f in fields}, sort_keys=True, default=str)
    return hashlib.sha256(payload.encode("utf-8")).hexdigest()[:32]


orders = [{"order_id": 42, "sku": "A-1", "qty": 3}]
print([row_key(o, ["order_id", "sku"]) for o in orders])

Upserts instead of inserts

With stable keys, writes become merges. Here is the Postgres flavour:

INSERT INTO analytics.orders AS t (row_key, order_id, sku, qty, loaded_at)
SELECT row_key, order_id, sku, qty, now()
FROM staging.orders_batch
ON CONFLICT (row_key) DO UPDATE
SET qty = EXCLUDED.qty,
    loaded_at = EXCLUDED.loaded_at
WHERE t.qty IS DISTINCT FROM EXCLUDED.qty;

The WHERE ... IS DISTINCT FROM clause is a small detail Grok insisted on: it avoids rewriting rows that didn’t change, which keeps the table’s change log honest.

Which strategy when?

Strategy	Re-run safe?	Cost	Best for
Append-only insert	No	Low	Immutable event logs with dedupe downstream
Upsert on natural key	Yes	Medium	Dimension tables, slowly changing facts
Partition overwrite	Yes	Medium	Daily batch facts (dt=2026-10-18)
Staging + atomic swap	Yes	Higher	Full refreshes that must never be half-visible

Letting the agent write the boring parts

The last part of the conversation was about delegation. I asked the coding agent to generate the swap step, and it produced something close to this:

#!/usr/bin/env bash
set -euo pipefail

psql "$DATABASE_URL" <<'SQL'
BEGIN;
ALTER TABLE analytics.orders RENAME TO orders_old;
ALTER TABLE staging.orders_next SET SCHEMA analytics;
ALTER TABLE analytics.orders_next RENAME TO orders;
DROP TABLE analytics.orders_old;
COMMIT;
SQL

Takeaways

1. Make keys a pure function of identity fields.
2. Write to staging, validate, then publish in one atomic step.
3. Prefer merges and partition overwrites over blind appends.
4. Let agents draft the plumbing, but keep a human on the DROP.