Databricks-DA-Assoc Practice Test 7 — 10 Questions

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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;