Troubleshooting, Monitoring, and Optimization practice questions

Use the 'broadcast' hint on the skewed table.

Why it fails: Broadcasting a skewed table will likely lead to an OOM error because the skewed partition will exceed the memory capacity of the executor. If the skewed key is massive, the broadcast mechanism cannot handle the unbalanced distribution, making this a dangerous approach for high-skew scenarios.

Increase the number of worker nodes to improve parallelism.

Why it fails: Adding more nodes does not fix data skew because the bottleneck is the single task processing the skewed key. No matter how many nodes you add, that specific task remains on a single executor, and the job will still wait for that one worker to finish processing the skew.

The spark_conf key name is invalid.

Why it fails: The configuration key provided is syntactically correct for Databricks. The issue is not the format or the key name itself, but rather the underlying semantic conflict between the requested retention duration and the platform's safety policies for protecting Delta tables against accidental data deletion.

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

Why it fails: VACUUM operates on the underlying Delta files on storage, independent of whether the table is registered in the Hive Metastore or Unity Catalog. The error is related to the retention duration policy, not the metadata cataloging status of the table being cleaned by the VACUUM command.

The cluster does not have sufficient permissions to delete files.

Why it fails: If permissions were the issue, the error would specifically report an 'Access Denied' or 'Permission Denied' exception related to the cloud storage provider. The failure to run VACUUM with a 0-hour retention period is a policy-driven error enforced by the Databricks engine itself.

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

Why it fails: Increasing driver memory may temporarily delay the OOM but does not solve the underlying issue of collecting too much data. If the dataset grows, the problem will recur. It is a band-aid rather than a fix. Moreover, it wastes resources and may not be feasible if the data size exceeds available memory limits.

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

Why it fails: AQE optimizes query plans at runtime, such as coalescing partitions or handling skew, but it does not reduce the amount of data collected to the driver. The OOM is caused by collect(), not by inefficient query execution. Enabling AQE may improve overall job performance but will not prevent the driver from running out of memory during a large collect().

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

Why it fails: collect() is an action that gathers all data to the driver, regardless of the number of executors. Adding executors does not distribute the driver's memory load; it only increases parallel processing before the collect. The driver remains the single point of accumulation, so this action does not mitigate the OOM error and may even worsen it by producing more data faster.

Convert the target table to a Parquet table.

Why it fails: Converting to Parquet removes Delta Lake's transaction log and advanced features like Z-ORDER, data skipping, and ACID guarantees. This would likely degrade performance and eliminate the ability to optimize file layout. Parquet is not a substitute for Delta Lake's optimization capabilities for MERGE operations.

Increase the shuffle partitions to 2000.

Why it fails: Increasing shuffle partitions may reduce per-partition data size but does not address the underlying issue of scanning and rewriting many files. Without proper data clustering, the merge still processes a large number of files. This setting might help with skew but is not the primary optimization for file-level pruning in MERGE operations.

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

Why it fails: Optimize Write reduces the number of small files during write operations, but it does not reorganize existing data for better file pruning during MERGE. It can help with file compaction on write, but the primary issue of scanning a large number of files during merge is better addressed by clustering the data on the merge key.

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

Why it fails: Increasing shuffle partitions can help with parallelism, but the issue is not necessarily due to insufficient parallelism. The problem is more likely caused by reading many small files from the source table. Adjusting shuffle partitions without addressing the file count may not yield significant improvement and could even add overhead.

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

Why it fails: Auto-optimize on the target table can help with write performance, but the scenario indicates that the source table has frequent small appends. The read side is likely the bottleneck. Auto-optimize on the target would not reduce the number of small files in the source, so it does not address the primary cause of increased latency.

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

Why it fails: Increasing the trigger interval reduces the number of micro-batches but does not address the root cause of increasing per-batch latency. Each micro-batch would still process more data and could become even slower due to larger volumes. The latency issue likely stems from inefficient file management in the source or target table, not the trigger frequency.

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

