When joining a huge fact table to a small dimension, why does an MPP engine broadcast the dimension?

## The problem an MPP join has to solve An analytical warehouse spreads each table's rows across many nodes (or slices/workers). For an equi-join `fact.dim_id = dim.id`, two matching rows can only be joined if they are physically on the same node at the same time. Unless the two tables happen to already be co-located on the join key, the engine must move data. The plan expresses this with an *exchange* operator, and there are essentially two shapes of exchange for a join. ## Shape 1 — hash redistribution Both inputs are re-partitioned by `hash(join_key) % node_count`. Every row of both tables travels over the network to the node that owns its key bucket. This is symmetric and always correct, but its cost is proportional to the size of *both* inputs. For a star join that means moving the whole fact table. ## Shape 2 — broadcast (replicated join) One input — the small one — is sent in its entirety to *every* node. The large input never moves. Network cost is `size(small) × N` where `N` is the number of nodes; the large side contributes zero network bytes. Each node then runs an independent local join: ```sql SELECT d.country, SUM(f.amount) FROM fact_sales f JOIN dim_customer d ON f.customer_id = d.customer_id GROUP BY d.country; ``` If `dim_customer` is 50 MB and `fact_sales` is 4 TB, broadcasting 50 MB to each of 32 nodes moves ~1.6 GB. Redistributing would move ~4 TB. The asymmetry is why broadcast is the default plan for a star join and why the pattern is sometimes called a *replicated* or *map-side* join. ## Why it pairs so well with a hash join After the broadcast, each node has a complete copy of the dimension — small enough to build an in-memory hash table keyed on the dimension's key. The node then streams its local fact partition through that hash table as the probe side. Nothing blocks except the (tiny) build; the fact scan stays pipelined straight into aggregation. That is the cheapest physical shape available for this query, and it also enables runtime filtering: because the build side is materialized first, the engine can derive a filter from the dimension keys and push it down into the fact scan. ## Where broadcast goes wrong The whole argument rests on "the dimension is small." Three failure modes: 1. **Bad estimates.** The planner estimates the build side from statistics. If a filter on the dimension is estimated at 1,000 rows but actually yields 40 million, the engine broadcasts 40 million rows to every node — `N` times the memory and `N` times the network. This is the classic MPP blow-up: memory pressure on every node at once, spilling, or an out-of-memory abort. 2. **Broadcast is per-node memory, not cluster memory.** A 2 GB dimension broadcast to 32 nodes consumes 2 GB *on each node*, not 2 GB total. The threshold for "small" is set by per-node memory, not cluster memory. 3. **High node counts.** As `N` grows, `size(small) × N` grows linearly. On a very wide cluster, a moderately sized dimension stops being cheap to broadcast. ## When redistribution is the right call When both sides are large — a fact-to-fact join, or a fact joined to a dimension that is itself hundreds of millions of rows — redistribution wins because broadcast's `× N` multiplier dominates. The engine hashes both sides on the join key, and each node handles one slice of the key space. The risk shifts from memory blow-up to **skew**: if one key value accounts for a large share of rows, one node receives a disproportionate share of the work and becomes a straggler while the rest idle. ## The third option: co-location Some engines let you physically arrange two tables so that rows with the same join key already live on the same node. Then neither side moves and the join is entirely local. This is powerful for a repeatedly joined pair, but you only get one such arrangement per table, so it is spent on the most important join and everything else still broadcasts or redistributes. ## What to say in an interview Name the three physical strategies — co-located, broadcast, redistribute — say that cost is dominated by bytes crossing the network, and explain that broadcast trades `N` copies of the small side for zero movement of the big side. Then show you know the failure mode: broadcast is only safe while the estimate for the small side is right, and a wrong estimate hurts every node simultaneously.