The $4M Bug: ROW_NUMBER Sort-Order Differences
Estimated read: 3 min
A migration team ports a commission-calculation procedure that ranks each sales rep’s deals by revenue and pays a bonus tier based on rank: top decile gets one multiplier, next decile another, and so on. The Oracle original used ROW_NUMBER() OVER (PARTITION BY sales_rep_id ORDER BY revenue DESC). The Spark translation, generated by an automated transpiler and lightly reviewed, carried the same ORDER BY revenue DESC forward verbatim. Row counts matched. Sum of revenue matched. The migration passed its first two reconciliation gates and moved toward cutover.
The bug was in what ORDER BY revenue DESC doesn’t specify: what happens when two deals have the exact same revenue value. Both Oracle and Spark’s ROW_NUMBER() function require an ORDER BY and, per the Databricks documentation on row_number(), assign “a unique, sequential number to each row… according to the ordering of rows in the window partition” – but neither engine guarantees which of two tied rows gets the lower number. That tie-break is left to each engine’s internal row-processing order, which depends on physical storage layout, partition pruning, and execution plan shape – none of which survived the migration unchanged. Oracle’s heap-organized table scan produced one tie-break order; Spark’s distributed, partition-parallel execution over Delta files produced a different one, deterministic within a single Spark run but not matching Oracle’s.
For sales reps with no tied revenue values, nothing changed – most rows were unaffected, which is exactly why the bug passed count and sum reconciliation and stayed invisible through the layers that check aggregates rather than row identity. But for the subset of reps with two or more deals landing on identical revenue – round-number contract values, matched-price renewals – the rank each deal received depended on an ordering neither system controlled, and reps landed in different bonus tiers than the source system had computed. The eventual discrepancy, caught only when finance ran a hash-diff reconciliation (Layer 4) against a sample of the compensation output, totaled roughly $4M in incorrectly attributed bonus payouts across one quarter before the pipeline was corrected.
The fix is a single addition: a deterministic tie-break appended to the ORDER BY so no two rows can ever compare as equal.
-- Before: ties break arbitrarily, differently per engine
ROW_NUMBER() OVER (PARTITION BY sales_rep_id ORDER BY revenue DESC)
-- After: a unique column guarantees identical results across engines
ROW_NUMBER() OVER (PARTITION BY sales_rep_id ORDER BY revenue DESC, deal_id ASC)
Adding deal_id ASC (or any column guaranteed unique per partition) as a secondary sort key means every row has a fully deterministic position regardless of which engine, which cluster configuration, or which file layout produced the result. This is not a Spark-specific gotcha – Oracle’s ROW_NUMBER was exactly as non-deterministic on ties, it simply happened to break ties in a way that matched the business’s informal expectations for years, until the underlying engine changed and exposed the assumption that had always been silently unsafe.
Any
ROW_NUMBER,RANK, orDENSE_RANKwindow function migrated from a legacy procedure should be audited for a non-uniqueORDER BYbefore cutover – add a deterministic tie-break column to every one, even if the source system’s behavior happened to look stable for years. Stability under one engine is not a guarantee of correctness, only a guarantee you haven’t hit the tie yet.
The next lecture covers a related but distinct failure mode: not a tie-break ordering bug, but genuine numeric drift between how Oracle and Spark represent and sum decimal values.
| ← Previous: The 5-Layer Stack: Count, Sum, Checksum, Hash, Semantic | Next: Floating-Point Variance: SUM(DECIMAL) Oracle vs Spark → | |:—|—:|