Databricks-DA-Assoc Practice Test 16 — 15 Questions

Question 1

A data analyst is creating a Databricks SQL dashboard to display a single key performance indicator: the total number of active subscriptions for the current day. The value must be prominent and readable at a glance, with no axes or categories. Which visualization type should the analyst choose?

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

Accepted Answer

Counter. A counter visualization is purpose-built to display one aggregated numeric value without axes, categories, or series. For a dashboard KPI such as the current day's active subscription count, it presents the figure prominently and is immediately readable. Other chart types require dimensions or series that do not exist in this single-value scenario, so they would add unnecessary complexity.

Answer

Bar chart

Answer

Line chart

Answer

Pie chart

Question 2

A data analyst is troubleshooting a slow Databricks SQL query that joins a large fact table with a small dimension table. The query plan shows a broadcast hash join, but the analyst notices that the small table is not being broadcast as expected. Which configuration should the analyst check to ensure the small table is broadcast?

Accepted Answer

spark.sql.autoBroadcastJoinThreshold. The broadcast hash join is governed by the autoBroadcastJoinThreshold configuration, which defines the maximum size for a table to be broadcast. If the small table exceeds this threshold, it will not be broadcast. Checking and potentially increasing this threshold can enable the broadcast, improving performance. Other settings affect different aspects of query execution and do not directly control broadcast eligibility.

Answer

spark.sql.adaptive.enabled

Answer

spark.databricks.delta.optimizeWrite.enabled

Answer

spark.sql.shuffle.partitions

Question 3

A data analyst is building a Databricks SQL dashboard that includes a line chart of daily active users over the past year. The stakeholder wants to hover over the line to see the exact date and user count for each point. The analyst notices that the tooltip currently shows only the user count and not the date. What should the analyst do to include the date in the tooltip?

Accepted Answer

Add the date column to the visualization's tooltip encoding or ensure it is included as a field in the chart's data so the hover tooltip can display it.. Tooltips reflect the fields encoded in the visualization. If the date is used only for the x-axis and not exposed to the tooltip, hovering shows only the measure. Including the date in the tooltip encoding or ensuring it is part of the chart's fields makes the hover display both the date and the user count, preserving the line chart while meeting the stakeholder's requirement.

Answer

Enable the dashboard-level option to show all fields in tooltips, which automatically includes every column returned by the query.

Answer

Modify the query to concatenate the date and user count into a single string column and use that as the measure in the line chart.

Answer

Change the visualization type from line chart to a table so that all columns, including the date, are visible without hovering.

Question 4

A data analyst is designing a Delta table in Databricks SQL to store clickstream events. The table will be queried primarily by filtering on event_date and then by user_id. The analyst wants to optimize data skipping for both columns without over-partitioning. Which approach should the analyst use?

Accepted Answer

Use Liquid Clustering on event_date and user_id.. Liquid Clustering on event_date and user_id provides automatic, incremental clustering that optimizes data skipping for filters on either column without the drawbacks of over-partitioning. It is designed for multi-column clustering and adapts as data changes, making it ideal for clickstream analysis where queries filter by date and user. Partitioning or Z-ORDER alone would not achieve the same balanced performance.

Answer

Partition the table by event_date and use Z-ORDER on user_id.

Answer

Create a materialized view that pre-aggregates clicks by event_date and user_id.

Answer

Partition the table by user_id and use Z-ORDER on event_date.

Question 5

A data analyst is creating a table in Databricks SQL to store product information. The analyst wants to ensure that the table is automatically optimized for query performance as data is added, without manual intervention. Which table type should the analyst use?

Accepted Answer

A Delta table with liquid clustering enabled on high-cardinality columns.. Liquid clustering is a feature in Delta Lake that automatically optimizes data layout as new data is written, without manual intervention. It is ideal for tables where query patterns may change or where continuous optimization is desired. By enabling liquid clustering, the analyst ensures that the table remains performant without needing to manually partition or schedule Z-ORDER operations.

Answer

A Delta table with Z-ORDER applied on a schedule.

Answer

A Delta table partitioned by a low-cardinality column.