Why it fails: Adaptive Query Execution optimizes shuffle partitions and join strategies at runtime, but it does not change the fact that collect() brings all data to the driver. AQE cannot prevent driver OOM caused by collecting a large dataset. While AQE is generally beneficial, it does not address the specific anti-pattern in this scenario.

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

Why it fails: Increasing driver memory may temporarily delay the failure but does not fix the underlying anti-pattern of collecting large data to the driver. The dataset may grow over time and exceed any fixed memory limit. The engineer should avoid bringing large data to the driver altogether, making this a stopgap rather than a proper solution.

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

Why it fails: Broadcast joins can cause driver or executor OOM if the broadcasted table is too large, but this job fails during a collect() operation, not a join. Disabling broadcast joins does not affect the collect() call and will not resolve the driver OOM. This setting is irrelevant to the described failure.

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

Why it fails: Setting spark.driver.maxResultSize to 0 disables the limit, which can actually worsen the problem by allowing the driver to attempt to hold even more data, leading to a crash. This configuration controls the maximum size of the result collected to the driver, and setting it to 0 is dangerous for large collects.

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

Why it fails: Using take(100) reduces the amount of data returned to the driver, which can prevent out-of-memory errors. However, it changes the semantics of the operation by only returning a subset of rows. If the goal is to retrieve all results, this is not a valid solution. The better approach is to avoid collecting large datasets to the driver altogether.

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

Why it fails: Increasing driver memory might temporarily alleviate the issue, but it is not a scalable solution. The collect() operation still brings all data to the driver, which can overwhelm even a larger node if the dataset is sufficiently large. The root cause is the anti-pattern of collecting large data to the driver, not insufficient memory.

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

Why it fails: delta.logRetentionDuration controls how long the transaction log history is retained, but it does not prevent the deletion of data files. The FileNotFoundException occurs because data files referenced by older snapshots have been physically removed by VACUUM. Extending log retention alone does not keep the data files, so it would not solve the problem.

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

Why it fails: Change Data Feed tracks row-level changes between versions, but it does not prevent the deletion of old data files. CDF is useful for capturing changes, but if the underlying files are vacuumed, the change data may also become inaccessible. Enabling CDF does not address the retention of the physical files themselves.

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

Why it fails: Disabling the retention duration check allows VACUUM to run with a shorter retention period than the default, which could lead to premature deletion of files and exacerbate the problem. This setting is a safety check to prevent accidental deletion of files still needed; disabling it is unsafe and not a solution for ensuring historical data availability.

Cache the smaller table in memory before the join.

Why it fails: Caching the smaller table might help if it is reused multiple times, but it does not directly address spilling during a single join. Caching consumes memory that could be used for join operations, potentially exacerbating spilling. For a one-time join, caching is unlikely to provide significant benefit and may waste resources.

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

Why it fails: Broadcasting a large table can cause out-of-memory errors on executors and is generally not advisable unless the table is small enough to fit in memory. The scenario mentions a large join, so broadcasting the larger table is likely to fail or cause severe performance degradation. Broadcasting is suitable only for small tables.

Reduce the number of shuffle partitions to minimize task overhead.

Why it fails: Reducing shuffle partitions would create larger partitions, increasing the likelihood of spilling and potentially causing out-of-memory errors. While fewer tasks reduce overhead, the trade-off is worse when data is large and already spilling. The goal is to balance partition size, not simply minimize task count.

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

Why it fails: This configuration does not exist. Delta Lake does not have a property named retryWriteConflicts. While Delta Lake does have automatic conflict resolution for certain operations, it is not controlled by this setting. The engineer should look for a more appropriate mechanism, such as job retries or idempotent writes.

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

Why it fails: Delta Lake uses optimistic concurrency control by default; there is no property called delta.optimisticConcurrency. The table property that controls isolation level is delta.isolationLevel, but it does not provide automatic retries. Setting it would not achieve the desired automatic retry on conflicts.

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

Why it fails: Delta Lake's MERGE INTO does not support a 'retryOnConflict' option. While MERGE can handle some conflicts through optimistic concurrency, there is no such option to enable retries. The engineer would need to implement retry logic manually or rely on job-level retries.

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

