Databricks-DA-Assoc Hard Questions — 25 Difficult Questions

Question 1

An analyst is preparing a Genie Space and wants to ensure users get the most accurate answers while maintaining security. Which TWO actions should the analyst take? (Choose two)

Accepted Answer

Exclude highly sensitive columns that are not needed for general analysis.. Proper curation of the Genie Space is vital for performance and security. By including only the necessary columns and providing explicit instructions, the analyst reduces noise and ensures the AI model operates within the boundaries of the organization's data governance framework. These steps directly impact the quality of the SQL generated and the safety of the information exposed to end-users via the AI interface.

Answer

Enable 'auto-query' for all users to maximize data exploration.

Answer

Grant all users the 'Owner' role on the Genie Space to allow for maximum customization.

Answer

Remove all constraints and indexes to allow the AI to move faster.

Question 2

A data analyst is using a Databricks SQL warehouse to query a table that is protected by row-level security using dynamic views in Unity Catalog. The analyst has SELECT on the view but not on the underlying table. When querying the view, the analyst receives an error about insufficient privileges on the base table. What is the most likely cause?

Accepted Answer

The view owner does not have SELECT privilege on the underlying table, so the view cannot access it.. In Unity Catalog, views execute with the permissions of the view owner, not the querying user. If the view owner lacks SELECT on the underlying table, the view cannot access the data, resulting in a permission error for any user. The analyst's privileges on the view are sufficient, but the owner must have the necessary privileges on the base table.

Answer

The analyst needs to be granted SELECT on the base table directly to query the view.

Answer

The view is defined with SQL SECURITY INVOKER, which requires the analyst to have base table access.

Answer

The view was created without the SECURITY DEFINER clause, so the analyst's permissions are used to access the base table.

Question 3

A security administrator is preparing to share sensitive customer data across different Databricks accounts using Delta Sharing. The administrator wants to ensure that specific personally identifiable information columns are completely hidden from the recipient without creating a separate filtered copy of the Delta table. Which approach fulfills this requirement efficiently?

Accepted Answer

Configure Unity Catalog column masks on the shared table before publishing the share to the designated Delta Sharing recipient.. Column masking in Delta Sharing allows data providers to apply transformation functions or mask sensitive attributes dynamically for external recipients. This feature ensures compliance with data privacy regulations by preventing unauthorized data exposure without incurring the storage costs and synchronization overhead of maintaining derivative physical tables for sharing workflows.

Answer

Export the table to CSV files, manually redact the sensitive columns using a script, and upload the redacted files to an external cloud bucket for the recipient.

Answer

Revoke read permissions on the entire table and share a derived view created using the CREATE VIEW statement in the recipient's metastore.

Answer

Set up an Apache Spark caching job that overwrites the original table with null values in sensitive columns whenever an external recipient runs a query.

Question 4

An analyst is building an AI/BI Genie space over a Unity Catalog schema containing both `orders` and `customers` tables. Business users often ask questions that require joining the two tables on `customer_id`. During testing, Genie frequently generates queries that join on the wrong column or omit the join entirely, producing inflated row counts. The analyst has already verified that both tables are selected and that column names are clear. Which action is most likely to correct Genie's join behavior?

Accepted Answer

Add example SQL queries to the Genie space that explicitly join `orders` and `customers` on `customer_id`.. When Genie produces incorrect joins despite clear table and column names, the most reliable correction is to supply example SQL that demonstrates the intended join condition. Examples give the model a concrete pattern to imitate, including the join key and aliases. General instructions help but lack the precision of a worked query, and metadata constraints are not automatically leveraged during generation.

Answer

Rename `customer_id` in both tables to `cust_key` so the join column is unique across the schema.

Answer

Add a general instruction stating that the `orders` and `customers` tables should always be joined when both are referenced.

Answer

Create a foreign key constraint between `orders.customer_id` and `customers.customer_id` in Unity Catalog.

Question 5

Refer to the exhibit. You are attempting to enable Change Data Feed (CDF) on an existing Delta table but receive this error. Why is this error occurring?

Accepted Answer

The table requires column mapping to track physical schema evolution for CDF.. Change Data Feed (CDF) tracks row-level changes, which requires a stable mapping between column names and their underlying physical file data. If the table was created without column mapping, it cannot safely track changes if column names are altered. Setting 'delta.columnMapping.mode' to 'name' decouples the column names from their physical file identifiers, which is a prerequisite for the metadata tracking required by the CDF feature.

Answer

The table is not in the Delta format.

Answer

The table has too many rows to enable CDF.

Answer

CDF is only supported on tables with primary keys.

