Analyzing Queries

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?

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?

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?

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?

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

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

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?

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

When analyzing query execution metrics in Databricks, what does 'Task Duration' represent?

Which feature in Databricks SQL allows an analyst to view the history and performance metrics of previously executed queries?

What is the primary benefit of using a 'Materialized View' in Databricks SQL for frequent, expensive queries?

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?

When reviewing a query profile, an analyst sees a 'Broadcast Hash Join'. What does this tell the analyst about the data being joined?

A data analyst runs a query against a large Delta table partitioned by date. The query filters on a timestamp column rather than the date partition column. How does Databricks handle this query execution?

A data analyst runs an Apache Spark SQL query against a Delta Lake table and notices that partition pruning is not occurring despite filtering on the partitioned column 'region'. The table definition shows 'region' is stored as a string, but the query passes an integer value. How does this type mismatch impact query execution?

An analyst executes a query that joins a large fact table with a small dimension table in Databricks. The analyst wants to force the Catalyst optimizer to broadcast the small dimension table to avoid an expensive shuffle join. Which standard Spark SQL hint should be included in the query text?

A data analyst is investigating why a specific Spark SQL query takes an exceptionally long time to complete and exhibits signs of severe data skew. Which TWO metrics or behaviors in the Databricks Spark UI typically indicate that data skew is impacting the query? (Choose two)

An analyst is writing a query in Databricks and needs to reference a temporary view that is scoped only to their current interactive notebook session. Which command or syntax ensures the view is automatically dropped when the session ends?

A data analyst is querying a large Delta table containing billions of rows of clickstream data. The table is frequently queried using a timestamp column. To optimize range queries on this timestamp, which Delta Lake feature should the table builder implement?

Refer to the exhibit. An analyst examines the physical execution plan of a Spark SQL join query. Based on the provided plan output, which optimization strategy was automatically applied by Adaptive Query Execution (AQE)?

A data analyst writes a complex Spark SQL query involving multiple window functions, CTEs, and aggregations. To debug the query performance, the analyst wants to inspect the logical optimization phases applied by the Catalyst optimizer. Which SQL command can the analyst use to view the unoptimized logical plan, optimized logical plan, and physical execution plan?

Refer to the exhibit. A data analyst reviews a physical execution plan where a table is scanned and shuffled multiple times in succession during a self-join operation. What underlying anti-pattern in the query construction likely caused this redundant exchange?

A data analyst notices that a query involving a join between a large table and a small lookup table is performing poorly. The analyst wants to optimize the join performance without changing the underlying data. Which technique should they apply?

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

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?

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

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?

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?

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?

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?

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?

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?

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.)

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?

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.)

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?

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.)

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?

A data analyst is investigating a query that uses a window function with PARTITION BY and ORDER BY, and the query is slow. The analyst suspects that data skew is causing some partitions to be much larger than others. Which approach should the analyst take to diagnose the skew in the Query Profile?