Spark Scala Sorting, Dedup & Set Operations: Examples & Syntax

The dataset

This module uses the two Foundations DataFrames:

peopleid, name, country, age, signup_date

ordersid, user_id, product, category, amount, qty, status, ordered_at

12.1

orderBy, sort, asc/desc & null ordering

orderBy (alias sort) sorts the whole DataFrame. Order by several columns, mix ascending and descending, and control exactly where nulls land with the *_nulls_first / *_nulls_last variants.

Signature

def orderBy(cols: Column*): Dataset[T]     def sort(cols: Column*): Dataset[T]
$"amount".asc   $"amount".desc
$"c".asc_nulls_first   $"c".asc_nulls_last   $"c".desc_nulls_first   $"c".desc_nulls_last

Scala

orders.select($"product", $"category", $"amount")
  .orderBy($"amount".desc)
  .show(false)

Run the equivalent Spark SQL — edit it and press Run

SQL

⌘/Ctrl + Enter

Default null placement is surprising: ascending puts nulls first, descending puts them last. If a report needs nulls at the bottom regardless of direction, say so explicitly with $"amount".desc_nulls_last. Also know the difference between orderBy (a global sort, one final shuffle) and sortWithinPartitions (sorts inside each partition only, no shuffle) — the latter is much cheaper when you only need local ordering before a write.

12.2

distinct & dropDuplicates

distinct removes fully-duplicate rows (all columns must match). dropDuplicates(cols) is more surgical — it removes rows that are duplicated on just the given columns, keeping one arbitrary row per key.

Signature

def distinct(): Dataset[T]                      // whole-row duplicates
def dropDuplicates(): Dataset[T]                // same as distinct()
def dropDuplicates(colNames: Seq[String]): Dataset[T]   // dedup on a subset of columns

Scala

orders.select($"category")
  .distinct()
  .orderBy($"category")
  .show(false)

Run the equivalent Spark SQL — edit it and press Run

SQL

⌘/Ctrl + Enter

dropDuplicates(Seq("category")) keeps one whole row per category — but which row is undefined. To make it deterministic ("the most recent order per category"), rank with a window first, then filter — baked here to show the intent:

Scala

// deterministic dedup: newest order per category
val w = Window.partitionBy($"category").orderBy($"ordered_at".desc)
orders.withColumn("rn", row_number().over(w))
  .filter($"rn" === 1)
  .select($"category", $"product", $"ordered_at")
  .show(false)

row_number + filter = deterministic 'keep newest per category' — safer than bare dropDuplicates

category	product	ordered_at
Electronics	USB-C Cable	2025-07-01
Home	Desk Lamp	2025-06-25
Stationery	Notebook	2025-05-12

baked output — Spark-only type, not runnable in the browser engine

Prefer the window + row_number pattern over dropDuplicates(subset) whenever it matters which row survives — dropDuplicates makes no ordering promise, so a rerun can keep a different row and quietly change results. In Structured Streaming, dropDuplicates needs a watermark or it buffers state forever.

12.3

union & unionByName

union stacks two DataFrames vertically (like SQL UNION ALL — it keeps duplicates). The catch: it matches columns by position, not name. unionByName matches by column name, which is what you almost always actually want.

Signature

def union(other: Dataset[T]): Dataset[T]              // by POSITION, keeps duplicates
def unionByName(other: Dataset[T]): Dataset[T]        // by NAME
def unionByName(other, allowMissingColumns = true)   // fill absent columns with null

Scala

val electronics = orders.filter($"category" === "Electronics").select($"product")
val home        = orders.filter($"category" === "Home").select($"product")

electronics.union(home).orderBy($"product").show(false)

Run the equivalent Spark SQL — edit it and press Run

SQL

⌘/Ctrl + Enter

The positional trap: if the two DataFrames have the same columns in a different order, union silently pastes city under name and corrupts your data with no error. Default to unionByName. When one side is missing a column, unionByName(other, allowMissingColumns = true) fills it with null instead of throwing. Remember Spark's union is UNION ALL — chain .distinct() if you want SQL's deduping UNION.

12.4

intersect & except

The set-difference operators. intersect keeps rows present in both DataFrames; except (aka subtract) keeps rows in the first but not the second. Both deduplicate; the *All variants preserve duplicates.

