Diagnose out-of-memory errors in a growing Spark job

WHAT THIS TESTS: This tests whether you can use Spark SQL tuning techniques to diagnose out-of-memory failures without simply adding hardware. The interviewer wants to see if you understand how partitioning, join strategy, adaptive execution, and caching configuration interact to create or relieve memory pressure.

A GOOD ANSWER COVERS: First, inspect the query plan and Spark UI to identify whether the OOM occurs during a shuffle, a join, or a cache operation. Second, look for data skew by checking if a small number of tasks process disproportionate input; mention that Adaptive Query Execution can split skewed shuffle partitions and optimize skew joins automatically. Third, review partition sizing because too few partitions from spark.sql.shuffle.partitions or large file reads from spark.sql.files.maxPartitionBytes can create oversized row batches in memory. Fourth, examine join strategy choices; a missing broadcast hint may force an expensive sort-merge join when AQE would otherwise convert it to a broadcast or shuffled hash join if statistics are available. Fifth, audit caching usage because spark.sql.inMemoryColumnarStorage.batchSize directly impacts memory utilization, and larger batch sizes improve compression but risk OOMs when caching data; also verify that only required columns are cached in the columnar format. Sixth, ensure table statistics are collected so the optimizer can pick efficient plans and AQE thresholds work correctly.

COMMON WRONG ANSWERS: The biggest red flag is recommending larger cluster instances as the first step without inspecting skew, partition counts, or join plans. Another mistake is blindly calling cache on large tables without considering the batch size or columnar compression settings. Candidates also lose credibility by ignoring Adaptive Query Execution entirely, or by manually setting shuffle partitions to a fixed high number without letting AQE coalesce post-shuffle partitions afterward.

LIKELY FOLLOW-UPS: An interviewer might ask when you would force a broadcast hint versus letting AQE decide, how you would tune spark.sql.inMemoryColumnarStorage.batchSize for a wide table, or what signals in the Spark UI indicate that skewed shuffle splitting is helping. They may also ask how you balance spark.sql.shuffle.partitions against AQE coalescing, or when to prefer a shuffled hash join over a sort-merge join.

ONE CONCRETE EXAMPLE: Suppose a job reading Parquet files starts failing after data volume doubles. The Spark UI shows a single sort-merge join stage where one task reads far more data than peers. The diagnosis path is: enable Adaptive Query Execution so skewed shuffle partitions are split automatically; collect statistics on both tables so the optimizer detects that the dimension table is small enough to convert the sort-merge join to a broadcast join; increase spark.sql.shuffle.partitions if the pre-shuffle stage has fewer than two hundred partitions; and reduce spark.sql.inMemoryColumnarStorage.batchSize if the job caches a wide intermediate result. After these changes, memory pressure drops because partitions are evenly sized, the broadcast avoids a heavy shuffle, and caching uses smaller compressed batches.