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

Question 1

Which capability is provided by Databricks SQL 'Alerts' when a threshold is triggered?

Accepted Answer

Sending notifications via integrated channels.. Alerts in Databricks SQL are designed to monitor specific query results and trigger notifications based on defined conditions. When the threshold is met, the system can send notifications via email or external integrations like Slack or Webhooks. This functionality is vital for business monitoring, enabling teams to respond quickly to changes in data, such as sudden drops in performance or reaching critical business KPIs without manual intervention.

Answer

Automatically restarting the SQL Warehouse.

Answer

Rolling back the last data transaction.

Answer

Purging the SQL result cache.

Question 2

Which of the following describes the purpose of 'Query History' in Databricks SQL?

Accepted Answer

To monitor query performance and troubleshoot errors.. Query History provides a comprehensive log of all queries executed against the SQL Warehouse. It includes critical information such as execution time, user identity, and status. For analysts, this tool is indispensable for identifying slow-running queries, debugging execution errors, and auditing data access. By reviewing the query profile, analysts can gain deep insights into how their code is performing, which is vital for continuous performance tuning and optimizing SQL code in production.

Answer

To permanently store query results in cold storage.

Answer

To manage user access permissions for tables.

Answer

To automatically optimize slow SQL code.

Question 3

Which TWO of the following are benefits of using Unity Catalog with Databricks SQL?

Accepted Answer

Centralized access control for all data assets.. Unity Catalog provides a unified governance layer for data and AI assets across the Databricks workspace. It enables fine-grained access control, centralized auditing, and simplified data discovery. By implementing Unity Catalog, organizations can ensure compliance and security while allowing analysts to collaborate effectively. This is a critical component of modern data platforms, providing the necessary visibility and control required by data teams to manage data assets securely at scale.

Answer

Automatic translation of SQL to Python code.

Answer

Increased query execution speed for simple SELECTs.

Answer

Ability to bypass authentication for local files.

Question 4

Which option is recommended to ensure that a SQL Warehouse automatically terminates when no queries are being executed, thereby minimizing unnecessary costs?

Accepted Answer

Setting 'Auto-stop' in the Warehouse configuration.. Auto-stop is a fundamental cost-management feature for Databricks SQL. By configuring a reasonable auto-stop period, analysts can ensure that compute resources are released when idle. This is a critical best practice for maintaining a cost-efficient data platform, particularly for development or staging environments where queries may be sporadic. Proper use of auto-stop directly impacts the overall cloud bill and is an essential configuration step for every SQL Warehouse instance.

Answer

Manual shutdown via the CLI.

Answer

Using a Cron job to delete the warehouse.

Answer

Reducing the cluster size to zero.

Question 5

When migrating a reporting workload to Databricks SQL, an analyst needs to ensure that specific query results are consistent across multiple runs, even if the underlying Delta table is being updated. Which feature should the analyst utilize?

Accepted Answer

Delta Lake Time Travel (VERSION AS OF / TIMESTAMP AS OF).. Time Travel allows analysts to query a table as it existed at a specific point in time or version. This is critical for audits and reproducibility, ensuring that reports generated today can be replicated exactly, even if new data is added or old data is deleted. Understanding how to use the 'AS OF' syntax is vital for maintaining high standards of data integrity in analytical reporting workflows within the Databricks environment.

Answer

Materialized Views.

Answer

Setting the table to 'Read-Only' during reports.

Answer

Database snapshots created via manual backups.

Question 6

An analyst is using Databricks SQL and wants to optimize query performance for large-scale joins. Which TWO actions should the analyst perform to improve the performance of join operations involving large Delta tables?

Accepted Answer

Run ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS join_key. For large joins, Databricks SQL relies on statistics and efficient data layout. Collecting accurate table statistics allows the Cost-Based Optimizer (CBO) to select the most efficient join type, such as Broadcast or Shuffle Hash. Additionally, Z-Ordering on join keys ensures that related data is physically colocated, drastically reducing data shuffling across the cluster nodes during execution, which leads to faster query runtimes and better resource utilization.

Answer

Manually partition the table by the join key column

Answer

Increase the number of partitions to the maximum allowed

Answer

Set the table format to Parquet instead of Delta

Question 7

Which Databricks SQL feature should an analyst use to view the execution plan and understand the data flow, including the number of rows processed and the time taken at each stage of a query?

Accepted Answer

The Query Profile UI. The Query Profile (or Query History UI) in Databricks SQL is the standard tool for monitoring and tuning queries. It provides a visual representation of the execution DAG (Directed Acyclic Graph), showing details like row counts, time spent in each operation, and potential bottlenecks, which is crucial for identifying why a specific query might be slow or inefficient.

Answer

The DESCRIBE DETAIL command

Answer

The SHOW TABLES command

Answer

The EXPLAIN command in a notebook

Question 8

An analyst needs to query a table that has many small files resulting from frequent streaming ingestions. Which command is most appropriate to consolidate these files into larger, more efficient files for future analytical queries?

Accepted Answer

OPTIMIZE table_name. The OPTIMIZE command is the standard way to compact small files in Delta tables. It merges multiple small files into larger files, which reduces metadata overhead and improves read performance. By combining this with Z-Ordering, the data layout is optimized for common query patterns, ensuring that the SQL engine reads only the necessary data blocks during execution, which is vital for efficient data warehousing.

