Databricks-DA-Assoc Executing Queries with Databricks SQL Practice Test 4 — 15 Questions

Question 1

Which THREE of the following are benefits of using the Delta Lake format in Databricks SQL?

Accepted Answer

ACID transactions for data integrity. Delta Lake provides the foundation for reliable, performant SQL querying in Databricks. Key benefits include ACID transactions, which prevent data corruption during concurrent operations; time travel, which allows analysts to query previous versions of data; and data skipping/compaction, which drastically improve query performance. Together, these features enable reliable data warehousing and complex analytics at scale, which are the main reasons why it is the standard format in Databricks SQL environments.

Answer

The ability to run queries without a cluster

Answer

Automatic conversion of all queries to Python

Question 2

A data analyst is running a complex aggregate query in Databricks SQL that frequently times out before finishing. The underlying Delta table contains hundreds of gigabytes of historical log data partitioned by date. What is the most effective Databricks SQL query optimization technique to apply directly within the SQL statement?

Accepted Answer

Include a explicit WHERE clause filtering on the date partition column to leverage partition pruning.. Applying a partition filter using the date column allows Databricks SQL to prune irrelevant files instantly, significantly reducing the data scan volume and preventing timeouts. This practice ensures queries execute efficiently over large historical datasets without needing constant infrastructure scaling or manual intervention.

Answer

Increase the overall timeout limit within the cluster configuration settings.

Answer

Convert the Delta table format into a standard Parquet format inside an external Hive metastore.

Answer

Wrap the main aggregation query inside a recursive common table expression.

Question 3

A data analyst is running a complex, resource-intensive query inside the Databricks SQL query editor that frequently times out before returning results. Which administrative feature should the analyst or workspace admin leverage to manage compute resources and prevent long-running queries from blocking other users?

Accepted Answer

Configure the automated query timeout setting on the SQL warehouse to automatically abort queries exceeding a specific duration.. Databricks SQL warehouses support query timeout limits and auto-stopping features that prevent runaway queries from consuming cluster resources indefinitely. Configuring these timeouts ensures cluster availability and protects shared analytical environments from deadlocks and excessive consumption, making it a critical skill for efficient query execution management.

Answer

Increase the maximum number of concurrent users in the SQL warehouse cluster settings.

Answer

Convert the query into a Spark Structured Streaming job using the Databricks notebook interface.

Answer

Manually restart the underlying Apache Spark driver node using the cluster UI restart button.

Question 4

A data analyst needs to share a frequently updated sales dashboard with business stakeholders who do not have access to the underlying raw tables containing Personally Identifiable Information (PII). Which Databricks SQL feature should be implemented to securely present this summary data?

Accepted Answer

Implement dynamic views utilizing column masking and row-level filtering based on user group identities.. Dynamic View Functions with masking policies allow administrators to restrict column-level visibility based on group memberships or user contexts. This ensures stakeholders see aggregated or masked data without requiring separate downstream tables, maintaining strict data governance while enabling seamless visualization sharing.

Answer

Create static CSV extracts of the summary data and upload them to a shared workspace folder.

Answer

Grant the stakeholders direct SELECT permissions on the raw tables, relying on them to ignore sensitive columns.

Answer

Duplicate the entire dataset into a separate schema, permanently deleting the sensitive columns from the copy.

Question 5

An analyst is writing a query in the Databricks SQL editor and needs to reference a temporary view that was created earlier in the same interactive session. How are temporary views scoped within Databricks SQL environments?

Accepted Answer

They are scoped strictly to the current interactive session and drop automatically upon disconnection.. Temporary views in Databricks are scoped to the specific session or notebook in which they are defined. They disappear automatically once the session terminates, ensuring temporary scratchpads do not pollute global catalog namespaces while allowing complex multi-step queries within a single workflow.

Answer

They are globally visible to all users across the entire Databricks workspace indefinitely.

Answer

They persist permanently inside the default Hive metastore schema until manually dropped by an admin.

