Databricks-Spark-Assoc Troubleshooting and Tuning DataFrame Apps — 30 Questions

Question 1

A Spark SQL query is running slowly because of a large broadcast join that keeps failing. What is the most likely reason for this failure?

Accepted Answer

The smaller table exceeds the maximum allowed broadcast memory.. Broadcast joins require the smaller table to fit entirely within the memory of each executor. If the table is too large, the executor will run out of memory attempting to store the broadcasted variable. Developers must monitor the size of tables involved in joins and either increase executor memory or force a shuffle join if the small table exceeds the broadcast threshold defined in the Spark configuration.

Answer

The data is not partitioned correctly for a broadcast join.

Answer

The driver node lacks sufficient memory to store the broadcasted table.

Answer

The tables are not indexed by the join key.

Question 2

Which TWO actions can help prevent data skew in a join operation?

Accepted Answer

Broadcasting the smaller DataFrame in the join.. Data skew is a common problem in distributed systems, where a few partitions hold significantly more data than others, causing some tasks to finish much later. By salting keys or broadcasting small tables, developers can ensure that data is evenly distributed across executors. This is essential for preventing bottlenecks that cause jobs to run significantly longer than expected, ultimately improving overall throughput and cluster efficiency in Databricks environments.

Answer

Using a higher number of shuffle partitions.

Answer

Increasing the executor memory limit.

Answer

Setting the join algorithm to merge sort.

Question 3

Which action allows Spark to perform 'predicate pushdown' when reading data from a Parquet file?

Accepted Answer

Filtering the DataFrame before an action.. Predicate pushdown allows Spark to filter rows at the storage level, so only the relevant data is read into memory. By applying filters directly to the DataFrame before any other operation, developers leverage the metadata stored within Parquet files (like min/max values for columns). This significantly reduces the amount of I/O, which is a major performance boost for large-scale data lake queries in Databricks environments.

Answer

Calling the repartition() method on the DataFrame.

Answer

Caching the DataFrame before filtering.

Answer

Using the collect() action on the unfiltered DataFrame.

Question 4

Your Spark job is failing with an OutOfMemoryError (OOM) during a shuffle-heavy transformation. Which TWO configurations should you investigate to troubleshoot this memory pressure?

Accepted Answer

spark.memory.fraction. OOM errors during shuffles are frequently caused by insufficient memory for shuffle buffers or suboptimal partition sizes. Adjusting the shuffle memory fraction and the number of partitions directly impacts how Spark manages data in memory during the exchange phase. Understanding these parameters is essential for any developer managing large-scale data processing to prevent pipeline failures in memory-constrained cluster environments.

Answer

spark.executor.cores

Answer

spark.driver.maxResultSize

Answer

spark.sql.autoBroadcastJoinThreshold

Question 5

Which action should be prioritized when observing that a DataFrame job is performing frequent 'spilling to disk' during a sort-merge join?

Accepted Answer

Increase the number of shuffle partitions to reduce task-level memory usage.. Frequent spilling indicates the dataset being processed exceeds the available memory per task. By increasing the cluster's memory or reducing the amount of data processed per task (via partition adjustments), you allow Spark to perform operations in-memory. Reducing disk I/O is critical for performance because memory access is orders of magnitude faster than disk operations, preventing significant delays in job completion times.

Answer

Decrease the number of executor instances in the cluster.

Answer

Switch from Parquet to CSV format to reduce serialization overhead.

Answer

Disable the Spark SQL Adaptive Query Execution feature.

Question 6

Which THREE factors can lead to 'Data Skew' in a Spark DataFrame application?

Accepted Answer

Join keys containing a large proportion of NULL values.. Data skew occurs when data is unevenly distributed across partitions, causing some tasks to take much longer than others. Identifying these causes—such as null keys, high-frequency keys, or poor partitioning strategies—is fundamental to troubleshooting performance. Developers must recognize these patterns early to apply remediation techniques like salting, broadcast joins, or custom repartitioning strategies to ensure the cluster resources are utilized efficiently during long-running batch jobs.

Answer

Using too many shuffle partitions in the Spark configuration.

Answer

The presence of a high-cardinality join key column.

Question 7

What is the primary function of the Spark UI 'SQL' tab when troubleshooting a poorly performing DataFrame application?

