Databricks-Spark-Assoc Troubleshooting and Tuning DataFrame Apps Practice Test 4 — 15 Questions

Question 1

A developer is troubleshooting a Spark job that fails with an OutOfMemoryError on the driver. The job collects a large DataFrame to the driver using .collect() and then processes it locally. The developer wants to avoid the driver OOM while still obtaining the results. Which approach is most appropriate?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Write the DataFrame to a distributed storage system and then read it back in smaller chunks for local processing.. Collecting a large DataFrame to the driver is an anti-pattern because it moves all data to a single node, risking OOM. The scalable solution is to persist the DataFrame to distributed storage and then read it in smaller partitions or batches for local processing. This maintains distribution and avoids overwhelming the driver.

Answer

Replace .collect() with .take(1000) to limit the number of rows returned.

Answer

Increase spark.driver.memory to a very large value to accommodate the collected data.

Answer

Use .foreach() to process each row on the driver instead of collecting the entire DataFrame.

Question 2

A PySpark job on Databricks repeatedly calls `df.count()` and `df.show()` inside a loop across 40 iterations, and the Spark UI shows the identical lineage being recomputed on every iteration even though the source Delta table is unchanged. You want to avoid re-executing the upstream transformations without materializing the data to disk. What should you do?

Accepted Answer

Call `df.persist(StorageLevel.MEMORY_AND_DISK)` before the loop and `df.unpersist()` after it.. Because the same DataFrame is acted on repeatedly and the underlying Delta table does not change, persisting the DataFrame keeps the computed partitions available across iterations, so the lineage is evaluated once rather than 40 times. Repartitioning, enabling adaptive execution, or round-tripping through RDDs all leave the recomputation behavior intact.

Answer

Wrap the DataFrame in `spark.createDataFrame(df.rdd)` so the derived object keeps the computed rows in the driver.

Answer

Set `spark.sql.adaptive.enabled` to true so the optimizer reuses prior query results automatically.

Answer

Add `.repartition(200)` to the DataFrame before the loop so each iteration reads a different partition set.

Question 3

A developer notices that a PySpark DataFrame transformation chain runs a full scan of a Delta table each time a new action is invoked, even though the source data has not changed. The developer wants to persist the intermediate DataFrame in memory across actions. Which method should be used?

Accepted Answer

Call DataFrame.cache() before the first action so the DataFrame is stored in memory on first computation.. cache() persists the DataFrame across actions using the default MEMORY_AND_DISK level, so the first action computes and stores partitions and later actions read from the cache rather than rescanning the Delta table. Checkpointing writes to disk and cuts lineage but does not keep data in memory, while AQE and collect() do not provide reusable in-memory persistence.

Answer

Call DataFrame.collect() after each transformation to materialize the results on the driver.

Answer

Set spark.sql.adaptive.enabled to true so the optimizer reuses prior scan results automatically.

Answer

Call DataFrame.checkpoint() to truncate the lineage and write the data to a reliable file system.

Question 4

A Databricks notebook job calls a PySpark UDF built on a Python function that performs string parsing. The job completes successfully on a small sample, but on the full production dataset it fails with a PythonException and the executor logs show high garbage collection time. Which change is the most appropriate first step to make the job reliable without changing business logic?

Accepted Answer

Replace the Python UDF with a Spark SQL built-in function such as regexp_extract or split, which runs inside the JVM and avoids Python serialization and per-row interpreter overhead.. A scalar Python UDF forces each row through a Python worker, incurring serialization, interpreter startup, and per-row overhead that shows up as GC pressure at scale. Rewriting the logic with Spark SQL built-in functions keeps execution in the JVM with whole-stage code generation, eliminating that overhead and typically resolving both the reliability and performance symptoms. It preserves the business logic while removing the root cause rather than masking it with more resources.

Answer

Set spark.sql.execution.arrow.pyspark.enabled to true so the UDF uses Arrow for every row it processes.

Answer

Mark the UDF with the deterministic flag and register it with spark.udf.register so Spark can cache its results automatically.

Answer

Increase spark.executor.memory and spark.executor.cores so each executor has more headroom to run the Python workers.

Question 5

A developer is tuning a Spark job that performs a join between a large fact table and a medium-sized dimension table. The job suffers from data skew, with a few keys having a disproportionately large number of rows. The developer wants to mitigate the skew. Which two actions are most effective? (Choose two.)

Accepted Answer

Enable Adaptive Query Execution (AQE) and set spark.sql.adaptive.skewJoin.enabled to true.. Data skew during a join occurs when a few keys have many more rows than others, causing some tasks to run much longer. Adaptive Query Execution with skew join optimization dynamically splits skewed partitions, while manual salting distributes hot keys across multiple tasks. Both approaches balance the workload and reduce the impact of skew.

Answer

Broadcast the medium-sized dimension table to all executors.

Answer

Use a repartition on the join key before the join to evenly distribute the data.

