Databricks-DA-Assoc Executing Queries with Databricks SQL — 20 Questions

Question 1

A data analyst needs to query a Delta table but wants to ensure the query only processes data from the last 24 hours to minimize costs. Which syntax should the analyst use to optimize this query?

Accepted Answer

SELECT * FROM table_name WHERE date_col >= CURRENT_DATE() - INTERVAL 1 DAY. To query only data from the last 24 hours (i.e., recent data), a standard WHERE clause combined with Delta Lake's data skipping (Z-ordering, statistics) is the correct and intended approach. Option A (or a standard WHERE filter on the timestamp/date column) allows Delta Lake to prune files based on min/max statistics. Using TIMESTAMP AS OF (Option C) queries the table state as it existed 24 hours ago, not the data from the last 24 hours. The explanation and trap notes incorrectly claim that WHERE clauses filter data *after* reading it, ignoring Delta Lake's file-level stats and data skipping capabilities.

Answer

SELECT * FROM table_name VERSION AS OF '2023-10-01'

Answer

SELECT * FROM table_name TIMESTAMP AS OF CURRENT_TIMESTAMP() - INTERVAL 1 DAY

Answer

SELECT * FROM table_name OPTIMIZE FOR 24_HOURS

Question 2

Refer to the exhibit. An analyst encounters this error while running a large analytical query. What is the most appropriate step to resolve this issue?

Accepted Answer

Increase the SQL Warehouse to a larger size.. This error signifies that the local disk on the SQL Warehouse nodes is full, usually caused by large temporary spillages during heavy sorts, aggregations, or joins. Scaling up the SQL Warehouse to a larger size provides more local SSD storage per node to handle these large intermediate spillages.

Answer

Run the ANALYZE TABLE command.

Answer

Clear the browser cache and try again.

Answer

Restart the Databricks cluster.

Question 3

Which TWO of the following are valid ways to improve the performance of a query that joins two large tables in Databricks SQL?

Accepted Answer

Use Z-Ordering on the join keys.. While Z-Ordering (A) is a valid optimization for join keys, partitioning (C) on join keys is generally discouraged in Databricks/Delta Lake because join keys often have high cardinality, leading to the 'small file problem' and excessive metadata overhead. A better second option would typically involve broadcast hints or optimizing the join type, but given the provided options, C is a common trap rather than a best practice.

Answer

Force a broadcast join on both tables.

Answer

Partition the tables on the join keys.

Answer

Convert all tables to CSV format.

Answer

Use a single-node warehouse.

Question 4

A data analyst needs to query a Delta table but wants to ensure the query only processes data from the last 24 hours to minimize costs. Which syntax should the analyst use to optimize this query?

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

Accepted Answer

Switch to a Serverless SQL Warehouse.. Serverless SQL Warehouses improve concurrency primarily through rapid startup times and efficient multi-cluster load balancing. However, the claim that they 'offload compute resources from the storage layer' to prevent contention is misleading; Delta Lake's ACID properties and optimistic concurrency control are what prevent blocking between readers and writers, not the warehouse type itself. The primary benefit of Serverless for concurrency is the ability to scale out clusters dynamically to handle high query volume.

Answer

Enable Auto-Stop on the SQL Warehouse.

Answer

Increase the maximum number of clusters.

Answer

Reduce the warehouse size to X-Small.

Question 5

An analyst is using the Databricks SQL editor and needs to ensure that their query results are not cached, forcing the engine to fetch the latest data from the source. What is the most effective way to achieve this?

Accepted Answer

Add a dummy comment with a dynamic value.. Databricks SQL result caching is automatically invalidated when the underlying data changes. There is no supported 'dummy comment' method to force a cache miss in the Databricks SQL documentation. The question implies a 'most effective way' exists, but the provided answer is a hack rather than a standard feature. Furthermore, the explanation contradicts itself by stating 'developers should note that SQL Warehouses automatically manage cache invalidation' while simultaneously recommending a workaround.

Answer

Use the REFRESH TABLE command before the query.

Answer

Set the warehouse to 'ReadOnly' mode.

Answer

Restart the SQL warehouse instance.

Question 6

A data analyst needs to query a Delta table but wants to ensure the query only processes data from the last 24 hours to minimize costs. Which syntax should the analyst use to optimize this query?

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

Accepted Answer

OPTIMIZE table_name ZORDER BY (column_name). Delta Lake uses Multi-Version Concurrency Control (MVCC) to allow concurrent reads and writes by default. However, frequent writes can lead to the 'small file problem,' which degrades read performance. The OPTIMIZE command compacts these small files into larger ones. When combined with ZORDER BY, it also organizes the data to allow for better data skipping, further improving query performance for analytical workloads.

Answer

ALTER TABLE table_name SET TBLPROPERTIES ('delta.enableChangeDataFeed' = 'true')

Answer

REFRESH TABLE table_name

Answer

ANALYZE TABLE table_name COMPUTE STATISTICS

Question 7

Refer to the exhibit. An analyst attempts to run a query on a Delta table and receives the error shown. Which action should the analyst perform to resolve this metadata inconsistency?

