Databricks-DA-Assoc Practice Test 18 — 15 Questions

Question 1

A data analyst runs a query in Databricks SQL that aggregates sales data by product category. The analyst notices that the query results include a category value of NULL. The analyst wants to exclude rows where the category is NULL from the aggregation. Which SQL clause should be added to the query?

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

Accepted Answer

WHERE category IS NOT NULL. To exclude NULL categories before aggregation, use a WHERE clause with IS NOT NULL. This filters out rows with NULL category before grouping, so the aggregation does not include a NULL group. HAVING filters after aggregation and is less efficient, while ORDER BY only sorts.

Answer

ORDER BY category NULLS LAST

Answer

GROUP BY category HAVING COUNT(*) > 0

Answer

HAVING category IS NOT NULL

Question 2

A data analyst needs to quickly upload a small CSV file from their local machine into Databricks to explore its contents. They want to avoid writing code and prefer a graphical interface. Which Databricks feature should they use?

Accepted Answer

The Add Data UI. The Add Data UI is specifically designed for uploading local files through a graphical interface, automatically creating tables and inferring schema. It requires no coding and is perfect for quick exploration. Other options involve command-line or programmatic interfaces, which are not as user-friendly for ad-hoc uploads.

Answer

The Spark DataFrame API

Answer

The Databricks CLI

Answer

The DBFS CLI

Question 3

An analyst wants to create a view named `main.sales.high_value` that returns only rows from `main.sales.orders` where `total_amount > 1000`, and wants the view to reflect future updates to the base table automatically. Which statement should the analyst run?

Accepted Answer

CREATE VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000;. Because the analyst wants results that always reflect the current state of the base table, a standard view is correct. A view persists only the query definition, and Databricks re-evaluates it on every read, so inserts or updates to the orders table appear immediately. Materialized views cache results and need refreshes, while temporary views vanish at session end, so neither matches the persistent, always-current requirement.

Answer

CREATE MATERIALIZED VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000;

Answer

CREATE TEMPORARY VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000;

Answer

CREATE OR REPLACE VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000 WITH SCHEMA EVOLUTION;

Question 4

Refer to the exhibit. An analyst receives this error when attempting to select from a table in Databricks SQL. What is the most likely cause?

Accepted Answer

The analyst is missing the USE CATALOG or USE SCHEMA statement.. The error indicates a Namespace resolution issue. In Databricks SQL, tables are organized in a three-level namespace: catalog.schema.table. If the current session context is not set to the correct catalog and schema, or if the user lacks the necessary privileges to see the object, the engine cannot resolve the path. Correcting the context or using a fully qualified name resolves this common accessibility error.

Answer

The table schema is corrupted and requires a REPAIR TABLE command.

Answer

The SQL Warehouse is stopped and needs to be restarted.

Answer

The table has too many partitions, causing a timeout.

Question 5

Refer to the exhibit. An analyst is reviewing the configuration JSON for a Genie Space. Why is the 'instructions' field critical in this scenario?

Accepted Answer

It ensures the AI correctly implements the required join logic between the two datasets.. The 'instructions' field serves as a critical bridge between natural language and technical SQL execution. Without this specific guidance, the Genie model might attempt to join tables incorrectly or ignore necessary filtering logic. By explicitly defining the join strategy, the analyst ensures that the AI consistently uses the standard business join keys, preventing the generation of incorrect, unoptimized, or cross-joined results that would compromise data accuracy.

Answer

It automatically generates a visualization for the user whenever a query is run.

Answer

It forces the user to provide a region ID every time they ask a question.

Answer

It caches the results of the query to improve performance for future users.

Question 6

An analyst wants to view the query profile for a specific query that was run recently. Where should they look in the Databricks SQL UI?

Accepted Answer

The Query History tab. The Query History interface is the primary tool for performance troubleshooting. It provides a visual representation of the query plan, including task breakdown, data read/write volumes, and execution time per operator. Understanding this UI is vital for a Data Analyst to identify bottlenecks such as data skew or excessive shuffling, enabling them to optimize slow queries effectively and improve the efficiency of their SQL workloads.

Answer

The Catalog Explorer

Answer

The SQL Editor

Answer

The SQL Warehouse settings page

Question 7

Which of these is a benefit of using a 'parameter' in a Databricks SQL query?

Accepted Answer

It allows users to dynamically filter results without changing the code.. Parameters enable dynamic query execution. By allowing users to provide input at runtime, the same query can serve multiple analytical needs. This reduces the need to create dozens of near-identical queries, simplifying the codebase and making the dashboard much more interactive. It empowers the end-user to drill down into the specific segments they are interested in, which is a key driver for self-service analytics adoption within organizations.

Answer

It permanently changes the data in the underlying table.

Answer

It forces every user to run the exact same query.

Answer

It creates a copy of the database for each user session.

Question 8

Which option is best for sharing a dashboard with internal stakeholders who do not have access to the Databricks workspace?

Accepted Answer

Configure an email subscription to send the dashboard periodically as a snapshot.. Databricks provides a subscription feature to email dashboard snapshots (PDFs or screenshots) to stakeholders. This ensures that users outside the immediate Databricks environment can consume relevant business insights without needing to manage complex identity and access management for the workspace. This is a key requirement for democratization of data, ensuring that executives and non-technical staff stay informed on KPIs regularly.

Answer

Create a temporary personal access token (PAT) for each stakeholder to login.

Answer

Publicly host the dashboard URL on the company intranet.

Answer

Manually export the dashboard as a CSV file to a shared file server.

Question 9

