Cloud concept · Updated

Broadcast Joins - Broadcast joins replicate a small join input to participating workers so each worker can join it locally with its portion of a larger input.

In short

A broadcast join avoids repartitioning the larger dataset by distributing the smaller dataset to the workers processing it. This can reduce network shuffle and mitigate join-key skew, but the replicated input must fit safely within each worker's available memory.

Cloud Concepts Hub →

6 key aspects of Broadcast Joins

Broadcast joins exchange replicated memory and distribution overhead for less large-side shuffling, making them useful for small-reference joins and certain join-key skew problems.

01
Replicated small input

The smaller join input is distributed to participating workers so they can perform local matches. Judge suitability using its filtered, projected, in-memory size rather than compressed storage size alone.

02
Reduced large-side shuffle

The larger input does not need to be repartitioned by the join key for this join. This can reduce network transfer, sorting work, and intermediate disk activity, although other query stages may still shuffle.

03
Join-key skew mitigation

Rows sharing a hot key can remain spread across existing large-side partitions instead of converging on one join partition. Broadcasting does not repair input partition imbalance or downstream aggregation skew.

04
Per-worker memory constraints

Each receiving worker needs enough memory for the replicated build input and its execution structures. Account for hash-table expansion, concurrent queries, and other operators before forcing a broadcast.

05
Optimizer and plan verification

Statistics, thresholds, runtime observations, and supported hints influence strategy selection. Inspect the physical plan and execution metrics to verify broadcasting and compare task duration, shuffle volume, and memory pressure.

06
Preserved join correctness

Broadcasting changes execution, not the logical relationship between rows. Validate duplicate reference keys, unmatched rows, null behavior, and expected output counts independently of performance tuning.

Cloud services for Broadcast Joins

AWS Glue and Amazon EMR apply broadcast joins in distributed Spark processing, often when enriching large datasets with small reference tables. The resulting data can be queried through Amazon Athena or loaded into Amazon Redshift, where query planners make their own join-distribution decisions. These services do not share a single broadcast configuration or automatically reuse one another's in-memory broadcast tables. At each processing layer, inspect the local execution plan, measure the effective build-side size, and verify that reduced shuffle outweighs replication costs without changing expected row counts or join semantics.

AWS GlueAWS Glue

AWS Glue Spark ETL jobs can use Spark broadcast joins to enrich large datasets with small reference tables. Inspect Spark execution plans and task metrics, and size the broadcast input against executor memory rather than its compressed source size.

Amazon EMRAmazon EMR

Apache Spark running on Amazon EMR supports optimizer-selected and explicitly requested broadcast joins. Spark adaptive query execution can also change eligible joins to broadcast strategies using runtime statistics, depending on the engine version and configuration.

Amazon RedshiftAmazon Redshift

Amazon Redshift can broadcast a join input across compute nodes, with DS_BCAST_INNER appearing in EXPLAIN output for that distribution choice. For suitable small dimensions, DISTSTYLE ALL provides persistent replication, which is related to broadcasting but differs from distributing an input during query execution.

Amazon AthenaAmazon Athena

Amazon Athena can use replicated, or broadcast, distribution for eligible joins. Use EXPLAIN to inspect distribution choices, keep the build input small, and avoid assuming that a small source file necessarily produces a small in-memory join structure.

Broadcast Joins exam questions

Real practice exam questions on Broadcast Joins, with the correct answer and its explanation. Answer them with every option in the exam simulator.

MLA-C02 (beta)

An AWS Glue Spark job prepares training data by enriching 4 TB of application events with attributes from a reference dataset. The job joins the datasets on error_code. Most events have the same error_code, but the event input partitions are approximately equal in size.

The engineer configures Spark to replicate the reference dataset to executors for the join. The job then fails with executor out-of-memory errors. Although the reference files occupy only 80 MB compressed, their in-memory join representation requires 6 GB. Each executor has approximately 1 GB available for the join. Most reference columns contain diagnostic text that is not needed in the output. Testing shows that retaining only error_code and the required attributes reduces the in-memory join representation to 30 MB without removing any reference rows.

The engineer must resolve the memory failure without increasing worker capacity or repartitioning the event dataset by error_code. Which change meets these requirements?

Show answerCORRECT ANSWER

Select only error_code and the required reference attributes before applying the broadcast operation, and join the events to that reduced DataFrame.

Projecting unused columns out before broadcasting reduces each executor's join representation to 30 MB, which fits safely in memory. The broadcast join can then match events locally without repartitioning the large event dataset by its skewed join key.

DEA-C01

An Amazon EMR Spark job checks incoming sensor readings against an approved-device registry before publishing valid readings. Many readings share the same device identifier, so the engineer uses an explicit broadcast hint on the registry to avoid a skewed shuffle join. After descriptive documents were added to the registry, the job began failing with executor out-of-memory errors while building the broadcast hash table.

The registry occupies 250 MB in compressed files but requires 9 GB as a broadcast hash table. Each executor has 3 GB available for the join. The validation requires only device_id and approval_status; these columns together require approximately 70 MB as a broadcast hash table. Each device_id is unique in the registry. The job must preserve the existing validation results without changing executor sizes or shuffling the readings by device_id.

Which change should the data engineer make?

Show answerCORRECT ANSWER

Project device_id and approval_status from the registry before applying the broadcast hint, and join the readings to that projected DataFrame.

Removing unused document columns before the broadcast reduces the hash table to approximately 70 MB, which fits within each executor's available memory. The required keys and approval values remain unchanged, preserving validation results while allowing each executor to process its readings without a join-key shuffle.

Practice Broadcast Joins in a full exam

Questions on Broadcast Joins in each practice exam, with every answer option and an explanation for each:

Start a free practice exam

What is Broadcast Joins

3 min read

Distributed joins often require moving rows so that matching keys reach the same worker. In a shuffle join, both inputs may be repartitioned by the join key. If one key occurs much more frequently than others, the partition responsible for that key can become a bottleneck. A broadcast join takes a different approach: it replicates one input to the participating workers, allowing each worker to match its existing partitions of the other input locally.

The typical implementation is a broadcast hash join. The engine distributes the smaller, build-side input and constructs an in-memory hash table for lookups. Workers then scan their partitions of the larger, probe-side input against that table. Broadcasting avoids the join-key shuffle of the larger input, although other stages in the query may still require shuffling. Broadcast describes the data-distribution strategy; not every broadcast join uses a hash algorithm.

For example, consider a multi-terabyte event dataset joined to a small table of country codes. If a large fraction of events share the same country code, repartitioning events by country can send disproportionate work to one partition. Broadcasting the country table lets workers process those events across their existing partitions instead. This mitigates skew introduced by join-key redistribution, provided the large input already has reasonably balanced partitions.

In cloud ETL and analytics engines, optimizers can select broadcasting using table statistics, estimated filtered sizes, configured thresholds, or runtime statistics. Some engines also provide join hints. Filter unnecessary rows and project unused columns out of the small input before joining, then inspect the physical execution plan to confirm the actual strategy. In Apache Spark, for example, a supported equijoin can use a broadcast hint or the broadcast DataFrame function, and the resulting plan can show BroadcastExchange and BroadcastHashJoin operators.

The main trade-off is replicated resource consumption. A table that occupies little space in compressed object storage can expand substantially when decoded and represented as an in-memory hash table. Each receiving executor or worker needs memory for that representation alongside concurrent tasks and other operators. Broadcasting also incurs distribution and build time, and aggregate network traffic grows with the number of recipients. Forcing an oversized broadcast can cause memory exhaustion or broadcast timeouts rather than improve performance.

Broadcasting is not a universal skew remedy. It does not rebalance already oversized input partitions, remove expensive per-row processing, or fix skew in a later aggregation. When both join inputs are large, alternatives include adaptive skew-partition splitting, carefully implemented key salting, or isolating hot keys for separate processing. Simply increasing the partition count does not split a single hot key under ordinary hash partitioning.

Join semantics and data quality remain unchanged. Duplicate keys in a reference table can multiply output rows regardless of the physical join strategy, and missing keys or null handling still require validation. Check expected key uniqueness, unmatched records, and output cardinality when changing the execution plan. Join type and engine capabilities also constrain which input can be broadcast; a hint cannot make every join eligible. For operational troubleshooting and exam scenarios, the strongest broadcast candidate is a large input joined to a genuinely small reference input, especially when repartitioning by a hot key causes straggler tasks.

Learn Broadcast Joins in lessons

2 short lessons on Broadcast Joins in 2 certification courses.

Open the lessons