Databricks-DE-Assoc Troubleshooting, Monitoring, and Optimization — Timed 30 Questions

Question 1

An engineer has discovered that a specific join operation is extremely slow due to severe data skew on the join key. Which strategy should they use to mitigate this?

Accepted Answer

Apply 'salting' to the join key.. While salting is a manual technique for skew, Databricks Adaptive Query Execution (AQE) includes a specific 'skew join optimization' feature (spark.sql.adaptive.skewJoin.enabled) designed to automatically detect and handle skewed joins without manual code changes. Option D is a valid and preferred solution in Databricks.

Answer

Use the 'broadcast' hint on the skewed table.

Answer

Increase the number of worker nodes to improve parallelism.

Question 2

Refer to the exhibit. An engineer observes that running VACUUM on a Delta table with a retention period of 0 hours results in an error. Why is this configuration likely failing?

Accepted Answer

The retention period must be at least 168 hours.. By default, Databricks enforces a minimum retention period of 168 hours (7 days) for VACUUM to prevent deleting files that might be needed by concurrent readers or time travel. However, this safety check can be explicitly disabled by setting the Spark configuration property 'spark.databricks.delta.vacuum.parallelDelete.enabled' or more specifically 'spark.datalineline...' no wait, the property to allow retention periods shorter than 168 hours is spark.databricks.delta.retentionDurationCheck.enabled = false. Therefore, setting it to 0 hours fails unless spark.datalineline/retentionDurationCheck is disabled, but if it fails directly, it is because of spark.databricks.delta.retentionDurationCheck.enabled.

Answer

The spark_conf key name is invalid.

Answer

VACUUM requires the table to be registered in the Hive Metastore.

Answer

The cluster does not have sufficient permissions to delete files.

Question 3

A data engineer is troubleshooting a Databricks job that occasionally fails with a 'java.lang.OutOfMemoryError: GC overhead limit exceeded' on the driver node. The job performs a large collect() operation on a DataFrame and then processes the results locally. Which TWO actions should the engineer take to resolve this issue? (Choose two.)

Accepted Answer

Use take(n) instead of collect() to retrieve a limited number of rows for local processing.. The driver OOM is caused by collect(), which pulls the entire dataset into driver memory. Replacing collect() with a distributed write or using take(n) to limit rows directly reduces driver memory pressure. These actions address the root cause by avoiding or minimizing data transfer to the driver, ensuring the job can complete without exceeding memory limits.

Answer

Increase the driver memory by configuring spark.driver.memory to a higher value.

Answer

Enable Adaptive Query Execution (AQE) by setting spark.sql.adaptive.enabled to true.

Answer

Increase the number of executors to distribute the collect() operation across more nodes.

Question 4

A data engineer notices that a Databricks job writing to a Delta table is taking much longer than expected. The job uses a MERGE INTO statement that updates a large target table from a small source DataFrame. The engineer runs DESCRIBE HISTORY on the target table and sees that each MERGE operation rewrites a large number of files. Which optimization technique should the engineer apply to improve the performance of the MERGE operation?

Accepted Answer

Enable Z-ORDER on the target table's join key columns.. Z-ORDER clustering organizes data by the specified columns, enabling Delta Lake's data skipping to prune files during the MERGE scan. When the merge condition filters on the Z-ORDERed columns, Databricks can skip irrelevant files, reducing the number of files rewritten and improving overall performance. This is a standard optimization for MERGE operations with selective predicates.

Answer

Convert the target table to a Parquet table.

Answer

Increase the shuffle partitions to 2000.

Answer

Set spark.databricks.delta.optimizeWrite.enabled to true.

Question 5

A data engineer runs a Structured Streaming job that reads from a Delta table and writes to another Delta table. The job is configured with a 5-minute trigger interval. After several hours, the engineer notices that the write latency has increased significantly and the streaming query is processing each micro-batch slower than before. The source Delta table is frequently updated with small append operations, and the target table is not optimized. Which action should the engineer take to improve the streaming job's performance?

Accepted Answer