Accepted Answer

Execute MSCK REPAIR TABLE table_name. When partitions added to the file system are not registered in the metastore, running MSCK REPAIR TABLE table_name scans the table directory and adds the missing partitions to the metastore.

Answer

Run VACUUM table_name RETAIN 0 HOURS

Answer

Execute FSCK REPAIR TABLE table_name

Answer

Drop and recreate the table using a CTAS statement

Question 8

Refer to the exhibit. An analyst executing a query in Databricks SQL receives the shown error message when attempting to query a table. What is the most likely root cause and remedy for this issue?

Accepted Answer

The query lacks the required three-level namespace qualification or the catalog context is misconfigured.. Databricks uses a three-level namespace consisting of catalog, schema, and table. If a schema cannot be found, it usually means the active catalog context does not contain that schema, requiring the analyst to fully qualify the table name with the correct catalog and schema.

Answer

The SQL warehouse is currently paused and needs manual resumption.

Answer

The analyst's user account has exceeded its daily query execution cost quota.

Answer

The underlying table data files have been physically deleted from cloud object storage.

Question 9

A data analyst is working with a Delta table in Databricks SQL that is frequently updated with new data. They need to run a query that returns the latest version of each row based on a timestamp column, ensuring that only the most recent record per primary key is returned. Which TWO of the following approaches are valid and efficient ways to achieve this in Databricks SQL? (Choose two.)

Accepted Answer

Use the QUALIFY clause with ROW_NUMBER() to filter the latest record per key.. Both the QUALIFY clause with ROW_NUMBER() and a subquery with ROW_NUMBER() are valid and efficient ways to retrieve the latest record per key. They leverage window functions to rank rows within each partition and filter to the top row. The other options either involve writing data or are less efficient.

Answer

Use the MERGE statement to update a target table with the latest records.

Answer

Use a self-join on the primary key and timestamp to find the maximum timestamp.

Answer

Use the LATERAL VIEW explode function to flatten the data.

Question 10

An analyst needs to run a query against a Delta table in Databricks SQL and wants to ensure that the query uses the most recent data available, even if there are concurrent writes. Which SQL command should they use to refresh the table metadata and see the latest data?

Accepted Answer

REFRESH TABLE table_name. REFRESH TABLE is the correct command to invalidate cached metadata and data, ensuring the query sees the latest version of the Delta table. The other commands serve different purposes: caching for performance, analyzing for statistics, and vacuuming for cleanup.

Answer

VACUUM table_name

Answer

CACHE TABLE table_name

Answer

ANALYZE TABLE table_name COMPUTE STATISTICS

Question 11

A data analyst is working in the Databricks SQL query editor and needs to ensure that a query against a Delta table returns the latest committed data, even if there are concurrent write operations. The analyst also wants to minimize the impact on other queries running in the same warehouse. Which TWO actions should the analyst take? (Choose two.)

Accepted Answer

Set the Spark configuration spark.databricks.delta.snapshotIsolation to true to ensure snapshot isolation.. Enabling snapshot isolation (spark.databricks.delta.snapshotIsolation) ensures the query reads a consistent snapshot of the table, preventing dirty reads from concurrent writes. Querying with VERSION AS OF the latest version from DESCRIBE HISTORY explicitly reads a committed snapshot. Both approaches minimize interference with other queries, unlike VACUUM or OPTIMIZE which are maintenance operations.

Answer

Use the OPTIMIZE command with ZORDER on the table to improve data skipping.

Answer

Set the Spark configuration spark.sql.adaptive.enabled to true to enable adaptive query execution.

Answer

Use the VACUUM command before running the query to remove old files and improve read performance.

Question 12

A data analyst is optimizing a Databricks SQL query that filters a large Delta table by a date column. The analyst wants to reduce the amount of data scanned. Which TWO actions should the analyst take? (Choose two.)

Accepted Answer

Ensure the date column is used in a WHERE clause with a literal or parameter.. To reduce data scanned when filtering by date, the analyst should use a WHERE clause on the date column and partition the table by that column. The WHERE clause enables predicate pushdown, while partitioning allows Delta Lake to skip entire partitions. Other actions like SELECT * or increasing shuffle partitions do not help and may hurt performance.

Answer

Increase the spark.sql.shuffle.partitions setting.

Answer

Use SELECT * to retrieve all columns for simplicity.

Answer

Apply a Z-order clustering on the date column.

Question 13

A data analyst is using the Databricks SQL query editor and wants to avoid re-scanning an entire 2 TB Delta table each time they tweak a visual in the accompanying dashboard. The table's underlying data changes only once per day at 02:00 UTC, and the analyst refreshes their dashboard at 08:00 UTC. Which Databricks SQL feature should the analyst use to persist the query result so subsequent dashboard interactions read from cached storage instead of re-executing the full scan?

Accepted Answer