Question 6

A data engineering team has created a managed Delta table in Unity Catalog. Which TWO of the following statements accurately describe the characteristics and management of this managed table? (Choose two)

Accepted Answer

Dropping the managed table using a DROP TABLE statement automatically deletes the underlying data files from cloud storage.. Managed tables in Unity Catalog store both metadata and underlying data files within the metastore's designated root storage location. Unity Catalog fully manages the lifecycle of these files, meaning dropping the table deletes both the catalog metadata and the underlying Parquet and Delta log files automatically.

Answer

Managed tables require the user to explicitly define a custom LOCATION clause pointing to an external cloud storage path.

Answer

External tools cannot read managed table data files directly because Unity Catalog encrypts all underlying Parquet files at rest using proprietary keys.

Answer

Converting a managed table to an external table can be performed in-place using an ALTER TABLE RENTER statement.

Question 7

Refer to the exhibit. An analyst is reviewing a cluster configuration JSON. If the cluster is currently idle, what happens after 40 minutes of inactivity?

Accepted Answer

The cluster terminates.. Databricks clusters with auto-termination enabled will shut down after the specified inactivity threshold is met. In this configuration, the 'autotermination_minutes' is set to 30. Therefore, once the cluster remains idle for 30 minutes, it will automatically terminate. By the 40-minute mark, the cluster has already completed the termination sequence, ensuring no additional costs are incurred for idle resources beyond the defined grace period.

Answer

The cluster scales down to 2 workers.

Answer

The cluster remains idle at 2 workers.

Answer

The cluster scales up to 8 workers.

Question 8

Which TWO of the following are benefits of using Unity Catalog with Databricks SQL?

Accepted Answer

Centralized access control for all data assets.. Unity Catalog provides a unified governance layer for data and AI assets across the Databricks workspace. It enables fine-grained access control, centralized auditing, and simplified data discovery. By implementing Unity Catalog, organizations can ensure compliance and security while allowing analysts to collaborate effectively. This is a critical component of modern data platforms, providing the necessary visibility and control required by data teams to manage data assets securely at scale.

Answer

Automatic translation of SQL to Python code.

Answer

Increased query execution speed for simple SELECTs.

Answer

Ability to bypass authentication for local files.

Question 9

A data analyst is using the Databricks Add Data UI to import a CSV file from cloud storage into a Delta table. The analyst wants to ensure the import process handles the data correctly. Which two actions can the analyst perform directly in the Add Data UI? (Choose two.)

Accepted Answer

Specify a custom delimiter for the CSV file.. The Add Data UI allows analysts to preview data, adjust column types, and specify a custom delimiter during import. It does not support scheduling, DDL schema definition, or schema evolution, which are features of other tools like Auto Loader or Jobs.

Answer

Set up a scheduled job to automatically ingest new files from the same location.

Answer

Enable schema evolution for future files.

Answer

Define a custom schema using DDL statements.

Question 10

A dashboard author wants viewers to click a bar in a Databricks SQL chart and have the dashboard display a second chart filtered to that bar's category. Which feature should the author configure to achieve this interactive behavior?

Accepted Answer

Dashboard cross-filtering or a dashboard-level filter driven by the selected value. Interactive cross-chart behavior comes from dashboard filtering driven by a selected value, so clicking a bar's category updates the other chart. Scheduled refresh only reloads data, a parameter default is static, and separate per-category queries create static charts, none of which respond to a viewer's selection.

Answer

A scheduled refresh on the dashboard so the second chart reloads on an interval

Answer

A separate query per category added as individual visualizations to the dashboard

Answer

A parameter default value set to a specific category in the second chart's query

Question 11

Which THREE actions occur when using Auto Loader with schema evolution enabled? (Choose three)

Accepted Answer

New columns found in the source files are automatically added to the target table.. Schema evolution is a powerful feature of Auto Loader that allows pipelines to adapt to changes in upstream data without requiring manual code updates. By understanding how the schema is managed, detected, and reconciled, analysts can build flexible ingestion systems that reduce maintenance overhead. This is critical in modern data environments where upstream sources frequently add or rename columns, requiring the platform to dynamically update downstream tables to accommodate the new structure.

Answer

Existing columns with data type conflicts cause the pipeline to stop immediately.

Answer

The schema is inferred from the metadata of the cloud storage provider.

Question 12

A data analyst must load a one-time snapshot of a JDBC-accessible PostgreSQL database into a Delta table in Unity Catalog using a Databricks notebook. The analyst wants to minimize cluster memory pressure and enable downstream predicate pushdown on the resulting Delta table. Which two configuration choices should the analyst apply? (Choose two.)

