Databricks-DA-Assoc Practice Test 13 — 15 Questions

Question 1

Which THREE of the following steps are necessary to create an interactive dashboard in Databricks SQL? (Choose three)

Accepted Answer

Write a SQL query that includes filter parameters.. Building an interactive dashboard involves creating the underlying queries, selecting appropriate visualizations, and adding parameters for end-user interaction. Mastering this workflow is the core competency of a Databricks Data Analyst. These steps create a robust, self-service environment where users can filter and analyze data dynamically, reducing the burden on analysts to generate custom reports and empowering stakeholders to find their own answers to business questions.

Answer

Define the visualization type in the SQL query code.

Answer

Upload a CSV file containing the final report data.

Question 2

Refer to the exhibit. An analyst is troubleshooting a performance issue where frequent small inserts into a Delta table result in degraded query performance over time. The exhibit shows the configuration applied. What is the expected behavior of these properties?

Accepted Answer

It improves read performance by reducing the number of small files created during ingestion.. These properties enable Delta Lake's auto-optimization features. 'optimizeWrite' dynamically adjusts the write size to produce larger, more efficient files, while 'autoCompact' automatically merges small files into larger ones during background processes. This combination is critical for sustaining query performance in streaming or high-frequency update environments, preventing the 'small-file problem' that leads to excessive metadata overhead and slow scan times in Databricks SQL.

Answer

It forces all writes to be synchronous, ensuring immediate data consistency across all cluster nodes.

Answer

It automatically triggers a FULL OPTIMIZE command every hour regardless of file counts.

Answer

It forces the table to use a liquid clustering layout for all future data partitions.

Question 3

Which visualization type is most appropriate for displaying the distribution of a single continuous numerical variable, such as transaction amounts, in a Databricks Dashboard?

Accepted Answer

Histogram. Histograms are specifically designed to display the frequency distribution of continuous numerical variables by grouping data into contiguous bins. Choosing the correct visualization ensures that stakeholders can instantly identify data skewness, central tendencies, and outliers without misinterpreting the underlying statistical distribution.

Answer

Pie chart

Answer

Line plot

Answer

Scatter plot

Question 4

A data analyst has a Delta table `events` in Unity Catalog and needs to see the history of operations performed on it, including which user ran each operation and when, in order to audit recent changes. Which command should the analyst run?

Accepted Answer

DESCRIBE HISTORY events;. Delta Lake records every transaction in its log, and DESCRIBE HISTORY exposes that log as a table with version, timestamp, operation, and user columns. This is the intended command for auditing who changed a table and when. The other options either list schema objects, describe storage metadata, or read data at a point in time, none of which provide the operation audit trail.

Answer

DESCRIBE DETAIL events;

Answer

SELECT * FROM events VERSION AS OF 0;

Answer

SHOW TABLES IN main.default;

Question 5

You are a data analyst working in Databricks SQL. Your workspace has Unity Catalog enabled. You need to inspect the metadata of a table named `customers` in the `sales` schema of the `retail` catalog, but you do not want to return any rows of data. Which SQL statement should you use?

Accepted Answer

DESCRIBE TABLE retail.sales.customers;. In Databricks SQL with Unity Catalog, DESCRIBE TABLE is the standard command to retrieve column names, types, and comments for a table, and it accepts the three-part namespace catalog.schema.table. The other commands either list table names, run a data query, or return a CREATE statement, none of which match the requirement to inspect column metadata without returning rows.

Answer

SELECT * FROM retail.sales.customers LIMIT 0;

Answer

SHOW CREATE TABLE retail.sales.customers;

Answer

SHOW TABLES IN retail.sales;

Question 6

A data analyst runs the following SQL in a Databricks SQL warehouse:

CREATE OR REPLACE TEMPORARY VIEW monthly_sales AS
SELECT month, SUM(revenue) AS total_revenue
FROM sales.orders
GROUP BY month;

Immediately afterward, the analyst opens a new query tab in the same SQL warehouse session and runs SELECT * FROM monthly_sales; The query fails with a TABLE_OR_VIEW_NOT_FOUND error. What is the most likely reason for the failure?

Accepted Answer

The view was created as a temporary view, which is scoped to the notebook or session that created it, so a new query tab cannot resolve it.. Temporary views are bound to the session or notebook that creates them and are not stored in Unity Catalog. When the analyst opens a separate query tab, that new session has its own namespace and cannot resolve the temporary view, producing TABLE_OR_VIEW_NOT_FOUND. Persisting the view as a regular view or a managed table would make it accessible across sessions.

Answer

The view definition was persisted in Unity Catalog, but the second tab lacks USE CATALOG privileges on the default catalog.