Run OPTIMIZE on the source Delta table to compact small files and improve read efficiency.. The streaming job reads from a Delta table with frequent small appends, resulting in many small files. This increases the overhead of file listing and reading per micro-batch. Running OPTIMIZE on the source table compacts small files into larger ones, reducing the number of files and improving read efficiency. This directly reduces per-batch latency and improves overall streaming performance.

Answer

Increase the number of shuffle partitions to improve parallelism during the streaming write.

Answer

Enable auto-optimize on the target Delta table to merge small files during writes.

Answer

Increase the trigger interval to 15 minutes to reduce the frequency of micro-batches.

Question 6

A data engineer is troubleshooting a Databricks job that fails with an OutOfMemoryError on the driver. The job collects a large DataFrame to the driver using collect() before writing results. The engineer wants to resolve the failure with minimal code changes. Which action should be taken?

Accepted Answer

Replace collect() with write() to persist the DataFrame directly to storage. Collecting a large DataFrame to the driver is a common cause of driver OutOfMemoryError. The driver is a single JVM with limited memory, and collect() materializes the entire dataset there. Writing the DataFrame directly to storage using write() keeps processing distributed and avoids the driver bottleneck. This change is minimal and directly resolves the failure.

Answer

Enable Adaptive Query Execution by setting spark.sql.adaptive.enabled to true

Answer

Increase the driver memory by setting spark.driver.memory to a higher value

Answer

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

Question 7

A data engineer is troubleshooting a Databricks job that fails with an error indicating that the driver node has run out of memory. The job performs a large collect() operation on a DataFrame. Which action should the engineer take to resolve this issue?

Accepted Answer

Write the DataFrame to a Delta table and process it there instead of collecting.. The collect() operation brings all data from the cluster to the driver, which can cause out-of-memory errors if the dataset is large. The best practice is to avoid collecting large datasets to the driver. Instead, write the data to a distributed storage like Delta Lake and continue processing in a distributed manner, which avoids the driver bottleneck.

Answer

Set spark.driver.maxResultSize to 0 to remove the limit on driver memory.

Answer

Replace collect() with take(100) to limit the data returned to the driver.

Answer

Increase the driver node's memory by selecting a larger instance type.

Question 8

A data engineer is troubleshooting a Databricks job that occasionally fails with a 'FileNotFoundException' when reading from a Delta table. The table is updated by a separate streaming job that performs frequent merges and deletes. The engineer suspects that the issue is related to data retention and file cleanup. Which configuration should the engineer adjust to ensure that the reading job can still access the required historical versions of the table?

Accepted Answer

Increase the Delta table property delta.deletedFileRetentionDuration to a longer period.. The FileNotFoundException arises because VACUUM has removed data files that are still referenced by an older snapshot. Increasing delta.deletedFileRetentionDuration extends the time before such files can be deleted, ensuring they remain available for historical reads. Log retention and CDF do not control physical file deletion, and disabling the retention check is unsafe.

Answer

Increase the Delta table property delta.logRetentionDuration to a longer period.

Answer

Enable Change Data Feed (CDF) on the Delta table.

Answer

Set the Spark configuration spark.databricks.delta.retentionDurationCheck.enabled to false.

Question 9

A data engineer is optimizing a Databricks job that performs a large join between two Delta tables. The engineer notices that the job is spilling data to disk and taking a long time. Which TWO actions should the engineer take to improve performance? (Choose two.)

Accepted Answer

Increase the number of shuffle partitions to distribute the data more evenly.. Spilling during a large join often indicates that shuffle partitions are too large or skewed. Increasing the number of shuffle partitions and enabling AQE to dynamically optimize partition sizes can both reduce spill and improve performance. Broadcasting a large table or reducing partitions would likely worsen the issue, and caching the smaller table does not directly solve spilling.

Answer

Cache the smaller table in memory before the join.

Answer

Broadcast the larger table to all nodes to avoid a shuffle.

Answer

Reduce the number of shuffle partitions to minimize task overhead.

Question 10

