Databricks-DA-Assoc Analyzing Queries Practice Test 3 — 15 Questions

Question 1

When analyzing the execution plan of a query in Databricks SQL, what does a 'BroadcastHashJoin' node typically indicate?

Accepted Answer

The optimizer successfully avoided a network shuffle by sending a small table to all executors.. A BroadcastHashJoin indicates that the query optimizer determined one table was small enough to be duplicated across all nodes. This avoids expensive shuffles by performing the join locally on each partition. Understanding execution plans allows analysts to verify if their queries are executing as intended and helps identify opportunities to apply hints or optimize join operations for better performance on large-scale distributed data processing tasks.

Answer

The query is performing a cross join between two large tables.

Answer

The query is failing because the join keys are not indexed.

Answer

The data is being read from multiple external storage locations simultaneously.

Question 2

An analyst is reviewing a slow-running query and wants to identify potential bottlenecks. Which TWO of the following metrics in the Databricks SQL query profile are most indicative of inefficient data distribution?

Accepted Answer

Shuffle Read/Write bytes are significantly higher than the input data size.. Data skew and excessive shuffling are primary causes of performance degradation. High 'Shuffle Read' and 'Shuffle Write' values indicate large amounts of data moving across the network, while skewed task durations signal that one executor is doing significantly more work than others. Identifying these metrics early allows analysts to apply techniques like salting keys or partitioning to balance the workload across the cluster.

Answer

The 'Scan' node shows high throughput and low execution time.

Answer

The query shows a high number of file metadata operations.

Answer

The query plan indicates a broadcast hash join is being used.

Question 3

Refer to the exhibit. The execution plan shows a SortMergeJoin with two Exchange nodes. What is the primary cause of the performance impact here?

Accepted Answer

The tables are being re-shuffled to align rows on the join key, causing network overhead.. The presence of two Exchange nodes indicates that the data is being repartitioned across the cluster to align join keys on the same worker nodes. This full shuffle is costly in terms of network I/O. Recognizing this pattern helps analysts understand why large joins can be slow and justifies investigating whether tables can be pre-partitioned to avoid these runtime exchanges.

Answer

The join keys are not sorted, forcing a full scan of both tables.

Answer

The optimizer is unable to choose a broadcast join because both tables are too large.

Answer

The cluster is running out of memory due to the high number of partitions.

Question 4

An analyst is optimizing a dashboard that queries a Delta table partitioned by 'event_date'. The query filters on 'event_date' and 'user_id', but is still slow. What is the most effective next step to improve query speed?

Accepted Answer

Enable Liquid Clustering on the 'user_id' column.. Partitioning by date is good for coarse pruning, but if 'user_id' is the primary filter, the engine still scans all files within the selected dates. Z-Ordering by 'user_id' creates data skipping metadata that allows the engine to skip files not containing the specific user, drastically reducing data read volume. This is a critical optimization for high-cardinality columns used in frequent point-lookups.

Answer

Change the table format to Parquet to improve scan performance.

Answer

Increase the cluster's driver node memory.

Answer

Rewrite the query to use a subquery instead of a JOIN.

Question 5

A data analyst runs a Databricks SQL query on a Delta table that has 500,000 small files. The query applies a filter on a high-cardinality column but still scans all files, resulting in slow performance. The analyst wants to reduce the number of files scanned without changing the query logic. Which action should the analyst take?

Accepted Answer

Run OPTIMIZE on the table to compact small files and improve data skipping.. Compacting small files with OPTIMIZE reduces the number of files that must be opened and enables Delta data skipping through collected statistics. This directly addresses the root cause: too many small files causing excessive I/O. Other options either disable useful optimizations, misuse syntax, or only mask the problem without reducing file count.

Answer

Set spark.sql.adaptive.enabled to false to force a static execution plan.

Answer

Increase the driver node size to handle the large number of files.

Answer

Add a Z-ORDER BY clause to the query to sort results by the filtered column.

Question 6

