Databricks-DA-Assoc Practice Test 17 — 15 Questions

Question 1

A data analyst at a retail company needs a serverless, fully managed SQL environment in Databricks to run BI queries against a gold Delta table. The team has no interest in managing clusters, and the queries must start instantly without a warm-up period. Which Databricks SQL warehouse type should the analyst select?

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

Accepted Answer

Serverless SQL warehouse. The requirement is a fully managed SQL endpoint that starts immediately and requires no cluster sizing, which is exactly what a serverless SQL warehouse provides. It runs on Databricks-managed compute, scales automatically for BI workloads, and eliminates warm-up and capacity planning. Classic and Pro warehouses and all-purpose clusters all involve customer-managed compute.

Answer

All-purpose cluster with the SQL dialect enabled

Answer

Pro SQL warehouse with Photon enabled

Answer

Classic SQL warehouse with a fixed cluster size

Question 2

A data analyst at a retail company has been asked to build interactive sales reports that refresh automatically each morning and can be shared with regional managers. The analyst wants to use Databricks SQL to create queries, schedule them, and publish visualizations without writing notebook code. Which Databricks component should the analyst use to accomplish this?

Accepted Answer

Databricks SQL. Databricks SQL is purpose-built for SQL analysts: it includes a query editor, visualization tools, dashboards, alerts, and scheduled refreshes running on SQL warehouses. Because the analyst needs to author queries, schedule them, and publish shareable visualizations without notebook code, this is the only listed component that covers the full workflow end to end.

Answer

Databricks Jobs

Answer

Databricks Marketplace

Answer

Databricks Repos

Question 3

A data analyst needs to grant a team member the ability to view the metadata of a table `main.finance.transactions` in Unity Catalog, but not query the data. Which privilege should the analyst grant?

Accepted Answer

`BROWSE`. The `BROWSE` privilege in Unity Catalog is specifically designed to allow users to discover and view metadata of objects without accessing the data. Granting `BROWSE` on the table or its parent objects satisfies the requirement. Other privileges like `SELECT`, `USAGE`, or `MODIFY` either grant data access or are insufficient for metadata visibility.

Answer

`MODIFY`

Answer

`SELECT`

Answer

`USAGE`

Question 4

A data analyst is new to a Databricks workspace and needs to understand which compute options are available for running SQL and notebooks. Which TWO of the following statements accurately describe Databricks compute in this context? (Choose two.)

Accepted Answer

SQL warehouses are optimized for running SQL and serving dashboards and scheduled queries.. SQL warehouses are purpose-built for SQL, dashboards, and scheduled queries, while job clusters are ephemeral compute created for a specific run and terminated afterward. Together these describe two real compute behaviors an analyst should know. The other statements misstate requirements or capabilities, such as claiming all-purpose clusters are needed for the SQL editor or that serverless cannot use Unity Catalog.

Answer

All-purpose clusters are required to run any query in the Databricks SQL editor.

Answer

SQL warehouses and all-purpose clusters share the same configuration settings and scaling behavior.

Answer

Serverless compute for notebooks cannot be used with Unity Catalog.

Question 5

A data analyst at a multinational bank needs to ensure that queries against a Unity Catalog table 'main.risk.transactions' automatically hide the 'customer_ssn' column for users in the 'contractors' group, while all other users see the full data. The analyst wants to implement this without creating separate views for each user group. Which Unity Catalog feature should the analyst use?

Accepted Answer

Column mask with a masking function. Column masks in Unity Catalog allow you to apply a masking function to a column that evaluates the user's identity and returns a masked value for specific groups. This directly satisfies the requirement to hide 'customer_ssn' for contractors while showing it to others.

Answer

Row-level security with a filter function

Answer

Attribute-based access control (ABAC) with tags

Answer

Dynamic view with a CASE statement

Question 6

A data analyst is using Databricks SQL to query a table `main.sales.orders` that has a column `credit_card` containing sensitive data. The analyst needs to run a query that aggregates orders by region but must not see the actual credit card numbers. Which Unity Catalog feature should be used to mask the `credit_card` column for this analyst?

