# Designing Idempotent, Replayable Pipelines

> Batch IDs, immutable attempts, manifests, ledgers and watermarks — the building blocks that make 'just re-run it' safe.

- Source: https://akshaythakare.com/notes/etl-elt/idempotent-pipelines
- Topic: ETL / ELT
- Level: intermediate
- Reading time: 3 min
- Tags: etl, idempotency, reliability, interview
- Updated: 2026-10-11

## The goal

A pipeline is **idempotent** if running the same batch twice leaves the target exactly as running it once. Retries, operator re-runs and orchestrator restarts all become safe.

## Building blocks

| Concept | What it is | What it protects |
| --- | --- | --- |
| `batch_id` | One logical publication (e.g. a business date) | Identity for replay checks |
| `attempt_id` | One physical try at extracting that batch | Retries never overwrite earlier evidence |
| Manifest | Explicit list of files, counts and checksums for an attempt | Replays read selected inputs, not a folder scan |
| Ledger | Committed batches with a content fingerprint | Second publish returns `ALREADY_PUBLISHED`; different content under the same ID fails |
| Watermark | Committed upper bound per source | Only moves forward, only on successful commit |

## The flow

```mermaid
flowchart LR
  E[Extract into new attempt] --> M[Write manifest last]
  M --> V[Validate counts + checksums]
  V --> T[Transform]
  T --> P{Ledger has batch?}
  P -- same content --> D[ALREADY_PUBLISHED]
  P -- different content --> F[Fail]
  P -- no --> C[One transaction:<br/>facts + history + ledger + watermark]
```

## One batch, step by step

  
### Extract into a new attempt
Write JSONL into `raw/batch_id=…/attempt_id=…/`. The directory must not exist yet — attempts are never reused.
  
### Write the manifest last
List every file with bytes and a checksum. Its presence means the attempt is complete.
  
### Validate before compute
Check expected datasets, path prefixes, counts and checksums. A missing or corrupt file stops the batch here.
  
### Check the ledger
Same batch, same content → `ALREADY_PUBLISHED`. Same batch, different content → fail loudly.
  
### Publish in one transaction
Facts, history, the ledger row and the new watermark commit together — or not at all.

## Rules of thumb

- **Write the manifest last.** Its existence means the attempt is complete
- **Never mutate an attempt.** Recover with a fresh attempt instead
- **Upserts by natural key** for facts; never blind inserts
- **Advance watermarks inside the publish transaction** — never before
- **Late, older batches must not rewind** the watermark
- Notifications and audit files live outside the transaction; their failure must not trigger a republish

## A useful test suite

Replay the same batch → identical metrics. Delete a raw file → publication blocked. Inject a failure mid-publish → no partial facts, watermark unchanged. Reuse a batch ID with different content → failure.