Answer

They are automatically replicated to all attached SQL warehouses for cross-warehouse analytics.

Question 6

Refer to the exhibit. An analyst attempts to query table changes using the table changes function (table_changes()) to track incremental updates for a reporting pipeline, but encounters the error shown in the exhibit. How should the analyst resolve this issue?

Accepted Answer

Run an ALTER TABLE command to set delta.enableChangeDataFeed = true, then re-run the query.. The Change Data Feed (CDF) must be explicitly enabled on a Delta table via table properties either at creation time or through an ALTER TABLE command. Without this configuration, Delta Lake does not record the low-level row insertions, updates, and deletions required for changestream queries.

Answer

Restart the SQL warehouse to clear internal metadata caches preventing change data feed access.

Answer

Convert the table format from Delta Lake to standard Apache Parquet using a CTAS statement.

Answer

Execute a VACUUM command with a retention period of zero hours to force immediate log compaction.

Question 7

A data analyst is using Databricks SQL to query a large Delta table. The query is performing slowly because it must scan the entire table to retrieve data for a specific date range. Which action should the analyst take to optimize query performance?

Accepted Answer

Execute an OPTIMIZE command with ZORDER BY on the relevant date columns.. Implementing Z-Ordering on the partition columns or columns frequently used in WHERE clauses significantly enhances data skipping capabilities. By physically organizing data on disk based on these values, Databricks SQL engine can skip irrelevant files during query execution. This optimization is critical in Databricks SQL environments because it reduces I/O overhead, lowers latency for end-user dashboards, and minimizes the computational resources required to process large-scale analytical workloads efficiently.

Answer

Convert the table format from Delta to Parquet to improve raw read throughput.

Answer

Increase the number of clusters in the SQL Warehouse to enable higher concurrency.

Answer

Use the CACHE SELECT statement on the entire table to force all data into memory.

Question 8

A data analyst has a Databricks SQL query that joins a large fact table to a small dimension table. The query is slow because the dimension table is being shuffled across the cluster. The analyst wants to avoid this shuffle and improve performance. Which SQL hint should they use?

Accepted Answer

BROADCAST. The BROADCAST hint tells the optimizer to send the smaller table to all worker nodes, allowing the join to occur locally without shuffling the larger table. This reduces network overhead and speeds up the query. The other options are either not hints or would increase shuffling.

Answer

REPARTITION

Answer

SHUFFLEMERGE

Answer

MERGE

Question 9

A data analyst is exploring a large Delta table named sales in the Databricks SQL query editor. Before writing a detailed report, the analyst wants to quickly view the first 10 rows to understand the column names and data types. Which SQL statement should the analyst execute?

Accepted Answer

SELECT * FROM sales LIMIT 10;. The analyst needs to preview actual rows from the sales table to understand its structure and content. Using SELECT * with LIMIT 10 retrieves a small sample efficiently. Other options return metadata or invalid syntax, none of which provide the required row-level data. This approach is standard for initial data exploration in Databricks SQL.

Answer

SHOW TABLES IN default LIKE 'sales';

Answer

DESCRIBE EXTENDED sales;

Answer

SELECT * FROM sales WHERE row_number <= 10;

Question 10

A data analyst is querying a Delta table that is frequently updated with new transactions. The analyst needs to ensure that a report always reflects the most recent committed data, even if a write operation is in progress. Which feature of Delta Lake should the analyst rely on?

Accepted Answer

Snapshot isolation. Delta Lake's snapshot isolation guarantees that every query reads a consistent snapshot of the table as of the latest committed transaction. This ensures the analyst sees the most up-to-date data even during concurrent writes. Other features like time travel or Z-order serve different purposes and do not provide the required freshness guarantee.

Answer

Z-order clustering

Answer

Optimistic concurrency control

Answer

Time travel

Question 11