Answer

Increase spark.sql.shuffle.partitions to a very high number.

Question 6

A Spark job performing a join between a 10 GB table and a 5 MB lookup table is running slowly, and the physical plan shows a SortMergeJoin. You want to avoid the shuffle. What should you do?

Accepted Answer

Set spark.sql.autoBroadcastJoinThreshold to a value larger than 5 MB, such as 10 MB, and ensure the small table is broadcast.. Broadcasting the small table eliminates the need to shuffle the large table, converting the join to a BroadcastHashJoin. This is achieved by ensuring the small table's size is below the auto broadcast join threshold or by using an explicit broadcast hint. The other options either do not change the join strategy or introduce unnecessary shuffles.

Answer

Repartition both DataFrames on the join key before the join.

Answer

Increase spark.sql.shuffle.partitions to 2000.

Answer

Use a cross join and filter afterward.

Question 7

A structured streaming DataFrame writes to a Delta table with a foreachBatch function that performs an upsert. After a cluster restart, the stream reprocesses some micro-batches and duplicate rows appear in the target table. The foreachBatch code already uses MERGE keyed on a unique id. Which change best prevents duplicates after restart?

Accepted Answer

Add a checkpointLocation to the writeStream options so the stream records its progress and resumes from the last committed offset.. Structured streaming tracks processed offsets and state in the checkpoint location. Without a checkpoint, a restarted query cannot know what it already committed and replays earlier micro-batches, creating duplicates even with an idempotent MERGE. Supplying checkpointLocation lets the query resume from the last committed offset, so each batch is processed once. Combined with the existing MERGE on a unique id, this yields the intended exactly-once behavior on restart.

Answer

Set the trigger to processingTime='1 minute' so micro-batches are larger and less likely to overlap.

Answer

Switch the output mode from append to complete so each micro-batch is fully replaced in the target table.

Answer

Add a deduplication step using dropDuplicates on the unique id inside the foreachBatch before the MERGE.

Question 8

A Databricks job joins a 500 GB sales table with a 300 GB returns table on a customer_id key. A few customer_id values account for a large fraction of rows on both sides, and the job fails with executor OOM during the join. The developer wants to distribute the hot keys across more partitions without changing the query logic. Which technique should be used?

Accepted Answer

Enable Adaptive Query Execution with skew join handling so Spark splits skewed partitions at runtime.. AQE skew join handling identifies partitions that are disproportionately large after the shuffle and splits them into smaller sub-partitions, each handled by a separate task. This spreads the hot customer_id values across executors and resolves the OOM. Raising shuffle partitions or repartitioning by the join key keeps each hot key in one partition, and broadcasting a 300 GB table is infeasible.

Answer

Broadcast the 300 GB returns table to every executor to avoid shuffling the skewed keys.

Answer

Increase spark.sql.shuffle.partitions to 8000 to spread the skewed keys across more tasks.

Answer

Repartition both DataFrames by customer_id with 8000 partitions before the join.

Question 9

A Spark job writes a large DataFrame to a Delta table partitioned by date. The job is taking much longer than expected, and the Spark UI shows that many tasks are writing very small files. You have already set spark.sql.shuffle.partitions to 200. What is the most effective way to reduce the number of small files written?

Accepted Answer

Enable optimized writes by setting spark.databricks.delta.optimizeWrite.enabled to true.. Enabling optimized writes on Databricks triggers an automatic shuffle before writing to Delta, which coalesces data into fewer, larger files. This directly addresses the small file problem without sacrificing parallelism or requiring manual tuning. The other options either worsen the issue, introduce bottlenecks, or do not target the root cause of many small files from partitioned writes.

Answer

Set spark.sql.files.maxRecordsPerFile to a high value to combine records into fewer files.

Answer

Use coalesce(1) before writing to reduce the number of output files.

Answer

Increase spark.sql.shuffle.partitions to 2000.

Question 10

A developer runs a PySpark job on Databricks that filters a Delta table and then calls count() and show(). The Spark UI shows two separate scans of the same table. The developer wants to avoid the second scan without changing the filter logic. Which action should be taken?

Accepted Answer

Call persist() with StorageLevel.MEMORY_AND_DISK on the filtered DataFrame before count() and show().. Persisting the filtered DataFrame with MEMORY_AND_DISK materializes it on the first action and lets the second action read the stored partitions instead of rescanning the Delta table. AQE, maxPartitionBytes, and checkpointing do not provide in-memory reuse across actions; they change plan shape or write to disk rather than caching the intermediate result for repeated access.

Answer

Call checkpoint() on the filtered DataFrame to write it to a reliable path before count().

Answer

Enable spark.sql.adaptive.enabled and set spark.sql.adaptive.coalescePartitions.enabled to true.

Answer

Set spark.sql.files.maxPartitionBytes to 256 MB so each scan reads fewer, larger partitions.

Question 11