A data analyst is setting up a recurring ingestion job that must load only new files from a cloud storage directory into a Delta table, must track which files have already been processed, and must fail clearly if the source schema changes unexpectedly. Which TWO capabilities should the analyst rely on? (Choose two.)

Accepted Answer

Auto Loader with a checkpoint location tracks processed files and supports schema enforcement or evolution policies.. Both COPY INTO and Auto Loader maintain ingestion state so only new files are processed, and both can be configured to surface unexpected schema changes rather than silently accepting them. COPY INTO uses table-level load history, while Auto Loader uses a checkpoint directory with an explicit schema policy. Either satisfies the incremental and schema-safety requirements.

Answer

Mounting the storage location and using spark.read on the directory with overwrite mode.

Answer

CREATE TABLE AS SELECT on the source directory rebuilds the table from all files on every run.

Answer

A scheduled notebook that lists the directory and compares filenames to a manually maintained table.

Question 10

Which feature in Databricks SQL allows an analyst to view the history and performance metrics of previously executed queries?

Accepted Answer

The Query History.. The Query History interface is the primary tool for reviewing past executions. It allows analysts to search, filter, and inspect the performance of queries run across the workspace. This is important for identifying long-running queries, diagnosing failures, and comparing performance over time, which supports the iterative process of optimizing data models and SQL code to ensure consistent, reliable, and performant data delivery for the organization.

Answer

The Data Explorer.

Answer

The Cluster Metrics tab.

Answer

The Workspace Audit Logs.

Question 11

A data analyst needs to share a frequently updated sales dashboard with business stakeholders who do not have access to the underlying raw tables containing Personally Identifiable Information (PII). Which Databricks SQL feature should be implemented to securely present this summary data?

Accepted Answer

Implement dynamic views utilizing column masking and row-level filtering based on user group identities.. Dynamic View Functions with masking policies allow administrators to restrict column-level visibility based on group memberships or user contexts. This ensures stakeholders see aggregated or masked data without requiring separate downstream tables, maintaining strict data governance while enabling seamless visualization sharing.

Answer

Create static CSV extracts of the summary data and upload them to a shared workspace folder.

Answer

Grant the stakeholders direct SELECT permissions on the raw tables, relying on them to ignore sensitive columns.

Answer

Duplicate the entire dataset into a separate schema, permanently deleting the sensitive columns from the copy.

Question 12

A data analyst manages a Delta table gold.orders that is updated by an upstream job every 15 minutes. Analysts run long-running dashboard queries against the table and occasionally see stale results, even seconds after an update. The analyst wants dashboard queries to reflect the latest committed data without restarting the SQL warehouse. Which action should the analyst take?

Accepted Answer

Configure the session to use a fresh snapshot by disabling snapshot reuse, for example by setting the appropriate Spark configuration for the session.. Snapshot reuse within a Spark or Databricks SQL session caches the table's state for performance, which can cause readers to miss recent commits. Disabling snapshot reuse forces the session to resolve the current Delta version on each query, so dashboards reflect the latest committed data. Cache tuning and OPTIMIZE address performance, not snapshot freshness, and converting formats sacrifices Delta guarantees.

Answer

Run OPTIMIZE gold.orders after every upstream commit so the table is compacted and readers pick up new files.

Answer

Enable the Delta cache on the SQL warehouse and increase the cache size so recent files stay resident.

Answer

Convert the table to a Parquet table so queries always read the latest files directly from cloud storage.

Question 13

A data analyst is troubleshooting a performance issue in a notebook. The query runs slowly when processing a large table. Which approach should the analyst take to improve performance?

Accepted Answer

Run the 'OPTIMIZE' command on the Delta table.. Performance tuning in Databricks often involves examining how data is stored and accessed. Identifying bottlenecks like small files or lack of partitioning is key. By using built-in optimization commands, analysts can improve query speed significantly. This process is a core skill for any Databricks analyst, as it directly impacts the cost of compute resources and the user experience when working with massive, real-world datasets.

Answer

Increase the number of users connected to the workspace.

Answer

Delete the table and re-create it without Delta Lake.

Answer

Force the notebook to run on a single-node cluster.

Question 14

A data analyst notices that a query involving a join between a large table and a small lookup table is performing poorly. The analyst wants to optimize the join performance without changing the underlying data. Which technique should they apply?

Accepted Answer

Add an explicit BROADCAST hint to the small table in the JOIN clause.. Broadcasting the small table forces the cluster to send the entire lookup table to every node in the cluster, avoiding a full shuffle of the large table. This technique significantly reduces network overhead during joins when one side fits in memory. Mastering join strategies is essential for analysts to write efficient SQL that minimizes cluster resource consumption and shortens execution times on large datasets.

Answer

Increase the number of shuffle partitions to 2000.

Answer

Convert the large table to a Delta table with Z-Ordering.

Answer

Use the MERGE INTO statement instead of a standard JOIN.

Question 15

A data analyst is reviewing a Databricks SQL query that runs slowly and opens the Query Profile. The analyst wants to identify whether an individual task is disproportionately slow compared with its peers, indicating skew. Which TWO areas of the Query Profile should the analyst examine? (Choose two.)

Accepted Answer

The number of rows and bytes written by each shuffle partition. Skew manifests as an individual task running far longer than its peers because one partition holds disproportionate data. Task duration distribution within a stage exposes the straggler, while per-partition shuffle write rows and bytes show the imbalanced partition that causes it. Together these two views confirm skew rather than a general resource shortage.

Answer

The version of the Databricks Runtime used by the warehouse

Answer

The SQL warehouse cluster size configured for the query

Answer

The total number of stages in the physical plan