Troubleshooting practice questions

A data analyst is troubleshooting a slow-running SQL query against a massive Delta table in Databricks. The query frequently scans the entire table despite filtering on a high-cardinality timestamp column. Which approach will most effectively reduce the data scanned by eliminating full-table reads?

Trap 1: Increase the cluster size to a driver instance with more memory to…

Scaling up cluster memory does not address the fundamental I/O bottleneck caused by scanning unindexed and unclustered data files from cloud object storage. Query performance remains poor because the underlying data layout still requires reading all historical partitions into memory.

Trap 2: Execute a VACUUM command with a retention threshold of zero hours…

VACUUM removes unreferenced old data files after the retention threshold; it does not reorganise data for read pruning, so full scans on the timestamp filter persist. It tempts because it reduces storage footprint, but the correct fix is liquid clustering or partitioning on that column to enable file skipping.

Trap 3: Convert the Delta table format to standard Parquet files to take…

Delta Lake is built on top of Parquet files and adds transaction logs, statistics, and advanced optimization features like Z-Ordering. Reverting to standard Parquet removes these built-in metadata capabilities, degrading overall query planning and update performance.

A
Run an OPTIMIZE command with a ZORDER BY clause on the timestamp column to co-locate related data and improve data skipping.
Z-Ordering clusters data with similar values into the same file spaces. This enables the Delta Lake file-skipping mechanism to bypass irrelevant files entirely when queries apply range filters on the target timestamp column, directly lowering overall scan volume and query duration.
B
Increase the cluster size to a driver instance with more memory to hold the entire uncompressed Delta table in cache.
Why it fails: Scaling up cluster memory does not address the fundamental I/O bottleneck caused by scanning unindexed and unclustered data files from cloud object storage. Query performance remains poor because the underlying data layout still requires reading all historical partitions into memory.
C
Execute a VACUUM command with a retention threshold of zero hours to purge old data files immediately.
Why it fails: VACUUM removes unreferenced old data files after the retention threshold; it does not reorganise data for read pruning, so full scans on the timestamp filter persist. It tempts because it reduces storage footprint, but the correct fix is liquid clustering or partitioning on that column to enable file skipping.
D
Convert the Delta table format to standard Parquet files to take advantage of native Apache Spark partitioning.
Why it fails: Delta Lake is built on top of Parquet files and adds transaction logs, statistics, and advanced optimization features like Z-Ordering. Reverting to standard Parquet removes these built-in metadata capabilities, degrading overall query planning and update performance.

Question 2hardmultiple choice

An analyst is troubleshooting a query that hangs indefinitely during a join. Which TWO metrics in the Query Profile should the analyst examine to diagnose the issue?

Trap 1: The number of users logged into the workspace.

The number of active users has no direct impact on the performance of a single query's execution plan. While workspace-wide concurrency might affect resource allocation if the warehouse is over-subscribed, it is not a metric that explains why a specific individual query is hanging during its join operation.

Trap 2: The total number of tables in the schema.

The number of tables in a schema is metadata that does not impact the execution plan of a specific query. The SQL engine only interacts with the objects explicitly referenced in the query, so the schema's total object count is irrelevant to identifying the bottleneck of an active join.

Trap 3: The total size of the database logs.

Database or transaction logs track operations for ACID compliance and recovery. They are not involved in query execution or performance tuning. Monitoring log sizes is a task for platform administrators, not for analysts trying to debug a query that is hanging due to join logic or data distribution.

A
Shuffle Read/Write bytes.
High shuffle bytes indicate that large amounts of data are being exchanged across the network nodes. If a query hangs, it may be due to this massive data movement exceeding the cluster's network or I/O bandwidth, suggesting a need to reduce the data volume or optimize the join strategy.
B
Maximum vs Median rows per task.
A large discrepancy between the maximum and median rows processed per task confirms data skew. This means one task is handling much more data than others, causing it to 'hang' while other tasks complete quickly. Identifying this allows for the implementation of salting or other skew-handling techniques in the SQL.
C
The number of users logged into the workspace.
Why it fails: The number of active users has no direct impact on the performance of a single query's execution plan. While workspace-wide concurrency might affect resource allocation if the warehouse is over-subscribed, it is not a metric that explains why a specific individual query is hanging during its join operation.
D
The total number of tables in the schema.
Why it fails: The number of tables in a schema is metadata that does not impact the execution plan of a specific query. The SQL engine only interacts with the objects explicitly referenced in the query, so the schema's total object count is irrelevant to identifying the bottleneck of an active join.
E
The total size of the database logs.
Why it fails: Database or transaction logs track operations for ACID compliance and recovery. They are not involved in query execution or performance tuning. Monitoring log sizes is a task for platform administrators, not for analysts trying to debug a query that is hanging due to join logic or data distribution.

