Which Side Is Small Enough

Concepts covered: 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 most stay small for years. A fact joined to a dimension is the most common join you will write, and the dimension is the broadcast candidate.

TableKindRowsIn memoryBroadcast?
page_viewsfact40 billion4TBNo
ordersfact2 billion300GBNo
customersdimension80 million8GBNo
productsdimension200,00040MBYes
storesdimension3,000600KBYes
currenciesdimension180under 20KBYes

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

The size that matters is the table as it will sit in an executor's memory, built into a hash table, and that is larger than the file on disk. Files are compressed and encoded column by column; the same rows decoded into in-memory objects take 3 to 5 times the space. A 10MB file can become a 40MB broadcast. The driver has to hold the table too, because it collects the rows before sending them, so a broadcast that fits the executors but overflows the driver fails anyway.

A quick estimate is rows multiplied by average row width. A catalog of 200,000 products at 200 bytes each is 40MB: fine to broadcast and fine to hold on every executor. A table of 80 million customers at 100 bytes is 8GB: shuffle it. For anything in between, in the hundreds of MB, the broadcast works on a cluster with roomy executors and fails with an out-of-memory error on a tight one, so check the executor memory before you add the hint.

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.

new_users = users.filter(F.col("signup_date") >= "2024-03-08")
joined = user_events.join(F.broadcast(new_users), "user_id")
Broadcast this side
  • 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
Shuffle both sides
  • 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.

About this lesson

This section is part of the Joins: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.