Reviewed by Aditya Kumar · Last reviewed 2026-08-08
Broadcast the small table. Spark sends a full copy to every executor so the large table is joined in place, which removes the shuffle on the expensive side entirely. Why this is so much faster A…
This medium-level SQL question appears frequently in data engineering interviews at companies like Datametica. While less common, it tests deeper understanding that distinguishes strong candidates. Mastering the underlying concepts (join, partition, spark) will help you answer variations of this question confidently.
Break this problem into components. Identify the core trade-offs involved, then walk the interviewer through your reasoning step by step. Demonstrate awareness of edge cases and production considerations - this is what separates good answers from great ones. The expert answer includes a code example that demonstrates the implementation pattern.
Broadcast the small table. Spark sends a full copy to every executor so the large table is joined in place, which removes the shuffle on the expensive side entirely.
from pyspark.sql.functions import broadcast
result = df_large.join(broadcast(df_small), "customer_id")
A standard join is a shuffle hash or sort-merge join: both sides are repartitioned by the join key across the network so matching keys land on the same executor. Shuffling a large table means writing it to disk, transferring it, and reading it back — usually the dominant cost of the job.
A broadcast join skips that. Each executor holds the small side in memory as a hash table and streams its local partitions of the large table through it. No large-side shuffle, no sort.
Spark broadcasts automatically when it estimates a side is under spark.sql.autoBroadcastJoinThreshold, which defaults to 10MB:
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", 50 1024 1024)
Automatic broadcast relies on table statistics. If stats are missing or stale — common with files read straight from object storage — Spark overestimates and falls back to a sort-merge join. An explicit broadcast() hint overrides the estimate, which is why the hint often produces a dramatic speedup on a join Spark "should" have broadcast already.
The broadcast table is collected to the driver first, then distributed. Broadcasting something too large causes a driver OOM or exceeds spark.driver.maxResultSize, killing the job. Each executor also holds its own copy, so memory cost multiplies. Broadcast only what is genuinely small — dimension tables, lookup and mapping tables, filtered subsets.
Repartition both on the join key so co-located partitions join without a further shuffle, and enable Adaptive Query Execution, which can convert to a broadcast at runtime using real statistics and split skewed partitions automatically:
spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")
In the interview, also mention checking the physical plan with .explain() for BroadcastHashJoin to confirm the hint was honoured.
Red Flag: Broadcasting 500MB table—OOM. Pro-Move: 'We broadcast 8MB dim_product to 200 executors—join went from 15min shuffle to 30s; monitored broadcast size in UI.'
Some links below are affiliate links. If you buy through them we may earn a small commission at no extra cost to you — it helps keep DataEngPrep free.
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.