Databricks-DA-Assoc Practice Test 15 — 15 Questions

Question 1

A data analyst is preparing an AI/BI Genie Space for a sales team. The team often asks questions like 'Show me the top 10 customers by revenue' and 'What were the total sales last quarter?' The analyst wants to ensure that Genie understands that the 'revenue' column in the sales table is calculated as 'quantity * unit_price' and that 'last quarter' refers to the most recent completed fiscal quarter. Which feature should the analyst use to provide this business context?

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

Accepted Answer

Configure the Genie Space with instructions and sample queries. Genie Spaces are configured with natural language instructions and sample queries to teach the model business-specific terminology and calculations. This allows Genie to correctly interpret terms like 'revenue' as a formula and 'last quarter' as a relative time period, enabling accurate responses to ad-hoc questions.

Answer

Set up a scheduled job to refresh the data and update statistics

Answer

Add table and column descriptions in the Unity Catalog

Answer

Create a view in Databricks SQL that pre-calculates revenue and filters for last quarter

Question 2

A data analyst is investigating a query that uses a window function with PARTITION BY and ORDER BY, and the query is slow. The analyst suspects that data skew is causing some partitions to be much larger than others. Which approach should the analyst take to diagnose the skew in the Query Profile?

Accepted Answer

Check the 'Shuffle Read Size' per task to see if some tasks read significantly more data than others.. Data skew in window functions is often revealed by uneven shuffle read sizes across tasks. The Query Profile provides task-level metrics, and a significant disparity in Shuffle Read Size indicates that some partitions are much larger, causing skew. This allows the analyst to confirm the skew and then apply mitigation techniques like salting or repartitioning.

Answer

Look at the 'Peak Execution Memory' to identify tasks using more memory.

Answer

Review the 'Number of Output Rows' for each task to see if some produce more rows.

Answer

Examine the 'Spill (Disk) Size' to see if any tasks spilled to disk.

Question 3

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 4

A data analyst is configuring an AI/BI Genie Space for a customer support dataset. The analyst notices that when users ask "how many open tickets per agent," Genie sometimes counts all tickets instead of only open ones. The analyst wants to fix this. What should the analyst do?

Accepted Answer

Add an instruction that defines "open tickets" as tickets where status = 'Open'.. The most direct fix is to add an instruction defining "open tickets" as tickets where status = 'Open'. This teaches Genie the business term and ensures it applies the correct filter when generating SQL, without restricting the data for other questions.

Answer

Add a comment to the status column listing all possible values.

Answer

Create a view that filters tickets to only those with status = 'Open' and add it to the Genie Space.

Answer

Rename the status column to ticket_status.

Question 5

An analyst is creating a Databricks SQL dashboard and wants to give viewers a single numeric value that stands out, such as total revenue for the current quarter. Which visualization type should the analyst use?

Accepted Answer

Counter. A single aggregated KPI is best represented by a counter, which renders one number with strong visual emphasis. Tables show detail, heatmaps show patterns across two dimensions, and box plots show distribution, so none of them present a lone value as clearly as a counter does.

Answer

Heatmap

Answer

Box plot

Answer

Table

Question 6

A data analyst has created a Databricks SQL dashboard with a visualization that uses a query with a parameter. The analyst wants to share the dashboard with a colleague who should be able to view the dashboard but not modify the queries or the dashboard itself. The colleague also needs to be able to change the parameter values to explore the data. What should the analyst do?

Accepted Answer

Grant the colleague CAN VIEW permission on the dashboard and ensure the parameter is not set to a fixed value.. To allow a colleague to view the dashboard and change parameter values without modifying it, the analyst should grant CAN VIEW permission. This permission level permits viewing and interacting with parameters but restricts editing. Ensuring the parameter is not fixed allows the colleague to adjust it as needed.

Answer

Grant the colleague CAN EDIT permission on the dashboard.

Answer

Share the underlying query with the colleague and ask them to run it with different parameter values.

Answer

Grant the colleague CAN MANAGE permission on the dashboard.

Question 7

An analyst is configuring an AI/BI Genie Space and wants to ensure that the model correctly interprets the term 'revenue' as the sum of the 'sales_amount' column. Which action should the analyst take?

Accepted Answer

Add a synonym 'revenue' to the 'sales_amount' column in the Genie Space's semantic configuration.. The correct approach is to add a synonym in the Genie Space's semantic configuration. This feature allows analysts to map business terms to specific columns, ensuring the model understands that 'revenue' refers to 'sales_amount'. Synonyms are part of the Space's semantic layer and are used during natural language to SQL translation, leading to more accurate and consistent results.

Answer

Create a view that renames 'sales_amount' to 'revenue' and add that view to the Genie Space.

Answer

Add a column comment to the 'sales_amount' column in Unity Catalog that says 'This is revenue'.

Answer

In the Genie Space instructions, write a note that says 'revenue means sales_amount'.

Question 8

An analyst has a Databricks SQL dashboard with a table visualization showing the top 20 customers by lifetime value. The stakeholder wants the same numbers represented as proportional horizontal bars so differences between customers are easier to compare visually. Which visualization type should the analyst choose?

Accepted Answer

Bar chart. A bar chart is the standard choice for comparing a numeric measure across discrete categories. Each customer gets a bar whose length equals lifetime value, so ranking and relative differences are immediately visible, and a horizontal orientation keeps long customer names legible. Databricks SQL's visualization editor supports horizontal bar charts and sorting, making it straightforward to present the top 20 customers in descending order.

