Analyzing Queries practice questions

OPTIMIZE TABLE.

Why it fails: OPTIMIZE is used to compact small files and improve data layout, which helps with I/O efficiency. While it is important, it does not directly update the statistics that the Cost-Based Optimizer (CBO) needs to determine the best join strategy or access method for a specific query.

VACUUM TABLE.

Why it fails: VACUUM is a maintenance command used to remove old, unreferenced data files that are beyond the retention period. It is essential for storage management and cost control, but it does not influence query planning, optimizer behavior, or the statistics used to determine the most efficient execution path.

REFRESH TABLE.

Why it fails: REFRESH TABLE invalidates the metadata cache and forces the system to reload the table's file list from the underlying storage. It is used when manual changes are made to the storage layer, but it does not generate or update the statistical metadata required by the CBO for intelligent query planning.

Pre-partition both tables on the join key using Delta Lake partitioning.

Why it fails: Partitioning tables on the join key can help with partition pruning for filters, but it does not avoid shuffling during a join unless the tables are already bucketed or co-located. Delta Lake partitioning alone does not guarantee that data is distributed by join key in a way that avoids shuffle; it may even create too many partitions.

Rewrite the query to use a broadcast join hint for the smaller table.

Why it fails: A broadcast join is effective only when one side is small enough to fit in memory. Here, both tables are large, so broadcasting would likely cause out-of-memory errors or excessive network transfer. The shuffle is due to the size of both inputs, so broadcasting the smaller table may not eliminate the shuffle if it is still too large.

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

Why it fails: Adaptive query execution can coalesce shuffle partitions and optimize join strategies at runtime, but it does not eliminate the need to shuffle data for a sort-merge join between two large tables. Coalescing partitions reduces the number of partitions, not the total shuffle volume, so the 1 TB shuffle per side remains.

Partition the Delta table by the status column and rely on partition pruning

Why it fails: Partitioning by a low-cardinality column creates a small number of very large partitions, so each query still scans a huge partition and pruning saves little. Over-partitioning on low-cardinality keys also harms file management, and the scenario explicitly wants reduced bytes scanned while preserving results, which this approach does not reliably achieve.

Add a ZORDER BY clause on the status column when optimizing the Delta table

Why it fails: Z-ordering clusters data by a column and can help skip files for high-cardinality filters, but a low-cardinality status column produces large, coarse ranges, so most files still contain matching values and little data is skipped. The overhead of rewriting the table rarely pays off for a column with only a handful of distinct values, and it does not address the fundamental scan volume.

Enable Delta Lake data skipping statistics and filter on a high-selectivity column instead

Why it fails: Data skipping helps when predicates map to narrow min/max ranges in file statistics, but changing the business filter to a different column alters the query's meaning and may not be possible for the dashboard. The scenario requires the same result, so replacing the low-cardinality predicate with another column is not a valid optimization and may return different rows.

Use a LEFT SEMI JOIN instead of the IN subquery.

Why it fails: A LEFT SEMI JOIN is semantically equivalent to an IN subquery and can be efficient, but it is not necessarily more efficient than an INNER JOIN. The INNER JOIN with a filter on region is more explicit and allows the optimizer to push down the filter and choose a broadcast join. LEFT SEMI JOIN might still require a shuffle.

Add a broadcast hint to the orders table to force a broadcast join.

Why it fails: Broadcasting the large orders table would be inefficient and could cause memory issues. The customers table is likely the smaller one and should be broadcast if beneficial. Hinting the orders table would not help and could degrade performance.

Replace the IN subquery with an EXISTS correlated subquery.

Why it fails: EXISTS can sometimes be more efficient than IN for correlated subqueries, but it depends on the optimizer. In many cases, Spark optimizes IN subqueries into joins. Simply switching to EXISTS without understanding the plan may not improve performance and could even be slower if the subquery is not correlated.

The duration of each task and the distribution of task durations.

