Databricks-DA-Assoc Practice Test 1 — 10 Questions

Question 1

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?

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

Accepted Answer

EXPLAIN EXTENDED SELECT * FROM table_name WHERE condition;. The EXPLAIN command in Spark SQL outputs the execution plans, including parsed logical plan, analyzed logical plan, optimized logical plan, and physical plan. Analyzing these plans helps data analysts identify inefficient join strategies, missing predicate pushdown, and unnecessary full table scans.

Answer

DESCRIBE EXTENDED table_name;

Answer

SHOW QUERY PLAN FOR SELECT * FROM table_name WHERE condition;

Answer

ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS;

Question 2

An analyst wants to ensure that a Genie space only uses the most recent data for all queries. How can this be achieved?

Accepted Answer

Use instructions to mandate filters on date columns. To enforce data freshness, the analyst should instruct the Genie space to use views or filter conditions that point to the latest partitions or versioned tables. By incorporating this logic into the instructions, the developer ensures that the model always applies the necessary filter clauses (like 'WHERE date = CURRENT_DATE') to every generated query, maintaining data integrity without requiring manual input from the users.

Answer

Set the SQL Warehouse to auto-terminate every hour

Answer

Delete old data from the Unity Catalog tables

Answer

Rename tables whenever new data arrives

Question 3

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?

Accepted Answer

Run an OPTIMIZE command with a ZORDER BY clause on the timestamp column to co-locate related data and improve data skipping.. Z-Ordering co-locates related data based on specified columns, significantly improving data skipping for queries with equality or range filters. When combined with correct partitioning, it minimizes the amount of data scanned from cloud storage, drastically reducing query latency and execution costs in Databricks environments.

Answer

Increase the cluster size to a driver instance with more memory to hold the entire uncompressed Delta table in cache.

Answer

Execute a VACUUM command with a retention threshold of zero hours to purge old data files immediately.

Answer

Convert the Delta table format to standard Parquet files to take advantage of native Apache Spark partitioning.

Question 4

An analyst is preparing a Genie Space and wants to ensure users get the most accurate answers while maintaining security. Which TWO actions should the analyst take? (Choose two)

Accepted Answer

Exclude highly sensitive columns that are not needed for general analysis.. Proper curation of the Genie Space is vital for performance and security. By including only the necessary columns and providing explicit instructions, the analyst reduces noise and ensures the AI model operates within the boundaries of the organization's data governance framework. These steps directly impact the quality of the SQL generated and the safety of the information exposed to end-users via the AI interface.

Answer

Enable 'auto-query' for all users to maximize data exploration.

Answer

Grant all users the 'Owner' role on the Genie Space to allow for maximum customization.

Answer

Remove all constraints and indexes to allow the AI to move faster.

Question 5

A data analyst is using a Databricks SQL warehouse to query a table that is protected by row-level security using dynamic views in Unity Catalog. The analyst has SELECT on the view but not on the underlying table. When querying the view, the analyst receives an error about insufficient privileges on the base table. What is the most likely cause?

Accepted Answer

The view owner does not have SELECT privilege on the underlying table, so the view cannot access it.. In Unity Catalog, views execute with the permissions of the view owner, not the querying user. If the view owner lacks SELECT on the underlying table, the view cannot access the data, resulting in a permission error for any user. The analyst's privileges on the view are sufficient, but the owner must have the necessary privileges on the base table.

Answer

The analyst needs to be granted SELECT on the base table directly to query the view.

Answer

The view is defined with SQL SECURITY INVOKER, which requires the analyst to have base table access.

Answer

The view was created without the SECURITY DEFINER clause, so the analyst's permissions are used to access the base table.

Question 6

An analyst is building an AI/BI Genie space over a Unity Catalog schema containing both `orders` and `customers` tables. Business users often ask questions that require joining the two tables on `customer_id`. During testing, Genie frequently generates queries that join on the wrong column or omit the join entirely, producing inflated row counts. The analyst has already verified that both tables are selected and that column names are clear. Which action is most likely to correct Genie's join behavior?

Accepted Answer

Add example SQL queries to the Genie space that explicitly join `orders` and `customers` on `customer_id`.. When Genie produces incorrect joins despite clear table and column names, the most reliable correction is to supply example SQL that demonstrates the intended join condition. Examples give the model a concrete pattern to imitate, including the join key and aliases. General instructions help but lack the precision of a worked query, and metadata constraints are not automatically leveraged during generation.

Answer

Rename `customer_id` in both tables to `cust_key` so the join column is unique across the schema.

Answer

Add a general instruction stating that the `orders` and `customers` tables should always be joined when both are referenced.

Answer

Create a foreign key constraint between `orders.customer_id` and `customers.customer_id` in Unity Catalog.

Question 7

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

Question 8

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

Question 9

Refer to the exhibit. You are attempting to enable Change Data Feed (CDF) on an existing Delta table but receive this error. Why is this error occurring?

Accepted Answer

The table requires column mapping to track physical schema evolution for CDF.. Change Data Feed (CDF) tracks row-level changes, which requires a stable mapping between column names and their underlying physical file data. If the table was created without column mapping, it cannot safely track changes if column names are altered. Setting 'delta.columnMapping.mode' to 'name' decouples the column names from their physical file identifiers, which is a prerequisite for the metadata tracking required by the CDF feature.

Answer

The table is not in the Delta format.

Answer

The table has too many rows to enable CDF.

Answer

CDF is only supported on tables with primary keys.

Question 10

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.