Joins, shuffles & broadcast

Databricks · Apache Spark on Databricks

Theory

df.join(other, on, how) combines two DataFrames; how can be inner, left, right, full, left_semi, left_anti or cross. To join big tables, Spark must bring rows with the same key to the same executor, so it shuffles both sides across the network (a sort-merge join or shuffle hash join). Shuffles write and read data between stages and are often the slowest part of a job.

If one side is small, Spark can broadcast it: send a full copy of the small table to every executor and join locally, with no shuffle of the big table. Spark does this automatically below a size threshold (spark.sql.autoBroadcastJoinThreshold), Adaptive Query Execution can switch to it at runtime, and you can force it with F.broadcast(customers). For example, joining millions of orders to a few thousand customers on customer_id should be a broadcast hash join; df.explain() shows BroadcastHashJoin when it is.

Why we use it

Joins are everywhere in ETL, and a bad join strategy can turn a two-minute job into an hour. Knowing when Spark shuffles and how broadcast avoids it is the most useful Spark tuning skill.

When to use it

Broadcast when one side is small enough to fit comfortably in executor memory (a dimension table). Do not broadcast two large tables; let Spark shuffle, and reduce data first with filters and column selection.

How it works

  1. Join orders with customers
  2. Spark estimates the size of each side
  3. Is one side small (under the threshold or hinted)? Yes: Broadcast hash join: copy the small side. No: Shuffle both sides and sort-merge join.
  4. Matching rows are combined per partition
  5. Joined DataFrame is passed to the next step

Syntax

big.join(small, "customer_id", "inner")
big.join(F.broadcast(small), on="customer_id", how="left")
SQL hint: SELECT /*+ BROADCAST(c) */ ...
spark.sql.autoBroadcastJoinThreshold
df.explain()  -> BroadcastHashJoin | SortMergeJoin

Example

from pyspark.sql import functions as F

orders = spark.read.table("main.sales.orders")
customers = spark.read.table("main.sales.customers")

joined = (orders.join(F.broadcast(customers), on="customer_id", how="inner")
                .select("order_id", "name", "city", "amount")
                .orderBy("order_id"))

joined.explain()   # look for BroadcastHashJoin
display(joined)

-- SQL version with a hint
SELECT /*+ BROADCAST(c) */ o.order_id, c.name, c.city, o.amount
FROM main.sales.orders o JOIN main.sales.customers c USING (customer_id);

Key points

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic