Joins: Beginner
Matching rows that live on different machines
A join pairs each row of one table with the rows of another that share a key. orders.join(products, "product_id") pairs order 1, which carries product 4017, with the one products row whose product_id is 4017. To build that pair, the 2 rows have to be in the same place at the same moment: inside one task, on one executor, in memory together.
Before the join, nothing has put them there. Spark read orders as 2 partitions by cutting its file at a byte offset, and read products as 2 partitions the same way. Which partition holds order 1 depends on where that row sat in the orders file, and which partition holds product 4017 depends on where it sat in the products file. The 2 rows land on whichever executors picked up those partitions, and the placement of one says nothing about the placement of the other.
A join is therefore a wide transformation on both of its inputs. Spark hashes the join key of every orders row and sends the row to the shuffle partition that hash selects, then does the same to every products row with the same hash function and the same number of partitions. Afterwards every row carrying product_id 4017, whichever table it came from, sits in one partition, and a single task can walk through that partition and emit each match.
| product_id | orders rows that carry it | products row | matches emitted |
|---|---|---|---|
| 4017 | order_id 1, 3, 6 | Desk lamp | 3 |
| 2210 | order_id 2, 5 | Notebook | 2 |
| 3305 | order_id 4 | Headphones | 1 |
| total | 6 rows | 3 rows | 6 |
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1)orders.join(products, "product_id").collect()
What the shuffle costs
Both tables cross the network. The tasks that read orders write every row to local disk, grouped by destination partition; the tasks that read products do the same; then the join tasks pull their partition's rows from every one of those writers. A join of a 200GB table to a 50GB table writes 250GB of shuffle data and reads 250GB back, and the reading side waits for the slowest writer to finish before it starts.
- ▸2 stages upstream of the join, each with Shuffle Write close to the size of the table it read.
- ▸1 join stage whose Shuffle Read is about the sum of those 2 writes.
- ▸A join stage far slower than its shuffle volume suggests usually has 1 task reading much more than the rest: 1 key holds most of the rows.
A job joins clicks, 400GB, to sessions, 100GB, on session_id, and neither side is small enough to broadcast. How much shuffle data does the job write before the join tasks run?
The cell below joins the seed's order_items fact table to its products dimension on product_id. Spark picks a strategy on its own here; the next section shows which and why. Run it and read the first row: its item_id and quantity came from order_items, its product_name came from products, and the join put them side by side because both carry the same product_id. Each item appears once, as each of orders 1 to 6 did in the table above, because every product_id matches a single products row.
from pyspark.sql import functions as F
joined = order_items.join(products, "product_id")
joined.select("item_id", "order_id", "product_id", "product_name", "quantity").orderBy("item_id").show(8)Move the small table, leave the big one
In the example, products has 3 rows and orders has 6. Shuffling orders to meet products costs the same as shuffling orders for any other purpose: every row written to disk, every row read back over the network. There is another way to get the matching rows into one task. Leave every orders partition where it is, and send the entire products table to every executor. Each task that holds an orders partition then has all 3 products in front of it and finds each order's product with a local hash lookup.
That is a broadcast join. The driver collects the small table, builds a hash table keyed on product_id, and sends 1 copy to each executor. The big table's partitions never move, so the join runs as a narrow transformation over orders, inside the same stage as whatever transformation came before it. The 2 shuffles of the previous section are gone, and so is the sort: a hash lookup needs no sorted input.
F.broadcast(products) is a hint that tells Spark to broadcast that side whatever it estimates the size to be. Watch the animation below: products leaves its partitions once, arrives whole at every orders task, and the output appears without any row of orders changing executor.
orders.join(F.broadcast(products), "product_id").collect()
What the broadcast costs instead
When Spark broadcasts without being asked
You do not always need the hint. Before choosing a strategy, Spark estimates the size of each side. If one side's estimate is under spark.sql.autoBroadcastJoinThreshold, which defaults to 10MB, Spark broadcasts that side on its own. That is what the -1 in section 0 switched off: setting the threshold to -1 tells Spark that no table is small enough, so both sides shuffle. The estimate comes from the files Spark reads, and after a filter or an aggregation it can be far from the truth, so those are the joins where you add the hint yourself.
F.broadcast() around the side you know is small when the planner cannot know it: a filtered table, the output of an aggregation, or a table read from a source that reports no size. Leave it off when the table is read straight from files under the threshold; Spark finds those on its own.events is 200GB and countries is 5MB, both read from files with default settings. How many shuffles does events.join(countries, "country_code") add to the job?
Run the same fact-to-dimension join as before, now with the hint on products. The rows that come back are identical; what changed is where the work happened. order_items stayed in its partitions and products travelled to them.
from pyspark.sql import functions as F
joined = order_items.join(F.broadcast(products), "product_id")
joined.select("item_id", "product_name", "quantity", "unit_price").orderBy("item_id").show(8)Dimension tables broadcast, fact tables do not
| Table | Kind | Rows | In memory | Broadcast? |
|---|---|---|---|---|
| page_views | fact | 40 billion | 4TB | No |
| orders | fact | 2 billion | 300GB | No |
| customers | dimension | 80 million | 8GB | No |
| products | dimension | 200,000 | 40MB | Yes |
| stores | dimension | 3,000 | 600KB | Yes |
| currencies | dimension | 180 | under 20KB | Yes |
The customers row is the one to study. It is a dimension by nature, 1 row per customer, and it still cannot be broadcast, because 80 million rows at 100 bytes each is 8GB, and sending 8GB to every executor and holding it in each one's memory would cost the cluster more than a shuffle would. Being a dimension tells you where to look for a broadcast candidate; the size decides whether it qualifies.
Size means size in memory
A filter can make a candidate
Size is measured at the join, after whatever transformations came before it. users with 50 million rows is too big to broadcast. users filtered to the 20,000 who signed up this week is a few MB, and broadcasting it is right. Spark's estimate, though, may still be the full table's, because the estimate came from the files and the filter happened after the read. This is the case the hint exists for: you know the filtered side is small, and Spark does not.
- A dimension or lookup table
- Tens of MB in memory, a few hundred at most
- Row count stable from month to month
- A table you filtered down to a known small set
- A fact or event table
- Both sides over the threshold
- A dimension so large it rivals the fact, like 80 million customers
- A side whose size you have not checked
You join payments, 900 million rows, to merchants, 120,000 rows, on merchant_id. Spark reads merchants as 30MB of files, which build into roughly 120MB in memory, and the threshold sits at its default. Which call gives you a broadcast join?
The seed's order_items is a fact table and products a dimension. The cell counts the fact rows and the distinct products they reference. The second number is bounded by the size of the dimension however many events pile up on the fact side, and that bound is why the dimension is the side to broadcast.
from pyspark.sql import functions as F
order_items.agg(
F.count("*").alias("fact_rows"),
F.countDistinct("product_id").alias("distinct_products")
).show()The type is yours, the strategy is Spark's
Every join carries 2 independent decisions. The join type is yours, written as the third argument: "inner", "left", "right", "full", "left_semi" or "left_anti". It defines which rows the result contains. The join strategy is Spark's, chosen while planning: broadcast or sort-merge, with the rules of the previous 2 sections deciding which. It defines how the matching rows are brought together and how much data moves. Change the type and the result changes; change the strategy and the result stays the same while the stages and shuffles change.
| Type | Rows in the result | A question it answers |
|---|---|---|
| inner | Pairs that match on the key | Which orders have a product in the catalog? |
| left | Every left row, with right columns or nulls | Every order, with its product where the catalog has one |
| right | Every right row, with left columns or nulls | Every product, with its orders where any exist |
| full | Every row from both sides, matched where possible | Orders and products together, nothing dropped |
| left_semi | Left rows that have a match, left columns only | Which orders reference a catalog product? |
| left_anti | Left rows that have no match | Which orders reference a product missing from the catalog? |
Strategy sits on the other axis. The same orders.join(products, "product_id", "left") returns the same rows whether Spark broadcasts products or shuffles both sides; the row count, the nulls, and the column values do not depend on the strategy. What depends on it is the plan: 0 shuffles and a single stage, or 2 shuffles and 3 stages.
The one place the axes touch
A broadcast join can only broadcast the side whose unmatched rows the join type is allowed to drop. Take the left join above. Every orders row must appear, matched or not. An executor holding a partition of orders can produce that correctly only if it has the whole of products, because a product missing from a partial copy might exist on another executor. Broadcasting products gives every executor the whole copy, so it works. Broadcasting orders would not, since an executor holding part of products could not tell a true non-match from a match it cannot see. So a left join broadcasts only its right side, a right join only its left side, and a full outer join neither: full outer joins run as sort-merge.
If you hint the side the type forbids, Spark drops the hint and shuffles. A left join that stays slow after you added F.broadcast() deserves a second look at which side the hint is wrapped around.
Debug each axis on its own
When a join misbehaves, name the symptom first, because the 2 axes have different tools. Wrong rows in the result point at the type and the keys: an inner join where a left join was meant drops rows, a key column with nulls drops rows in an inner join because a null never equals a null, and a duplicated key multiplies rows, which the next section covers. A slow join points at the strategy. explain() names it on the join's own line of the physical plan, and each shuffle shows up as an Exchange hashpartitioning beneath the join.
| Line in the physical plan | What it tells you |
|---|---|
| SortMergeJoin or BroadcastHashJoin | The strategy Spark chose for this join |
| Exchange hashpartitioning on product_id | A shuffle of 1 side on the key: 2 beneath a sort-merge join, none beneath a broadcast join |
- Check the join type argument
- Check for nulls in the key column
- Check for duplicate keys on either side
- Changing the strategy will not change a row
- Run explain() and find the join line
- Count the Exchange lines beneath it
- Ask whether a side is small enough to broadcast
- Changing the type would change the answer
events.join(users, "user_id", "left") returns the rows you expect but runs for 40 minutes, and explain() shows SortMergeJoin with 2 Exchange lines. users is around 30MB in memory. Which change speeds it up without changing a single result row?
The cell runs a left join of users to user_profiles with the right side broadcast, which the type allows, and returns its row count: every user, with country and plan null where no profile exists. Note the number, change "left" to "inner", and run again. Each user without a profile drops out and the count falls by that many, while the strategy, a broadcast of user_profiles, stays put.
from pyspark.sql import functions as F
joined = users.join(F.broadcast(user_profiles), "user_id", "left")
joined.count()One key, 2 rows, double the output
Give products a version column and let product 4017 carry 2 rows: version 1 priced at 25 and version 2 priced at 27. The catalog now has 4 rows for 3 products. Join orders, 5 rows, to it on product_id and count the result. Order 1 carries 4017 and matches both versions, producing 2 output rows. Order 3 carries 4017 and does the same. Orders 2, 4 and 5 each match 1 catalog row. The result has 7 rows from 5 orders, and nothing in the code hints that anything multiplied.
| product_id | orders rows | products rows | output rows |
|---|---|---|---|
| 4017 | 2 | 2 | 4 |
| 2210 | 2 | 1 | 2 |
| 3305 | 1 | 1 | 1 |
| total | 5 | 4 | 7 |
The rule behind the table: for each key, the output holds left rows times right rows. A key that is unique on the right contributes exactly its left count, 2 rows on the right double that count, and 3 rows on each side turn 6 input rows into 9 output rows. In the program below, products is 4 rows, far under the threshold, so Spark broadcasts it. Watch the 2 orders that carry 4017: each one meets both versions in its task's copy of the catalog and emits 2 rows.
orders.join(products, "product_id").collect()
Why it hurts at scale
A left table holds key A on 3 rows and key B on 2 rows. A right table holds A on 2 rows, B on 1 row and C on 4 rows. How many rows does an inner join on the key return?
Detect before you join
Prevent it
Decide which row per key the join should see, and collapse the side to it before joining. If the duplicates are versions, keep the latest: find the maximum version per product and join the catalog back to that list on both columns. Exact copies need only dropDuplicates(["product_id"]). When the rows differ and you have no rule, dropDuplicates still returns 1 row per key, but it chooses that row arbitrarily, and a different run can choose a different one.
- Check that a dimension is unique on its key before joining to it
- Compare the left row count with the result row count
- Collapse to 1 row per key with a rule you can state
- Treat a side with several rows per key as a fact, not a dimension
- Assume a table named like a dimension is unique on its key
- Fix a blowup by calling dropDuplicates on the joined result
- Rely on dropDuplicates to pick the row you meant
- Sum a measure across a join whose row count you have not checked
The seed's order_items holds several rows per order_id, as a line-item table should. The cell shows the orders with the most items. Join orders to order_items on order_id and each order appears as many times as this count says: the expected result for a fact-to-fact join, and the blowup from this section if order_items had been mistaken for a one-row-per-order dimension.
from pyspark.sql import functions as F
per_order = order_items.groupBy("order_id").count()
per_order.orderBy(F.col("count").desc(), "order_id").show(8)> A retailer's nightly report joins 3 billion order line items to a product catalog of 400,000 rows and a store table of 2,000 rows, then sums revenue by category and region. The first version took 2 hours and reported revenue 30% above the finance ledger.
F.broadcast(), and the line items never leave their partitions.product_id before the join, then confirm the result has exactly as many rows as the line items table.explain() should show 2 BroadcastHashJoin lines with no Exchange hashpartitioning beneath either join. The 1 Exchange left sits above them, from the revenue aggregation by category and region, and its shuffle write is the only one the Spark UI shows.Where a join moves data, when a broadcast spares the shuffle, and how one duplicated key multiplies your rows.
- Category
- Spark
- Difficulty
- beginner
- Duration
- 13 minutes
- Challenges
- 17 hands-on challenges
Topics covered: Why a Join Moves Data, Broadcasting the Small Side, Which Side Is Small Enough, Join Type vs Join Strategy, The Duplicate-Key Blowup
Lesson Sections
- Why a Join Moves Data (concepts: Distributed Primitives)
Matching rows that live on different machines The first line of the program below turns off Spark's automatic broadcasting, so that the join you watch is the shuffle version. Section 1 explains what that setting does when it is left alone. Watch for 2 shuffles, one per table, before any output row appears, and notice that each output row is built inside the task that received both of its halves. What the shuffle costs Inside each shuffle partition, Spark sorts both sides by the key and then merg
- Broadcasting the Small Side (concepts: Broadcast Join)
Move the small table, leave the big one What the broadcast costs instead A broadcast has its own bill. The small table travels once to each executor, and each executor keeps a hash table of it in memory for the length of the stage. A 20MB dimension on a cluster of 50 executors therefore crosses the network 50 times, and each of the 50 executors holds 20MB more than it otherwise would. Set that against shuffling a 200GB fact table, where every byte is written to disk and read back, and the broadc
- Which Side Is Small Enough (concepts: Broadcast Join)
Dimension tables broadcast, fact tables do not Almost every join pairs a fact table with a dimension table. Fact tables record events: orders placed, pages viewed, payments settled, ads clicked. They gain a row per event, so they grow with traffic, and they run to billions of rows. Dimension tables describe the entities events refer to: products, stores, customers, currencies, device types. They gain a row when the business adds a product or opens a store, so they grow with the business, and mos
- Join Type vs Join Strategy
The type is yours, the strategy is Spark's The one place the axes touch Debug each axis on its own
- The Duplicate-Key Blowup
One key, 2 rows, double the output Why it hurts at scale Getting 7 rows from 5 is a 40% inflation, and every total built on the result is wrong: quantity summed over the join counts orders 1 and 3 twice. Scale that to production. A fact table of 2 billion orders joined to a catalog that averages 3 versions per product comes out at 6 billion rows. The join's own shuffle is unchanged: it moves the 2 billion orders and the catalog as they are, and the multiplication happens afterwards, inside the j