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

Question 1

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

Accepted Answer

The time elapsed for a single core to process a task.. Task Duration measures the time taken by individual executor nodes to perform their assigned portion of the work. It is distinct from total query time because it highlights parallel execution efficiency. Understanding this is vital because if one task is much longer than others, it indicates data skew or uneven distribution, helping analysts identify the root cause of performance bottlenecks that are hidden within the overall query execution time.

Answer

The time taken to compile the SQL query.

Answer

The total time the cluster was running.

Answer

The latency added by network overhead only.

Question 2

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

Accepted Answer

The Query History.. The Query History interface is the primary tool for reviewing past executions. It allows analysts to search, filter, and inspect the performance of queries run across the workspace. This is important for identifying long-running queries, diagnosing failures, and comparing performance over time, which supports the iterative process of optimizing data models and SQL code to ensure consistent, reliable, and performant data delivery for the organization.

Answer

The Data Explorer.

Answer

The Cluster Metrics tab.

Answer

The Workspace Audit Logs.

Question 3

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

Accepted Answer

It stores precomputed results to improve read performance.. Materialized views precompute query results, allowing for faster read performance by storing the output rather than recalculating it on every request. This is critical for data analysts working with large, complex datasets, as it significantly reduces latency and compute costs for repetitive dashboards or reports. By shifting the workload from query time to refresh time, analysts can provide consistent and fast performance for their business stakeholders.

Answer

It bypasses the need for Unity Catalog permissions.

Answer

It automatically scales the cluster to infinite size.

Answer

It eliminates the need for any data partitioning.

Question 4

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?

Accepted Answer

Shuffle Read/Write bytes.. When a join hangs, it is often due to massive data movement (shuffle) or extreme data skew. Monitoring the shuffle size helps determine if the network or I/O is saturated. Checking the 'Max vs Median' row count helps identify skew. These metrics are the most reliable indicators of join failure, enabling analysts to decide whether to adjust join keys, use broadcast hints, or address underlying data distribution issues.

Answer

The number of users logged into the workspace.

Answer

The total number of tables in the schema.

Answer

The total size of the database logs.

Question 5

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

Accepted Answer

One of the tables is small enough to fit in memory.. A Broadcast Hash Join indicates that one side of the join is small enough to be sent to all executor nodes. This is an extremely efficient join type as it eliminates shuffling. For analysts, recognizing this confirms that the optimizer has successfully identified a small table, which is a positive performance indicator. If a join is expected to be small but isn't broadcast, the analyst may need to check table statistics.

Answer

The join is extremely memory-intensive.

Answer

The query is performing a cross join.

Answer

The data is skewed and needs re-partitioning.

Question 6

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?

Accepted Answer

The query optimizer performs a full scan of all partitions because partition pruning requires filters on the exact partition columns.. Databricks performs partition pruning based on the partition columns defined in the table metadata. When filtering on a timestamp column that differs from the partition column, the query engine cannot prune partitions effectively, resulting in a full table scan of all files unless liquid clustering or secondary indexing is applied. This optimization is critical for maintaining high query performance on large datasets.

Answer

The query automatically re-partitions the Delta table on the fly to match the timestamp filter.

Answer

The engine evaluates the timestamp column to prune partitions directly mapped to timestamps.

Answer

Databricks converts the timestamp into the partition column format and successfully prunes partitions.

Question 7

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?

Accepted Answer

Catalyst fails to match directory paths to filter values, forcing a full table scan across all partitions.. Type mismatches between predicate filters and partition columns prevent catalyst optimization from pushing down partition filters. Spark attempts implicit casting, which often invalidates static partition pruning mechanisms, forcing the engine to scan every single data file across all partitions, drastically increasing input data volume and slowing down query execution performance.

Answer

Spark automatically casts the column to match the filter type without impacting performance.

Answer

The query fails immediately with a TypeMismatch analysis exception before any jobs are launched.

Answer

Delta Lake automatically rewrites the underlying table schema to match the incoming query filter type.

Question 8

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?

Accepted Answer

/*+ BROADCAST(dimension_table) */. Query hints allow analysts to provide explicit instructions to the Catalyst optimizer regarding physical execution plans. The MAPJOIN or BROADCAST hint directs the engine to broadcast the smaller table to all worker nodes, eliminating the need for a costly shuffle exchange across the network and greatly accelerating join execution performance.

Answer

/*+ MERGE(dimension_table) */

Answer