Why it fails: Recreating the table would drop and recreate it, losing data and history. The FileNotFoundException is not due to corrupted metadata but due to missing files. Recreating the table would not restore the missing files and would disrupt downstream consumers. It is an overly destructive action that does not address the underlying issue of files being vacuumed while still in use.

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

Why it fails: Running VACUUM with 0 hours retention deletes all old files, including those still needed by active readers. This would exacerbate the problem, causing more FileNotFoundExceptions. VACUUM is meant to remove files no longer referenced by the Delta log after the retention period, but setting retention to 0 removes files that may be required for time travel or concurrent reads. It does not resolve the error and can cause data loss.

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

Why it fails: Auto compaction merges small files to improve read performance, but it does not prevent files from being deleted by VACUUM. The error is caused by missing files, not by small file overhead. Enabling auto compaction might reduce the number of files but will not bring back deleted files or stop VACUUM from removing files needed by active readers. It is unrelated to the FileNotFoundException.

Decrease the number of partitions in the Spark cluster.

Why it fails: Decreasing the number of partitions results in larger individual partitions being processed by each executor. This actually increases the likelihood of OOM errors because each executor must manage a larger chunk of data at once. Effective Spark tuning typically involves increasing partition counts to distribute memory pressure across nodes.

Enable dynamic allocation for the cluster.

Why it fails: Dynamic allocation adjusts the number of executors based on the current workload. While it helps with resource efficiency and cost, it does not increase the per-executor memory available for a specific task. If a single task is too large for an executor, dynamic allocation will not prevent the OOM error.

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

Why it fails: Caching all DataFrames indiscriminately consumes excessive memory and often leads to even faster OOM failures. Caching should be used strategically only on data that is reused multiple times within the same job, and even then, it should be managed carefully to ensure it does not exhaust the available heap.

The Delta Lake History logs.

Why it fails: Delta Lake history logs are used to audit table changes, view versioning information, and facilitate time travel. They contain metadata about transactions but do not provide the execution plans, physical operators, or performance metrics necessary to diagnose why a specific SQL query is running slowly or inefficiently.

The Cluster Metrics dashboard.

Why it fails: Cluster metrics track hardware utilization such as CPU, memory, and network throughput over time. While useful for identifying resource starvation, these metrics do not show the specific SQL execution plan or the logic behind the data transformation, making them insufficient for debugging query-level performance problems or logical bottlenecks.

The Data Explorer.

Why it fails: The Data Explorer is designed for inspecting schema, table properties, and file-level metadata within the Databricks Unity Catalog or Hive Metastore. It serves as a visual interface for discovery and management but does not provide runtime performance data or query execution plans for active or historical SQL jobs.

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

Why it fails: While auto-scaling helps adjust resource usage for batch jobs, streaming jobs perform best with a stable set of resources. Changing cluster size dynamically can cause latency spikes during resharding. A fixed-size cluster is actually preferred for streaming to ensure predictable performance and state management stability.

Using too few partitions in the input source.

Why it fails: While parallelization is important, having 'too few' partitions is usually solved by repartitioning data after the initial ingest. Input source parallelism is typically dictated by the external system (like Kafka partitions) and cannot be easily changed in Databricks without modifying the external upstream source configuration.

Implement a Shuffle Hash Join.

Why it fails: A shuffle hash join requires redistributing both datasets across the cluster based on join keys. This triggers heavy network I/O, which is counterproductive when one side is small enough to fit in memory. It is generally less efficient than a broadcast join for these specific join scenarios.

Increase the number of shuffle partitions.

Why it fails: Increasing shuffle partitions is useful for breaking up data skew, but it increases the number of tasks and network overhead. For joins involving small tables, shuffling is exactly what you want to avoid; therefore, increasing partition counts does not address the core inefficiency of the join operation.

Enable Z-Ordering on the join key.

Why it fails: Z-Ordering improves data skipping by clustering related information within files. While it makes table scans faster, it does not change how Spark executes the join algorithm. It is a storage optimization technique, not a compute execution strategy, and does not replace the benefits of a broadcast join.

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