Answer

The aggregated alias total_revenue conflicts with a reserved keyword, so the view was dropped automatically after creation.

Answer

The CREATE OR REPLACE TEMPORARY VIEW statement silently failed because the underlying sales.orders table lacks a primary key.

Question 7

You are an analyst in a Unity Catalog-enabled Databricks workspace. A colleague has shared a table `finance.transactions` with you, and you need to confirm what privileges you currently hold on it before running a sensitive query. Which TWO of the following statements about inspecting privileges in Unity Catalog are accurate? (Choose two.)

Accepted Answer

You can run SHOW GRANTS ON TABLE finance.transactions to see the privileges granted on that specific table.. Unity Catalog uses a hierarchical privilege model, so inspecting a table involves both the direct grants on the table and the inherited grants from its catalog and schema. SHOW GRANTS ON TABLE reveals direct table grants, while effective access can come from higher levels. The other statements misstate the command's scope, required permissions, or inheritance behavior and would lead an analyst to wrong conclusions about their access.

Answer

You must be the table owner or a metastore admin to run SHOW GRANTS ON TABLE on a table you can query.

Answer

SHOW GRANTS ON TABLE finance.transactions will also list the privileges granted on the parent catalog and schema automatically.

Answer

SHOW GRANTS ON TABLE returns only the privileges of the current user and hides grants to other principals for privacy.

Question 8

An analyst needs to create a new table in a Unity Catalog schema to store aggregated results, and the table should be managed by Unity Catalog so that storage lifecycle and access are handled by the platform. Which SQL statement correctly creates a managed Delta table named `summary` in the `analytics` schema of the `reporting` catalog?

Accepted Answer

CREATE TABLE reporting.analytics.summary (id INT, total DOUBLE);. In Unity Catalog, a table created with CREATE TABLE and a three-part name but no LOCATION is managed by Unity Catalog, which controls its storage and lifecycle. External tables require a LOCATION and are not managed by the platform. The MANAGED keyword is not part of the syntax, and legacy dbfs paths are not the Unity Catalog managed pattern.

Answer

CREATE MANAGED TABLE reporting.analytics.summary (id INT, total DOUBLE);

Answer

CREATE EXTERNAL TABLE reporting.analytics.summary (id INT, total DOUBLE) LOCATION '/mnt/data/summary';

Answer

CREATE TABLE reporting.analytics.summary USING DELTA LOCATION 'dbfs:/user/hive/warehouse/summary';

Question 9

A data analyst must load a Parquet file from an S3 bucket into a Databricks DataFrame, but the bucket is in a different AWS account and requires temporary credentials. The analyst has an AWS access key ID, secret access key, and session token. Which code snippet correctly configures Spark to read the file securely?

Accepted Answer

spark.conf.set("fs.s3a.access.key", access_key)
spark.conf.set("fs.s3a.secret.key", secret_key)
spark.conf.set("fs.s3a.session.token", session_token)
df = spark.read.parquet("s3a://bucket/path/file.parquet"). To read from S3 with temporary credentials, you must set the access key, secret key, and session token using the fs.s3a.* configuration keys and use the s3a:// URI scheme. This ensures Spark authenticates correctly to the cross-account bucket. Omitting the session token or using the wrong scheme will cause failures, so all three elements are essential for a successful read.

Answer

spark.conf.set("fs.s3.access.key", access_key)
spark.conf.set("fs.s3.secret.key", secret_key)
spark.conf.set("fs.s3.session.token", session_token)
df = spark.read.parquet("s3://bucket/path/file.parquet")

Answer

spark.conf.set("fs.s3a.access.key", access_key)
spark.conf.set("fs.s3a.secret.key", secret_key)
spark.conf.set("fs.s3a.session.token", session_token)
df = spark.read.parquet("s3://bucket/path/file.parquet")

Answer

spark.conf.set("fs.s3a.access.key", access_key)
spark.conf.set("fs.s3a.secret.key", secret_key)
df = spark.read.parquet("s3a://bucket/path/file.parquet")

Question 10

A data analyst runs a Databricks SQL query that joins a 40 GB Delta table to a 12 GB Delta table and the Query Profile shows two Exchange nodes surrounding a SortMergeJoin. The analyst wants to confirm whether the shuffle is the dominant cost before rewriting the query. Which Query Profile metric should the analyst inspect first to quantify the shuffle's impact?

Accepted Answer

Shuffle write bytes and shuffle read bytes on the Exchange nodes. The Exchange operators are the shuffle stages that redistribute rows by join key before the SortMergeJoin, so their shuffle write and read byte counts are the direct measurement of redistribution cost. Inspecting those byte totals tells the analyst whether the shuffle dominates runtime and justifies alternatives such as broadcast join or pre-partitioning.

