Databricks-DA-Assoc Practice Questions 2 — 50 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.

Question 11

A data analyst runs an Apache Spark SQL query against a Delta Lake table and notices that partition pruning is not occurring despite filtering on the partitioned column 'region'. The table definition shows 'region' is stored as a string, but the query passes an integer value. How does this type mismatch impact query execution?

Accepted Answer

Catalyst fails to match directory paths to filter values, forcing a full table scan across all partitions.. Type mismatches between predicate filters and partition columns prevent catalyst optimization from pushing down partition filters. Spark attempts implicit casting, which often invalidates static partition pruning mechanisms, forcing the engine to scan every single data file across all partitions, drastically increasing input data volume and slowing down query execution performance.

Answer

Spark automatically casts the column to match the filter type without impacting performance.

Answer

The query fails immediately with a TypeMismatch analysis exception before any jobs are launched.

Answer

Delta Lake automatically rewrites the underlying table schema to match the incoming query filter type.

Question 12

An analyst is troubleshooting a query that hangs indefinitely during a join. Which TWO metrics in the Query Profile should the analyst examine to diagnose the issue?

Accepted Answer

Shuffle Read/Write bytes.. When a join hangs, it is often due to massive data movement (shuffle) or extreme data skew. Monitoring the shuffle size helps determine if the network or I/O is saturated. Checking the 'Max vs Median' row count helps identify skew. These metrics are the most reliable indicators of join failure, enabling analysts to decide whether to adjust join keys, use broadcast hints, or address underlying data distribution issues.

Answer

The number of users logged into the workspace.

Answer

The total number of tables in the schema.

Answer

The total size of the database logs.

Question 13

Which TWO of the following are true about Databricks SQL Alerts? (Choose two)

Accepted Answer

Alerts can trigger external workflows using webhooks.. Alerts in Databricks SQL are a critical component of a proactive monitoring strategy. By tracking the results of a query, they provide timely notifications that help analysts and business users respond to events immediately. Understanding the configuration of thresholds and notification channels is essential for building a reliable operational dashboard suite that moves beyond passive data reporting to active, event-driven data management.

Answer

Alerts only function if the dashboard is currently open.

Answer

Alerts automatically delete data that triggers the condition.

Answer

Alerts are only compatible with Python-based notebooks.

Question 14

Which of the following is the best strategy for refining Genie space performance when the model consistently struggles with complex joins?

Accepted Answer

Add primary/foreign key definitions and clarify join logic in instructions. When a Genie space fails on multi-table joins, the underlying issue is often a lack of clear relationship definitions. By updating the table metadata in Unity Catalog (such as primary and foreign key constraints) and using the Genie instructions to explicitly define how tables should be joined, the developer provides the model with the necessary 'map' to build complex queries accurately.

Answer

Flatten all tables into a single wide table

Answer

Increase the number of allowed tables

Answer

Switch to a larger, more expensive SQL Warehouse

Question 15

Which primary resource must be granted to users for them to successfully interact with an AI/BI Genie Space to generate insights?

Accepted Answer

CAN_USE permission on the Genie Space and SELECT access on underlying data.. Users require explicit access permissions to both the Genie Space itself and the underlying data assets (tables or views) it exposes. Without sufficient SELECT privileges on the underlying Unity Catalog assets, the Genie engine cannot execute the SQL generated to answer natural language questions. Managing these permissions centrally through Unity Catalog ensures that data governance and security policies are consistently applied, even when users are interacting via natural language interfaces.

Answer

CAN_EDIT permission on the Genie Space.

Answer

Global admin access within the Databricks Workspace.

Answer

Access to the underlying SQL Warehouse only.

Question 16

An analyst is preparing to upload a small local CSV file to a Databricks workspace. Which tool is most appropriate for a quick, one-time upload without requiring infrastructure setup?

Accepted Answer

Databricks Add Data UI. The 'Add Data' UI in Databricks provides a user-friendly interface to upload small files directly from a local machine to DBFS. This approach is ideal for exploratory analysis or small datasets where setting up external storage or complex ingestion pipelines is unnecessary. Understanding this tool allows analysts to quickly pivot from local analysis to platform-based workflows without needing deep knowledge of cloud storage configurations.

Answer

Auto Loader

Answer

COPY INTO command

Answer

Databricks Connect

Question 17

Which THREE of the following are benefits of using Delta Lake over standard Parquet files in Databricks?

Accepted Answer

ACID transaction support. Delta Lake introduces ACID transactions, time travel, and schema enforcement to the data lake, transforming it into a lakehouse. These features are critical for maintaining data integrity in complex production environments. Understanding these benefits allows analysts to make informed decisions about storage formats, ensuring that the data platform remains reliable, scalable, and capable of handling complex analytical requirements without risking data corruption or inconsistency.

Answer

Automatic data compression to non-standard formats

Answer

The ability to run queries without a compute engine

Question 18

An analyst is using the Spark DataFrame API to read a large JSON dataset. The dataset contains nested fields that are causing schema inference to fail. Which approach best resolves this?

Accepted Answer

Define a schema explicitly using StructType.. When dealing with complex, nested JSON, relying on automated schema inference often fails or results in poor performance. Manually defining a schema using StructType allows the analyst to explicitly control data types, including handling nested structures. This approach is more robust for production-grade pipelines where data consistency is required, as it prevents schema mismatch errors during ingestion and improves overall query performance and type safety within the Spark environment.

Answer

Use the option 'mergeSchema' set to true.

Answer

Convert the JSON to CSV before reading it.

Answer

Increase the number of partitions during read.

Question 19

Which Databricks SQL object allows an analyst to save a specific query result set and share it with other users, ensuring they see a snapshot of the data at the time of execution?

Accepted Answer

Query. In Databricks SQL, a Query is the primary object for executing SQL statements. When a user runs a query, the results are stored in the results cache. By sharing the query, other users can access the saved results or re-run the query to obtain the latest data. Understanding how to manage and share query objects is essential for collaborative data analysis and maintaining consistent reporting across organizational teams.

Answer

Dashboard

Answer

Alert

Answer

SQL Warehouse

Question 20

A data analyst writes a complex Spark SQL query involving multiple window functions, CTEs, and aggregations. To debug the query performance, the analyst wants to inspect the logical optimization phases applied by the Catalyst optimizer. Which SQL command can the analyst use to view the unoptimized logical plan, optimized logical plan, and physical execution plan?

Accepted Answer

EXPLAIN EXTENDED SELECT ...;. Spark SQL provides the EXPLAIN command to inspect the underlying plans generated by the Catalyst optimizer. By specifying the EXTENDED or FORMATTED modifier, analysts can review parsed logical plans, analyzed logical plans, optimized logical plans, and final physical execution plans.

Answer

DESCRIBE EXTENDED table_name;

Answer

SHOW QUERY PLAN SELECT ...;

Answer

ANALYZE TABLE table_name COMPUTE STATISTICS;