Databricks-DA-Assoc Analyzing Queries — 15 Questions

Question 1

An analyst notices that a specific query is slow because it performs a full table scan instead of using the intended index. Which command should the analyst check to ensure the optimizer has the necessary information to choose the correct plan?

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

Accepted Answer

ANALYZE TABLE.. In Databricks, the command to collect statistics for the cost-based optimizer is 'ANALYZE TABLE table_name COMPUTE STATISTICS'. However, the stem's premise regarding 'indexes' is misleading for Databricks, as it relies on Z-Ordering or Data Skipping rather than traditional indexes. Furthermore, the command 'ANALYZE TABLE' is often deprecated in favor of 'ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS'.

Answer

OPTIMIZE TABLE.

Answer

VACUUM TABLE.

Answer

REFRESH TABLE.

Question 2

A data analyst is investigating a slow Databricks SQL query that joins two large Delta tables. The query profile shows a SortMergeJoin with two Exchange nodes, and each Exchange shuffles over 1 TB of data. The analyst wants to reduce shuffle. Which approach is most likely to improve performance?

Accepted Answer

Use bucketing on the join key for both tables and ensure the number of buckets matches.. Bucketing both tables on the join key with the same number of buckets allows Spark to perform a sort-merge join without exchanging data across the network. This eliminates the two Exchange nodes and the associated 1 TB shuffles, significantly improving performance. Other options either do not eliminate shuffle or are not applicable when both tables are large.

Answer

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

Answer

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

Answer

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

Question 3

A data analyst uses a Databricks SQL dashboard that refreshes every 15 minutes. One query reads from a large Delta table, and the analyst notices in the Query Profile that the scan operator reads far more bytes than the rows the final result returns, even though the WHERE clause filters on a low-cardinality status column. The analyst wants to reduce bytes scanned without changing the result. Which action is most appropriate?

Accepted Answer

Use a materialized view that pre-aggregates or pre-filters the data the dashboard needs. Because the filter targets a low-cardinality column, file-level skipping and partitioning both leave large amounts of data to scan. A materialized view precomputes the filtered or aggregated result so the recurring dashboard query reads a compact table, preserving the result while sharply reducing bytes scanned on each refresh.

Answer

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

Answer

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

Answer

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

Question 4

A data analyst runs a query that includes a subquery in the WHERE clause: SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE region = 'North'). The query is slow. The analyst wants to rewrite the query to improve performance. Which approach is most likely to be more efficient?

Accepted Answer

Rewrite the query as an INNER JOIN between orders and customers on customer_id, with a filter on region.. Rewriting the IN subquery as an INNER JOIN with a filter on region makes the join explicit, enabling Spark to apply optimizations like broadcast join if the customers table is small. This often results in a more efficient execution plan than a subquery, which may be evaluated separately.

Answer

Use a LEFT SEMI JOIN instead of the IN subquery.

Answer

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

Answer

Replace the IN subquery with an EXISTS correlated subquery.

Question 5

A data analyst is examining the Databricks SQL Query Profile for a query that is slow due to a join. The analyst wants to identify whether the join is causing a shuffle and whether data skew is present. Which two metrics should the analyst examine? (Choose two.)

Accepted Answer

The number of rows and bytes shuffled per stage.. Shuffle metrics (rows and bytes) directly show whether data is being exchanged and how much, confirming a shuffle-based join. Peak memory usage per task helps identify skew: if some tasks use much more memory, they are likely processing more data due to skewed keys. Together, these metrics diagnose the two issues.

Answer

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

Answer

The query plan's node types and their order.

Answer

The number of partitions in the table.

Question 6

A data analyst runs a SELECT * query on a Delta table with 10,000 partitions, but only 5 partitions match the WHERE clause on a partition column. The analyst observes that the query still scans all partitions. Which action should the analyst take to ensure partition pruning occurs?

Accepted Answer

Use a filter on the partition column in the WHERE clause.. Partition pruning is a critical optimization in Databricks SQL that relies on the query filter referencing the partition column. Without such a filter, Spark must scan all partitions, leading to unnecessary I/O. The correct action is to ensure the WHERE clause includes a condition on the partition column, allowing the optimizer to skip irrelevant partitions.

Answer

Increase the number of shuffle partitions.

Answer

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

Answer

Run OPTIMIZE on the table to compact small files.

Question 7

A data analyst runs a Databricks SQL query against a Delta table and wants to inspect the detailed logical and physical execution plan, including statistics used by the cost-based optimizer, to understand why a filter was not pushed down. The analyst has access to the query's statement ID from the Query History. Which approach provides the most detailed execution plan information?

Accepted Answer

Prefix the query with EXPLAIN EXTENDED and review the output.. To understand why a filter was not pushed down, the analyst needs the optimized logical and physical plans along with statistics. EXPLAIN EXTENDED outputs these plans and exposes optimizer decisions such as predicate pushdown, making it the right tool. Runtime-only views like the Query Profile or Spark UI show execution behavior but not the plan transformations that determine pushdown.

Answer

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

Answer

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

Answer

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

Question 8

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?

Accepted Answer

