Replay determinism test

Measure your machineNeeds Python

Problem

A transformation that reads the current time gives a different answer depending on when it runs, so a rerun (a backfill, a recovery) does not reproduce the original output. The fix is to make time an input: pass the logical run date as a parameter, so the same run date always produces the same result.

Take a transformation that labels orders as overdue using current_timestamp and run it twice. Compare outputs. Run it again as a rerun tomorrow would. Replace the ambient read with a run_date parameter and run it twice, once "tomorrow" with the same run date.

The question. Do repeated runs produce the same output, with current_timestamp and with a run_date parameter?

Predict

With current_timestamp in the query, do two runs two seconds apart give the same output?

YesNo

Run it

1Install uv once. It fetches Python and the lab's packages for you.
Terminal
```
curl -LsSf https://astral.sh/uv/install.sh | sh
```

2Get the script. It is the whole lab: read it, then download it.

replay-determinism-test.py42 linesDownload

View the code

replay-determinism-test.py

# /// script
# requires-python = ">=3.10"
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""Replay determinism test.

A transformation that labels orders as overdue using current_timestamp, run
twice a little apart, and run with the clock moved a day (as a rerun tomorrow
would); then the same transformation with an explicit run_date parameter.
Compares the outputs of each pair by hash.
"""
import argparse, hashlib, json, sqlite3, time

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--orders", type=int, default=10_000, help="Orders.")
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()

c = sqlite3.connect(":memory:")
c.execute("CREATE TABLE orders (id INT, due TEXT)")
base = time.time()
c.executemany("INSERT INTO orders VALUES (?, datetime(?, 'unixepoch'))", ((i, base - 86400 + i * 17) for i in range(args.orders)))   # due dates around now
def ambient(shift_days=0):
    q = f"SELECT id, due < datetime('now', '{shift_days:+d} days') AS overdue FROM orders ORDER BY id"
    return hashlib.sha256(str(c.execute(q).fetchall()).encode()).hexdigest()[:16]
def parameterized(run_date):
    q = "SELECT id, due < ? AS overdue FROM orders ORDER BY id"
    return hashlib.sha256(str(c.execute(q, (run_date,)).fetchall()).encode()).hexdigest()[:16]
run_date = c.execute("SELECT datetime(?, 'unixepoch')", (base,)).fetchone()[0]
first = ambient(); time.sleep(2); second = ambient(); tomorrow = ambient(1)
p1 = parameterized(run_date); p2 = parameterized(run_date)
rows = [{"version": "current_timestamp", "run": "now", "hash": first}, {"version": "current_timestamp", "run": "2 s later", "hash": second},
        {"version": "current_timestamp", "run": "rerun tomorrow", "hash": tomorrow},
        {"version": "run_date parameter", "run": "today", "hash": p1}, {"version": "run_date parameter", "run": "rerun tomorrow, same run_date", "hash": p2}]
summary = {"ambient_identical": "yes" if first == second == tomorrow else "no", "param_identical": "yes" if p1 == p2 else "no"}
for r in rows: print(f"{r['version']:<20} {r['run']:<32} {r['hash']}")

if args.json:
    with open(args.json, "w") as f:
        json.dump({"summary": summary, "tables": {"runs": rows}}, f)

3Run it in the folder you saved it to, with the defaults first. Then change a knob from the table below and run it again.
Terminal
```
uv run replay-determinism-test.py
uv run replay-determinism-test.py --help     # every knob
```

Knobs
Knob · default	What it changes
--orders10,000	Orders.

Our run

Output hashes

Output hashes
Version	Run	Output hash
current_timestamp	now	261f5a2a20e2f0bd
current_timestamp	2 s later	261f5a2a20e2f0bd
current_timestamp	rerun tomorrow	ebd8fe5f47020d83
run_date parameter	today	261f5a2a20e2f0bd
run_date parameter	rerun tomorrow, same run_date	261f5a2a20e2f0bd

Reference runApple M2 · 8 cores · 8 GB

Output · uv run replay-determinism-test.py

current_timestamp    now                              261f5a2a20e2f0bd
current_timestamp    2 s later                        261f5a2a20e2f0bd
current_timestamp    rerun tomorrow                   ebd8fe5f47020d83
run_date parameter   today                            261f5a2a20e2f0bd
run_date parameter   rerun tomorrow, same run_date    261f5a2a20e2f0bd

macOS 26.6.2 · Python 3.13.3 · run 2026-10-20

What we found

With current_timestamp, two runs two seconds apart happened to match, and the rerun "tomorrow" did not (identical across all three: no): an ambient clock gives the same answer only while nothing crosses a boundary between runs. With the run_date parameter, the rerun matched the original (yes).

Read it

Ambient time fails intermittently, which is worse than failing always: tests that run seconds apart pass, and a backfill a day later silently disagrees. Every time a pipeline reads should be its logical run time, passed in.