Day 23 of DataBrickster

📡Broadcast Joins

Picture a massive classroom with thousands of students. Now, let’s say one student wants to share a short note with everyone. The traditional way? Each student stands up, walks across the room, and reads the note individually. This will lead to chaos, with everyone rushing to that student's table and trying to get it.

Instead, what if the note was duplicated and magically appeared on every desk? That’s a broadcast join — a shortcut to avoid unnecessary trips across the room when one of the tables is small.

What is a Broadcast Join?

A broadcast join is a Spark optimisation where a small table is copied (broadcasted) to all nodes (executors) so that it doesn’t need to be shuffled across the cluster. This reduces network overhead and significantly speeds up join operations.

What is Shuffling in Spark?

Shuffling refers to the redistribution of data across partitions or nodes in a cluster, typically during operations like joins, aggregations, groupBy, or repartitions.

It’s like mixing and sorting data so that all related records (with the same key) end up on the same machine to be processed together.

Let’s take an example: You’re analysing online purchases

• orders: 5 TB (big_table)
• countries: 30 KB (small_table)

SELECT o.*, c.country_name
FROM orders o
JOIN countries c ON o.country_id = c.id

Here’s what happens without broadcast:

1. orders table is partitioned randomly or by default partitioning (say on order_id).
2. countries table is partitioned (likely very small, but still separate).
3. Spark needs to match rows by country_id = id, but those matching records might not be on the same executor (machine).

So Spark:

• Shuffles both tables based on the join key.
• Moves records across the cluster to align rows with the same key into the same partition.
• Only then does the join.

The bold point above is what Broadcast joins help to mitigate.

With broadcasting:

• The small table is broadcast (copied) to all executors.
• Each executor joins its chunk of the large table with the full small table locally.
• No shuffle needed.

What Happens Without a Broadcast Join?

Shuffling:

• Spark shuffles both tables across the network to align matching keys (e.g. country_id = id)
• This is an expensive operation — Spark has to repartition data and move large chunks of the orders table across nodes.

Memory Usage:

• Both orders and The countries data must fit into memory on the executor doing the join.
• Even though It countries It is small, so Spark treats it like any other table without broadcasting.

Join Execution:

• Spark performs a hash join or sort-merge join.
• These joins are slower due to disk I/O, CPU cost, and network traffic.

Query Latency

• Increased latency.
• Job stages take longer.
• More shuffle files are created → longer garbage collection and disk pressure.

How to Use It in Databricks?

Option 1: Let Spark Decide Automatically
Spark tries to broadcast tables smaller than the default threshold (spark.sql.autoBroadcastJoinThreshold, default = 10MB).

SELECT * FROM big_table JOIN small_table ON big_table.id = small_table.id

Option 2: Force Broadcast Join with a Hint

SELECT /*+ BROADCAST(small_table) */ *
FROM big_table
JOIN small_table
ON big_table.id = small_table.id

Or in PySpark:

from pyspark.sql.functions import broadcast

result = big_table.join(broadcast(small_table), "id")

When Should You Use a Broadcast Join?

• When one of your tables is small (a few MBs to 100s of MBs).
• When you’re joining a small dimension table with a large fact table.
• When you want to avoid expensive shuffle operations.

Caution: When NOT to Use It

• If the small table is not small, it can overwhelm the memory of each executor.
• If the small table is dynamic and varies in size, monitor it first.
• If you’re running on tight memory budgets (serverless, spot clusters, etc.).

Final Thought

Broadcast joins are like passing secret notes in class: quick, quiet, and efficient — but only if the note is short enough to fold and sneak around. Use them wisely, and your queries will whisper their results in no time.

Check out the last one.