The job that worked for two years until the lookup table got bigger

Overview

Broadcast joins are the fastest option when one side is small, and a reliable source of production outages when that side quietly stops being small. AceMQ provides named senior engineers to diagnose and resolve these failures during the incident, not after it.

Challenge

A dimension table crosses the broadcast threshold and the driver either runs out of memory materializing it or the broadcast times out waiting for executors to receive it. Stale statistics make it worse — the optimizer plans a broadcast based on a row count collected months ago. An explicit broadcast hint left in the code makes it worse still, because the optimizer has no discretion to back out. The failure appears suddenly in a job nobody has touched.

Environment

Apache Spark on YARN, Kubernetes, EMR, or Databricks, running joins against dimension tables that grow over time.

Approach

AceMQ engineers confirm from the physical plan whether a broadcast is being attempted and why, then compare the actual build-side size against the threshold and the driver's available memory. Immediate resolution is usually a threshold correction, hint removal, or driver memory adjustment; the durable fix is statistics maintenance and a review of every remaining explicit broadcast hint in the codebase.

Solution

• 15-minute emergency SLA with named senior engineers, 24/7, no tier-1 triage
• Physical plan review to confirm broadcast selection and measure the real build-side size against the configured threshold
• autoBroadcastJoinThreshold and broadcastTimeout correction sized against actual driver memory
• Removal or scoping of explicit broadcast hints that override the optimizer regardless of table growth
• Statistics refresh and ANALYZE TABLE scheduling so the optimizer plans against current row counts
• Adaptive Query Execution configuration so the plan can fall back to a sort-merge join at runtime

Outcome

Technologies