Answer

A Parquet table with manually defined partitions.

Question 6

A data analyst has been asked to grant a new team member read access to a specific table named 'customer_orders' in Unity Catalog. The analyst wants to ensure the team member can query the table but cannot see any other tables in the schema. Which SQL command should the analyst use?

Accepted Answer

GRANT SELECT ON TABLE main.sales.customer_orders TO `user@example.com`;. To grant read access to a specific table in Unity Catalog, the SELECT privilege must be granted on that table. This allows the user to query the table's data while leaving other tables inaccessible. Schema- or catalog-level grants are broader and would inadvertently expose additional data, so they are not suitable for this scenario.

Answer

GRANT SELECT ON SCHEMA main.sales TO `user@example.com`;

Answer

GRANT USAGE ON TABLE main.sales.customer_orders TO `user@example.com`;

Answer

GRANT SELECT ON CATALOG main TO `user@example.com`;

Question 7

A data analyst is designing a table to store customer orders. The table will be frequently queried by order_date and customer_id. The analyst wants to optimize query performance for these filters. Which physical data modeling technique should the analyst use?

Accepted Answer

Use Liquid Clustering on order_date and customer_id.. Liquid Clustering is designed to optimize query performance on multiple columns by clustering data dynamically. It is a table-level property that can be applied to new or existing tables and supports incremental clustering as data is added. Unlike partitioning, it handles high-cardinality columns well and does not require manual maintenance, making it ideal for optimizing filters on both order_date and customer_id.

Answer

Partition the table by order_date.

Answer

Use Z-ORDER BY on order_date and customer_id.

Answer

Create a materialized view that aggregates orders by order_date and customer_id.

Question 8

A data analyst is tuning an AI/BI Genie space used by the finance department. Users report that Genie sometimes returns plausible but incorrect aggregations, and that questions referencing 'net revenue' produce inconsistent results. The analyst wants to improve grounding and reliability. Which TWO actions should the analyst take? (Choose two.)

Accepted Answer

Add a general instruction in the Genie space that defines net revenue as gross revenue minus returns and discounts.. Inconsistent results for a business term like net revenue are best resolved by encoding the definition explicitly and demonstrating it. General instructions provide the textual rule, while example SQL queries show the exact joins and arithmetic. Together they ground the model's interpretation and reduce plausible-but-incorrect outputs. Compute scaling and access restrictions do not address semantic ambiguity.

Answer

Remove all tables except one from the Genie space so the model has fewer choices.

Answer

Switch the Genie space to use a larger SQL warehouse so the model has more compute for reasoning.

Answer

Restrict the space to read-only access for all users so no one can modify the underlying data.

Question 9

A data analyst is building a Databricks SQL dashboard that includes a parameter for the region. The dashboard should allow viewers to select a region from a dropdown list that is populated dynamically from the distinct values in the sales table's region column. Which feature should the analyst use to implement this?

Accepted Answer

Create a query-based dropdown parameter that runs a SELECT DISTINCT region FROM sales query to populate the list.. Databricks SQL dashboards support query-based parameters, which dynamically populate a dropdown from a SQL query. This ensures the list of regions is always up to date. Static lists or text inputs do not provide dynamic population from the data, and filter widgets are not parameters.

Answer

Add a filter widget on the dashboard that uses a SELECT DISTINCT query, and link it to the visualizations.

Answer

Implement a text input parameter and instruct users to type the region name.

Answer

Use a static dropdown parameter and manually enter all possible region values.

Question 10

A data analyst needs to run a scheduled SQL query every morning and deliver the result to a finance team as a CSV file in cloud storage. The query logic is already tested in a Databricks SQL query. Which Databricks capability should the analyst use to automate this delivery?

Accepted Answer

A Databricks SQL query schedule with a destination. Databricks SQL supports scheduling saved queries so they execute on a recurring cadence, and a schedule can include a destination such as cloud storage or email. That combination automates both execution and delivery of CSV results, exactly matching the finance team's requirement. Alerts, widgets, and Delta Live Tables address monitoring, interactivity, and pipeline ETL rather than scheduled result delivery.