Question 3mediummultiple choice

An analyst notices that a dashboard visualization is displaying an incorrect aggregation of total revenue. After reviewing the SQL, the analyst confirms the query logic is correct. What is the most likely cause of this discrepancy in the rendered visualization?

Trap 1: The SQL warehouse is currently running in 'Serverless' mode.

The warehouse mode (Serverless vs. Pro vs. Classic) affects performance and startup time but does not alter the mathematical results of a deterministic SQL query. If the SQL query logic is correct, the underlying engine will return the correct values regardless of the compute cluster's infrastructure configuration.

Trap 2: The Unity Catalog metastore is failing to update the table…

Table statistics in Unity Catalog are primarily used by the query optimizer to plan execution paths. They do not impact the raw results returned by a SELECT statement. If the SQL query returns the correct output in the SQL editor, the statistics are irrelevant to the dashboard visualization's rendering.

Trap 3: The dashboard is using a legacy version of the Databricks SQL…

While Databricks updates its visualizer components, core aggregation logic remains consistent. A visualization version update would not cause a sudden, incorrect aggregation of data. Discrepancies are almost always traced back to user-defined configuration settings within the specific chart setup or data type mismatches in the source table.

A
The SQL warehouse is currently running in 'Serverless' mode.
Why it fails: The warehouse mode (Serverless vs. Pro vs. Classic) affects performance and startup time but does not alter the mathematical results of a deterministic SQL query. If the SQL query logic is correct, the underlying engine will return the correct values regardless of the compute cluster's infrastructure configuration.
B
The visualization configuration in the dashboard editor has an unintended aggregation setting.
Databricks visualizers allow users to perform secondary aggregations on the query results. If the user accidentally sets the visualization to 'Average' when they intended to show a 'Sum', the chart will display incorrect totals. This is a common UI configuration error that occurs after the SQL query executes.
C
The Unity Catalog metastore is failing to update the table statistics.
Why it fails: Table statistics in Unity Catalog are primarily used by the query optimizer to plan execution paths. They do not impact the raw results returned by a SELECT statement. If the SQL query returns the correct output in the SQL editor, the statistics are irrelevant to the dashboard visualization's rendering.
D
The dashboard is using a legacy version of the Databricks SQL visualizer.
Why it fails: While Databricks updates its visualizer components, core aggregation logic remains consistent. A visualization version update would not cause a sudden, incorrect aggregation of data. Discrepancies are almost always traced back to user-defined configuration settings within the specific chart setup or data type mismatches in the source table.

Question 4hardmultiple choice

Refer to the exhibit. An analyst notices that widget 2 is displaying incorrect trends. Based on the configuration provided, what is the most likely cause?

Exhibit

Refer to the exhibit.

[ { "id": 1, "type": "bar", "y_axis": "sales", "x_axis": "date" }, { "id": 2, "type": "line", "y_axis": "revenue", "x_axis": "date" } ]

Trap 1: The widget type is set to 'line' instead of 'bar'.

Choosing 'line' versus 'bar' is a stylistic choice and does not inherently make a trend 'incorrect'. While some trends are better shown with one type than the other, the type setting alone does not cause a calculation or data display error in the underlying values of the trend line.

Trap 2: The X-axis for widget 2 is not properly aligned to the date range.

The exhibit explicitly shows that widget 2 is configured with the 'date' X-axis. Unless there is a data quality issue in the source table, the X-axis configuration itself is valid. If the trend is wrong, the issue likely lies within the query logic or the aggregation method.

Trap 3: The 'revenue' column is incompatible with the 'line' chart type.

Revenue is a numerical value, which is perfectly compatible with line charts. Line charts are actually the preferred visualization for revenue trends over time. There is no technical incompatibility between a numerical metric and a line chart that would lead to an incorrect trend display in this context.

