Definition
- Not stable – Spark's
sort()andorderBy()do not guarantee that rows with equal sort keys keep their original relative order. - Cause – data is partitioned across executors, sorted in parallel, and merged in an order that depends on partitioning and task scheduling.
- Fix – add a unique tie-breaker column (natural sequence, timestamp, or
monotonically_increasing_id()) to the sort so no two rows are truly equal. - Window functions –
row_number()and other window functions suffer the same tie problem and also need a unique column in the order by.
Imagine the National Bank of India has a pile of trade slips, and several slips are all for the same amount of fifty thousand rupees. You ask ten clerks (executors) to each sort their own stack by amount and then merge the stacks. The fifty-thousand slips all end up grouped together, but whether Rajesh's slip comes before Priya's depends on which clerk had which slip. If you want a guaranteed order, you must also sort by something unique like the slip's sequence number.
Understanding the Concepts
Is Spark's sort stable?
No. In a single-machine sort like Java's Collections.sort, stability can be guaranteed because there is one list and one algorithm. Spark is different because a DataFrame is split into partitions across executors. When you call orderBy('amount'), Spark range-partitions and sorts in parallel, and the merge of equal-key rows is non-deterministic.
from pyspark.sql import SparkSession
import pyspark.sql.functions as F
spark = SparkSession.builder.appName("NBI-Stable-Sort").getOrCreate()
trades = spark.createDataFrame([
("T001", "SAV2001", 50000.00, "2026-01-15 09:00"),
("T002", "SAV2002", 50000.00, "2026-01-15 09:05"),
("T003", "CUR3001", 50000.00, "2026-01-15 09:10"),
("T004", "SAV2001", 75000.00, "2026-01-15 09:15"),
("T005", "SAV2002", 50000.00, "2026-01-15 09:20")
], ["trade_id", "account_id", "amount", "submitted_at"])
# Sort by amount only: order of equal-amount rows is non-deterministic
sorted_trades = trades.orderBy("amount")
sorted_trades.show()
+--------+----------+--------+----------------+
|trade_id|account_id| amount| submitted_at|
+--------+----------+--------+----------------+
| T003| CUR3001| 50000.0|2026-01-15 09:10|
| T001| SAV2001| 50000.0|2026-01-15 09:00|
| T005| SAV2002| 50000.0|2026-01-15 09:20|
| T002| SAV2002| 50000.0|2026-01-15 09:05|
| T004| SAV2001| 75000.0|2026-01-15 09:15|
+--------+----------+--------+----------------+
(Note: the four 50000 rows may appear in any order across runs)
Why is Spark's sort unstable?
The instability comes from distribution. A DataFrame is split into partitions across executors. Spark range-shuffles the data so equal keys land together, then sorts each partition locally. Rows with equal keys can come from several source partitions and be interleaved in an order that depends on partitioning and task scheduling.
# The equal-amount rows come from different partitions:
# Partition 0: T001, T002
# Partition 1: T003, T004, T005
# After shuffle, the 50000 rows from both partitions merge
# in an order determined by the shuffle, not their original positions
# Two runs of the SAME job may produce different orderings
# for the four 50000 rows
How do you simulate a stable sort?
Add a unique tie-breaker column to the sort key so no two rows are truly equal. If your data has a natural monotonic column like a trade ID or timestamp, use that.
# Fix 1: Use a natural tie-breaker (trade_id or submitted_at)
sorted_stable = trades.orderBy("amount", "trade_id")
sorted_stable.show()
+--------+----------+--------+----------------+
|trade_id|account_id| amount| submitted_at|
+--------+----------+--------+----------------+
| T001| SAV2001| 50000.0|2026-01-15 09:00|
| T002| SAV2002| 50000.0|2026-01-15 09:05|
| T003| CUR3001| 50000.0|2026-01-15 09:10|
| T005| SAV2002| 50000.0|2026-01-15 09:20|
| T004| SAV2001| 75000.0|2026-01-15 09:15|
+--------+----------+--------+----------------+
(Deterministic: trade_id breaks ties)
How does monotonically_increasing_id() help?
If there is no natural tie-breaker, Spark provides monotonically_increasing_id(), which generates increasing but not necessarily consecutive long values. Add it as a column and include it in the sort.
# Fix 2: Use monotonically_increasing_id as tie-breaker
trades_with_id = trades.withColumn("rid", F.monotonically_increasing_id())
sorted_with_id = trades_with_id.orderBy("amount", "rid")
sorted_with_id.show()
+--------+----------+--------+----------------+---+
|trade_id|account_id| amount| submitted_at|rid|
+--------+----------+--------+----------------+---+
| T001| SAV2001| 50000.0|2026-01-15 09:00| 0|
| T002| SAV2002| 50000.0|2026-01-15 09:05| 1|
| T003| CUR3001| 50000.0|2026-01-15 09:10| 2|
| T005| SAV2002| 50000.0|2026-01-15 09:20| 4|
| T004| SAV2001| 75000.0|2026-01-15 09:15| 3|
+--------+----------+--------+----------------+---+
How does this affect window functions?
Window functions like row_number() suffer the same tie problem. If the window's order by has ties, the numbering is non-deterministic. Always include a unique column in the window's order by.
from pyspark.sql.window import Window
# WRONG: row_number with ties in order by
w_bad = Window.partitionBy("account_id").orderBy("amount")
trades.withColumn("rn", F.row_number().over(w_bad)).show()
# If two trades for SAV2001 have the same amount, rn assignment is random
# CORRECT: add unique column to break ties
w_good = Window.partitionBy("account_id").orderBy("amount", "trade_id")
trades.withColumn("rn", F.row_number().over(w_good)).show()
+--------+----------+--------+----------------+---+
|trade_id|account_id| amount| submitted_at| rn|
+--------+----------+--------+----------------+---+
| T001| SAV2001| 50000.0|2026-01-15 09:00| 1|
| T004| SAV2001| 75000.0|2026-01-15 09:15| 2|
| T002| SAV2002| 50000.0|2026-01-15 09:05| 1|
| T005| SAV2002| 50000.0|2026-01-15 09:20| 2|
| T003| CUR3001| 50000.0|2026-01-15 09:10| 1|
+--------+----------+--------+----------------+---+
What is the caveat with monotonically_increasing_id?
The IDs reflect the current partitioning, not the original source order. To preserve true source order, capture the ID right after reading in a known order, or use input_file_name() plus a row index.
# monotonically_increasing_id gives determinism, not source order
# IDs are: (partition_id << 33) + row_within_partition
# They are unique and increasing within each partition
# but NOT consecutive across partitions
# For true source order, capture ID right after reading:
txn = spark.read.parquet("hdfs:///bank/transactions/") \
.withColumn("source_order", F.monotonically_increasing_id())
# Now source_order reflects the order Spark read the data
What are common mistakes?
The most common mistake is assuming sort preserves original order for equal keys, leading to non-deterministic reports or representative-row selection.
# WRONG: picking first row per group without tie-breaker
w = Window.partitionBy("account_id").orderBy("amount")
first_per_account = trades.withColumn("rn", F.row_number().over(w)) \
.filter(F.col("rn") == 1)
# Which trade is rn=1 among equal amounts? Non-deterministic!
# CORRECT: add tie-breaker
w = Window.partitionBy("account_id").orderBy("amount", "trade_id")
first_per_account = trades.withColumn("rn", F.row_number().over(w)) \
.filter(F.col("rn") == 1)
# Now rn=1 is always the trade with the lowest trade_id per amount
What should a senior engineer know?
A senior engineer never relies on the order of equal keys from a bare sort. They always add a deterministic tie-breaker when correctness, reproducibility, or auditability depends on the ordering.
# Senior rule: no bare sorts with ties
# Always add a unique column to sort/orderBy and window.orderBy
# For audit-stable output files:
trades.orderBy("amount", "trade_id") \
.write.mode("overwrite").csv("/bank/reports/settlement.csv")
# Identical output every run
Visual Overview
Unstable Sort in Spark:
Input (across partitions):
Partition 0: T001(50K), T002(50K)
Partition 1: T003(50K), T005(50K), T004(75K)
orderBy("amount"):
Range shuffle → sort locally → merge
Run 1 result: Run 2 result:
T003(50K) T001(50K)
T001(50K) T005(50K)
T005(50K) T002(50K)
T002(50K) T003(50K)
T004(75K) T004(75K)
↑ equal-key rows ↑ different order!
in random order
Fix: orderBy("amount", "trade_id")
Both runs:
T001(50K) ← deterministic
T002(50K)
T003(50K)
T005(50K)
T004(75K)
Key Takeaway
Spark's sort and orderBy are not stable, meaning they do not guarantee that rows with equal sort keys keep their original relative order. The reason is distribution: a DataFrame is split into partitions across executors, Spark range-shuffles the data so equal keys land together, then sorts each partition in parallel. The rows with equal keys can come from several source partitions and be interleaved in an order that depends on partitioning and task scheduling, so two runs of the same job can place tied rows differently.
The practical consequence is that any logic relying on the order of equal keys is non-deterministic: picking the first row per group as a representative, generating sequence numbers, or producing output that an auditor expects to look identical every time. The fix is to make the full sort key unique by adding a tie-breaker. If the data has a natural monotonic column like a trade sequence number, use that. If not, Spark provides monotonically_increasing_id for determinism.
At the National Bank of India I ran into this when a settlement report sorted trades by amount and a downstream step chose the first trade per amount bucket as the reconciliation representative. With identical data the chosen trade changed between runs, which alarmed the audit team. I fixed it by ordering on amount and then the trade's natural sequence number.
The rule I follow now is to never depend on the order of equal keys from a bare sort, and to always add a deterministic tie-breaker whenever correctness, reproducibility, or auditability depends on the ordering.
Remember one sentence: Spark's sort is not stable because distributed parallel sorting reorders equal keys, so always add a unique tie-breaker column for deterministic ordering.
---