Accepted Answer

It shows the DAG representation of the query plan and its stage metrics.. The SQL tab provides a visual representation of the query plan, including the physical execution stages and metrics. By inspecting this, developers can identify bottlenecks like expensive shuffles, broadcast joins that failed to trigger, or skewed partitions. It is the single most important diagnostic tool for understanding how Spark translates high-level DataFrame transformations into optimized low-level physical operations, allowing for targeted tuning and performance improvements.

Answer

It displays the live system memory usage of the driver and executors.

Answer

It automatically generates a list of code refactoring suggestions for the script.

Answer

It allows for real-time modification of the Spark SQL execution engine code.

Question 8

You are optimizing a Spark SQL query that performs a sort on a large dataset. The job is currently failing because the sort operation exceeds the memory of the executors. Which configuration should be adjusted to allow the sort to spill to disk?

Accepted Answer

Ensure spark.local.dir points to a large, high-performance disk.. By default, Spark is configured to allow spilling when memory limits are reached. If a sort operation fails, it usually implies memory pressure that cannot be handled. Ensuring the local directory has enough disk space and that memory management is configured to allow spilling to disk is essential. This allows the operation to continue by leveraging slower but more abundant disk storage.

Answer

Increase spark.driver.maxResultSize.

Answer

Set spark.memory.fraction to 1.0.

Answer

Disable spark.sql.adaptive.enabled.

Question 9

Which THREE actions help mitigate the 'small files problem' in Spark?

Accepted Answer

Use the coalesce() transformation before writing.. The small files problem occurs when a job produces many tiny files, leading to high metadata overhead and inefficient I/O. Mitigating this involves controlling how data is partitioned before writing and using specialized techniques like coalesce or repartition to adjust the number of output files. Ensuring files are written in optimal sizes is key to maintaining high performance in downstream read operations.

Answer

Increase the number of shuffle partitions.

Answer

Use repartition() to increase parallelism.

Question 10

A developer runs a PySpark job on Databricks that joins a 400 GB Delta table with a 2 GB Parquet table. The job is slow and the Spark UI shows that the 2 GB table is being shuffled across the network for every join stage. The developer wants to avoid this shuffle. Which change should be made to the join?

Accepted Answer

Raise spark.sql.autoBroadcastJoinThreshold above 2 GB so the small table is broadcast to every executor instead of shuffled.. Broadcasting the smaller table lets each executor build a local hash table and probe it with its partition of the large table, removing the shuffle of the 2 GB side. The threshold must be raised because the default is 10 MB, far below the table size. Other tuning knobs such as shuffle partitions or file format do not change the join strategy and therefore leave the observed network exchange in place.

Answer

Increase spark.sql.shuffle.partitions to 4000 so the shuffle of the 2 GB table is parallelized across more tasks.

Answer

Convert the 2 GB Parquet table to ORC and rely on predicate pushdown to remove the shuffle during the join.

Answer

Set spark.sql.autoBroadcastJoinThreshold to -1 to disable broadcast joins, then repartition both tables by the join key.

Question 11

A data engineer notices that a Spark job on Databricks is running slowly and the Spark UI shows that many tasks are spilling to disk. The job performs a groupBy on a high-cardinality key. Which configuration change is most likely to reduce spilling and improve performance?

Accepted Answer

Increase spark.sql.shuffle.partitions to a larger number.. Spilling to disk during a shuffle indicates that individual tasks have partitions too large to fit in memory. Increasing the number of shuffle partitions splits the data into smaller chunks, reducing per-task memory requirements. This directly mitigates spilling and can improve performance, especially with high-cardinality keys that produce many distinct groups.

Answer

Set spark.sql.adaptive.enabled to false.

Answer

Decrease spark.sql.shuffle.partitions to a smaller number.

Answer

Increase spark.executor.memory to a larger value.

Question 12

A PySpark job on Databricks joins a 500 GB Delta table with a 2 GB Delta table using a broadcast hash join. The job runs out of memory on the executors. You have already increased executor memory, but the issue persists. What should you do to resolve the memory pressure while keeping the join efficient?

Accepted Answer

