Databricks-DA-Assoc Practice Test 14 — 15 Questions

Question 1

A data analyst is analyzing a slow-running Databricks SQL query. The analyst suspects that the query is suffering from excessive shuffling. Which two actions should the analyst take to reduce shuffle and improve performance? (Choose two.)

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

Accepted Answer

Enable Adaptive Query Execution (AQE) to dynamically coalesce shuffle partitions.. Enabling AQE allows dynamic coalescing of shuffle partitions and skew handling, reducing shuffle overhead. Using broadcast joins for small tables avoids shuffling the large table entirely. Both are effective strategies to minimize shuffle and improve query performance.

Answer

Repartition the large table on a high-cardinality column before the join.

Answer

Increase the number of shuffle partitions by setting spark.sql.shuffle.partitions to a very high value.

Answer

Cache the large table in memory to avoid shuffling it in subsequent operations.

Question 2

An analyst is preparing a Databricks SQL dashboard for an executive review and needs to ensure the numbers shown are trustworthy and reproducible. The analyst wants to document the data lineage and make the dashboard resilient to schema changes in the source tables. Which TWO actions should the analyst take? (Choose two.)

Accepted Answer

Use fully qualified three-level namespace names such as catalog.schema.table in the dataset query.. Trustworthy, reproducible dashboards depend on a versioned, reviewable source of SQL and on unambiguous object references. Storing the query in a Git-backed workspace folder provides history and peer review, while using fully qualified catalog.schema.table names makes dependencies explicit and guards against silent redirection when defaults change. Together these practices support lineage documentation and resilience, unlike refresh tuning, type coercion, or over-permissive grants.

Answer

Convert every numeric column to a string in the query so formatting is consistent.

Answer

Grant the dashboard viewers CAN MANAGE permission on the underlying tables.

Answer

Enable the dashboard's scheduled refresh to run every minute.

Question 3

A data analyst runs a query on a Delta table with a WHERE clause on a timestamp column, but the query scans the entire table. The analyst checks the table's metadata and sees that the column is not a partition column but is frequently used in filters. The analyst wants to enable data skipping to avoid full scans. Which action should the analyst take?

Accepted Answer

Run OPTIMIZE with ZORDER on the timestamp column to co-locate related data and improve data skipping.. Z-ordering on a frequently filtered column clusters data so that min/max statistics can be used to skip files. This is ideal for high-cardinality columns like timestamps where partitioning would create too many small files. OPTIMIZE with ZORDER rearranges data without changing the table structure.

Answer

Set spark.sql.parquet.filterPushdown to true to enable filter pushdown on the timestamp column.

Answer

Convert the table to a partitioned table using the timestamp column as the partition key.

Answer

Enable Delta Lake change data feed on the table to track changes to the timestamp column.

Question 4

A data analyst runs a query in Databricks SQL that returns the total sales per region. The query is slow, and the analyst notices that the execution plan shows a full table scan on a large Delta table. The analyst wants to reduce the amount of data read. Which action is most likely to improve performance?

Accepted Answer

Add a WHERE clause on a partitioned column to enable partition pruning.. Partition pruning is the most direct way to reduce data read. By filtering on a partitioned column, Delta Lake can skip partitions that do not match the filter, turning a full table scan into a targeted read. Other options do not reduce the volume of data read or are inapplicable to an aggregation query.

Answer

Use a broadcast hint to force a broadcast join.

Answer

Cache the table using CACHE TABLE before running the query.

Answer

Increase the cluster size to add more worker nodes.

Question 5

A data analyst is using the Databricks SQL Connector for Python to query a large Delta table. The query returns millions of rows, and the analyst wants to process the results in batches to avoid memory issues. Which method should the analyst use to fetch rows in chunks?

Accepted Answer

cursor.fetchmany(size). The fetchmany(size) method allows fetching a specified number of rows at a time, enabling batch processing of large result sets. This is the standard way to avoid loading all rows into memory. Other methods either fetch all rows, one row, or require manual query modifications.

Answer

cursor.fetchone()

Answer

cursor.fetchall()

Answer

cursor.execute() with a LIMIT clause

Question 6

A data analyst has built a Databricks SQL dashboard with a bar chart that shows total revenue by region. The dashboard has a parameter named `region_param` that allows the viewer to select one or more regions. The analyst wants the bar chart to update automatically whenever the viewer changes the selected regions, without requiring a manual refresh. What should the analyst do?

Accepted Answer

