Databricks-DA-Assoc Practice Test 21 — 15 Questions

Question 1

An analyst needs to perform a join between a very large table and a small lookup table. Which optimization strategy should the analyst ensure is utilized to maximize performance?

Accepted Answer

Broadcast Hash Join. Broadcast joins are the most efficient way to join a small table with a large table. By broadcasting the small table to all worker nodes, Databricks avoids expensive data shuffling, which is the most common bottleneck in distributed joins. Utilizing the query optimizer's ability to perform broadcast joins is a key skill for Databricks SQL analysts, as it drastically reduces network traffic and query latency in large-scale data processing.

Answer

Shuffle Hash Join

Answer

Sort Merge Join

Answer

Cartesian Product Join

Question 2

An analyst is designing a Delta table in Databricks SQL that will store customer transactions. The table must enforce that the 'transaction_amount' is always positive and that 'customer_id' is not null. The analyst wants to ensure that any future inserts or updates that violate these rules are rejected. Which approach should the analyst use?

Accepted Answer

Define a CHECK constraint on the table for 'transaction_amount > 0' and a NOT NULL constraint on 'customer_id'.. Delta Lake's CHECK and NOT NULL constraints are enforced at write time, ensuring that any data violating the rules is rejected. A CHECK constraint on 'transaction_amount > 0' and a NOT NULL constraint on 'customer_id' will prevent invalid inserts or updates, maintaining data integrity directly in the table definition.

Answer

Use a MERGE statement with a condition that only updates rows where 'transaction_amount' > 0 and 'customer_id' IS NOT NULL.

Answer

Add a column mask that returns NULL for 'transaction_amount' when it is not positive.

Answer

Create a view on top of the table that filters out rows where 'transaction_amount' is not positive or 'customer_id' is null.

Question 3

Refer to the exhibit. An analyst is attempting to read a CSV file using Spark. Why is the code failing?

Accepted Answer

The file path provided does not exist in the mounted location.. This error occurs because the specified path in DBFS does not resolve to an actual file. This often happens if the mount point was not created correctly or if the file name was mistyped. Debugging file paths is a fundamental skill for analysts, as it involves verifying storage connectivity and ensuring the file system abstraction layer correctly points to the underlying cloud storage container where the data resides.

Answer

The cluster is running on a single-node configuration.

Answer

Spark does not support reading CSV files directly from DBFS.

Answer

The user lacks sufficient memory to read the file.

Question 4

An analyst wants to include a chart from a Databricks SQL query in a presentation and also allow stakeholders to explore the same query interactively later. Which two-part approach best fits this need?

Accepted Answer

Download the chart as an image for the presentation, and share the saved query or dashboard for interactive exploration. The scenario needs both a static visual for slides and a live, explorable view for stakeholders. Exporting the chart as an image handles the presentation, and sharing the saved query or dashboard provides governed interactivity. Copying code, distributing notebooks, or screenshotting the editor fails to produce a proper chart and does not support shared exploration.

Answer

Create a separate notebook for each stakeholder and email the notebook files

Answer

Take a screenshot of the SQL editor and paste it into the slides

Answer

Copy the query text into the presentation slides and ask stakeholders to run it themselves

Question 5

An analyst wants to visualize trends over time using a Databricks SQL dashboard. Which feature should they use to allow end-users to dynamically filter the dashboard data without modifying the underlying SQL queries?

Accepted Answer

Dashboard Parameters.. Dashboard parameters are the standard mechanism for creating interactive reports in Databricks SQL. By defining parameters in the query definition, analysts can expose these as input fields on the dashboard interface. This enables end-users to change filter criteria—such as date ranges or categories—dynamically. This approach democratizes data access and reduces the maintenance burden on data analysts, as a single dashboard can now serve multiple user requirements effectively.

Answer

Hard-coding filters in the WHERE clause.

Answer

Creating separate queries for every filter combination.

Answer