A
The widget type is set to 'line' instead of 'bar'.
Why it fails: Choosing 'line' versus 'bar' is a stylistic choice and does not inherently make a trend 'incorrect'. While some trends are better shown with one type than the other, the type setting alone does not cause a calculation or data display error in the underlying values of the trend line.
B
The X-axis for widget 2 is not properly aligned to the date range.
Why it fails: The exhibit explicitly shows that widget 2 is configured with the 'date' X-axis. Unless there is a data quality issue in the source table, the X-axis configuration itself is valid. If the trend is wrong, the issue likely lies within the query logic or the aggregation method.
C
The query for widget 2 does not aggregate data to match the X-axis.
If the query does not group by the 'date' column, the visualization may attempt to plot raw, un-aggregated data points, leading to a jagged or incorrect trend line. For a time-based chart, the SQL query must include an appropriate 'GROUP BY' clause to ensure the visualization displays the correct trend.
D
The 'revenue' column is incompatible with the 'line' chart type.
Why it fails: Revenue is a numerical value, which is perfectly compatible with line charts. Line charts are actually the preferred visualization for revenue trends over time. There is no technical incompatibility between a numerical metric and a line chart that would lead to an incorrect trend display in this context.

Question 5hardmultiple choice

Refer to the exhibit. An analyst is troubleshooting a performance issue where frequent small inserts into a Delta table result in degraded query performance over time. The exhibit shows the configuration applied. What is the expected behavior of these properties?

Exhibit

ALTER TABLE sales_data SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true', 'delta.autoOptimize.autoCompact' = 'true');

Trap 1: It forces all writes to be synchronous, ensuring immediate data…

Auto-optimization properties do not alter the consistency model or transaction isolation of Delta Lake. Delta Lake already provides ACID guarantees by default. These properties focus on the physical layout and file size optimization rather than the synchronization of write operations across nodes, which is handled by the Delta transaction log.

Trap 2: It automatically triggers a FULL OPTIMIZE command every hour…

Auto-compaction is an asynchronous background process that occurs based on write patterns and file sizes, not a fixed time schedule. It does not perform a full table optimization, which is a resource-intensive operation. Instead, it opportunistically compacts small files to maintain read performance during normal table operations.

Trap 3: It forces the table to use a liquid clustering layout for all…

Auto-optimization properties are independent of liquid clustering. While both aim to improve performance, they operate on different mechanisms. Auto-optimization manages file sizing and compaction, whereas liquid clustering manages data organization within files. Applying one does not automatically enable or convert the table to use the other feature.

A
It forces all writes to be synchronous, ensuring immediate data consistency across all cluster nodes.
Why it fails: Auto-optimization properties do not alter the consistency model or transaction isolation of Delta Lake. Delta Lake already provides ACID guarantees by default. These properties focus on the physical layout and file size optimization rather than the synchronization of write operations across nodes, which is handled by the Delta transaction log.
B
It automatically triggers a FULL OPTIMIZE command every hour regardless of file counts.
Why it fails: Auto-compaction is an asynchronous background process that occurs based on write patterns and file sizes, not a fixed time schedule. It does not perform a full table optimization, which is a resource-intensive operation. Instead, it opportunistically compacts small files to maintain read performance during normal table operations.
C
It improves read performance by reducing the number of small files created during ingestion.
By enabling these properties, the table writer attempts to optimize file sizes during the write phase, and the background process cleans up lingering small files. This significantly improves read performance because the query engine needs to scan fewer files, which reduces I/O overhead and speeds up overall data retrieval.
D
It forces the table to use a liquid clustering layout for all future data partitions.
Why it fails: Auto-optimization properties are independent of liquid clustering. While both aim to improve performance, they operate on different mechanisms. Auto-optimization manages file sizing and compaction, whereas liquid clustering manages data organization within files. Applying one does not automatically enable or convert the table to use the other feature.

Question 6hardmultiple choice

A data analyst is troubleshooting a slow Databricks SQL query that joins a large fact table with a small dimension table. The query plan shows a broadcast hash join, but the analyst notices that the small table is not being broadcast as expected. Which configuration should the analyst check to ensure the small table is broadcast?

Trap 1: spark.sql.adaptive.enabled

This setting enables adaptive query execution, which can dynamically optimize joins at runtime, including converting sort-merge joins to broadcast joins if one side is small enough. However, it relies on the auto-broadcast threshold. If the threshold is too low, adaptive execution may still not broadcast. The analyst should first check the threshold, making this option less direct.