Reference the parameter in the query using the `{{ region_param }}` syntax and ensure the parameter is added to the dashboard.. Parameters in Databricks SQL allow dynamic values to be passed into queries. By referencing the parameter with double curly braces in the query and adding it to the dashboard, the visualization will automatically update when the parameter value changes. This is the standard mechanism for interactive dashboards in Databricks SQL.

Answer

Create a separate query for each region and add them as individual visualizations to the dashboard.

Answer

Use a dashboard-level filter that applies to all visualizations and set the parameter to a fixed value.

Answer

Add a filter widget to the dashboard and link it to the parameter.

Question 7

An analyst is loading Parquet files from cloud storage into a Delta table using COPY INTO. The source directory contains files with several different schemas because upstream teams added columns over time. The analyst wants the table to accept the union of all columns without manual intervention. Which COPY INTO behavior should the analyst configure?

Accepted Answer

Set mergeSchema to true in the COPY INTO options so new columns are added to the target table.. The mergeSchema option in COPY INTO adds any columns found in the source files to the target table, producing the union of columns across heterogeneous files. This matches the requirement to accept new columns without manual ALTER TABLE statements, while retaining all previously loaded data.

Answer

Enable overwrite mode so each run replaces the table with the latest file's schema.

Answer

Create the target table with a manually defined superset schema containing every possible column.

Answer

Use a permissive mode that silently drops columns not present in the target table schema.

Question 8

An analyst queries a view `main.ops.active_shipments` and receives a row-level filtered result, but the view's definition does not contain a WHERE clause. The analyst has SELECT on the view and on the base table. What most likely explains the filtered output?

Accepted Answer

A row filter function is attached to the base table, and the view inherits the filter because it reads the base table at query time.. Row filters in Unity Catalog are attached to tables and evaluated on every read, including reads performed through views. Since a standard view re-runs its definition against the base table, the base table's row filter silently restricts the rows returned, even though the view's own SQL has no WHERE clause. Column masks alter values, not row counts, and time travel returns different table versions rather than predicate-filtered subsets.

Answer

The analyst's group has a column mask applied to the view that removes rows where the masked column is null.

Answer

The view was created with a row filter using the CREATE VIEW ... WITH ROW FILTER syntax, but the filter text is hidden from SHOW CREATE TABLE output.

Answer

Delta Lake time travel is enabled on the view, so the analyst is reading an older snapshot that contains fewer rows.

Question 9

A data analyst notices that a query performing a join between a large fact table and a small dimension table is slow. The analyst wants to ensure that the small table is broadcasted to all executors to avoid a shuffle. Which configuration should the analyst adjust to increase the likelihood of a broadcast join?

Accepted Answer

spark.sql.autoBroadcastJoinThreshold. The key to encouraging a broadcast join is to ensure the small table's size is below the autoBroadcastJoinThreshold. By increasing this threshold, the analyst allows Spark to consider the small table for broadcast, eliminating the shuffle of the large table and significantly speeding up the join.

Answer

spark.sql.adaptive.enabled

Answer

spark.sql.shuffle.partitions

Answer

spark.sql.broadcastTimeout

Question 10

A data analyst needs to create a new table that stores only aggregated daily sales totals derived from an existing Unity Catalog table, and the result must be refreshed nightly. They want the simplest object that persists the results and can be queried by other analysts. Which approach should they use?

Accepted Answer

Create a materialized view with `CREATE MATERIALIZED VIEW sales.daily_totals AS SELECT ...` and schedule a refresh.. Persisting aggregated results that refresh on a schedule is precisely what a materialized view provides. It stores computed data, supports scheduled refresh, and is queryable like a table by other users. Plain views recompute each time, temporary views do not persist or share, and external tables require manual orchestration, so the materialized view is the simplest fitting object.

Answer

Create a temporary view with `CREATE TEMPORARY VIEW sales.daily_totals AS SELECT ...` and grant other analysts access.

Answer

Create a view with `CREATE VIEW sales.daily_totals AS SELECT ...` so the aggregation is recomputed on every query.

Answer

Create an external table at a cloud storage path and populate it with `INSERT INTO` after each aggregation run.

Question 11

A data analyst is building a dashboard in Databricks SQL that includes a visualization showing sales by product category. The analyst wants to ensure that the dashboard is interactive and allows viewers to filter the data by date range and product category. Which TWO of the following actions should the analyst take to enable this interactivity? (Choose two.)

Accepted Answer