Why it fails: Task duration distribution can indicate skew, but it is not a direct metric of shuffle. While useful for diagnosing skew, it does not specifically show shuffle volume. The question asks for metrics to identify shuffle and skew; shuffle bytes and peak memory are more direct for skew detection.

The query plan's node types and their order.

Why it fails: The query plan shows the logical and physical operators, such as SortMergeJoin or BroadcastHashJoin, but it does not provide runtime metrics like shuffle bytes or memory usage. While useful for understanding the join strategy, it does not directly show skew or shuffle volume.

The number of partitions in the table.

Why it fails: The number of partitions in the table is a table property, not a runtime metric in the Query Profile. It does not indicate whether a shuffle occurred or if skew is present during the query execution. It is not relevant to diagnosing join performance in the Query Profile.

Increase the number of shuffle partitions.

Why it fails: Shuffle partitions control the parallelism of shuffles during operations like joins or aggregations. They have no impact on partition pruning during scans. Adjusting this setting might affect performance of later stages but will not reduce the number of partitions read from the table, so it does not solve the problem.

Add a Z-ORDER BY clause on the partition column.

Why it fails: Z-ORDER BY is used for multi-dimensional clustering to improve data skipping on non-partition columns, not for partition pruning. It reorganizes data within partitions but does not affect which partitions are scanned. Using Z-ORDER on a partition column is redundant and does not address the root cause of scanning all partitions.

Run OPTIMIZE on the table to compact small files.

Why it fails: OPTIMIZE compacts small files into larger ones, improving scan efficiency, but it does not enable partition pruning. Pruning depends on the query filter being on a partition column and the table being partitioned. If the filter is correct, pruning should already happen; OPTIMIZE alone will not fix a pruning issue caused by a non-partition filter or improper table setup.

Enable the Spark UI and inspect the SQL tab's DAG visualization for the query.

Why it fails: The Spark UI SQL tab shows stages, tasks, and a DAG of the physical execution, which is helpful for diagnosing skew and shuffle behavior. However, it does not present the optimized logical plan or the statistics-based reasoning behind optimizer choices like filter pushdown. For detailed plan-level insight, EXPLAIN EXTENDED is more appropriate than the DAG visualization.

Open the Query History, select the query, and view the Query Profile's operator-level metrics.

Why it fails: The Query Profile shows runtime operator metrics such as rows processed and time spent, which helps identify bottlenecks, but it does not expose the logical plan transformations or the statistics used by the cost-based optimizer. It cannot directly show why a filter was not pushed down, because pushdown decisions are made during optimization and reflected in the logical plan, not as separate profile operators.

Run DESCRIBE DETAIL on the Delta table referenced in the query.

Why it fails: DESCRIBE DETAIL returns table-level metadata such as format, location, number of files, and partition columns. It is useful for understanding table layout and file counts, but it provides no information about the query's execution plan or optimizer decisions. It cannot explain filter pushdown for a specific query, because it does not analyze the query itself.

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.

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.

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.

DESCRIBE EXTENDED table_name;

Why it fails: DESCRIBE EXTENDED provides detailed metadata about a specific table, such as its column names, data types, partitioning structure, and storage location, but it does not reveal the query execution plan or how specific SQL statements are evaluated.

SHOW QUERY PLAN FOR SELECT * FROM table_name WHERE condition;

Why it fails: SHOW QUERY PLAN is not a valid SQL syntax command in Databricks or Apache Spark SQL. The correct keyword for viewing execution plans is EXPLAIN, followed by the target query statement.

ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS;

Why it fails: ANALYZE TABLE computes and updates table and column statistics in the metastore. While this helps the Catalyst optimizer make better cost-based decisions, it does not display the execution plan of a query.

The Query History list view.

Why it fails: The Query History list view provides high-level metadata such as duration, user, and status for historical queries. It does not contain granular task-level metrics or data distribution statistics necessary to diagnose skew issues within specific join operations or individual stages of the query execution plan.

The Cluster Usage dashboard.

Why it fails: The Cluster Usage dashboard displays aggregate resource consumption metrics like DBU usage, CPU load, and memory utilization across a cluster. While useful for cost management and general capacity planning, it lacks the specific task-level row count details required to isolate data skew occurring within an individual query.