Trap 2: spark.databricks.delta.optimizeWrite.enabled

This configuration controls whether Delta Lake optimizes write operations by reducing the number of small files. It has no impact on join strategies or broadcast behavior. The analyst's issue is about query execution, not write optimization, so this setting is irrelevant.

Trap 3: spark.sql.shuffle.partitions

This configuration controls the number of partitions used when shuffling data for joins or aggregations. While it affects performance, it does not determine whether a table is broadcast. Even with an optimal partition count, a table will not be broadcast if it exceeds the auto-broadcast threshold. Thus, it is not the correct setting to check for broadcast behavior.

A
spark.sql.adaptive.enabled
Why it fails: This setting enables adaptive query execution, which can dynamically optimize joins at runtime, including converting sort-merge joins to broadcast joins if one side is small enough. However, it relies on the auto-broadcast threshold. If the threshold is too low, adaptive execution may still not broadcast. The analyst should first check the threshold, making this option less direct.
B
spark.sql.autoBroadcastJoinThreshold
This Spark configuration sets the maximum size in bytes for a table to be considered for broadcasting in a join. If the small table's size exceeds this threshold, it will not be broadcast. The analyst should verify this value and adjust it if necessary to allow the small table to be broadcast, which can improve join performance by avoiding a shuffle.
C
spark.databricks.delta.optimizeWrite.enabled
Why it fails: This configuration controls whether Delta Lake optimizes write operations by reducing the number of small files. It has no impact on join strategies or broadcast behavior. The analyst's issue is about query execution, not write optimization, so this setting is irrelevant.
D
spark.sql.shuffle.partitions
Why it fails: This configuration controls the number of partitions used when shuffling data for joins or aggregations. While it affects performance, it does not determine whether a table is broadcast. Even with an optimal partition count, a table will not be broadcast if it exceeds the auto-broadcast threshold. Thus, it is not the correct setting to check for broadcast behavior.

Question 7mediummultiple choice

An analyst wants to view the query profile for a specific query that was run recently. Where should they look in the Databricks SQL UI?

Trap 1: The Catalog Explorer

Catalog Explorer is designed for browsing tables, schemas, and volumes within Unity Catalog. It provides metadata information, lineage, and sample data but does not contain execution-level details, performance metrics, or query plans for historical SQL statements. It is not the appropriate interface for analyzing query performance or reviewing execution history.

Trap 2: The SQL Editor

The SQL Editor is where queries are written and executed. While it shows the current output and runtime for a session, it does not provide the detailed Query Profile visualization necessary to analyze bottlenecks. For full performance analysis, the dedicated Query History view must be accessed for the specific query.

Trap 3: The SQL Warehouse settings page

The SQL Warehouse settings page is used to configure warehouse-level parameters like scaling policies, auto-stop intervals, and instance types. It is an administrative page for managing infrastructure, not an analytical tool for inspecting individual query performance or viewing execution plans for specific SQL statements that have already been executed.

A
The Catalog Explorer
Why it fails: Catalog Explorer is designed for browsing tables, schemas, and volumes within Unity Catalog. It provides metadata information, lineage, and sample data but does not contain execution-level details, performance metrics, or query plans for historical SQL statements. It is not the appropriate interface for analyzing query performance or reviewing execution history.
B
The SQL Editor
Why it fails: The SQL Editor is where queries are written and executed. While it shows the current output and runtime for a session, it does not provide the detailed Query Profile visualization necessary to analyze bottlenecks. For full performance analysis, the dedicated Query History view must be accessed for the specific query.
C
The Query History tab
The Query History tab in the Databricks SQL UI logs all queries executed on the warehouse. By selecting a specific query ID, users can access the Query Profile, which displays the execution graph, metrics, and bottlenecks, allowing for granular analysis of how the engine processed the data during runtime.
D
The SQL Warehouse settings page
Why it fails: The SQL Warehouse settings page is used to configure warehouse-level parameters like scaling policies, auto-stop intervals, and instance types. It is an administrative page for managing infrastructure, not an analytical tool for inspecting individual query performance or viewing execution plans for specific SQL statements that have already been executed.

Question 8mediummultiple choice

A data analyst is troubleshooting a performance issue in a notebook. The query runs slowly when processing a large table. Which approach should the analyst take to improve performance?