Why it fails: This command removes data from the table log and marks it as deleted in the Delta metadata, but it does not physically remove the underlying Parquet files from cloud storage. The storage space remains occupied until a vacuum operation is executed to garbage collect the files.

TRUNCATE TABLE table_name;

Why it fails: TRUNCATE removes all data from the table but does not necessarily reclaim the physical storage space in the cloud provider's storage layer immediately. It leaves the underlying directory structure and potentially some data files behind, requiring a vacuum operation to perform final cleanup and reclamation of space.

DROP TABLE table_name;

Why it fails: Dropping a table removes the entry from the metastore, but depending on the configuration and storage type, it may not automatically delete the underlying data files in S3 or ADLS. Relying on DROP TABLE for space management is unreliable and does not provide granular control over file retention.

The Spark UI.

Why it fails: The Spark UI focuses on task-level execution, stage progression, and SQL plan details. While it shows some resource information, the dedicated Metrics tab in the cluster UI provides the comprehensive, time-series hardware utilization data required for effective resource management and cluster tuning beyond the scope of individual Spark jobs.

The Query Profile.

Why it fails: The Query Profile is a specialized tool for analyzing the performance of SQL queries, offering insights into operator-level execution times and data movement. It is excellent for logic optimization but lacks the hardware-level telemetry and cluster-wide resource utilization metrics provided by the native cluster monitoring tools.

The Databricks Job Run History.

Why it fails: Job run history displays the status, duration, and logs of finished or running jobs. It helps in auditing performance and debugging failed jobs but does not provide the granular, real-time hardware metrics like CPU and memory usage necessary for troubleshooting resource-level issues while the job is still active.

The requirement for high availability during query execution.

Why it fails: High availability is not a differentiator between Job and All-Purpose clusters; both types can be configured with similar reliability features. The primary difference between the two is the intended use case—production automation versus interactive data science—and the resulting cost structure, rather than the intrinsic availability of the infrastructure.

The number of users concurrently using the cluster.

Why it fails: While All-Purpose clusters can be shared by multiple users, this is a policy decision rather than a technical limitation distinguishing them from job clusters. Job clusters are single-task oriented and not meant for multi-user, interactive access, but concurrent usage is not the primary factor in selecting the cluster type.

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

Why it fails: The choice between Job and All-Purpose clusters is independent of the data source being accessed. Both types of clusters support the same connectors and security configurations (like Unity Catalog). The decision is purely based on the cost, lifecycle, and interaction requirements of the specific business process being executed.

spark.sql.autoBroadcastJoinThreshold

Why it fails: This configuration sets the size threshold for broadcasting tables during a join. While it is an important performance tuning setting, it is not related to enabling the Adaptive Query Execution framework, which handles a much wider range of runtime optimizations beyond just broadcast join decisions.

spark.databricks.adaptive.execution

Why it fails: This is not a valid Spark or Databricks configuration property. The correct setting is spark.sql.adaptive.enabled. Using incorrect configuration names will lead to ignored settings and no performance improvements, demonstrating the need for engineers to be familiar with the official Spark documentation for valid optimization parameters.

spark.sql.shuffle.partitions

Why it fails: This setting controls the default number of shuffle partitions, which is a static configuration used by Spark before executing a query. It is not the command that enables the AQE engine itself, although AQE can dynamically adjust the number of shuffle partitions if enabled properly.

Partition the table by every column used in the query.

Why it fails: Partitioning by too many columns (high-cardinality columns) leads to the 'small file problem'. This creates too many tiny files, which increases metadata overhead and slows down query planning and execution. Partitioning should be used judiciously, typically on columns with low cardinality like dates or regions.

Always set the broadcast join threshold to -1.

Why it fails: Setting the broadcast threshold to -1 disables broadcast joins, which forces Spark to use shuffle-heavy join algorithms. This is generally detrimental to performance for common analytical queries where dimension tables are small enough to be broadcast, making it an incorrect strategy for improving read efficiency.