Answer

Line chart

Answer

Pie chart

Answer

Scatter plot

Question 9

A data analyst is reviewing audit logs in Databricks to investigate who accessed a sensitive table 'main.finance.payroll'. The analyst needs to identify the user, the timestamp, and the action performed. Which audit log service should the analyst query to find this information?

Accepted Answer

The 'unityCatalog' audit log service. Unity Catalog audit logs capture data access events, including user, timestamp, and action, for tables governed by Unity Catalog. The 'unityCatalog' service is the correct source for investigating access to 'main.finance.payroll'.

Answer

The 'jobs' audit log service

Answer

The 'notebook' audit log service

Answer

The 'clusters' audit log service

Question 10

A data analyst is reviewing the Query Profile in Databricks SQL for a query that spills data to disk during a sort operation. Which two metrics should the analyst examine to confirm and diagnose the spill? (Choose two.)

Accepted Answer

Spill (Disk) Size. Spill (Disk) Size directly quantifies data written to disk due to memory overflow, confirming spill. Peak Execution Memory indicates memory pressure that causes spill. Together, they diagnose spill during sort. Other metrics like shuffle read size or output rows do not directly measure spill and are less relevant for this specific issue.

Answer

Number of Output Rows

Answer

Total Time Spent in Shuffle

Answer

Shuffle Read Size

Question 11

A data analyst is configuring an AI/BI Genie Space for a marketing team. The team frequently asks questions that require joining multiple tables, such as 'Which campaigns generated the most leads?' and 'What is the conversion rate by channel?' The analyst notices that Genie sometimes produces incorrect joins, leading to inflated numbers. Which TWO actions should the analyst take to improve Genie's join accuracy? (Choose two.)

Accepted Answer

Define foreign key relationships in the Unity Catalog. To improve Genie's join accuracy, the analyst should define foreign key relationships in Unity Catalog and provide sample queries with correct joins. These actions give Genie the necessary metadata and examples to understand table relationships and generate accurate SQL, reducing errors like Cartesian products or mismatched keys.

Answer

Enable caching for the tables used in the Genie Space

Answer

Increase the compute cluster size for the Genie Space

Answer

Restrict the Genie Space to a single table to avoid joins

Question 12

A Databricks SQL dashboard uses a counter visualization to display total revenue for the current month. The analyst notices that the counter shows a value even when the underlying query returns zero rows for the selected filter. Which behavior explains this and what should the analyst do to make the counter reflect the empty result set correctly?

Accepted Answer

The counter is configured to display the last non-null value or a default when the result set is empty; the analyst should adjust the counter's empty-state or default-value setting.. When a counter's dataset returns no rows, the aggregate value is NULL, and Databricks SQL counters can be configured with a default or fallback display that produces the misleading number. The correct remedy is to review the counter's visualization settings, including any default value or empty-state option, and align them with the intended behavior so an empty result is shown honestly rather than replaced by a placeholder.

Answer

The counter is using a COUNT(*) aggregation, which returns 0 for empty sets; the analyst should switch to SUM(revenue).

Answer

The dashboard cache is serving a stale result; the analyst should disable caching for the warehouse.

Answer

The counter is aggregating a column that contains NULLs; the analyst should wrap the measure in COALESCE to force a zero.

Question 13

A data analyst needs to query a table that contains sensitive customer financial data. Company policy requires that analysts see only masked values for account numbers in query results, and the masking must apply regardless of which tool or user queries the table. Which Databricks capability should be used to enforce this consistently at the data layer?

Accepted Answer

Column masks applied to the table. Column masks attach a masking expression directly to a table column, so the transformation is evaluated at query time for every read, independent of the client or user. This data-layer enforcement is exactly what the policy demands: unauthorized analysts receive masked account numbers whether they query via a notebook, the SQL editor, or a BI tool, and authorized users can still see real values.

Answer

A view that filters rows before returning results

Answer

Workspace-level notebook permissions

Answer

Cluster access mode configuration

Question 14

A data analyst has a notebook that reads a Delta table and produces summary statistics. The analyst wants colleagues to see the latest results in a dashboard without rerunning the notebook manually each time. Which Databricks capability should the analyst use to keep the dashboard current?

Accepted Answer

Convert the notebook into a Databricks SQL dashboard and schedule the underlying query. Databricks SQL dashboards are built on saved queries that can be scheduled to refresh, so viewers see current results without anyone rerunning a notebook. Recreating the summary logic as a query and scheduling it automates currency. Documentation, larger clusters, and static exports do not refresh a dashboard, so they fail to meet the requirement of up-to-date shared results.

Answer

Add a markdown cell describing how to run the notebook

Answer

Increase the cluster size used by the notebook

Answer

Export the notebook results to a static PDF for distribution

Question 15

A data analyst is creating a view in Databricks SQL that joins a fact table with several dimension tables. The analyst wants to ensure that the view always returns the latest data and that any changes to the underlying tables are immediately reflected. Which type of view should the analyst create?

Accepted Answer

A standard view. A standard view is a saved query that executes on demand, so it always reflects the current state of the underlying tables. Materialized views store data and require refreshes, temporary views are session-bound, and streaming views are for continuous processing. Thus, a standard view best meets the need for immediate data visibility and persistence.

Answer

A temporary view

Answer

A streaming view

Answer

A materialized view