Trap 1: Increase the number of users connected to the workspace.

Increasing the number of users adds load to the platform and does not improve individual query performance. In fact, it might lead to resource contention, slowing down all users. Performance tuning should focus on optimizing data storage and query logic, not increasing the number of active users.

Trap 2: Delete the table and re-create it without Delta Lake.

Removing Delta Lake would forfeit critical features like ACID compliance and time travel while potentially reducing query performance. Delta Lake is specifically designed to provide superior performance over standard Parquet. Reverting to basic Parquet files would make the data harder to manage and likely slower to query.

Trap 3: Force the notebook to run on a single-node cluster.

Single-node clusters are meant for small datasets and development. For large tables, they lack the distributed computing power required to process data in parallel. Forcing a large-scale query onto a single node will lead to memory errors and extremely slow processing speeds, defeating the purpose of using Databricks.

A
Increase the number of users connected to the workspace.
Why it fails: Increasing the number of users adds load to the platform and does not improve individual query performance. In fact, it might lead to resource contention, slowing down all users. Performance tuning should focus on optimizing data storage and query logic, not increasing the number of active users.
B
Run the 'OPTIMIZE' command on the Delta table.
The OPTIMIZE command compacts small files into larger, more efficient files, significantly improving read performance. It is a standard procedure for data analysts to maintain high performance in Delta Lake tables. This simple action can drastically reduce I/O overhead for analytical queries scanning large volumes of data.
C
Delete the table and re-create it without Delta Lake.
Why it fails: Removing Delta Lake would forfeit critical features like ACID compliance and time travel while potentially reducing query performance. Delta Lake is specifically designed to provide superior performance over standard Parquet. Reverting to basic Parquet files would make the data harder to manage and likely slower to query.
D
Force the notebook to run on a single-node cluster.
Why it fails: Single-node clusters are meant for small datasets and development. For large tables, they lack the distributed computing power required to process data in parallel. Forcing a large-scale query onto a single node will lead to memory errors and extremely slow processing speeds, defeating the purpose of using Databricks.

Question 9easymultiple choice

What is the primary function of the 'Databricks Assistant' within the notebook environment?

Trap 1: To automatically execute all notebooks in a production pipeline.

The Databricks Assistant is a developer productivity tool, not an automation or orchestration engine. Job scheduling and workflow orchestration are handled by the Jobs component. The Assistant provides coding help, not execution control for production-level pipelines that require rigorous monitoring, error handling, and dependency management for critical business data workflows.

Trap 2: To manage user permissions across the entire workspace.

User permissions are managed by administrators through the workspace settings or Unity Catalog interface, not through an AI coding assistant. The Assistant has no administrative capabilities regarding access control or identity management, as these are critical security functions that require explicit administrative oversight and auditing to ensure compliance.

Trap 3: To store and back up notebook versions automatically.

Version control is provided by the Git integration in Databricks Repos. The Assistant does not manage version history or backups of notebooks. Relying on an AI tool for version control is incorrect, as it lacks the necessary functionality to maintain a structured, auditable history of code changes over time.

A
To automatically execute all notebooks in a production pipeline.
Why it fails: The Databricks Assistant is a developer productivity tool, not an automation or orchestration engine. Job scheduling and workflow orchestration are handled by the Jobs component. The Assistant provides coding help, not execution control for production-level pipelines that require rigorous monitoring, error handling, and dependency management for critical business data workflows.
B
To assist in writing, debugging, and explaining notebook code.
The Databricks Assistant is an AI-integrated tool that provides suggestions, debugging help, and code explanations directly within the notebook. It enhances developer productivity by accelerating the coding process and providing immediate support for syntax-related issues, making it a powerful tool for analysts at all experience levels to write efficient code.
C
To manage user permissions across the entire workspace.
Why it fails: User permissions are managed by administrators through the workspace settings or Unity Catalog interface, not through an AI coding assistant. The Assistant has no administrative capabilities regarding access control or identity management, as these are critical security functions that require explicit administrative oversight and auditing to ensure compliance.
D
To store and back up notebook versions automatically.
Why it fails: Version control is provided by the Git integration in Databricks Repos. The Assistant does not manage version history or backups of notebooks. Relying on an AI tool for version control is incorrect, as it lacks the necessary functionality to maintain a structured, auditable history of code changes over time.