When and how do you use Broadcast Join in Spark?

Broadcast join sends the small table to every executor; join happens locally without shuffle. Trigger: broadcast(df) hint or spark.sql.autoBroadcastJoinThreshold (default 10MB). Why use it: Avoids shuffling the large table—the dominant cost in sort-merge join. When: One table fits in executor memory (typically < 100MB after serialization). Scalability trade-off: Driver fetches and distributes; oversized broadcast causes driver OOM. Cost implication: Broadcast is free (no shuffle) for the large table; sort-merge shuffle scales with data size. Architectural logic: Fact–dimension joins (fact large, dimension small) are prime candidates. Anti-pattern: Broadcasting a 500MB table—exceeds memory, spills, or fails. Best practice: Set autoBroadcastJoinThreshold based on executor memory; use broadcast hint when optimizer chooses wrong side; monitor driver memory.