Signature

def intersect(other: Dataset[T]): Dataset[T]     def intersectAll(other): Dataset[T]
def except(other: Dataset[T]): Dataset[T]        def exceptAll(other): Dataset[T]

Scala

val boughtElec = orders.filter($"category" === "Electronics").select($"user_id")
val boughtHome = orders.filter($"category" === "Home").select($"user_id")

// users who bought Electronics but NOT Home
boughtElec.except(boughtHome).orderBy($"user_id").show(false)

Run the equivalent Spark SQL — edit it and press Run

SQL

⌘/Ctrl + Enter

intersect and except compare whole rows, so both sides must have the same schema. They're implemented as joins under the hood (a shuffle) — for a simple membership test against one small column, a left_semi / left_anti join (previous module) is usually cheaper and clearer. Swap EXCEPT for INTERSECT in the editor to see who bought from both categories.

12.5

limit, sample & randomSplit

limit takes the first N rows. sample draws a random fraction (great for dev on a slice of prod data), and randomSplit partitions a DataFrame into weighted chunks — the standard train/test split for ML.

Signature

def limit(n: Int): Dataset[T]
def sample(fraction: Double, seed: Long): Dataset[T]        // ~fraction of rows
def randomSplit(weights: Array[Double], seed: Long): Array[Dataset[T]]

Scala

orders.orderBy($"amount".desc)
  .limit(3)
  .select($"product", $"amount")
  .show(false)

Run the equivalent Spark SQL — edit it and press Run

SQL

⌘/Ctrl + Enter

sample and randomSplit are non-deterministic without a fixed seed, so they're shown baked:

Scala

// reproducible 70/30 split for train/test
val Array(train, test) = orders.randomSplit(Array(0.7, 0.3), seed = 42)
train.count()  // ~4
test.count()   // ~2

randomSplit with a fixed seed → reproducible partitions (exact counts vary by data)

split	approx_rows
train (0.7)	4
test (0.3)	2

baked output — Spark-only type, not runnable in the browser engine

Always pass a seed to sample/randomSplit or you'll get different rows on every run — and, worse, on every re-evaluation of the same DataFrame, which can make a train/test split leak. limit on an unordered DataFrame returns arbitrary rows; pair it with orderBy for a real "top N". Note limit still triggers a job — it's not free like a metadata peek.

DevAnalytics · Kettle & Co.now

Two quick lists 🔀 — which users bought from both Electronics and Home? Give me just user_id, sorted.

🎯 Your mission Return the user_ids that appear in orders for both category = 'Electronics' and category = 'Home', sorted ascending. (Hint: INTERSECT.)

SQL

⌘/Ctrl + Enter

In Spark: boughtElec.intersect(boughtHome).
SELECT user_id FROM orders WHERE category='Electronics' INTERSECT SELECT user_id FROM orders WHERE category='Home' ORDER BY user_id;

Frequently asked questions

What is the difference between union and unionByName in Spark?

union stacks two DataFrames matching columns by position, so a different column order silently corrupts the data. unionByName matches by column name, which is almost always what you want, and with allowMissingColumns = true it fills absent columns with null. Both keep duplicates (they behave like SQL UNION ALL).

What is the difference between distinct and dropDuplicates in Spark?

distinct removes rows that are duplicated across all columns. dropDuplicates(cols) removes rows duplicated on just the specified subset of columns, keeping one arbitrary row per key. When it matters which row survives, use a window with row_number and filter instead — dropDuplicates makes no ordering guarantee.

Does Spark union remove duplicates?

No. Spark's union is equivalent to SQL UNION ALL — it keeps duplicate rows. Chain .distinct() afterwards if you want the deduplicating behaviour of SQL's plain UNION.

How do I do a train/test split in Spark?

Use df.randomSplit(Array(0.7, 0.3), seed = 42), which returns an array of DataFrames split by the given weights. Always pass a fixed seed so the split is reproducible and doesn't change on re-evaluation, which would otherwise leak rows between train and test.

Sorting, Dedup & Set Operations

The dataset

orderBy, sort, asc/desc & null ordering

distinct & dropDuplicates

union & unionByName

intersect & except

limit, sample & randomSplit

Frequently asked questions

Ready for the full data stack?

Comments