Accepted Answer

Column-level dynamic data masking using a user-defined function. Column-level dynamic data masking in Unity Catalog allows administrators to attach a masking function to a column. The function can return a masked value (e.g., '****') for unauthorized users while returning the actual value for authorized users. This enables the analyst to run aggregations without seeing sensitive data. Other options either do not address column masking or are not supported features.

Answer

Granting `SELECT` only on specific columns

Answer

Using `REDACT` function in the query

Answer

Row-level security using a dynamic view

Question 7

A data analyst wants to share a Unity Catalog table with an external partner using Delta Sharing. The partner uses a non-Databricks client that supports the Delta Sharing protocol. Which Unity Catalog object must the analyst create to enable the partner to access the shared data?

Accepted Answer

A share. To share a table with an external partner, the analyst must create a share in Unity Catalog, add the table to the share, and then grant the recipient access to the share. The recipient can then use the Delta Sharing protocol to read the data. A share is the container for shared data, while a recipient identifies the external party.

Answer

A recipient

Answer

A provider

Answer

A storage credential

Question 8

An analyst runs a notebook against an all-purpose cluster and notices that the first cell, which reads a large Delta table, takes several minutes while subsequent similar queries finish quickly. The analyst wants to understand why this happens and ensure the same quick response on later runs. Which explanation best describes the underlying behavior?

Accepted Answer

The cluster had to start and initialize, and Delta caching kept data in memory for later queries. The slow first cell reflects two overlapping costs: the all-purpose cluster must start and initialize before any query runs, and the initial scan reads Delta files from remote storage. Once that scan completes, Databricks caches the data on local SSD, so later queries that touch the same files avoid remote reads. Warm compute plus caching explains the dramatic speedup on subsequent runs.

Answer

The first query compiled a query plan that Databricks permanently stored in the table metadata

Answer

The first query used a different execution engine, and later queries switched to Photon

Answer

The Delta table was automatically optimized after the first read, rewriting all files

Question 9

A data analyst is preparing to publish a Databricks SQL dashboard for a team of business users. The analyst wants the dashboard to load quickly and to remain usable as the underlying Delta table grows. Which two practices should the analyst follow? (Choose two.)

Accepted Answer

Create the dashboard datasets against a SQL Warehouse that is sized appropriately and has auto-stop configured. Fast, scalable dashboards rely on appropriately sized SQL Warehouse compute with auto-stop and on reducing the data scanned through pre-aggregation such as summary tables or materialized views. Storing datasets as CSV, disabling auto-stop, and granting broad manage permissions do not improve performance and introduce cost or governance problems.

Answer

Store the dashboard datasets as CSV files in a Unity Catalog volume for faster reads

Answer

Disable the SQL Warehouse auto-stop so the dashboard always has warm compute

Answer

Grant every viewer CAN MANAGE on the dashboard so they can tune queries themselves

Question 10

A data analyst is querying a Unity Catalog managed table named sales in the analytics catalog and the marketing schema. A query fails with an error indicating the table cannot be found, even though the analyst can see the table in Catalog Explorer. The analyst is connected to a SQL Warehouse in the same workspace. Which action is most likely to resolve the access issue?

Accepted Answer

Verify that the analyst has USE CATALOG on analytics, USE SCHEMA on marketing, and SELECT on the sales table. Unity Catalog enforces a privilege hierarchy: USE CATALOG on the catalog, USE SCHEMA on the schema, and SELECT on the table. Visibility in Catalog Explorer does not imply these grants. Restarting the warehouse, converting the table to external, or enabling legacy cluster table access control do not address missing Unity Catalog privileges.

Answer

Restart the SQL Warehouse so it picks up the latest Unity Catalog metadata

Answer

Ask a workspace administrator to enable table access control on the all-purpose cluster

Answer

Convert the managed table to an external table so the analyst can read the underlying files

Question 11