A data analyst runs a Databricks SQL query that joins a 40 GB Delta table to a 12 GB Delta table and the Query Profile shows two Exchange nodes surrounding a SortMergeJoin. The analyst wants to confirm whether the shuffle is the dominant cost before rewriting the query. Which Query Profile metric should the analyst inspect first to quantify the shuffle's impact?

Accepted Answer

Shuffle write bytes and shuffle read bytes on the Exchange nodes. The Exchange operators are the shuffle stages that redistribute rows by join key before the SortMergeJoin, so their shuffle write and read byte counts are the direct measurement of redistribution cost. Inspecting those byte totals tells the analyst whether the shuffle dominates runtime and justifies alternatives such as broadcast join or pre-partitioning.

Answer

Peak memory usage of the driver process

Answer

Rows read from the smaller table's scan operator

Answer

Number of files scanned in the Delta table directory

Question 7

A data analyst runs a Databricks SQL query that joins a 50 GB fact_sales Delta table with a 200 MB dim_product Delta table. The query takes 15 minutes. The analyst runs EXPLAIN FORMATTED and sees a SortMergeJoin instead of a BroadcastHashJoin. The analyst has already confirmed that the small table is not being broadcast. Which action should the analyst take to improve performance?

Accepted Answer

Increase spark.sql.autoBroadcastJoinThreshold to a value larger than 200 MB, such as 250 MB, so the small table can be broadcast.. Increasing the auto broadcast join threshold above the size of the small table enables Spark to broadcast it, converting the SortMergeJoin to a BroadcastHashJoin. This avoids shuffling the large fact table, which is the main cost. The default threshold is 10 MB, so a 200 MB table is not broadcast unless the threshold is raised.

Answer

Add a broadcast hint to the large fact table so it is replicated to all executors.

Answer

Set spark.sql.autoBroadcastJoinThreshold to -1 to disable broadcasting and force a shuffle hash join.

Answer

Repartition both tables on the join key before the join to ensure co-located partitions.

Question 8

A data analyst runs a query on a Delta table with a WHERE clause on a timestamp column, but the query scans the entire table. The analyst checks the table's metadata and sees that the column is not a partition column but is frequently used in filters. The analyst wants to enable data skipping to avoid full scans. Which action should the analyst take?

Accepted Answer

Run OPTIMIZE with ZORDER on the timestamp column to co-locate related data and improve data skipping.. Z-ordering on a frequently filtered column clusters data so that min/max statistics can be used to skip files. This is ideal for high-cardinality columns like timestamps where partitioning would create too many small files. OPTIMIZE with ZORDER rearranges data without changing the table structure.

Answer

Set spark.sql.parquet.filterPushdown to true to enable filter pushdown on the timestamp column.

Answer

Convert the table to a partitioned table using the timestamp column as the partition key.

Answer

Enable Delta Lake change data feed on the table to track changes to the timestamp column.

Question 9

A data analyst runs a query in Databricks SQL that returns the total sales per region. The query is slow, and the analyst notices that the execution plan shows a full table scan on a large Delta table. The analyst wants to reduce the amount of data read. Which action is most likely to improve performance?

Accepted Answer

Add a WHERE clause on a partitioned column to enable partition pruning.. Partition pruning is the most direct way to reduce data read. By filtering on a partitioned column, Delta Lake can skip partitions that do not match the filter, turning a full table scan into a targeted read. Other options do not reduce the volume of data read or are inapplicable to an aggregation query.

Answer

Use a broadcast hint to force a broadcast join.

Answer

Cache the table using CACHE TABLE before running the query.

Answer

Increase the cluster size to add more worker nodes.

Question 10

A data analyst is reviewing a Databricks SQL query that runs slowly and opens the Query Profile. The analyst wants to identify whether an individual task is disproportionately slow compared with its peers, indicating skew. Which TWO areas of the Query Profile should the analyst examine? (Choose two.)

Accepted Answer

The number of rows and bytes written by each shuffle partition. Skew manifests as an individual task running far longer than its peers because one partition holds disproportionate data. Task duration distribution within a stage exposes the straggler, while per-partition shuffle write rows and bytes show the imbalanced partition that causes it. Together these two views confirm skew rather than a general resource shortage.