Using the LIMIT clause in the query.

Question 6

You are configuring a Databricks SQL dashboard that includes a filter widget based on a query parameter. Which TWO statements correctly describe how parameters and filter widgets behave in this dashboard? (Choose two.)

Accepted Answer

A dashboard filter widget can be applied to multiple visualizations at once, so changing its value updates all connected queries that reference the same parameter.. Parameters written with the double-curly-brace syntax become dashboard filter widgets, and a parameter shared by name across queries lets one widget control multiple visualizations. The remaining statements are wrong because parameters are resolved at run time, filter widgets can drive many charts, and parameters support text and date types as well as numbers.

Answer

Parameter values are permanently baked into the saved query definition, so changing a filter widget does not alter the SQL that is sent to the warehouse.

Answer

A filter widget can only be bound to a single visualization, and adding the same parameter to a second chart creates an independent, unlinked control.

Answer

Parameters can only accept numeric values, so a filter widget cannot be used to filter a dashboard by a text field such as region or product category.

Question 7

A data analyst needs to grant a colleague the ability to run an existing Databricks SQL query and view its results, but the colleague must not be able to edit the query text or change its schedule. The query is saved in the workspace. Which permission level should the analyst assign on the query object?

Accepted Answer

CAN RUN. CAN RUN is the least-privilege permission that lets a user execute a saved query and view results while preventing edits to the query text or schedule. CAN VIEW does not grant run capability, while CAN EDIT and CAN MANAGE both allow modifications that the scenario explicitly forbids.

Answer

CAN EDIT

Answer

CAN VIEW

Answer

CAN MANAGE

Question 8

A data analyst is setting up a new Genie space. Which TWO of the following are prerequisites for the successful creation and usage of a Genie space?

Accepted Answer

A Unity Catalog-enabled workspace. Genie spaces require a foundational environment where data is discoverable and executable. Unity Catalog provides the necessary governance and metadata, while a SQL Warehouse provides the compute resources to execute the generated code. Without these, the Genie space lacks both the data access layer and the processing capability required to answer user questions, making these two components absolute necessities for the feature to function correctly.

Answer

A pre-trained custom Large Language Model

Answer

A local Python environment with Genie SDK

Answer

A Delta Live Table pipeline with high availability

Question 9

An analyst is preparing a report and needs to ensure that the dashboard data is always current. What is the most efficient way to achieve this?

Accepted Answer

Set a refresh schedule on the dashboard.. Setting a regular refresh schedule is the correct way to ensure data remains fresh. This automation removes the manual effort of refreshing queries, ensuring that the dashboard always represents the latest state of the business. It is a fundamental operational task that guarantees the dashboard's reliability and relevance, preventing stakeholders from making decisions based on stale or outdated information, which is a major risk in any data-driven environment.

Answer

Manually refresh the dashboard every morning.

Answer

Re-run the SQL queries in a notebook and export the results.

Answer

Ask the SQL warehouse to stay running indefinitely.

Question 10

A data analyst runs a Databricks SQL query that joins a 50 GB fact_sales Delta table with a 200 MB dim_product Delta table. The query takes 15 minutes. The analyst runs EXPLAIN FORMATTED and sees a SortMergeJoin instead of a BroadcastHashJoin. The analyst has already confirmed that the small table is not being broadcast. Which action should the analyst take to improve performance?

Accepted Answer

Increase spark.sql.autoBroadcastJoinThreshold to a value larger than 200 MB, such as 250 MB, so the small table can be broadcast.. Increasing the auto broadcast join threshold above the size of the small table enables Spark to broadcast it, converting the SortMergeJoin to a BroadcastHashJoin. This avoids shuffling the large fact table, which is the main cost. The default threshold is 10 MB, so a 200 MB table is not broadcast unless the threshold is raised.

Answer

Add a broadcast hint to the large fact table so it is replicated to all executors.

Answer

