Databricks-DA-Assoc Practice Test 19 — 15 Questions

Question 1

A business analyst is using an AI/BI Genie Space to explore customer data. They ask, 'What is the average customer age?' The Genie Space returns a result. Which underlying Databricks component does Genie use to execute the generated SQL query?

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

Accepted Answer

Databricks SQL warehouse. AI/BI Genie Spaces rely on Databricks SQL warehouses to execute the SQL queries generated from natural language. The SQL warehouse provides the necessary compute and is optimized for BI workloads, ensuring fast and reliable results. Other components like Spark clusters or notebooks are not used for this purpose.

Answer

Apache Spark cluster

Answer

Delta Live Tables pipeline

Answer

Databricks notebook

Question 2

A data analyst is reviewing audit logs in Databricks to investigate unauthorized access attempts to a sensitive table. Which audit log event type should the analyst filter on to find failed attempts to query the table?

Accepted Answer

`permissionDenied`. To investigate unauthorized access attempts, the analyst should filter audit logs for `permissionDenied` events. These events are generated when a user lacks the required privileges for an action, such as querying a table. Other event types like `tableAccess` or `sqlQuery` focus on successful operations or query details, not permission failures. `login` events are about authentication, not table access.

Answer

`login`

Answer

`tableAccess`

Answer

`sqlQuery`

Question 3

A data analyst needs to perform a one-time upload of a 15 MB compressed Parquet file from a local laptop into a Unity Catalog volume in a Databricks workspace. The analyst has the Databricks SQL editor open and no cluster running. Which approach should the analyst take to place the file into the volume path?

Accepted Answer

Navigate to the volume in Catalog Explorer, use the Upload to volume action, and select the local Parquet file to transfer it into the volume path.. Unity Catalog volumes are designed to hold non-tabular files, and Catalog Explorer provides an Upload to volume action that pushes a local file directly into the chosen volume directory. This avoids spinning up a cluster and does not require the SQL warehouse, making it the appropriate tool for a small, one-time upload from a laptop into a governed storage location.

Answer

Run COPY INTO from the SQL editor, referencing the local file path as the source and the volume path as the target.

Answer

Use dbfs cp from a terminal with the Databricks CLI to copy the file into /Volumes/catalog/schema/volume/path.

Answer

Use the Catalog Explorer's Create table UI to upload the file, selecting the target volume as the destination and the Parquet format.

Question 4

Refer to the exhibit. A data scientist can query both tables, but the join fails with a permission error. What is the most likely reason?

Accepted Answer

The user lacks USAGE on the 'default' schema.. In Unity Catalog, the user must have the USAGE privilege on every schema involved in a query, even if they have SELECT access on the tables within those schemas. If the 'default' schema lacks the USAGE grant for the group, the query engine cannot validate the path to the data, causing the join to fail at the permission check level despite table-level SELECT access.

Answer

The user lacks SELECT access on the 'prod' catalog.

Answer

The join condition requires elevated privileges.

Answer

The tables are in different catalogs.

Question 5

What is the primary function of the 'Databricks Assistant' within the notebook environment?

Accepted Answer

To assist in writing, debugging, and explaining notebook code.. The Databricks Assistant is an AI-powered coding companion designed to help users write, debug, and optimize code faster. By leveraging context-aware suggestions, it helps analysts become more efficient. Understanding how to use the Assistant is beneficial for streamlining development workflows, as it assists with complex syntax, troubleshooting common errors, and generating boilerplate code, allowing the analyst to focus on the core logic of their data analysis tasks.

Answer

To automatically execute all notebooks in a production pipeline.

Answer

To manage user permissions across the entire workspace.

Answer

To store and back up notebook versions automatically.

Question 6

Which TWO of the following are valid ways to improve the performance of a slow-loading Databricks SQL dashboard? (Choose two)

Accepted Answer

Enable query result caching on the SQL warehouse.. Improving dashboard performance is critical for user adoption. By utilizing query result caching and optimizing the underlying SQL queries, analysts can significantly reduce latency. Caching stores the output of frequent queries, while query optimization ensures that the compute resources are used efficiently. These techniques are standard operational practices that ensure dashboard responsiveness, even when dealing with massive datasets common in modern data lakehouses.

Answer

Convert all visualizations into a single complex query.

Answer

Add more users to the dashboard to increase processing power.

Answer

Switch the dashboard to manual refresh mode only.

Question 7

A data analyst needs to perform ad-hoc SQL queries on a large dataset while ensuring the compute resources automatically terminate when idle to minimize costs. Which compute resource is most appropriate for this task?

Accepted Answer

SQL Warehouse. SQL Warehouses are designed specifically for SQL-based workloads, supporting BI tools and ad-hoc analysis through the SQL editor. They feature auto-stop functionality, which shuts down the cluster after a specified period of inactivity, directly addressing the cost-optimization requirement. Understanding the distinction between SQL Warehouses and All-Purpose clusters is vital for analysts, as Warehouses provide optimized performance for SQL queries and efficient resource management for collaborative reporting environments.

Answer

Job Compute

Answer

All-Purpose Compute

Answer

Delta Live Tables Pipeline

Question 8

When analyzing the execution plan of a query in Databricks SQL, what does a 'BroadcastHashJoin' node typically indicate?

Accepted Answer