Answer

The version of the Databricks Runtime used by the warehouse

Answer

The SQL warehouse cluster size configured for the query

Answer

The total number of stages in the physical plan

Question 11

A data analyst writes a query in the Databricks SQL editor that returns an error stating the column cannot be resolved, even though the analyst is certain the column exists in the Delta table. The analyst wants to quickly confirm the actual column names and data types of the table before editing the query. Which approach is most efficient?

Accepted Answer

Run DESCRIBE TABLE on the table in the SQL editor. DESCRIBE TABLE reads table metadata from the metastore and returns column names, data types, and comments without scanning data. That makes it the fastest, cheapest way to confirm exact spelling and casing so the analyst can fix the unresolved column reference in the query.

Answer

Open the table's Delta transaction log JSON files and read the schema

Answer

Query the table with SELECT * and inspect the result grid columns

Answer

Recreate the table with a CREATE OR REPLACE statement matching the intended schema

Question 12

A data analyst is analyzing a slow-running Databricks SQL query. The analyst suspects that the query is suffering from excessive shuffling. Which two actions should the analyst take to reduce shuffle and improve performance? (Choose two.)

Accepted Answer

Enable Adaptive Query Execution (AQE) to dynamically coalesce shuffle partitions.. Enabling AQE allows dynamic coalescing of shuffle partitions and skew handling, reducing shuffle overhead. Using broadcast joins for small tables avoids shuffling the large table entirely. Both are effective strategies to minimize shuffle and improve query performance.

Answer

Repartition the large table on a high-cardinality column before the join.

Answer

Increase the number of shuffle partitions by setting spark.sql.shuffle.partitions to a very high value.

Answer

Cache the large table in memory to avoid shuffling it in subsequent operations.

Question 13

A data analyst runs a query that joins a fact table to a dimension table and groups by a dimension attribute. The Query Profile shows a BroadcastHashJoin, yet the query still takes much longer than expected and one stage shows a single task consuming most of its runtime. The dimension table is small, so the analyst suspects the bottleneck is elsewhere. Which explanation best fits the evidence?

Accepted Answer

The grouping key is skewed, so one task in the aggregation stage processes far more rows than the others. Because the broadcast join removes shuffle from the join itself, the remaining slow stage with one dominant task is the aggregation. An unevenly distributed grouping key concentrates most rows into one partition, so that task runs far longer than its peers. Addressing the skew in the group-by, not the join, is what improves runtime.

Answer

The fact table lacks statistics, causing Spark to choose a nested loop join

Answer

The broadcast of the small table is failing and silently falling back to a sort-merge join

Answer

The dimension table is too large to broadcast and must be repartitioned before joining

Question 14

A data analyst is reviewing the Query Profile in Databricks SQL for a query that spills data to disk during a sort operation. Which two metrics should the analyst examine to confirm and diagnose the spill? (Choose two.)

Accepted Answer

Spill (Disk) Size. Spill (Disk) Size directly quantifies data written to disk due to memory overflow, confirming spill. Peak Execution Memory indicates memory pressure that causes spill. Together, they diagnose spill during sort. Other metrics like shuffle read size or output rows do not directly measure spill and are less relevant for this specific issue.

Answer

Number of Output Rows

Answer

Total Time Spent in Shuffle

Answer

Shuffle Read Size

Question 15

A data analyst notices that a query performing a join between a large fact table and a small dimension table is slow. The analyst wants to ensure that the small table is broadcasted to all executors to avoid a shuffle. Which configuration should the analyst adjust to increase the likelihood of a broadcast join?

Accepted Answer

spark.sql.autoBroadcastJoinThreshold. The key to encouraging a broadcast join is to ensure the small table's size is below the autoBroadcastJoinThreshold. By increasing this threshold, the analyst allows Spark to consider the small table for broadcast, eliminating the shuffle of the large table and significantly speeding up the join.

Answer

spark.sql.adaptive.enabled

Answer

spark.sql.shuffle.partitions

Answer

spark.sql.broadcastTimeout