Add a date range parameter to the dashboard and reference it in the query's WHERE clause.. To enable interactive filtering by date range and product category, the analyst should use parameters. A date range parameter and a dropdown list parameter for product category, both referenced in the query, allow viewers to dynamically filter the data. This is the standard and most efficient method in Databricks SQL dashboards.

Answer

Embed the dashboard in an iframe and use JavaScript to pass filter values.

Answer

Use a dashboard-level filter that applies to all visualizations and set it to a fixed date range.

Answer

Create a separate visualization for each product category and add them all to the dashboard.

Question 12

You are building a Databricks SQL dashboard and have a query that returns columns: order_date (DATE), region (STRING), and total_sales (DECIMAL). You want a single visualization that shows the trend of total_sales over order_date, with a separate line for each region, all on the same chart. Which visualization type should you select?

Accepted Answer

Line chart. The requirement is a temporal trend with one line per region, which is exactly what a line chart provides when a categorical column such as region is used as the series. The other chart types either compare static categories, show parts of a whole, or display point correlations, and none of them express continuous multi-series trends over dates.

Answer

Pie chart

Answer

Scatter chart

Answer

Bar chart

Question 13

An analyst has a CSV file at `abfss://raw@storage.dfs.core.windows.net/exports/2024_orders.csv` and wants to query it directly from Databricks SQL without loading it into a Delta table. Which approach is appropriate?

Accepted Answer

Use a read_files table-valued function in a query, for example SELECT * FROM read_files('abfss://raw@storage.dfs.core.windows.net/exports/2024_orders.csv', format => 'csv');. The read_files table-valued function is designed to query external files in place, returning a relation you can select from without creating a persistent object. It supports CSV and other formats and accepts options for headers and delimiters. COPY INTO writes into Delta tables, foreign tables are not a Databricks SQL construct, and a temporary view cannot select from a bare storage path without a file-reading function.

Answer

Create a foreign table with CREATE FOREIGN TABLE and query it with standard SQL.

Answer

Run COPY INTO with the file path as the target and then query the resulting table.

Answer

Create a temporary view with CREATE TEMPORARY VIEW csv_data AS SELECT * FROM 'abfss://raw@storage.dfs.core.windows.net/exports/2024_orders.csv';

Question 14

A data analyst is designing a dimension table in Databricks SQL that will be used in a star schema. The table contains a natural business key (e.g., product_code) and a surrogate key (e.g., product_sk). The analyst wants to ensure that the surrogate key is unique and automatically generated for each new row, while also enforcing that the natural key is unique. Which approach best achieves these requirements?

Accepted Answer

Define product_sk as GENERATED ALWAYS AS IDENTITY and add a PRIMARY KEY constraint on product_sk and a UNIQUE constraint on product_code.. The requirement is for an automatically generated, unique surrogate key and a unique natural key. The IDENTITY column property in Delta Lake automatically generates unique, sequential values for new rows. Combining it with a PRIMARY KEY constraint on the surrogate key and a UNIQUE constraint on the natural key enforces both uniqueness rules. Other options either misuse functions, lack persistence, or do not enforce uniqueness correctly.

Answer

Define product_sk as BIGINT and set a default value using the UUID() function, then add a PRIMARY KEY constraint on product_sk and a UNIQUE constraint on product_code.

Answer

Use a Delta table with a CHECK constraint that validates product_code IS NOT NULL, and generate product_sk using the ROW_NUMBER() window function in a view.

Answer

Define product_sk as STRING and use the MONOTONICALLY_INCREASING_ID() function during inserts to populate it, then add a PRIMARY KEY constraint on product_sk.

Question 15

A data analyst builds an AI/BI Genie Space over a Unity Catalog table named sales.transactions. Business users report that when they ask questions such as "total revenue by region," Genie returns figures that include cancelled orders, which should be excluded. The analyst wants Genie to always apply this filter. What should the analyst do?

Accepted Answer

Create a view in Unity Catalog that filters out cancelled orders, and point the Genie Space at that view.. The most reliable way to enforce a permanent business rule is to define it in the data layer. A Unity Catalog view that filters out cancelled orders ensures every Genie-generated query respects the rule. Instructions and user prompts are not guaranteed to add the filter, and compute resources do not apply data logic.

Answer

Create a SQL warehouse that only returns rows where status is not cancelled.

Answer

Ask business users to include "excluding cancelled orders" in every question.

Answer

Add an instruction to the Genie Space telling it to filter out cancelled orders.