When and how do you use Broadcast Join?

When: One join side fits in memory across executors (typically < ~100MB, configurable via spark.sql.autoBroadcastJoinThreshold). How: Use broadcast(df) hint or rely on Spark auto-broadcast. Driver sends the small table to all executors; join runs locally without shuffle of the large table. Why: Shuffle of a large fact table is expensive (network, disk, serialization); broadcasting the dimension avoids it. Scalability trade-off: As the small table grows, broadcast uses more driver and executor memory; beyond a point, Sort-Merge Join is safer. Cost implication: Broadcast join can be 10–50× cheaper in CPU and I/O for dimension–fact joins. Monitor driver memory—broadcast originates there. Best practice: Use for dimension tables, lookups; profile table sizes; avoid broadcasting large tables (OOM risk).