Databricks-DA-Assoc Practice Test 1 — 30 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.

Question 11

Refer to the exhibit. An analyst is reviewing a cluster configuration JSON. If the cluster is currently idle, what happens after 40 minutes of inactivity?

Accepted Answer

The cluster terminates.. Databricks clusters with auto-termination enabled will shut down after the specified inactivity threshold is met. In this configuration, the 'autotermination_minutes' is set to 30. Therefore, once the cluster remains idle for 30 minutes, it will automatically terminate. By the 40-minute mark, the cluster has already completed the termination sequence, ensuring no additional costs are incurred for idle resources beyond the defined grace period.

Answer

The cluster scales down to 2 workers.

Answer

The cluster remains idle at 2 workers.

Answer

The cluster scales up to 8 workers.

Question 12

An analyst has a Delta table `bronze.raw_events` that has accumulated many small files over months of streaming writes. They now need to optimize read performance for downstream dashboards. Which Databricks SQL command should they run?

Accepted Answer

`OPTIMIZE bronze.raw_events;`. The small-file problem from streaming writes is resolved by compaction, and OPTIMIZE performs exactly that by merging small files into larger ones. Statistics gathering and vacuuming address different concerns, and write-time properties only influence future writes rather than existing layout. Compaction is the correct maintenance action for improving scan performance on a fragmented Delta table.

Answer

`VACUUM bronze.raw_events;`

Answer

`ANALYZE TABLE bronze.raw_events COMPUTE STATISTICS;`

Answer

`ALTER TABLE bronze.raw_events SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true');`

Question 13

A Databricks workspace administrator wants to optimize costs and manage resources effectively. Which TWO of the following capabilities allow for automatic cluster termination and scaling?

Accepted Answer

Auto-termination. Databricks provides built-in mechanisms to manage compute costs through Auto-termination and Auto-scaling. Auto-termination automatically shuts down clusters after a period of inactivity, preventing wasteful billing. Auto-scaling allows the cluster to resize itself based on the current workload requirements, adding or removing workers dynamically. These features are fundamental for maintaining a cost-efficient data platform while ensuring sufficient performance for varying data volumes during analysis.

Answer

Cluster Tagging

Answer

Delta Cache

Answer

Instance Pools

Question 14

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 15

When importing data into Databricks, what is the primary benefit of using Delta Lake over standard Parquet files?

Accepted Answer

Delta Lake supports ACID transactions and schema enforcement.. Delta Lake adds a transaction log (the _delta_log folder) to Parquet files, enabling ACID transactions and time travel. This prevents data corruption during concurrent writes and allows users to query previous versions of data. This is a foundational concept for data reliability, as it ensures that analytical queries are always executed against a consistent and version-controlled state, which is impossible with standard, non-transactional Parquet files in a distributed system.

Answer

Delta Lake files require less storage space than Parquet.

Answer

Delta Lake is faster for reading large tables than Parquet.

Answer

Delta Lake is natively supported by all external BI tools.

Question 16

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 17

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 18

What is the primary purpose of the Databricks Catalog Explorer?

Accepted Answer

To browse and manage metadata for catalogs, schemas, tables, and volumes.. Catalog Explorer is a unified interface within Databricks for managing and exploring the data ecosystem. It allows users to browse schemas, tables, and views, manage data permissions, and view data lineage. For a data analyst, it is the central hub for understanding data assets, verifying table metadata, and ensuring that the necessary access rights are in place before starting analysis, making it an essential tool for data discovery and governance.

Answer

To manage the deployment and monitoring of automated machine learning models.

Answer

To monitor the health and logs of active Spark clusters.

Answer

To develop and execute Python scripts in interactive notebooks.

Question 19

When creating a dashboard, an analyst needs to compare two different categories side-by-side. Which visualization is best suited for this task?

Accepted Answer

Grouped Bar Chart. A grouped bar chart is the most effective visualization for comparing distinct categories side-by-side. It allows the viewer to easily perceive differences in magnitude between related items. This is a common requirement in business reporting, where analysts must compare performance across regions, products, or time periods. By selecting the right visualization, the analyst reduces the cognitive effort required for the audience to extract insights from the data.

Answer

Heatmap

Answer

Bubble Chart

Answer

Stacked Area Chart

Question 20

A data analyst needs to ingest a daily batch of comma-separated value (CSV) files stored in cloud object storage into a Databricks Delta table. The files contain a header row, use a semicolon (;) as the delimiter, and occasionally include embedded newlines within quoted fields. Which approach ensures the parser correctly handles the multi-line fields and missing values?

Accepted Answer

Apply the spark.read.format("csv").option("delimiter", ";").option("header", "true").option("multiLine", "true").load(path) command.. Configuring the Spark CSV reader with explicit options like multiLine set to true ensures that records containing embedded newlines are not split prematurely across rows. Data analysts frequently encounter messy raw external datasets where standard single-line parsing fails, leading to corrupted row counts and schema mismatches. Explicitly defining formatting options guarantees reliable ingestion before downstream business intelligence reporting takes place.

Answer

Use spark.read.table() with default parameters because Spark automatically infers delimiters and multiline configurations for all incoming CSV files.

Answer

Convert the CSV files into Parquet format using a local Python script outside Databricks before uploading them to the workspace file system.

Answer

Execute a standard SQL COPY INTO command without specifying any options, as Delta tables automatically resolve non-standard delimiters.