Answer

A Databricks SQL alert

Answer

A Delta Live Tables pipeline

Answer

A notebook-scoped widget parameter

Question 11

A data analyst needs to grant a service principal read access to a specific external location in Unity Catalog so that a scheduled job can read data from an S3 bucket. The analyst has already created a storage credential that references an IAM role with the necessary S3 permissions. Which Unity Catalog object must the analyst grant the service principal access to, in addition to the storage credential?

Accepted Answer

The external location. To allow a service principal to read from an external location, the analyst must grant the service principal `READ FILES` on that external location. The external location is the Unity Catalog securable that combines the cloud storage path with the storage credential, and it is the object against which privileges are granted. The storage credential itself is not granted to users.

Answer

The AWS IAM role

Answer

The storage credential

Answer

The S3 bucket policy

Question 12

A data analyst is working with a Delta table that contains a column 'sensitive_info' which should be redacted for users in the 'marketing' group. The analyst wants to ensure that users in that group see a masked value while other users see the actual data. Which Databricks feature should the analyst use?

Accepted Answer

Column masks. Column masks in Unity Catalog are designed to dynamically redact column values based on the user's identity or group membership. They allow the analyst to define a masking expression that applies only to specified users or groups, leaving the original data intact for others. This provides fine-grained security without duplicating data or creating multiple views.

Answer

Row filters

Answer

Dynamic views

Answer

Table ACLs

Question 13

A data analyst has a Unity Catalog table `main.finance.payroll` that contains a column `ssn` with sensitive data. The analyst wants to allow the HR team to query the table but mask the `ssn` column so that only users in the `hr_admins` group see the actual values; all other users should see `***` instead. Which Unity Catalog feature should the analyst use to achieve this?

Accepted Answer

Column-level masking using a user-defined function (UDF) applied via `ALTER TABLE ... ALTER COLUMN ... SET MASK`.. Column masks in Unity Catalog allow conditional redaction of sensitive column values based on the querying user's identity or group memberships. By applying a mask function to the `ssn` column, the analyst can ensure that only members of `hr_admins` see the real values, while others see a masked placeholder. This satisfies the requirement without changing how users query the table.

Answer

Table ACLs granting SELECT only to `hr_admins` and denying SELECT to others.

Answer

Dynamic view that selects all columns except `ssn` for non-admin users.

Answer

Row-level security using a filter expression on the table.

Question 14

A data analyst is new to a Databricks workspace and needs to work with data stored in Unity Catalog. The analyst wants to understand which statements about Unity Catalog are accurate. (Choose two.)

Accepted Answer

Unity Catalog access control can be applied at the catalog, schema, table, and column levels, including row and column filters.. Unity Catalog centralizes governance with a three-level namespace and supports privileges from the catalog level down to columns, including row filters and column masks. These two characteristics let an analyst reference data with fully qualified names and trust that access rules are enforced consistently. The other statements misstate storage requirements, metadata placement, or the range of supported query interfaces.

Answer

Unity Catalog metadata is stored separately in each workspace, so grants must be repeated for every workspace.

Answer

Unity Catalog can only be queried from notebooks and not from Databricks SQL warehouses or dashboards.

Answer

Unity Catalog requires that all tables be stored as external tables in cloud object storage rather than managed tables.

Question 15

A data analyst in the 'marketing' group needs to query the table `main.sales.leads` but should not be able to read the underlying data files directly. The analyst currently has `SELECT` on the table and `READ FILES` on the external location where the table's data resides. Which Unity Catalog privilege should the analyst's `READ FILES` on the external location be removed to enforce least privilege?

Accepted Answer

`READ FILES` on the external location. To enforce least privilege, the analyst should not have direct file access when table-level access suffices. `READ FILES` on the external location allows bypassing Unity Catalog table permissions, so it must be removed. `SELECT` on the table remains to allow querying, while `USAGE` on the schema and `BROWSE` on the catalog are unrelated to file-level reads.

Answer

`SELECT` on the table

Answer

`BROWSE` on the catalog `main`

Answer

`USAGE` on the schema `main.sales`