Run an OPTIMIZE command with a ZORDER BY clause on the timestamp column to co-locate related data and improve data skipping.. Z-Ordering co-locates related data based on specified columns, significantly improving data skipping for queries with equality or range filters. When combined with correct partitioning, it minimizes the amount of data scanned from cloud storage, drastically reducing query latency and execution costs in Databricks environments.

Answer

Increase the cluster size to a driver instance with more memory to hold the entire uncompressed Delta table in cache.

Answer

Execute a VACUUM command with a retention threshold of zero hours to purge old data files immediately.

Answer

Convert the Delta table format to standard Parquet files to take advantage of native Apache Spark partitioning.

Question 9

A data analyst needs to inspect the logical and physical execution plans of a slow Spark SQL query to understand how filters and joins are being evaluated. Which command should the analyst execute in a Databricks notebook cell?

Accepted Answer

EXPLAIN EXTENDED SELECT * FROM table_name WHERE condition;. The EXPLAIN command in Spark SQL outputs the execution plans, including parsed logical plan, analyzed logical plan, optimized logical plan, and physical plan. Analyzing these plans helps data analysts identify inefficient join strategies, missing predicate pushdown, and unnecessary full table scans.

Answer

DESCRIBE EXTENDED table_name;

Answer

SHOW QUERY PLAN FOR SELECT * FROM table_name WHERE condition;

Answer

ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS;

Question 10

A data analyst notices that a query involving a large join between two tables is consistently slow. The analyst suspects that one of the tables is significantly skewed. Which tool in the Databricks SQL query profile is most effective for confirming this skew?

Accepted Answer

The SQL Query Profile 'Metrics' tab.. The Query Profile provides visual metrics to identify bottlenecks. By examining the 'Max vs Median' row count metrics per task, an analyst can see if specific executors are processing significantly more data than others, indicating skew. Understanding data distribution is critical in Databricks, as skew often leads to 'straggler' tasks that delay entire query execution, regardless of overall cluster size or compute power.

Answer

The Query History list view.

Answer

The Cluster Usage dashboard.

Answer

The Table details metadata panel.

Question 11

An analyst is optimizing a query that performs multiple aggregations on a Delta table. Which TWO actions can the analyst take to improve query performance via the SQL editor?

Accepted Answer

Use Z-Ordering on columns frequently used in WHERE filters.. Improving performance requires reducing the amount of data read. Using partition pruning allows the engine to skip irrelevant files, while Z-Ordering improves data skipping by clustering related information together. These techniques are fundamental for Databricks SQL performance, as they minimize I/O overhead. Mastering these adjustments allows analysts to write highly efficient code that scales effectively with growing datasets, reducing both latency and costs for the organization.

Answer

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

Answer

Force a Broadcast Join for all small tables.

Answer

Convert all tables to Parquet format.

Question 12

Which of the following is the primary purpose of examining the 'Query Profile' in Databricks SQL?

Accepted Answer

To visualize the execution steps and identify bottlenecks.. The Query Profile is the central diagnostic tool for Databricks SQL. It provides a visual representation of how a query was executed, including the time spent in each stage and where bottlenecks occur. Mastering this tool is vital for data analysts because it transforms abstract performance issues into actionable insights, enabling them to reduce query latency, optimize costs, and effectively communicate performance bottlenecks to data engineers for backend architecture improvements.

Answer

To change the permissions of the underlying table.

Answer

To automatically rewrite SQL for better performance.

Answer

To view the raw logs of the cluster driver.

Question 13

When analyzing query performance, what does a high 'Spill to Disk' metric indicate?

Accepted Answer

The cluster has insufficient memory for the operation.. Spilling to disk occurs when a query's operations, such as sorts or joins, exceed the available memory in the cluster's executors. This is a major performance bottleneck because disk I/O is exponentially slower than memory. Recognizing this indicator is crucial for analysts because it signals that the current cluster size or query logic needs adjustment to fit the workload comfortably within RAM, significantly improving query speed and stability.

Answer

The query is successfully using the cache.

Answer

The data is highly compressed.

Answer

The query is finished and writing results to the table.

Question 14

An analyst is reviewing a slow query and identifies that the 'FileScan' stage is taking most of the time. Which THREE factors could be causing this inefficiency?

Accepted Answer

The table contains a high number of small files.. Slow FileScans usually point to I/O-related issues. If too many small files are present, the metadata overhead becomes significant. Without proper pruning, the system scans unnecessary data. Z-Ordering or partitioning issues cause the engine to read more data than required. Addressing these factors is vital for analysts, as they directly impact the 'Data Skipping' efficiency of the Delta Lake engine, which is the cornerstone of high-performance analytics in Databricks.

Answer

The query uses a cross join.

Answer

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

Question 15

Why should an analyst use the 'EXPLAIN' command before running a complex SQL query on a very large dataset?

Accepted Answer

To verify the query plan and identify inefficiencies.. The EXPLAIN command provides the logical and physical execution plan without actually running the query. For analysts, this is a proactive safety and performance measure. It allows them to spot inefficient patterns, such as unexpected cross joins or massive data shuffles, before consuming expensive compute resources. This practice is essential for cost control and ensuring that queries are optimized for the scale of the underlying production data.

Answer

To bypass the authentication requirement for queries.

Answer

To view the projected cost in US Dollars.

Answer

To automatically apply Z-Ordering to the table.