Answer

VACUUM table_name

Answer

REFRESH TABLE table_name

Answer

ALTER TABLE table_name REORGANIZE

Question 9

Refer to the exhibit. The table 'sales' is partitioned by 'region' and 'order_date'. How does the Databricks SQL engine process this query to ensure optimal performance?

Accepted Answer

It uses partition pruning to skip files in partitions not matching the filters. Databricks SQL utilizes partition pruning to ignore directories that do not match the WHERE clause filters. By identifying that the query filters on both 'region' and 'order_date', the engine restricts the scan to specific subdirectories on storage. This significantly reduces the amount of I/O required, which is the primary driver of performance in large-scale data lake queries, as it avoids reading irrelevant data from the storage layer.

Answer

It performs a full table scan and filters the results in memory

Answer

It re-indexes the table columns to facilitate faster lookup

Answer

It automatically broadcasts the table to all nodes for faster processing

Question 10

An analyst is preparing a dashboard and needs to ensure that the queries powering it are as performant as possible. Which THREE techniques should the analyst use to optimize these SQL queries in Databricks?

Accepted Answer

Regularly run OPTIMIZE on the underlying tables. Optimizing dashboards requires a combination of efficient data layout, metadata management, and query-level tuning. Using the 'OPTIMIZE' command keeps files consolidated, while 'ANALYZE' provides the CBO with the statistics it needs for optimal plan selection. Finally, using 'Z-Ordering' on high-cardinality columns frequently appearing in filters or joins ensures the query engine can skip large volumes of irrelevant data, directly leading to faster dashboard response times.

Answer

Always use SELECT * to fetch all available columns

Answer

Convert all Delta tables to CSV format for speed

Question 11

An analyst runs a long-running query that fails with an 'Out of Memory' (OOM) error. Which approach is the most effective way to resolve this without changing the underlying data structure?

Accepted Answer

Resize the SQL Warehouse to a larger instance type. OOM errors in Databricks SQL often occur due to large joins or aggregations that exceed available executor memory. Using hints, such as a broadcast join hint, can instruct the engine to handle data movement differently. If a specific join is causing the OOM, forcing a broadcast join (for small tables) or increasing the memory of the SQL warehouse can provide the necessary resources to complete the task.

Answer

Increase the number of partitions to 10,000

Answer

Use a broadcast hint for large-to-large table joins

Answer

Reduce the number of columns selected in the query

Question 12

Refer to the exhibit. Why might the analyst choose to create a new table with 'AS SELECT *' instead of just running OPTIMIZE on the original table?

Accepted Answer

It provides a cleaner way to reorganize data and apply Z-Ordering. Creating a new table via CTAS (Create Table As Select) allows the analyst to re-partition or re-cluster the data from scratch, which is often faster and cleaner than reorganizing an existing, fragmented table. This process also allows for the application of Z-Ordering during the initial creation, ensuring that the new table has the optimal data layout from the first day of its existence.

Answer

The original table is read-only and cannot be modified

Answer

It is the only way to delete old data from the table

Answer

CTAS automatically creates indexes on the table

Question 13

When querying a Delta table that is updated frequently, what is the most reliable way to ensure an analyst sees the most up-to-date data?

Accepted Answer

The Delta Lake engine automatically ensures read-consistency. Delta Lake maintains a transaction log that ensures ACID compliance. When a query is initiated, the engine reads the latest version of the transaction log to determine which data files are current. Because the transaction log is the source of truth, the engine automatically provides read-consistent snapshots, ensuring that the analyst always queries the current state of the table without needing manual cache refreshes.

Answer

Run REFRESH TABLE before every query

Answer

Clear the browser cache in the SQL editor

Answer

Execute the VACUUM command to force an update

Question 14

Which of the following describes the purpose of 'Data Skipping' in Databricks SQL when querying Delta tables?

Accepted Answer

It uses min/max statistics to skip irrelevant files. Data Skipping is a performance optimization where the Delta engine uses file-level statistics (min/max values) to prune files that do not contain data relevant to the query's filter conditions. By avoiding the I/O of reading irrelevant data files, the engine significantly speeds up queries. This is a primary benefit of the Delta format and is automatically handled by the engine during the planning phase of a SQL query.

Answer

It deletes old data files to save storage space

Answer

It caches frequently used tables in the browser

Answer

It allows queries to run without reading any files

Question 15

An analyst is observing that a specific query is slow because it performs a large shuffle. Which of the following is the most likely cause, and how can it be mitigated?

Accepted Answer

Cause: Poor data colocation; Mitigation: Use Z-Ordering. Large shuffles occur when data must be redistributed across nodes to perform a join or group by operation. This often happens if the data is not partitioned or clustered effectively on the join keys. To mitigate this, the analyst should ensure that the table is Z-Ordered on the relevant columns, as this colocates the data and allows the engine to perform more efficient local operations instead of cross-node shuffles.

Answer

Cause: Lack of partitioning; Mitigation: Increase cluster size

Answer

Cause: Too many files; Mitigation: Run VACUUM

Answer

Cause: Column data types; Mitigation: Use VARCHAR instead of STRING