A data analyst has a Databricks SQL dashboard that reads a table in the 'finance' catalog. The dashboard works for the analyst but shows an error for a colleague who has SELECT on the table. The colleague lacks USE CATALOG on 'finance' and USE SCHEMA on the containing schema. What is the most likely cause of the error the colleague sees?

Accepted Answer

The colleague cannot traverse the catalog and schema, so Unity Catalog denies resolution of the table even though SELECT is granted. Unity Catalog resolves objects through their full three-part name, which requires USE CATALOG on the catalog and USE SCHEMA on the schema before SELECT on the table is even evaluated. The colleague holds SELECT but lacks the traversal privileges, so name resolution fails and the dashboard query errors until those grants are added.

Answer

The dashboard was created with the analyst's credentials embedded, so it always runs as the analyst

Answer

The colleague is connected to a different SQL warehouse that cannot reach the finance catalog

Answer

The table is a view that references tables the colleague cannot access

Question 12

Which feature in Databricks allows an analyst to automatically send a dashboard snapshot to a stakeholder's email on a recurring schedule?

Accepted Answer

Dashboard Subscriptions. The 'Subscription' feature in Databricks SQL is specifically designed for this purpose. It automates the distribution of dashboard results, which is a key requirement for keeping stakeholders informed without manual intervention. This feature ensures that critical business insights are delivered consistently, increasing the visibility of data-driven findings and ensuring that team members stay updated even if they do not log into the Databricks workspace daily.

Answer

Alerts

Answer

Query Snapshots

Answer

Data Refresh Jobs

Question 13

An analyst is building a dimensional model in Databricks SQL and needs to create a table that stores slowly changing dimension type 2 (SCD2) history for customers. The table must track valid_from and valid_to timestamps and a current flag. Which table type in Databricks SQL is best suited for this purpose?

Accepted Answer

A Delta table with appropriate columns and MERGE operations to maintain history. Delta tables with SCD2 columns and MERGE operations are the standard way to implement slowly changing dimensions type 2 in Databricks SQL. Delta Lake's ACID transactions and MERGE support allow you to update existing rows (expire old records) and insert new versions atomically. Other options like change data feed or temporary views do not provide the necessary persistent history structure.

Answer

A Delta table with change data feed enabled

Answer

A temporary view that joins the current customer table with a history table

Answer

A managed table with partitioning by customer_id

Question 14

An analyst is configuring an AI/BI Genie Space for a retail dataset. The analyst wants Genie to map the business term "top sellers" to a specific calculation that ranks products by total units sold. Where should the analyst define this mapping?

Accepted Answer

In the Genie Space's instructions, add a definition: "top sellers" means products ranked by total units sold.. Genie Space instructions are designed to capture business glossary definitions and calculation rules. Defining "top sellers" as products ranked by total units sold directly in the instructions gives Genie the semantic knowledge to generate correct SQL for that phrase, without altering the data model.

Answer

Create a Unity Catalog function named top_sellers and reference it in the Genie Space.

Answer

Add a comment to the units_sold column stating that it is used for top sellers.

Answer

Create a metric view that pre-calculates top sellers and add it to the Genie Space.

Question 15

A data analyst joined a new Databricks workspace and sees a catalog named 'sales_prod' containing schemas 'bronze', 'silver', and 'gold'. The analyst needs to query only the 'gold' schema tables and must not see or query the 'bronze' or 'silver' tables. Which Unity Catalog object should the workspace administrator grant to satisfy this least-privilege requirement?

Accepted Answer

USE CATALOG on sales_prod and USE SCHEMA plus SELECT on the gold schema. Unity Catalog enforces a privilege hierarchy where traversing a catalog requires USE CATALOG and reading a schema's tables requires USE SCHEMA and SELECT on that schema. Granting USE CATALOG on sales_prod and USE SCHEMA plus SELECT on gold gives read access to only the gold tables, while the absence of grants on bronze and silver keeps them hidden.

Answer

USE SCHEMA on bronze and silver only

Answer

SELECT on the sales_prod catalog

Answer

ALL PRIVILEGES on the sales_prod catalog