Enable the query's Result Cache in the Databricks SQL warehouse settings.. Result Cache on a Databricks SQL warehouse stores query results on local SSD, so identical queries return instantly without re-reading Delta files. Since the table only changes once daily and the dashboard refreshes after that window, the cached result stays valid throughout the analyst's interactive session. This is the intended mechanism for avoiding repeated large scans during dashboard exploration.

Answer

Convert the query into a Databricks SQL Alert that triggers on a schedule.

Answer

Add a Z-ORDER BY clause on the join key of the Delta table.

Answer

Create a Delta Live Tables pipeline that materializes the query as a streaming table.

Question 14

An analyst is running queries on a shared Databricks SQL Warehouse. Which TWO actions improve query performance by reducing the impact of high concurrency?

Accepted Answer

Enable Result Set Caching on the SQL Warehouse.. Managing concurrency in Databricks SQL involves both infrastructure configuration and query optimization techniques. By using materialized views or caching strategies, you reduce the compute load on the warehouse. These strategies prevent redundant processing of complex logic, ensuring that concurrent users receive faster responses without requiring the warehouse to constantly re-compute heavy analytical joins and aggregations on raw tables.

Answer

Use SELECT * exclusively in all production dashboards.

Answer

Increase the warehouse size to 4XL regardless of query complexity.

Answer

Always use ORDER BY in every subquery.

Question 15

Refer to the exhibit. An analyst receives this error when attempting to select from a table in Databricks SQL. What is the most likely cause?

Accepted Answer

The analyst is missing the USE CATALOG or USE SCHEMA statement.. The error indicates a Namespace resolution issue. In Databricks SQL, tables are organized in a three-level namespace: catalog.schema.table. If the current session context is not set to the correct catalog and schema, or if the user lacks the necessary privileges to see the object, the engine cannot resolve the path. Correcting the context or using a fully qualified name resolves this common accessibility error.

Answer

The table schema is corrupted and requires a REPAIR TABLE command.

Answer

The SQL Warehouse is stopped and needs to be restarted.

Answer

The table has too many partitions, causing a timeout.

Question 16

An analyst is performing a join between a large fact table and a small dimension table. To ensure optimal performance in Databricks SQL, which join type is preferred when the dimension table fits in memory?

Accepted Answer

Broadcast hash join. When one side of a join is significantly smaller than the other, the engine can use a broadcast join. By sending the smaller table to all worker nodes, the engine avoids a costly shuffle of the larger fact table. This is the most efficient way to perform joins in a distributed environment, as it minimizes network traffic and accelerates data processing speeds significantly.

Answer

Sort-merge join

Answer

Cartesian join

Answer

Full outer join

Question 17

You want to create a temporary view that is only available for the duration of the current SQL session. Which command achieves this?

Accepted Answer

CREATE TEMPORARY VIEW view_name AS SELECT * FROM table. Temporary views are essential for breaking down complex analytical tasks into manageable chunks without cluttering the persistent metadata layer. By scoping the view to the session, you ensure that temporary tables do not interfere with other users or permanent database objects. This is a standard best practice for modularizing complex data preparation scripts before inserting them into final reporting tables.

Answer

CREATE VIEW view_name AS SELECT * FROM table

Answer

CREATE LOCAL VIEW view_name AS SELECT * FROM table

Answer

CREATE SESSION TABLE view_name AS SELECT * FROM table

Question 18

An analyst is preparing a report and needs to ensure that sensitive PII columns are not exposed. Which TWO techniques can be used to achieve this in Databricks SQL?

Accepted Answer

Apply Dynamic Data Masking to the sensitive columns.. Data governance and security are paramount in Databricks SQL. Using column-level filtering and dynamic data masking ensures that analysts only see the data they are authorized to access. These security controls are enforced at the query plan level by Unity Catalog, ensuring that security policies are consistent regardless of the tool or interface the user is utilizing to connect to the warehouse.

Answer

Use the GRANT ALL PRIVILEGES command on all tables.

Answer

Implement Row-Level Security using a filter clause in the view definition.

Answer

Run the query as a superuser to bypass masks.

Question 19

An analyst wants to view the query profile for a specific query that was run recently. Where should they look in the Databricks SQL UI?

Accepted Answer

The Query History tab. The Query History interface is the primary tool for performance troubleshooting. It provides a visual representation of the query plan, including task breakdown, data read/write volumes, and execution time per operator. Understanding this UI is vital for a Data Analyst to identify bottlenecks such as data skew or excessive shuffling, enabling them to optimize slow queries effectively and improve the efficiency of their SQL workloads.

Answer

The Catalog Explorer

Answer

The SQL Editor

Answer

The SQL Warehouse settings page

Question 20

Which clause is used in Databricks SQL to filter results after an aggregation has been performed?

Accepted Answer

HAVING. The HAVING clause is specifically designed to filter groups created by the GROUP BY clause. Unlike the WHERE clause, which filters rows before aggregation, HAVING operates on the resulting aggregated data. Mastering this distinction is fundamental for writing reports that require thresholds on calculated metrics, such as identifying products with total sales greater than a specific monetary value in a monthly summary.

Answer

WHERE

Answer

GROUP BY

Answer

FILTER