Data Engineering / Basic: contracts and repeatable batches / Lesson 6

Deterministic batches and idempotent runs

Make a rerun preserve the intended dataset state. Separate deterministic computation, event identity, run identity and atomic effect/checkpoint publication.

About 4 min readContent reviewed 2026-10-26

On this page

Lesson goals

Explain deterministic inputs and stable deduplication identity.
Distinguish a repeated attempt from a new business event.

A retry is not necessarily a new batch

Deterministic computation produces the same declared result from the same input, configuration and transformation version. Idempotent application means repeating the same intended operation does not duplicate its business effect. Those properties are related but different: a deterministic list of INSERT statements can still append duplicate rows on every retry. Separate source event identity, logical batch interval and execution attempt identity. A new attempt identifier is useful for diagnostics but should not automatically make previously delivered events look new to the sink.

Make an order-independent batch representation explicit

The local example sorts rows by stable event identity before serializing a small manifest representation. Its hash then agrees for the two input orders. This is a declared canonicalization policy, not proof of trusted provenance or a universal JSON canonicalization standard. Duplicate identities and conflicting content still require validation before hashing. The algorithm reads only invented values and uses Python’s standard library. A digest can identify expected bytes under a trusted reference, but it does not decide whether a source supplied the correct business events.

python Offline declared batch canonicalization

import hashlib
import json

def batch_digest(rows):
    ordered = sorted(rows, key=lambda row: row['event_id'])
    payload = json.dumps(ordered, sort_keys=True, separators=(',', ':')).encode('utf-8')
    return hashlib.sha256(payload).hexdigest()

rows = [{'event_id': 'e2', 'qty': 2}, {'event_id': 'e1', 'qty': 1}]
assert batch_digest(rows) == batch_digest(list(reversed(rows)))

Reversing input order leaves this declared representation’s digest unchanged. No source authenticity or collision-free business identity is asserted.

Deduplicate by the event that must not repeat

An idempotency key should identify the logical operation, not a random value generated on every retry. A unique key can reject duplicate identities, but the flow must decide whether matching identity and different content is an error, a valid correction or a versioned update. Blind last-write-wins can hide conflicting source facts. UPSERT is not a universal deduplication algorithm, and incrementing an aggregate on every upsert attempt can still double-count. Verify the sink’s constraints and update meaning before equating successful retry with safe delivery.

Apply a repeated event once in an isolated sink

The complete SQLite memory program uses a primary key and DO NOTHING to accept the same synthetic event twice without two stored rows. It also verifies the preserved amount. This policy is appropriate only when a duplicate identity with differing content is separately detected or known impossible; DO NOTHING alone would hide a conflict. SQLite UPSERT is available from 3.24.0, and the course verifies the actual runtime before marking examples runnable. The program creates no disk file, configured database connection or service and closes its memory database.

python Idempotent-identity fixture in SQLite memory

import sqlite3

db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE event(id TEXT PRIMARY KEY, amount INTEGER NOT NULL)')
for _ in range(2):
    db.execute('INSERT INTO event VALUES(?,?) ON CONFLICT(id) DO NOTHING', ('e1', 25))
assert db.execute('SELECT COUNT(*),SUM(amount) FROM event').fetchone() == (1, 25)
db.close()

Two delivery attempts yield one row and total 25 under this policy. Conflict-content handling is a separate requirement, not implemented by DO NOTHING.

Coordinate accepted effects with progress

If a checkpoint advances before the sink effect is durable, restart can skip work; if the effect commits first and progress is not recorded, restart can repeat work. Use one supported transaction when target effects and checkpoint share a boundary, or a documented distributed protocol with idempotency and reconciliation when they do not. A scheduler retry flag does not bridge arbitrary systems atomically. Keep replayable input and transformation identity long enough for the recovery objective, and test lost responses, partial batches and conflicting duplicates rather than only rerunning a perfectly clean fixture.

Common errors and fixes

Every retry appends another copy

Why it happens: Execution attempt identity was used instead of logical event identity.
How to fix it: Define stable event/batch identity and enforce the intended sink policy.
Prevent it next time: Test duplicate and conflicting delivery attempts.

A checkpoint skips an uncommitted effect

Why it happens: Progress and target publication were committed out of order without coordination.
How to fix it: Use a supported atomic boundary or explicit recovery protocol.
Prevent it next time: Inject failure between effect and checkpoint in isolated fixtures.

A hash is treated as proof the batch is trustworthy

Why it happens: Byte identity was confused with source authenticity and business correctness.
How to fix it: Verify provenance and dataset acceptance separately.
Prevent it next time: Record exactly what each digest and invariant supports.

Check your understanding

1. A deterministic transformation inserts ten rows on every retry. Is the sink idempotent?

Show worked solution

Not if each attempt adds ten more copies. Determinism describes the computation’s repeated result, while idempotency describes repeated application of the intended operation. Give events stable identity and design the sink constraint/update policy, including conflicts and atomic progress publication.

2. Why can DO NOTHING on a duplicate key hide a serious source problem?