Databricks-Spark-Assoc Troubleshooting and Tuning DataFrame Apps Practice Test 1 — 15 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.