Databricks-DA-Assoc Practice Test 6 — 10 Questions

Question 1

An analyst needs to combine data from a Delta table in the 'dev' catalog with a CSV file uploaded to a volume. Which feature allows the analyst to manage both in a single query?

Accepted Answer

Unity Catalog Volumes. Unity Catalog Volumes enable analysts to manage non-tabular data, such as CSV or JSON files, alongside traditional Delta tables. This unification simplifies the data integration process. By treating files in volumes as first-class objects within the catalog, analysts can use SQL to query and join disparate data sources, reducing the complexity of data pipelines and making it easier to perform multi-source analyses within a secure, governed environment.

Answer

External locations

Answer

Databricks File System (DBFS) mounting

Answer

Manual file upload via a web browser

Question 2

Refer to the exhibit. A developer is configuring a Genie Space. The model is still including discounts in its revenue calculations despite the instruction. What is the most likely cause?

Accepted Answer

The model lacks sufficient metadata or schema constraints on the 'discount' column.. The AI model is likely relying on the underlying schema metadata or the actual data distribution more heavily than the provided system instructions. When instructions are contradictory to standard SQL column names or common business logic, the model requires more explicit guidance. Improving the precision of the instructions, such as specifying columns to exclude, or modifying the view definition, is necessary to override the model's default behavior.

Answer

The temperature is set too low.

Answer

The Genie Space requires a higher temperature setting to understand complex exclusions.

Answer

The 'data_sources' array is missing the 'discounts' table.

Question 3

Which THREE of the following are benefits of using Liquid Clustering instead of traditional partitioning in Databricks SQL?

Accepted Answer

It simplifies data layout management by removing the need for manual partitioning.. Liquid Clustering provides a flexible alternative to manual partitioning by automatically managing data layout based on query patterns. It eliminates the need for manual 'partition evolution' and solves issues related to skewed data distribution or small file problems. By offloading cluster management to the Databricks engine, analysts can spend more time on business logic and less on the intricacies of physical data file management for large-scale datasets.

Answer

It provides faster write throughput by disabling transaction logs during updates.

Answer

It forces data to be sorted by every column in the table automatically.

Question 4

A data platform administrator needs to configure secure data access policies in Unity Catalog. Which TWO actions can the administrator perform to restrict access to sensitive columns within a Delta table? (Choose TWO)

Accepted Answer

Grant SELECT privilege on the specific sensitive columns to authorized users while withholding it on other columns in the table.. Unity Catalog supports fine-grained governance through column-level access controls and dynamic masking functions. Administrators can restrict column visibility directly by revoking SELECT privileges on specific columns or by applying SQL-based masking functions that evaluate user attributes at query time. These native capabilities ensure sensitive information remains protected across all notebooks, dashboards, and SQL queries without duplicating physical data assets.

Answer

Modify the cloud storage bucket permissions in AWS S3 or Azure Blob Storage to block read access to individual table files containing sensitive columns.

Answer

Enable row-level security filters on the table to automatically exclude rows containing sensitive attribute values from query results.

Answer

Create a static clone of the table using the CLONE command and delete the sensitive columns from the cloned copy for general users.

Question 5

A data analyst has a Databricks SQL query that joins a large fact table to a small dimension table. The query is slow because the dimension table is being shuffled across the cluster. The analyst wants to avoid this shuffle and improve performance. Which SQL hint should they use?

Accepted Answer

BROADCAST. The BROADCAST hint tells the optimizer to send the smaller table to all worker nodes, allowing the join to occur locally without shuffling the larger table. This reduces network overhead and speeds up the query. The other options are either not hints or would increase shuffling.

Answer

REPARTITION

Answer

SHUFFLEMERGE

Answer

MERGE

Question 6

A data analyst runs a query that joins a fact table to a dimension table and groups by a dimension attribute. The Query Profile shows a BroadcastHashJoin, yet the query still takes much longer than expected and one stage shows a single task consuming most of its runtime. The dimension table is small, so the analyst suspects the bottleneck is elsewhere. Which explanation best fits the evidence?

Accepted Answer

The grouping key is skewed, so one task in the aggregation stage processes far more rows than the others. Because the broadcast join removes shuffle from the join itself, the remaining slow stage with one dominant task is the aggregation. An unevenly distributed grouping key concentrates most rows into one partition, so that task runs far longer than its peers. Addressing the skew in the group-by, not the join, is what improves runtime.

Answer

The fact table lacks statistics, causing Spark to choose a nested loop join

Answer

The broadcast of the small table is failing and silently falling back to a sort-merge join

Answer

The dimension table is too large to broadcast and must be repartitioned before joining

Question 7

Refer to the exhibit. A data analyst reviews a physical execution plan where a table is scanned and shuffled multiple times in succession during a self-join operation. What underlying anti-pattern in the query construction likely caused this redundant exchange?

Accepted Answer

The table was referenced multiple times in complex join logic without caching, causing Spark to recompute its lineage.. Redundant exchanges and scans in an execution plan often occur when a DataFrame or table is referenced multiple times in complex joins or transformations without being cached or persisted. Spark recomputes the lineage from storage for each reference, leading to duplicate scans and shuffles.

Answer

The analyst applied a broadcast hint to a table that exceeded the broadcast size configuration threshold.

Answer

The partition column data type mismatch forced Catalyst to duplicate the partition directories.

Answer

Automatic table compaction via OPTIMIZE was running concurrently during the execution phase.

Question 8

When importing data into Databricks using the 'Add Data' UI, what is the default file format for the created table if the source is a CSV file?

Accepted Answer

Delta. Databricks defaults to Delta Lake for all managed table creation because it provides ACID transactions, scalability, and time travel. Even when importing simple CSV files, Databricks automatically converts them into the Delta format to ensure that the data is queryable with high performance and reliability. Recognizing this default behavior is essential for understanding how Databricks standardizes data storage behind the scenes for all analytical workloads.

Answer

CSV

Answer

Parquet

Answer

JSON

Question 9

Which Databricks component is the primary interface for collaborative, interactive data analysis and visualization?

Accepted Answer

Databricks Notebooks. Databricks Notebooks are the core collaborative environment where users write code, execute queries, and create visualizations. They support polyglot programming (SQL, Python, R, Scala) and are integrated directly with the platform's compute resources. For analysts, notebooks provide a persistent workspace to document workflows, share insights with stakeholders, and execute complex data transformations, making them the most important tool for iterative data analysis and team collaboration within the Databricks environment.

Answer

Databricks SQL Warehouse

Answer

Delta Live Tables

Answer

Unity Catalog

Question 10

Which of the following describes the purpose of 'Query History' in Databricks SQL?

Accepted Answer

To monitor query performance and troubleshoot errors.. Query History provides a comprehensive log of all queries executed against the SQL Warehouse. It includes critical information such as execution time, user identity, and status. For analysts, this tool is indispensable for identifying slow-running queries, debugging execution errors, and auditing data access. By reviewing the query profile, analysts can gain deep insights into how their code is performing, which is vital for continuous performance tuning and optimizing SQL code in production.

Answer

To permanently store query results in cold storage.

Answer

To manage user access permissions for tables.

Answer

To automatically optimize slow SQL code.