A warehouse stores a very large sales fact table that is repeatedly joined to smaller dimension tables on customer_id. Which distribution style reduces network traffic during those joins?
Show answer and explanation
Answer: B
With KEY distribution, rows hashing to the same value of customer_id land on the same slice. If the dimension table is distributed or replicated on the same key, the join is co-located and each node works on its local rows, so nothing has to be broadcast or reshuffled. ALL suits small dimensions because it copies them everywhere, which is wasteful for a large fact table. EVEN spreads rows evenly for load balance but forces redistribution during key joins.
Why the other options are wrong
- A. ALL copies the entire table to every node, multiplying storage and load time; it is intended for small dimension tables.
- C. EVEN gives balanced storage but ignores join keys, so the engine must move rows across nodes to complete the join.
- D. Omitting distribution and sort keys leaves the optimiser without co-location or zone-map benefits, so scans and joins read more blocks.