Accepted Answer

Write the DataFrame using df.write.format('delta').mode('overwrite').saveAsTable('catalog.schema.tbl') so the snapshot is materialized as a managed Delta table.. Parallel JDBC reads via partitionColumn, lowerBound, upperBound, and numPartitions split the extraction across executors, which reduces per-task memory pressure and shortens load time. Writing the resulting DataFrame with the Delta writer using saveAsTable into a Unity Catalog three-part name materializes the snapshot as a managed Delta table whose statistics support downstream predicate pushdown. Together these choices address both the read bottleneck and the target table's query performance.

Answer

Read the source using spark.read.jdbc with a single connection and rely on Delta Lake's automatic file compaction to parallelize the load.

Answer

Use CREATE TABLE ... USING DELTA LOCATION to register the Delta table in Unity Catalog, relying on the managed flag to preserve the data.

Answer

Enable spark.databricks.delta.optimizeWrite.enabled and set spark.sql.adaptive.enabled to false to force a single shuffle partition.

Question 13

A data analyst is loading a CSV dataset into a Delta table using spark.read.csv with header=true and inferSchema=true. The CSV contains an ID column whose values are numeric but must be preserved as strings to avoid losing leading zeros. The analyst notices that the resulting Delta table has the ID column typed as bigint, and downstream joins against a string-typed source are failing. Which change should the analyst make to the load to produce the correct schema?

Accepted Answer

Set inferSchema to false and rely on the default all-string schema, then cast only the columns that need numeric types.. Type inference in the CSV reader examines sample values and will classify a numeric-looking ID column as bigint, dropping leading zeros. Disabling inference makes every column a string, after which the analyst can cast only the columns that truly need numeric types. This yields a Delta table whose ID column is string-typed, preserving the values and allowing joins against the string-typed source to succeed.

Answer

Add the option mode='PERMISSIVE' to the read so the ID values are kept as strings while other columns are inferred.

Answer

Pass a schema via the schema option that declares the ID column as StringType, and let the remaining columns be inferred automatically.

Answer

Set the option columnNameOfCorruptRecord to 'ID' so the CSV reader treats the ID field as an opaque string column.

Question 14

An analyst is reviewing a slow-running query and wants to identify potential bottlenecks. Which TWO of the following metrics in the Databricks SQL query profile are most indicative of inefficient data distribution?

Accepted Answer

Shuffle Read/Write bytes are significantly higher than the input data size.. Data skew and excessive shuffling are primary causes of performance degradation. High 'Shuffle Read' and 'Shuffle Write' values indicate large amounts of data moving across the network, while skewed task durations signal that one executor is doing significantly more work than others. Identifying these metrics early allows analysts to apply techniques like salting keys or partitioning to balance the workload across the cluster.

Answer

The 'Scan' node shows high throughput and low execution time.

Answer

The query shows a high number of file metadata operations.

Answer

The query plan indicates a broadcast hash join is being used.

Question 15

A user wants to import a local 50MB CSV file into a Databricks workspace for a quick ad-hoc analysis. Which TWO methods are available within the Databricks UI to accomplish this task directly?

Accepted Answer

Using the 'Create or modify table from file upload' feature in the 'Add' menu.. Databricks provides several entry points for local file uploads to facilitate rapid analysis. The 'Create or modify table from file upload' UI is the primary method for non-programmatic ingestion, while Unity Catalog Volumes offer a modern way to manage non-tabular data. Both options allow analysts to bypass complex cloud storage configurations when dealing with small, local datasets for immediate exploratory data science tasks.

Answer

Using the Databricks File System (DBFS) CLI to push the file to the workspace.

Answer

Using the SQL Editor to copy and paste the raw text into a new table.

Answer

Using the Workspace Sidebar 'Import' button to select the local CSV file.

Question 16

A data analyst needs to ensure that sensitive information in a table is not visible to unauthorized users. Which Unity Catalog feature is the most efficient way to achieve this at the row level?

Accepted Answer

Apply Row Filters and Column Masks in Unity Catalog.. Row-level security (RLS) and column-level security are fundamental features of Unity Catalog that enable fine-grained access control. By applying these policies, organizations can ensure compliance with privacy regulations while maintaining data usability. Understanding how to define these filters allows analysts to secure data effectively without creating multiple copies of the same dataset for different user groups, which simplifies maintenance and ensures consistency.

Answer

Create a separate view for each user group.

Answer

Use hard-coded SQL 'WHERE' clauses in every notebook.

Answer

Move sensitive data to a completely separate, private workspace.

Question 17

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 18

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 19

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 20

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.