Databricks-DA-Assoc Practice Test 3 — 30 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;

Question 11

A business user asks a Genie Space, 'Show me the revenue by product.' The model returns an error. What is the most common reason for this failure in a properly configured environment?

Accepted Answer

The model cannot distinguish between multiple columns that could mean 'revenue'.. The most common failure in Genie occurs when the model cannot uniquely identify the requested columns or the business logic for 'revenue' is ambiguous. If multiple columns could represent revenue or if the product identifier is unclear, the model will struggle. Clarifying the schema via table comments and standardizing the definition of key metrics in the data layer is the standard fix for this ambiguity issue.

Answer

The user does not have access to the underlying SQL Warehouse.

Answer

The Genie Space is currently set to 'read-only' mode.

Answer

The user is using the wrong language for the query.

Question 12

When sharing data with external organizations using Delta Sharing, which component manages the secure exchange of data?

Accepted Answer

Unity Catalog metastore.. Delta Sharing is an open protocol that enables the secure exchange of data across different organizations without requiring them to have accounts in the same Databricks workspace. The Unity Catalog metastore acts as the central control point for these shares. By creating a 'Share' object, an administrator defines exactly which tables are shared, and the recipient uses a token to access that data securely.

Answer

Workspace-local Hive metastore.

Answer

Databricks File System (DBFS) Root.

Answer

User-managed S3 bucket policies.

Question 13

An analyst needs to query a table that has many small files resulting from frequent streaming ingestions. Which command is most appropriate to consolidate these files into larger, more efficient files for future analytical queries?

Accepted Answer

OPTIMIZE table_name. The OPTIMIZE command is the standard way to compact small files in Delta tables. It merges multiple small files into larger files, which reduces metadata overhead and improves read performance. By combining this with Z-Ordering, the data layout is optimized for common query patterns, ensuring that the SQL engine reads only the necessary data blocks during execution, which is vital for efficient data warehousing.

Answer

VACUUM table_name

Answer

REFRESH TABLE table_name

Answer

ALTER TABLE table_name REORGANIZE

Question 14

A data analyst is working in a Databricks workspace and needs to ensure that their notebook code is version-controlled and collaborative. Which TWO actions should they take?

Accepted Answer

Use the built-in Databricks Repos feature to connect to a Git provider.. Integrating version control and collaboration is essential for professional data engineering workflows. By linking notebooks to Git providers, analysts ensure that changes are tracked, auditable, and easily revertible. This standard practice prevents accidental data loss and fosters teamwork, allowing multiple contributors to manage code repositories effectively within the Databricks environment while maintaining a single source of truth for all analytical project documentation and complex transformation logic.

Answer

Copy and paste code into a local text file.

Answer

Create a new notebook file for every code change.

Answer

Download the notebook to their local machine every hour.

Question 15

What is the primary function of a Storage Credential in Unity Catalog?

Accepted Answer

To provide a secure way to access cloud storage locations.. A Storage Credential is an object in Unity Catalog that encapsulates long-term cloud provider credentials (like an AWS IAM role or Azure Service Principal). It allows Databricks to access data in cloud storage without requiring users to configure individual cloud credentials on every cluster. This centralizes security by keeping sensitive cloud keys away from end users and developers.

Answer

To encrypt files stored in the S3 bucket.

Answer

To manage user passwords for the Databricks workspace.

Answer

To define table schema definitions for external tables.

Question 16

A data analyst is querying a large Delta table containing billions of rows of clickstream data. The table is frequently queried using a timestamp column. To optimize range queries on this timestamp, which Delta Lake feature should the table builder implement?

Accepted Answer

Applying Z-Order clustering on the timestamp column to improve multi-dimensional data skipping.. Delta Lake features like Z-Ordering colocate related information in the same set of files based on specified columns. However, for efficient range filtering on high-cardinality timestamps, Liquid Clustering or traditional Z-Ordering combined with proper partitioning or data skipping statistics ensures the engine reads minimal files.

Answer

Enabling column mapping to allow renaming columns without rewriting underlying parquet files.

Answer

Setting the file size compaction threshold to the maximum allowable limit of 1 gigabyte.

Answer

Adding an explicit check constraint on the timestamp column to reject future dates.

Question 17

Which THREE of the following are essential components of a well-architected AI/BI Genie Space setup?

Accepted Answer

Detailed documentation in the Unity Catalog table/column comments.. A robust Genie Space setup relies on clear semantic metadata, optimized data access, and controlled user permissions. By defining clear table descriptions in Unity Catalog, you provide the LLM with the context needed for accurate SQL generation. Combining this with granular permissions and well-crafted system instructions ensures the solution is both secure and reliable. These three pillars create a scalable environment that allows users to self-serve insights while maintaining strict organizational data governance.

Answer

Providing the end-users with direct read access to all raw source tables.

Answer

Granting all users 'owner' privileges on the Genie Space.

Question 18

An analytics team is building an AI/BI Genie space to allow business users to query sales data using natural language. After setting up the base tables, the initial user questions return inaccurate filter values because the LLM struggles to map colloquial region names to the exact string codes stored in the database. What is the most effective feature within AI/BI Genie to resolve this mapping issue without modifying the underlying physical tables?

Accepted Answer

Configure table and column descriptions, add explicit instructions, and provide verified queries demonstrating the correct region mappings.. Adding instructions, descriptions, and verified queries to the Genie space gives the underlying model precise semantic context about colloquial terms, business definitions, and expected filters. This targeted guidance steers natural language translation toward the correct database columns and values without requiring costly data transformations or restructuring of source tables in the data lakehouse.

Answer

Create a materialized view containing hardcoded string mappings for every possible regional variation queried by business users.

Answer

Enable automatic schema inference and let the Genie space rebuild its internal vector embeddings from scratch overnight.

Answer

Modify the source table column constraints to reject any query that does not use the exact database region code.

Question 19

An analyst notices that the Genie space is consistently ignoring a specific column during query generation. What is the most effective way to force the model to consider this column?

Accepted Answer

Add a column comment in Unity Catalog and mention it in instructions. LLMs can sometimes overlook columns if they aren't described clearly in the metadata or instructions. Providing explicit, high-quality descriptions in the table metadata (within Unity Catalog) or adding a specific note in the Genie space instructions ensures the model recognizes the column's semantic value and usage context, forcing it to include the information in its reasoning process during user interactions.

Answer

Rename the column to start with 'Required_'

Answer

Hard-code the column in the SQL Warehouse settings

Answer

Delete and recreate the Genie space

Question 20

Which THREE features are provided by AI/BI Genie spaces to help business users analyze data independently?

Accepted Answer

Conversational natural language querying. Genie spaces empower business users by abstracting complex SQL development. They provide a natural language interface for asking questions, automatic visualization of results for immediate insight, and a feedback loop where users can rate answers to refine the model's future performance. These features collectively reduce the burden on data teams while fostering a self-service culture where users can safely interact with governed data assets.

Answer

One-click deployment of production ETL pipelines

Answer

Automatic generation of unit tests for Python code