Reduce the number of executor cores per executor to lower the number of concurrent broadcast copies.. Reducing the number of cores per executor lowers the number of concurrent tasks, which in turn reduces the number of broadcast table copies held simultaneously in executor memory. This directly alleviates the memory pressure while preserving the efficient broadcast hash join. The other options either abandon the broadcast join entirely or adjust settings unrelated to memory consumption.

Answer

Increase spark.sql.broadcastTimeout to 1800 seconds to allow more time for broadcasting.

Answer

Disable broadcast join by setting spark.sql.autoBroadcastJoinThreshold to -1.

Answer

Repartition the large table to 2000 partitions before the join.

Question 13

A Databricks job processing a Delta table with 50,000 small files is slow because each task opens many files. The developer wants to reduce the number of files read per task without rewriting the table. Which two actions should be taken? (Choose two.)

Accepted Answer

Enable spark.sql.adaptive.enabled and let Adaptive Query Execution coalesce post-shuffle partitions.. Adaptive Query Execution coalesces small post-shuffle partitions at runtime, cutting the number of tasks that process fragmented data, while Delta optimized writes reduce the number of files produced by shuffle writes. Together they lower file-open overhead without a manual rewrite. Settings that increase partition counts or disable broadcast joins do not consolidate small files and can make the problem worse.

Answer

Set spark.sql.files.maxPartitionBytes to a very small value so each task reads fewer bytes.

Answer

Increase spark.sql.shuffle.partitions to 4000 so more tasks can read the small files in parallel.

Answer

Set spark.sql.autoBroadcastJoinThreshold to 0 to force a broadcast join and avoid reading small files.

Question 14

A Spark application on Databricks reads a Parquet dataset with 10,000 small files, each averaging 10 KB. The job performs a simple aggregation and takes hours to complete. The developer wants to improve performance without changing the aggregation logic. Which action is most effective?

Accepted Answer

Use Delta Lake's OPTIMIZE command to compact small files into larger ones.. The small files problem causes excessive task overhead and slow reads due to per-file metadata operations. Delta Lake's OPTIMIZE command compacts small files into larger ones, reducing the number of files and improving read efficiency. This is the most direct and effective solution without altering the aggregation logic.

Answer

Increase the number of executors to process more files in parallel.

Answer

Convert the Parquet files to CSV format to reduce file size.

Answer

Set spark.sql.files.maxPartitionBytes to a very large value to combine files into fewer partitions.

Question 15

A PySpark job on Databricks repeatedly reads the same 200 GB Delta table with different filters across several transformations, and the driver has 64 GB of RAM. You want to avoid recomputing the full table scan for each action while keeping memory pressure manageable. Which approach should you use?

Accepted Answer

Persist the filtered DataFrame with StorageLevel.MEMORY_AND_DISK and trigger an action to materialize it.. The key is to materialize the reused DataFrame so that Spark does not re-execute the same Delta table scan for each action. Persisting the filtered DataFrame with MEMORY_AND_DISK and forcing materialization via an action ensures the data is computed once and reused, balancing memory and disk usage. Other approaches either do not prevent recomputation of the base scan or are not directly aimed at eliminating repeated execution of the same lineage.

Answer

Rely on Databricks Delta Cache by enabling it on the cluster.

Answer

Persist the base DataFrame with StorageLevel.MEMORY_AND_DISK after the first action.

Answer

Call .cache() on the DataFrame after the first transformation.

Question 16

A Spark DataFrame job on Databricks reads a large Parquet dataset and performs a groupBy aggregation. The job is slow, and the Spark UI shows that most tasks are reading the same partitions repeatedly. Which configuration change would most directly improve performance?

Accepted Answer

Cache the DataFrame in memory using .cache() before the aggregation.. Caching the DataFrame before the aggregation eliminates redundant reads of the same partitions by materializing the data after the first computation. This directly addresses the repeated partition reads shown in the Spark UI. While other options can improve performance in different scenarios, caching is the most targeted fix for this specific symptom.

Answer

Enable adaptive query execution by setting spark.sql.adaptive.enabled to true.

Answer

Increase the driver memory to 8 GB.

Answer

Set spark.sql.shuffle.partitions to 2000.

Question 17

