The engines quietly disagreed.

A scoring pipeline the business already trusted — fourteen chained models, each feeding the next, ending in a risk score that people act on — had to move from SQL Server to BigQuery. The success criterion was strict on purpose: row-level parity. Same inputs, same outputs, to the row. Not “close.” A migration that changes the numbers isn’t a migration; it’s a new, unvalidated model wearing the old one’s name.

Both pipelines ran clean. No errors, no nulls where they shouldn’t be, aggregate counts matching. But the final scores disagreed for a small, unstable subset of accounts — and worse, the set of disagreeing rows changed between runs on the same frozen input. That last detail is the tell. Deterministic logic doesn’t produce a different diff on identical data; something in the chain was making an arbitrary choice, and each engine — sometimes each run — was choosing differently.

Fourteen models is too many to eyeball, so the hunt was a bisection: materialize both engines’ outputs per model, diff each layer, and find the first one where the results diverge. The diff tool is the plainest SQL there is:

SELECT * FROM model_07_engine_a
EXCEPT DISTINCT
SELECT * FROM model_07_engine_b;
-- …and the mirror query. Zero rows both ways = this layer is clean.

Layers one through six: identical. Layer seven: sixty-odd rows of drift, growing downstream as later models amplified the difference. Everything below layer seven was innocent — the bug had one address.

Layer seven deduplicated events per account: keep the “first” record per account per day. The original code:

ROW_NUMBER() OVER (
  PARTITION BY account_id, event_date
  ORDER BY event_timestamp        -- ties exist. order among ties: engine’s choice
) AS rn
-- downstream: WHERE rn = 1

Multiple events can share a timestamp — batch loads land that way constantly. The SQL standard says nothing about which tied row gets rn = 1; SQL Server picked one by storage order, BigQuery by shard order, and neither promises stability run-to-run. Both engines were correct. The query was underspecified. It had been silently arbitrary for years — the old system just made the same arbitrary choice consistently enough that nobody saw it.

ROW_NUMBER() OVER (
  PARTITION BY account_id, event_date
  ORDER BY event_timestamp,
           source_system,
           event_id            -- unique key = total order = one possible answer
) AS rn

One line: extend the ordering until it reaches a unique key, so there are no ties left for the engine to break. Re-ran the full chain on both engines: zero-row diffs at every layer, stable across reruns. Parity proven, migration signed off, and the fix was backported to the legacy side so the old system stopped being secretly lucky.

• Every ORDER BY inside a window function must reach a unique key — determinism is a contract, not a default.
• “Same code, different engine” is a myth: migrations validate behavior, not syntax.
• EXCEPT-based layer bisection turns “the numbers are wrong somewhere” into “the bug lives in model seven.”
• An unstable diff on frozen input means nondeterminism — stop reading logic, start hunting arbitrary choices.