A data analyst runs a query in Databricks SQL that joins a large fact table to a small dimension table. The query spills to disk and is slow. The analyst adds a /*+ BROADCAST(dim) */ hint to the query. What is the primary effect of this hint on the query execution?

Accepted Answer

It forces the optimizer to broadcast the dimension table to all executor nodes, avoiding a shuffle of the large fact table.. The BROADCAST hint tells the optimizer to replicate the small dimension table to all executors, so the join with the large fact table can be done locally without shuffling the fact table. This reduces network traffic and avoids disk spills, making the query faster. The hint is effective when the broadcasted table is small enough to fit in memory.

Answer

It caches the dimension table in Delta cache to speed up subsequent reads.

Answer

It partitions the fact table by the join key before the join to co-locate matching rows.

Answer

It sorts both tables by the join key before merging them, ensuring a sort-merge join.

Question 12

A data analyst is troubleshooting a slow Databricks SQL query that joins a large fact table with a small dimension table. The query plan shows a broadcast hash join, but the analyst notices that the small table is not being broadcast as expected. Which configuration should the analyst check to ensure the small table is broadcast?

Accepted Answer

spark.sql.autoBroadcastJoinThreshold. The broadcast hash join is governed by the autoBroadcastJoinThreshold configuration, which defines the maximum size for a table to be broadcast. If the small table exceeds this threshold, it will not be broadcast. Checking and potentially increasing this threshold can enable the broadcast, improving performance. Other settings affect different aspects of query execution and do not directly control broadcast eligibility.

Answer

spark.sql.adaptive.enabled

Answer

spark.databricks.delta.optimizeWrite.enabled

Answer

spark.sql.shuffle.partitions

Question 13

A data analyst needs to query a Delta table named sales and retrieve only the rows where the sale_date is in the year 2023. The table is partitioned by sale_date. Which SQL statement will most efficiently return the required data?

Accepted Answer

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01'. A range predicate directly on the partition column allows Databricks to prune partitions, reading only the data for 2023. Applying a function such as year() or cast() to the column disables pruning and forces a full scan. Therefore, the range condition is the most efficient.

Answer

SELECT * FROM sales WHERE cast(sale_date as string) LIKE '2023%'

Answer

SELECT * FROM sales WHERE year(sale_date) = 2023

Answer

SELECT * FROM sales WHERE sale_date LIKE '2023%'

Question 14

A data analyst is writing a Databricks SQL query that needs to join a large table with a small table and then aggregate the results. To optimize performance, they want to ensure the small table is broadcasted. Which SQL hint syntax should they use in the query?

Accepted Answer

SELECT /*+ BROADCAST(small_table) */ .... The BROADCAST hint in Databricks SQL is specified as /*+ BROADCAST(table_name) */ after SELECT. It directs the optimizer to broadcast the smaller table, avoiding a shuffle of the larger table. Other hints like MAPJOIN or BROADCASTJOIN are not supported.

Answer

SELECT /*+ MAPJOIN(small_table) */ ...

Answer

SELECT /*+ SHUFFLE_HASH(small_table) */ ...

Answer

SELECT /*+ BROADCASTJOIN(small_table) */ ...

Question 15

A data analyst is building a Databricks SQL dashboard that includes a parameter for the region. The dashboard should allow viewers to select a region from a dropdown list that is populated dynamically from the distinct values in the sales table's region column. Which feature should the analyst use to implement this?

Accepted Answer

Create a query-based dropdown parameter that runs a SELECT DISTINCT region FROM sales query to populate the list.. Databricks SQL dashboards support query-based parameters, which dynamically populate a dropdown from a SQL query. This ensures the list of regions is always up to date. Static lists or text inputs do not provide dynamic population from the data, and filter widgets are not parameters.

Answer

Add a filter widget on the dashboard that uses a SELECT DISTINCT query, and link it to the visualizations.

Answer

Implement a text input parameter and instruct users to type the region name.

Answer

Use a static dropdown parameter and manually enter all possible region values.