The optimizer successfully avoided a network shuffle by sending a small table to all executors.. A BroadcastHashJoin indicates that the query optimizer determined one table was small enough to be duplicated across all nodes. This avoids expensive shuffles by performing the join locally on each partition. Understanding execution plans allows analysts to verify if their queries are executing as intended and helps identify opportunities to apply hints or optimize join operations for better performance on large-scale distributed data processing tasks.

Answer

The query is performing a cross join between two large tables.

Answer

The query is failing because the join keys are not indexed.

Answer

The data is being read from multiple external storage locations simultaneously.

Question 9

Which of the following is the primary purpose of examining the 'Query Profile' in Databricks SQL?

Accepted Answer

To visualize the execution steps and identify bottlenecks.. The Query Profile is the central diagnostic tool for Databricks SQL. It provides a visual representation of how a query was executed, including the time spent in each stage and where bottlenecks occur. Mastering this tool is vital for data analysts because it transforms abstract performance issues into actionable insights, enabling them to reduce query latency, optimize costs, and effectively communicate performance bottlenecks to data engineers for backend architecture improvements.

Answer

To change the permissions of the underlying table.

Answer

To automatically rewrite SQL for better performance.

Answer

To view the raw logs of the cluster driver.

Question 10

A data analyst has a Delta table silver.customers in Unity Catalog. A new privacy requirement states that analysts must never see the raw email column, but they still need to query all other columns. The analyst has CREATE VIEW and SELECT privileges on the schema. Which approach best enforces the requirement while keeping the table queryable?

Accepted Answer

Create a view that selects all columns except email, grant SELECT on the view to the analysts, and revoke SELECT on the base table.. Projecting only the safe columns into a view and revoking direct SELECT on the base table is the canonical least-privilege pattern in Unity Catalog. Analysts query the view, which returns every column except email, while the raw table remains inaccessible. Column masks or row filters do not remove the column from query results in the same unambiguous way.

Answer

Add a row filter to the table so rows containing emails are excluded for analysts, and keep base-table SELECT granted.

Answer

Rename the email column to a non-obvious name and rely on security by obscurity to hide it from analysts.

Answer

Apply a column mask on the email column using a masking function, and leave SELECT on the base table in place.

Question 11

Which TWO of the following are best practices for writing instructions in an AI/BI Genie space?

Accepted Answer

Provide clear, concise business logic definitions. Effective Genie instructions are concise, specific, and structured. They should prioritize clarity to prevent ambiguity and ensure the model consistently follows business rules. By focusing on explicit examples and avoiding irrelevant information, developers help the LLM maintain high accuracy and reduce the risk of hallucination, which is vital for building trust with business users who rely on the provided insights.

Answer

Include historical SQL queries for every possible outcome

Answer

Include sensitive security credentials for authentication

Answer

Use long-form essay style descriptions for all tables

Question 12

Which audit log category is most useful for identifying unauthorized attempts to access sensitive tables?

Accepted Answer

Unity Catalog audit logs.. The 'Unity Catalog' audit log category records all actions performed within the metastore, including access attempts. By monitoring this log, security analysts can identify unauthorized 'SELECT' or 'DESCRIBE' attempts that failed due to permission denials. This is critical for detecting potential internal threats or misconfigured access policies that could lead to unauthorized data exposure.

Answer

Cluster-level Spark logs.

Answer

Job run output logs.

Answer

Workspace-level login logs.

Question 13

Who is the primary intended audience for an AI/BI Genie space in a Databricks environment?

Accepted Answer

Business analysts and non-technical stakeholders. Genie spaces are specifically designed to bridge the gap between technical data infrastructure and business users. By providing a natural language interface, they allow non-technical stakeholders to ask questions and explore data without needing to write SQL or understand complex data schemas. This democratizes access to data and reduces dependency on data teams for basic reporting.

Answer

Data engineers building ETL pipelines

Answer

Machine Learning engineers for model training

Answer

System administrators for managing clusters

Question 14

A Genie Space is producing inaccurate results when asked about 'Customer Lifetime Value'. The analyst realizes the AI model is misinterpreting the calculation logic. What is the most effective way to improve the AI's accuracy for this specific business term?

Accepted Answer

Update the Genie Space instructions with clear, natural language definitions for the calculation.. Providing clear instructions in the Genie Space instructions field allows developers to define business-specific logic that the AI might not inherently understand. By embedding these definitions directly into the Genie configuration, you constrain the AI's query generation process to adhere to pre-approved business formulas. This is essential for ensuring that multiple users get consistent answers for complex metrics, regardless of how they phrase their natural language questions.

Answer

Modify the underlying Delta table data to include a pre-computed column for every possible metric.

Answer

Restrict access to the Genie Space so only the senior data architect can run queries.

Answer

Rename all columns in the Unity Catalog table to match the exact wording of the user's questions.

Question 15

When designing an AI/BI Genie Space, how can an analyst prevent the model from accessing PII (Personally Identifiable Information) columns in a sensitive table?

Accepted Answer

Create a filtered view in Unity Catalog that excludes sensitive columns.. The best approach to prevent PII exposure is to create a secure view in Unity Catalog that excludes the PII columns and point the Genie Space to that view instead of the raw table. This effectively enforces data masking and access control at the data layer. By only exposing the non-sensitive data required for analysis, you maintain privacy compliance while still allowing the AI to generate useful insights for the business.

Answer

Add a disclaimer in the system instructions to 'not use PII'.

Answer

Use a specialized, private LLM that has no knowledge of PII.

Answer

Set the temperature to 0 to minimize hallucinations involving PII.