A PySpark job on Databricks processes a 200 GB Delta table using df.groupBy('customer_id').agg(sum('amount')). The job runs for 3 hours, but Spark UI shows that 95% of tasks finish in 2 minutes while a few tasks run for over 2 hours. The shuffle read size is evenly distributed. Which Spark configuration change is most likely to resolve the long-running tasks?

Accepted Answer

Set spark.sql.adaptive.enabled=true and spark.sql.adaptive.skewJoin.enabled=true to allow AQE to handle skewed partitions automatically.. When a few tasks run far longer than the majority and shuffle read sizes are even, the root cause is typically skewed grouping keys. Adaptive Query Execution with skew join support can detect and split oversized partitions during execution. This dynamic optimization rebalances the work, reducing the tail latency caused by hot keys, and is the most direct fix for the observed behavior.

Answer

Increase spark.executor.memory to 32g to give each executor more memory for the large partitions.

Answer

Set spark.sql.autoBroadcastJoinThreshold=-1 to disable broadcasting and force a sort-merge join.

Answer

Increase spark.sql.shuffle.partitions from 200 to 2000 to create more partitions.

Question 18

A PySpark DataFrame job on Databricks repeatedly runs out of memory during a groupBy aggregation on a 500 GB Parquet dataset. The Spark UI shows that the task with the largest shuffle read processes 20 times more records than the median task, and the job fails with java.lang.OutOfMemoryError on the executor. You want to resolve the failure without changing the aggregation logic. Which action should you take?

Accepted Answer

Enable adaptive query execution and set spark.sql.adaptive.skewJoin.enabled to true so Spark splits the skewed partition before the aggregation.. The failure is a classic aggregation skew problem: one shuffle partition is far larger than the others, so a single task exhausts executor memory. Adaptive query execution with skew join handling detects and splits those oversized partitions at runtime, letting the same aggregation complete with balanced task sizes and without rewriting the transformation logic.

Answer

Call repartition(200) on the DataFrame immediately before the groupBy to evenly distribute the records.

Answer

Increase spark.sql.shuffle.partitions from 200 to 10000 so every task receives fewer records.

Answer

Set spark.executor.memory to 64g and spark.executor.cores to 1 so each executor has maximum heap for the aggregation.

Question 19

A nightly PySpark job joins a 900 GB transactions DataFrame with a 3 GB customers DataFrame. The join key is heavily skewed: one customer ID accounts for roughly 35 percent of all transactions, and a single task runs for hours while the rest of the stage finishes quickly. You cannot change the source data or the join key. Which approach most directly resolves the skew?

Accepted Answer

Enable adaptive query execution with skew join handling by setting `spark.sql.adaptive.skewJoin.enabled` to true.. Skew join handling in Adaptive Query Execution is designed for exactly this case: a post-shuffle stage where a few partitions vastly exceed the median. It splits oversized partitions and replicates the corresponding build-side rows, so the hot key's aggregation or join work is parallelized across several tasks while the data and key stay untouched.

Answer

Broadcast the 3 GB customers DataFrame with `broadcast(df_customers)` to eliminate the shuffle.

Answer

Call `df_transactions.coalesce(8)` before the join to reduce the number of input partitions.

Answer

Increase `spark.sql.shuffle.partitions` to 4000 so the hot partition is split across more tasks.

Question 20

A Spark Structured Streaming job on Databricks reads from a Kafka topic and writes to a Delta table using foreachBatch. The job processes 10,000 records per second, but after several hours, the batch duration steadily increases and eventually the job falls behind. The Spark UI shows that the number of active batches is growing and shuffle spill is high. Which action should you take to stabilize the streaming job?

Accepted Answer

Configure a trigger interval that allows the job to keep up, such as using Trigger.AvailableNow or increasing the processing time between micro-batches.. A streaming job that cannot keep up with its input rate will accumulate batches, leading to increasing latency and resource contention. Adjusting the trigger to process data in larger chunks or at a controlled pace allows the engine to catch up. Using Trigger.AvailableNow processes all available data in one batch, which can drain a backlog efficiently, while a longer processing interval gives each batch more time to complete.

Answer

Enable Kafka consumer group rebalancing by setting a unique group.id per executor.

Answer

Set spark.streaming.kafka.maxRatePerPartition to a very high value to consume faster.

Answer

Increase spark.sql.shuffle.partitions to match the number of Kafka partitions.