The Table details metadata panel.

Why it fails: The Table details panel provides information about table schema, file counts, and general statistics like table size. While it shows total row counts, it does not provide insight into how that data is distributed across partitions or how it is processed during complex join operations in real-time.

Increase the cluster size to 'Large' for every query execution.

Why it fails: Scaling up to a larger cluster is not always efficient, as it incurs higher costs and may not resolve I/O-bound queries. Proper performance tuning focuses on data layout and query logic first, as increasing compute power without addressing underlying data skipping issues yields diminishing returns and higher expenses.

Force a Broadcast Join for all small tables.

Why it fails: Forcing a join strategy can lead to 'Out of Memory' errors if the table size exceeds available executor memory. The query optimizer automatically chooses the most efficient join method based on table statistics. Manually overriding this without deep analysis often results in unstable query performance and failed executions.

Convert all tables to Parquet format.

Why it fails: Delta Lake, which is built on Parquet, provides critical features like ACID transactions and time travel that standard Parquet lacks. Converting back to raw Parquet loses these optimizations and metadata handling, which actually degrades performance and reliability in the Databricks ecosystem compared to using native Delta tables.

To change the permissions of the underlying table.

Why it fails: Permissions and access control are managed through the Data Explorer or SQL commands like GRANT and REVOKE. The Query Profile is a read-only diagnostic tool designed for performance analysis and does not provide administrative controls to modify security settings or object-level access rights within the Databricks workspace.

To automatically rewrite SQL for better performance.

Why it fails: While Databricks utilizes an intelligent Cost-Based Optimizer (CBO) to rewrite queries internally, the Query Profile itself does not perform query refactoring. It provides the diagnostic data needed for the human analyst to manually rewrite queries or optimize table structures for better future performance based on observed execution patterns.

To view the raw logs of the cluster driver.

Why it fails: Raw driver logs are typically accessed through the cluster 'Logs' tab in the compute UI. While the Query Profile might show some errors, it is designed for SQL-level performance visualization rather than low-level infrastructure logging, which is required for debugging cluster-wide issues like hardware failure or library conflicts.

The query is successfully using the cache.

Why it fails: Caching involves storing data in memory to avoid redundant reads. Spilling to disk is the opposite, indicating that there is insufficient memory. It is a sign of performance degradation, not an optimization, as the system is forced to swap data from memory to disk to complete the execution process.

The data is highly compressed.

Why it fails: Compression affects storage size, but spilling is a runtime memory-management issue. High compression can actually reduce the likelihood of spilling by fitting more data into memory. Therefore, spilling is independent of compression levels and is specifically related to the relationship between the active working set and allocated RAM.

The query is finished and writing results to the table.

Why it fails: Writing results to a table is an 'Output' operation. Spilling is a background mechanism that happens during intermediate execution stages like joins or sorts. It is a symptom of memory pressure during calculation, not a part of the standard final write process to the target table or materialized view.

The query uses a cross join.

Why it fails: While a cross join is extremely inefficient, it affects the 'Join' stage of the physical plan, not the 'FileScan' stage. The FileScan stage is strictly related to data retrieval from storage, whereas a cross join is a compute-heavy operation that occurs after the data has been read into memory.

The query is using a User-Defined Function (UDF).

Why it fails: UDFs are executed during the row-processing stages of the query. They do not influence the efficiency of reading data from the underlying Parquet/Delta files. While UDFs can be slow, they would manifest as bottlenecks in the evaluation stages, not within the initial FileScan operation of the execution plan.

To bypass the authentication requirement for queries.

Why it fails: The EXPLAIN command does not bypass security or authentication controls. All queries, including those analyzed with EXPLAIN, are subject to the same identity and access management policies. Security is enforced globally at the workspace and Unity Catalog level, and execution plans cannot be used to circumvent those protections.

To view the projected cost in US Dollars.

