Databricks-DA-Assoc Practice Test 5 — 10 Questions

Question 1

An analyst executes a query that joins a large fact table with a small dimension table in Databricks. The analyst wants to force the Catalyst optimizer to broadcast the small dimension table to avoid an expensive shuffle join. Which standard Spark SQL hint should be included in the query text?

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

Accepted Answer

/*+ BROADCAST(dimension_table) */. Query hints allow analysts to provide explicit instructions to the Catalyst optimizer regarding physical execution plans. The MAPJOIN or BROADCAST hint directs the engine to broadcast the smaller table to all worker nodes, eliminating the need for a costly shuffle exchange across the network and greatly accelerating join execution performance.

Answer

/*+ MERGE(dimension_table) */

Answer

/*+ SHUFFLE_HASH(dimension_table) */

Answer

/*+ CACHE(dimension_table) */

Question 2

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 3

A data analyst needs to query a Delta table named sales and retrieve only the rows where the sale_date is in the year 2023. The table is partitioned by sale_date. Which SQL statement will most efficiently return the required data?

Accepted Answer

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01'. A range predicate directly on the partition column allows Databricks to prune partitions, reading only the data for 2023. Applying a function such as year() or cast() to the column disables pruning and forces a full scan. Therefore, the range condition is the most efficient.

Answer

SELECT * FROM sales WHERE cast(sale_date as string) LIKE '2023%'

Answer

SELECT * FROM sales WHERE year(sale_date) = 2023

Answer

SELECT * FROM sales WHERE sale_date LIKE '2023%'

Question 4

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 5

An analyst needs to manage data lifecycle and performance in Databricks SQL. Which TWO of the following tasks are best achieved using the Liquid Clustering feature?

Accepted Answer

Optimize data skipping for frequently filtered columns.. Liquid Clustering is a flexible way to manage data layout in Delta tables, replacing static partitioning and Z-Ordering. It automatically adapts to data distribution changes over time without manual intervention. Choosing the correct clustering columns ensures that queries filter data efficiently while minimizing the storage overhead associated with maintaining high-cardinality partitions, which often lead to small-file problems in large datasets.

Answer

Enforce strict schema validation during ingestion.

Answer

Implement row-level security policies.

Answer

Manage concurrent write conflicts in Delta tables.

Question 6

Refer to the exhibit. The execution plan shows a SortMergeJoin with two Exchange nodes. What is the primary cause of the performance impact here?

Accepted Answer

The tables are being re-shuffled to align rows on the join key, causing network overhead.. The presence of two Exchange nodes indicates that the data is being repartitioned across the cluster to align join keys on the same worker nodes. This full shuffle is costly in terms of network I/O. Recognizing this pattern helps analysts understand why large joins can be slow and justifies investigating whether tables can be pre-partitioned to avoid these runtime exchanges.

Answer

The join keys are not sorted, forcing a full scan of both tables.

Answer

The optimizer is unable to choose a broadcast join because both tables are too large.

Answer

The cluster is running out of memory due to the high number of partitions.

Question 7

A data analyst runs `DROP TABLE IF EXISTS main.default.customer_orders;` in a Databricks SQL warehouse. The table is a managed Delta table in Unity Catalog and the analyst's identity has no applicable owner or admin privileges. What happens?

Accepted Answer

The statement fails with an insufficient privileges error because DROP TABLE requires ownership of the table or MANAGE on the parent schema.. Removing a managed Unity Catalog table is a privileged DDL operation. The executing identity needs to be the table owner or hold MANAGE on the parent schema, along with USE CATALOG and USE SCHEMA on the enclosing objects. The IF EXISTS qualifier only prevents a not-found error; it does not relax authorization, so the drop is rejected before any data or metadata is touched.

Answer

The statement succeeds but the table is moved to a recycle bin schema where it can be restored for 30 days by any workspace user.

Answer

The statement succeeds and only the table's metadata is removed while the underlying Parquet data files remain in the managed storage location.

Answer

The statement succeeds because IF EXISTS implicitly converts the operation into a metadata-only soft delete that any schema user can perform.

Question 8

An analyst needs to combine data from two tables that share a common key. Which join type returns all rows from both tables, even if there is no match in the other table?

Accepted Answer

FULL OUTER JOIN. A full outer join is the only join type that preserves every record from both source tables, filling in NULL values where matches do not exist. This is essential for comprehensive data auditing and reconciliation tasks where the analyst needs to identify missing records or orphans in either dataset. Understanding the impact of NULLs in the result set is crucial for accurate analysis.

Answer

INNER JOIN

Answer

LEFT JOIN

Answer

CROSS JOIN

Question 9

A data analyst is creating a new AI/BI Genie Space for the sales team. They want to add a trusted table that contains curated sales data. Which of the following is the correct way to add a table to the Genie Space?

Accepted Answer

In the Genie Space configuration, navigate to the Data tab and add the table from the Unity Catalog.. To add a table to an AI/BI Genie Space, you must use the Genie Space configuration interface. The Data tab allows you to select tables and views from Unity Catalog, ensuring the Space has access to the correct data sources. This is the only supported method that integrates the table with the natural language query engine, providing accurate and secure responses.

Answer

Use the Databricks SQL query editor to run a CREATE TABLE statement.

Answer

Use the Databricks CLI to run 'databricks genie add-table' with the table name.

Answer

Create a Databricks notebook that reads the table and register it as a temporary view.

Question 10

What is the primary purpose of adding 'Instructions' to a Genie space in Databricks?

Accepted Answer

To provide context and guidance for query generation. Instructions serve as a system prompt that guides the AI's behavior, defining business logic, specific terminology, and preferred analytical approaches. By setting these instructions, developers improve the accuracy of query generation, ensuring the AI understands company-specific jargon and data relationships that aren't inherently obvious from table schemas alone. This significantly enhances the user experience and reliability of the natural language interface.

Answer

To automate the deployment of SQL Warehouses

Answer

To enable automatic updates for underlying tables

Answer

To store user credentials for data access