/*+ SHUFFLE_HASH(dimension_table) */

Answer

/*+ CACHE(dimension_table) */

Question 9

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)

Accepted Answer

The maximum task duration is drastically higher than the 75th and median task durations for a given stage.. Data skew occurs when records are unevenly distributed across partitions, causing a few tasks to process the vast majority of data while others finish quickly. In the Spark UI, this manifests as a massive disparity between task duration percentiles and extremely high memory consumption or spill metrics on the tasks handling the skewed keys.

Answer

The total number of input files scanned is zero because all data is successfully pruned by partition filters.

Answer

The driver node runs out of memory during the initial parsing phase before any execution stages are generated.

Answer

All tasks across all worker nodes complete in nearly identical timeframes with minimal variance.

Question 10

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?

Accepted Answer

CREATE TEMP VIEW view_name AS SELECT .... Spark SQL supports various view scopes for different collaboration needs. A standard temporary view is tied to the lifecycle of the active SparkSession. Once the notebook session terminates, the temporary view is automatically cleaned up, preventing namespace pollution in shared database catalogs.

Answer

CREATE GLOBAL TEMP VIEW view_name AS SELECT ...

Answer

CREATE TABLE view_name AS SELECT ...

Answer

CREATE MANAGED VIEW view_name AS SELECT ...

Question 11

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?

Accepted Answer

Applying Z-Order clustering on the timestamp column to improve multi-dimensional data skipping.. Delta Lake features like Z-Ordering colocate related information in the same set of files based on specified columns. However, for efficient range filtering on high-cardinality timestamps, Liquid Clustering or traditional Z-Ordering combined with proper partitioning or data skipping statistics ensures the engine reads minimal files.

Answer

Enabling column mapping to allow renaming columns without rewriting underlying parquet files.

Answer

Setting the file size compaction threshold to the maximum allowable limit of 1 gigabyte.

Answer

Adding an explicit check constraint on the timestamp column to reject future dates.

Question 12

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

Accepted Answer

AQE dynamically converted a sort-merge join into a broadcast hash join based on runtime size statistics.. Adaptive Query Execution in Databricks dynamically optimizes query plans at runtime based on precise statistics gathered from completed shuffle stages. The exhibit clearly displays a BroadcastHashJoin where the right side of the join was converted from a sort-merge join into a broadcast join using a broadcast exchange stage.

Answer

Dynamic partition pruning was used to eliminate scanning partitions on the orders table.

Answer

Skew join optimization was triggered to split the skewed partition keys into multiple sub-tasks.

Answer

The Catalyst optimizer pushed down aggregations past the join boundary to reduce intermediate data size.

Question 13

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?

Accepted Answer

EXPLAIN EXTENDED SELECT ...;. Spark SQL provides the EXPLAIN command to inspect the underlying plans generated by the Catalyst optimizer. By specifying the EXTENDED or FORMATTED modifier, analysts can review parsed logical plans, analyzed logical plans, optimized logical plans, and final physical execution plans.

Answer

DESCRIBE EXTENDED table_name;

Answer

SHOW QUERY PLAN SELECT ...;

Answer

ANALYZE TABLE table_name COMPUTE STATISTICS;

Question 14

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?

Accepted Answer

The table was referenced multiple times in complex join logic without caching, causing Spark to recompute its lineage.. Redundant exchanges and scans in an execution plan often occur when a DataFrame or table is referenced multiple times in complex joins or transformations without being cached or persisted. Spark recomputes the lineage from storage for each reference, leading to duplicate scans and shuffles.

Answer

The analyst applied a broadcast hint to a table that exceeded the broadcast size configuration threshold.

Answer

The partition column data type mismatch forced Catalyst to duplicate the partition directories.

Answer

Automatic table compaction via OPTIMIZE was running concurrently during the execution phase.

Question 15

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?

Accepted Answer

Add an explicit BROADCAST hint to the small table in the JOIN clause.. Broadcasting the small table forces the cluster to send the entire lookup table to every node in the cluster, avoiding a full shuffle of the large table. This technique significantly reduces network overhead during joins when one side fits in memory. Mastering join strategies is essential for analysts to write efficient SQL that minimizes cluster resource consumption and shortens execution times on large datasets.

Answer

Increase the number of shuffle partitions to 2000.

Answer

Convert the large table to a Delta table with Z-Ordering.

Answer

Use the MERGE INTO statement instead of a standard JOIN.