Why it fails: EXPLAIN provides information about the execution plan (operators, joins, scans), but it does not provide cost estimations in currency. While Databricks provides cost monitoring through the SQL warehouse usage logs, EXPLAIN is strictly an engine-level diagnostic tool meant for technical query optimization, not for financial budgeting or forecasting.

To automatically apply Z-Ordering to the table.

Why it fails: Z-Ordering is an administrative task performed by the OPTIMIZE command, which requires full write access to the table and triggers a data layout rewrite. EXPLAIN is a read-only command meant for query analysis. It has no capabilities to modify table storage, metadata, or physical data layout in any way.

The time taken to compile the SQL query.

Why it fails: Query compilation is the 'Analysis' and 'Planning' phase that occurs before execution. Task Duration, conversely, is a metric gathered during the actual execution phase, once tasks have been distributed to worker nodes. Compilation time is a separate overhead that is not reflected in individual executor task metrics.

The total time the cluster was running.

Why it fails: Cluster uptime is a measure of compute availability, not execution efficiency. Task duration specifically relates to the workload of a single unit of execution. Cluster uptime includes idle time, query processing, and system maintenance, whereas task duration is granular to the specific query's compute work within the engine.

The latency added by network overhead only.

Why it fails: Task Duration includes the time taken for all activities, including data reading, shuffling, sorting, and aggregation. While network overhead is a component of this time, it is not the sole definition. Attributing the duration purely to network overhead ignores the significant compute-intensive work happening locally on each worker node.

The Data Explorer.

Why it fails: The Data Explorer is used for managing databases, tables, schemas, and columns. It provides metadata, schema definitions, and sample data views but does not track the history of SQL query executions or their performance metrics, making it the wrong tool for auditing or debugging historical query performance.

The Cluster Metrics tab.

Why it fails: The Cluster Metrics tab displays hardware-level resource utilization such as CPU, memory, and network throughput for a specific compute resource. While helpful for infrastructure health, it does not provide query-level details or history, making it impossible to map specific SQL statements to the resource consumption observed in these charts.

The Workspace Audit Logs.

Why it fails: Audit Logs are designed for administrative tracking of user actions, such as login events and access control changes, for compliance purposes. They are not intended for performance analysis or query-level debugging, as they contain high-level event data rather than the detailed execution plans and metrics needed by data analysts.

It bypasses the need for Unity Catalog permissions.

Why it fails: Materialized views are first-class objects within the Unity Catalog and fully respect access control policies. Using them does not bypass security; all users must still have the appropriate permissions to read the underlying data and the materialized view itself, ensuring that data security remains strictly enforced in the workspace.

It automatically scales the cluster to infinite size.

Why it fails: Materialized views are a storage and precomputation optimization, not a cluster scaling strategy. While they reduce compute needs by avoiding redundant calculations, they do not change the cluster's underlying capacity or ability to scale. Scaling is handled by the SQL warehouse settings, which are independent of the view definition.

It eliminates the need for any data partitioning.

Why it fails: Materialized views are still backed by Delta tables and can benefit from partitioning and Z-Ordering. Eliminating partitioning would actually hurt performance for queries that need to filter specific data slices. Proper indexing and partitioning are still required to keep the refresh and query performance of the view optimal.

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.

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.

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.

The join is extremely memory-intensive.

Why it fails: Broadcast joins are actually memory-efficient for the large table, as they avoid moving it. The small table is loaded into the memory of every executor node. While this requires the small table to fit in memory, it is generally much faster than a standard shuffle join for most scenarios.

The query is performing a cross join.

Why it fails: A cross join (Cartesian product) would be identified by a 'CartesianProduct' operator in the plan, which is usually a massive performance issue. A Broadcast Hash Join is a specific join strategy and does not inherently imply that the query logic is a cross join without an ON clause.

The data is skewed and needs re-partitioning.

Why it fails: A broadcast join is often a sign of good optimization. If data were highly skewed, the optimizer might struggle to choose a broadcast join if the skewed table is large. Seeing this in the plan generally suggests that the data distribution is balanced enough for the optimizer to proceed.