A developer runs a PySpark job that joins a 10 GB DataFrame with a 50 MB lookup DataFrame. The job takes far longer than expected, and the Spark UI shows a SortMergeJoin with a large shuffle read and write for both sides. The developer wants the smallest change that most improves performance. Which action should the developer take?

Accepted Answer

Broadcast the 50 MB lookup DataFrame using broadcast() or by raising spark.sql.autoBroadcastJoinThreshold above 50 MB.. The Spark UI shows a SortMergeJoin with large shuffle on both sides, which is unnecessary when one input is only 50 MB. Broadcasting the small lookup table turns the join into a BroadcastHashJoin, removes the shuffle and sort of both DataFrames, and requires only a hint or a threshold adjustment.

Answer

Increase spark.sql.shuffle.partitions so the sort-merge join shuffle uses more tasks and completes faster.

Answer

Repartition the 50 MB lookup DataFrame by the join key before the join to co-locate matching rows.

Answer

Cache the 10 GB DataFrame with persist(StorageLevel.MEMORY_AND_DISK) before the join.

Question 12

A PySpark DataFrame job on Databricks runs slowly. Inspection of the Spark UI shows that a shuffle stage writes 200 partitions but downstream stages process only a few, and the physical plan shows an Exchange before a filter. Which two changes are most likely to improve performance? (Choose two.)

Accepted Answer

Reduce the number of shuffle partitions or coalesce the output so fewer, larger partitions are produced.. The plan shows an Exchange feeding stages that process only a few partitions, meaning shuffle volume and partition sizing are both inefficient. Filtering earlier cuts the rows that ever reach the shuffle, and consolidating partitions removes the overhead of many tiny tasks. Together they reduce both the data moved across the network and the number of tasks scheduled, which is what the UI evidence points to. Resource or caching changes would not target either cause.

Answer

Increase spark.sql.shuffle.partitions to a much larger number so each task handles fewer rows.

Answer

Repartition the DataFrame on the join key before the shuffle to colocate matching rows.

Answer

Cache the shuffled DataFrame with persist so downstream stages reuse the same partitions.

Question 13

A Databricks job reads a Parquet dataset, applies a chain of `withColumn` transformations, and writes it back with `.write.mode("overwrite").parquet(path)`. The Spark UI shows 6000 small output files totaling 50 GB, and a downstream reader is slow because of per-file overhead. You want to reduce the file count while keeping the write as a Spark-native operation. What should you do?

Accepted Answer

Call `df.repartition(200)` immediately before the write to consolidate data into fewer output partitions.. Output file count equals the number of partitions at write time, so consolidating partitions with a shuffle-based repartition before writing produces fewer, larger files. Read-side settings and per-file record caps do not change the write fan-out, and dropping to RDD text output sacrifices the columnar format that makes the downstream reads fast.

Answer

Convert the DataFrame to an RDD and call `saveAsTextFile` to let Spark choose the file layout.

Answer

Set `spark.sql.files.maxPartitionBytes` to a very large value before the write.

Answer

Add `.option("maxRecordsPerFile", 100000)` to the write call to merge small files.

Question 14

A Spark application reads a large CSV file and performs a series of transformations, including a filter and a join with a small lookup table. The job is running slowly, and the Spark UI shows that the CSV parsing stage is taking a long time. Which action would most improve performance?

Accepted Answer

Convert the CSV to Parquet and read the Parquet file instead.. Converting the CSV to Parquet addresses the slow CSV parsing by leveraging Parquet's columnar storage, which enables column pruning and predicate pushdown. This reduces the amount of data read and parsed, directly improving the performance of the initial stage. The other options either do not target the parsing bottleneck or could worsen performance.

Answer

Increase the number of partitions when reading the CSV by setting a lower spark.sql.files.maxPartitionBytes.

Answer

Set spark.sql.autoBroadcastJoinThreshold to -1 to disable broadcast join.

Answer

Cache the CSV DataFrame immediately after reading.

Question 15

A streaming DataFrame job on Databricks writes to a Delta table every 10 seconds. Over several hours, the number of files in the target directory grows into the hundreds of thousands, and downstream reads slow dramatically. The job uses foreachBatch with a write that produces many small files per micro-batch. Which action should be taken to reduce the small-files problem for this streaming write?

Accepted Answer

Configure the write with optimizeWrite enabled (or set the target table property delta.autoOptimize.optimizeWrite = true) so Spark coalesces output files per partition before committing.. The streaming job creates many small files because each micro-batch writes multiple files per partition. Optimize Write coalesces those files into fewer, larger ones at commit time, directly reducing file proliferation and improving downstream read performance without sacrificing parallelism or increasing latency.

Answer

Call repartition(1) on the streaming DataFrame before the write in foreachBatch.

Answer

Increase the trigger interval from 10 seconds to 10 minutes so each micro-batch writes more data.

Answer

Set spark.sql.shuffle.partitions to 1 so all output is written by a single task.