Answer

Peak memory usage of the driver process

Answer

Rows read from the smaller table's scan operator

Answer

Number of files scanned in the Delta table directory

Question 11

A data analyst runs a Databricks SQL query on a Delta table that has 500,000 small files. The query applies a filter on a high-cardinality column but still scans all files, resulting in slow performance. The analyst wants to reduce the number of files scanned without changing the query logic. Which action should the analyst take?

Accepted Answer

Run OPTIMIZE on the table to compact small files and improve data skipping.. Compacting small files with OPTIMIZE reduces the number of files that must be opened and enables Delta data skipping through collected statistics. This directly addresses the root cause: too many small files causing excessive I/O. Other options either disable useful optimizations, misuse syntax, or only mask the problem without reducing file count.

Answer

Set spark.sql.adaptive.enabled to false to force a static execution plan.

Answer

Increase the driver node size to handle the large number of files.

Answer

Add a Z-ORDER BY clause to the query to sort results by the filtered column.

Question 12

A data analyst is preparing a Delta table in Unity Catalog for a dashboard that must return results quickly and consistently. The analyst needs to reduce the number of small files and improve data skipping on a frequently filtered column. (Choose two.)

Accepted Answer

Run OPTIMIZE on the table to compact small files into larger ones.. OPTIMIZE compacts small files into larger ones, reducing read overhead, while ZORDER BY co-locates related values so Delta can skip files using column statistics. Together they address both the small-file problem and the data-skipping requirement. VACUUM only deletes unreferenced files, views do not change storage layout, and larger clusters do not fix inefficient file organization.

Answer

Increase the SQL warehouse cluster size so more workers can scan small files in parallel.

Answer

Convert the table to a view so queries always compute results from the latest data.

Answer

Run VACUUM with a retention of zero hours to remove old files and speed up reads.

Question 13

A data analyst is building a Databricks SQL dashboard that tracks daily active users across four global regions. The dashboard has a single dataset query, and the analyst wants the region filter to apply to all visualizations on the dashboard without editing each widget's query. Which dashboard feature should the analyst configure?

Accepted Answer

A dashboard-level parameter with the keyword {{region}} applied in the dataset query.. Dashboards in Databricks SQL let analysts define parameters that can be referenced in dataset queries with the {{name}} syntax. A dashboard-level parameter appears as a control for viewers and, because multiple visualizations can share one dataset, changing the parameter re-runs the dataset and updates every widget bound to it. This satisfies the requirement of a single region filter driving all visualizations without editing each widget's SQL.

Answer

A Unity Catalog row filter function attached to the underlying table.

Answer

A separate query snippet embedded in each visualization's SQL that hardcodes the region list.

Answer

A scheduled refresh on the dashboard set to run every five minutes.

Question 14

An analyst is loading a CSV file where one column contains values like 007, 012, and 003. After creating a table through the Add Data UI, those values appear as 7, 12, and 3. The analyst needs the leading zeros preserved for downstream reporting. What should the analyst do?

Accepted Answer

Specify the column as a string type in the schema settings before creating the table.. Leading zeros carry meaning only in text form, so the column must be declared as a string before the table is created. Letting the reader infer an integer type silently drops the zeros, and no post-load fix reliably restores them. Overriding the type in the schema settings preserves the original values.

Answer

Apply a formatting function after loading that pads the numbers back to three characters.

Answer

Enable schema evolution so the column can switch types after the first load.

Answer

Set the file's delimiter to a character that prevents numeric interpretation of the column.

Question 15

A data analyst queries a partitioned Delta table `logs.events` that has a `event_date` column. The query filters `WHERE event_date = '2024-06-01'`. Performance is poor even though the table is partitioned on `event_date`. Which factor most likely explains the poor performance?

Accepted Answer

The table has thousands of small files within the matching partition, so pruning selects the right partition but still reads excessive files.. Partition pruning only eliminates partitions that cannot match; it does not fix fragmentation inside a matching partition. If the correct partition contains many small files, the query reads all of them, incurring high overhead. The likely remedy is compaction with OPTIMIZE on that partition, possibly combined with ZORDER, rather than changing the partition column or the predicate form.

Answer

The `event_date` column is stored as a string while the predicate compares to a string literal, forcing a full scan due to implicit casting.

Answer

The table was partitioned by a high-cardinality column other than `event_date`, such as a unique event identifier.

Answer

The predicate is wrapped in a non-deterministic function such as `WHERE CAST(event_date AS DATE) = current_date()`, preventing partition pruning.