Set spark.sql.autoBroadcastJoinThreshold to -1 to disable broadcasting and force a shuffle hash join.

Answer

Repartition both tables on the join key before the join to ensure co-located partitions.

Question 11

A data analyst writes a query in the Databricks SQL editor that returns an error stating the column cannot be resolved, even though the analyst is certain the column exists in the Delta table. The analyst wants to quickly confirm the actual column names and data types of the table before editing the query. Which approach is most efficient?

Accepted Answer

Run DESCRIBE TABLE on the table in the SQL editor. DESCRIBE TABLE reads table metadata from the metastore and returns column names, data types, and comments without scanning data. That makes it the fastest, cheapest way to confirm exact spelling and casing so the analyst can fix the unresolved column reference in the query.

Answer

Open the table's Delta transaction log JSON files and read the schema

Answer

Query the table with SELECT * and inspect the result grid columns

Answer

Recreate the table with a CREATE OR REPLACE statement matching the intended schema

Question 12

When analyzing query performance, what does a high 'Spill to Disk' metric indicate?

Accepted Answer

The cluster has insufficient memory for the operation.. Spilling to disk occurs when a query's operations, such as sorts or joins, exceed the available memory in the cluster's executors. This is a major performance bottleneck because disk I/O is exponentially slower than memory. Recognizing this indicator is crucial for analysts because it signals that the current cluster size or query logic needs adjustment to fit the workload comfortably within RAM, significantly improving query speed and stability.

Answer

The query is successfully using the cache.

Answer

The data is highly compressed.

Answer

The query is finished and writing results to the table.

Question 13

Why should an analyst use the 'feedback' feature provided in an AI/BI Genie space interface?

Accepted Answer

To improve the accuracy of future answers. The feedback mechanism is crucial for the iterative improvement of the Genie space. When users rate answers as 'helpful' or 'not helpful,' they provide signal that Databricks uses to refine the model's performance over time. This continuous learning process ensures that the assistant becomes more accurate, more relevant, and better aligned with the specific business context, ultimately providing higher value to the entire user organization.

Answer

To log a support ticket with Databricks Engineering

Answer

To increase the SQL Warehouse query limit

Answer

To export the answer as a CSV file

Question 14

Refer to the exhibit. An analyst examines the physical execution plan of a Spark SQL join query. Based on the provided plan output, which optimization strategy was automatically applied by Adaptive Query Execution (AQE)?

Accepted Answer

AQE dynamically converted a sort-merge join into a broadcast hash join based on runtime size statistics.. Adaptive Query Execution in Databricks dynamically optimizes query plans at runtime based on precise statistics gathered from completed shuffle stages. The exhibit clearly displays a BroadcastHashJoin where the right side of the join was converted from a sort-merge join into a broadcast join using a broadcast exchange stage.

Answer

Dynamic partition pruning was used to eliminate scanning partitions on the orders table.

Answer

Skew join optimization was triggered to split the skewed partition keys into multiple sub-tasks.

Answer

The Catalyst optimizer pushed down aggregations past the join boundary to reduce intermediate data size.

Question 15

A data analyst is investigating why a specific Spark SQL query takes an exceptionally long time to complete and exhibits signs of severe data skew. Which TWO metrics or behaviors in the Databricks Spark UI typically indicate that data skew is impacting the query? (Choose two)

Accepted Answer

The maximum task duration is drastically higher than the 75th and median task durations for a given stage.. Data skew occurs when records are unevenly distributed across partitions, causing a few tasks to process the vast majority of data while others finish quickly. In the Spark UI, this manifests as a massive disparity between task duration percentiles and extremely high memory consumption or spill metrics on the tasks handling the skewed keys.

Answer

The total number of input files scanned is zero because all data is successfully pruned by partition filters.

Answer

The driver node runs out of memory during the initial parsing phase before any execution stages are generated.

Answer

All tasks across all worker nodes complete in nearly identical timeframes with minimal variance.