A data engineer has a Databricks job that writes to a Delta table. The job sometimes fails due to concurrent write conflicts. The engineer wants to ensure that the job can automatically retry on such conflicts. Which feature should the engineer use?

Accepted Answer

Configure the Databricks job to retry on failure with a maximum retry count.. Databricks jobs support automatic retries on failure. Configuring a retry policy allows the job to restart when a transient error such as a concurrent write conflict occurs. This is the most straightforward approach. Other options reference non-existent configurations or options, and do not provide automatic retries.

Answer

Set the Spark configuration spark.databricks.delta.retryWriteConflicts.enabled to true.

Answer

Enable optimistic concurrency control by setting delta.optimisticConcurrency to true.

Answer

Use the MERGE INTO statement with the option 'retryOnConflict' set to true.

Question 11

A data engineer is troubleshooting a Databricks job that reads from a Delta table and writes to another Delta table. The job suddenly starts failing with a `FileNotFoundException` during read operations. The engineer verifies that the source table exists and is accessible. Which action should the engineer take to resolve this error?

Accepted Answer

Increase the retention period for VACUUM to ensure files are not deleted prematurely.. The FileNotFoundException typically occurs when VACUUM deletes files that are still referenced by active readers. This can happen if the retention period is set too short or if a long-running job reads files after they have been vacuumed. Increasing the retention period ensures that files remain available for the duration of the read operations. Other options either worsen the problem, are destructive, or do not address the missing files.

Answer

Recreate the source table using `CREATE OR REPLACE TABLE` to refresh metadata.

Answer

Run `VACUUM` on the source table with a retention period of 0 hours to clean up old files.

Answer

Enable auto compaction on the source table to merge small files.

Question 12

A Databricks job is failing due to 'Out of Memory' (OOM) errors during a join operation on two large datasets. Which TWO actions could help mitigate this issue?

Accepted Answer

Increase the cluster's worker node instance type with more memory.. OOM errors during joins often occur because the driver or worker nodes lack enough memory to handle the shuffle or broadcast operations. Increasing the cluster memory allows for larger data partitions to reside in memory, while adjusting the broadcast join threshold prevents the optimizer from attempting to broadcast datasets that exceed available node capacity. These configurations are essential for stabilizing large-scale ETL pipelines that process high-volume data.

Answer

Decrease the number of partitions in the Spark cluster.

Answer

Enable dynamic allocation for the cluster.

Answer

Use the cache() method on every DataFrame in the pipeline.

Question 13

Which Databricks feature should a data engineer use to view the execution plan, including information about the physical operators and data lineage, to troubleshoot a slow-running SQL query?

Accepted Answer

The Spark UI SQL tab.. The Spark UI (specifically the SQL tab) provides a comprehensive graphical and textual representation of the query execution plan. By examining the 'Analyzed Plan' and 'Physical Plan', engineers can identify bottlenecks like expensive joins, full table scans, or lack of pruning. Mastering these diagnostic tools is essential for optimizing query performance and ensuring that execution logic aligns with the intended data processing requirements in the Databricks environment.

Answer

The Delta Lake History logs.

Answer

The Cluster Metrics dashboard.

Answer

The Data Explorer.

Question 14

A streaming job using Structured Streaming is lagging significantly behind the 'current time'. Which THREE of the following could be the root cause of this processing latency?

Accepted Answer

The trigger interval is set to a time shorter than the processing time of the batch.. Streaming latency often stems from resource contention, inefficient state management, or micro-batch trigger configurations. When the input rate exceeds the processing rate, lag accumulates. Identifying these bottlenecks requires analyzing the Spark UI for processing time vs. batch time. Understanding these factors is vital for maintaining low-latency pipelines and ensuring that SLAs are met in production-grade streaming environments deployed on Databricks.

Answer

Using a fixed-size cluster with no auto-scaling enabled.

Answer

Using too few partitions in the input source.

Question 15

An engineer has a large table that is frequently joined with small dimension tables. To optimize this, which optimization technique should be applied to the join operation?

Accepted Answer

Use a Broadcast Hash Join.. Broadcasting small tables prevents the 'shuffle' of the larger table. By sending a copy of the dimension table to every executor, Spark can perform the join locally, significantly reducing network overhead. This is a fundamental optimization for star schemas in Databricks. Knowing when and how to force broadcast joins allows engineers to dramatically speed up analytical queries by minimizing the movement of massive datasets across the cluster network.

Answer

Implement a Shuffle Hash Join.

Answer

Increase the number of shuffle partitions.

Answer

Enable Z-Ordering on the join key.

Question 16

Which Databricks command should you use to recover storage space by removing files that are no longer referenced by a Delta table and are older than the retention period?

Accepted Answer

VACUUM table_name RETAIN 30 DAYS;. VACUUM is the standard tool for cleaning up orphan files in Delta Lake. Because Delta Lake uses versioning, old files are kept for 'Time Travel' functionality until specifically vacuumed. Managing this storage is essential for cost control and compliance with data retention policies. Engineers must understand that once VACUUM is run, the history of the table is permanently pruned, making point-in-time recovery to those older versions impossible.

Answer

DELETE FROM table_name WHERE date < current_date() - 30;

Answer

TRUNCATE TABLE table_name;

Answer

DROP TABLE table_name;

Question 17

Which tool in Databricks provides real-time monitoring of cluster resource usage, including CPU, memory, and network throughput, for an active job?

Accepted Answer

The Ganglia UI.. The Metrics tab in the Databricks cluster UI offers a visual dashboard showing resource utilization. Monitoring these metrics helps engineers identify if a job is CPU-bound, memory-bound, or network-bound. This visibility is crucial for rightsizing clusters. By observing these trends, engineers can optimize costs by reducing cluster size for underutilized jobs or improve performance by scaling up resources for jobs that encounter significant hardware contention.

Answer

The Spark UI.

Answer

The Query Profile.

Answer

The Databricks Job Run History.

Question 18

When configuring a Databricks Job, which TWO factors most directly influence the choice between a 'Job Cluster' and an 'All-Purpose Cluster'?

Accepted Answer

The need for interactive debugging and notebook testing.. Choosing the right cluster type is vital for balancing cost and performance. Job Clusters are ephemeral, optimized for production tasks, and significantly cheaper, whereas All-Purpose Clusters are persistent and intended for interactive development. Understanding the cost implications and lifecycle management of these clusters is fundamental for any Databricks engineer responsible for managing operational budgets and ensuring efficient resource utilization across various environments.

Answer

The requirement for high availability during query execution.

Answer

The number of users concurrently using the cluster.

Answer

The type of data source (e.g., S3 vs. ADLS).

Question 19

Which Spark configuration property can be used to enable Adaptive Query Execution (AQE) in Databricks?

Accepted Answer

spark.sql.adaptive.enabled. Adaptive Query Execution (AQE) is a critical optimization framework in Spark that re-optimizes query plans at runtime based on statistics collected during shuffle stages. It enables features like coalescing shuffle partitions and dynamically switching join strategies. Enabling AQE is a best practice for modern Databricks workloads as it provides significant performance gains without manual intervention, automatically adapting to varying data distributions in production pipelines.

Answer

spark.sql.autoBroadcastJoinThreshold

Answer

spark.databricks.adaptive.execution

Answer

spark.sql.shuffle.partitions

Question 20

Which THREE strategies are recommended to improve the performance of reading from a Delta table in Databricks?

Accepted Answer

Use Z-Ordering on columns frequently used in WHERE clauses.. Optimizing reads is crucial for analytics. Techniques like Z-Ordering, Data Skipping, and Partitioning allow Spark to ignore irrelevant data files, reducing I/O. Proper file sizing ensures that Spark can efficiently read data in parallel. These strategies together minimize the amount of data scanned and transferred across the network, leading to significantly faster query results in large-scale data lake environments.

Answer

Partition the table by every column used in the query.

Answer

Always set the broadcast join threshold to -1.