Databricks-DA-Assoc Practice Test 22 — 15 Questions

Question 1

Which component of the Databricks platform acts as the central entry point for users to manage their data, notebooks, and experiments?

Accepted Answer

The Databricks Workspace. The Workspace is the primary user interface in Databricks where data analysts interact with the platform. It provides a unified environment for writing code in notebooks, organizing folders, managing libraries, and viewing experiments. Understanding the Workspace is essential for navigating the platform, as it organizes all collaborative assets and provides the tools necessary for data exploration, cleaning, and visualization across the entire data lifecycle.

Answer

The Control Plane

Answer

The Data Plane

Answer

The Unity Catalog

Question 2

Which visualization type is most appropriate for displaying the distribution of a single numerical variable across continuous intervals in a Databricks SQL dashboard?

Accepted Answer

Histogram. Histograms are the standard statistical tool for visualizing the frequency distribution of numerical data. By binning data into intervals, analysts can easily identify patterns, skewness, or outliers within a dataset. Understanding distribution is fundamental in data analysis for characterizing datasets before applying advanced statistical models or performing deeper exploratory data analysis within the Databricks environment.

Answer

Pie chart

Answer

Scatter plot

Answer

Funnel chart

Question 3

Refer to the exhibit. The Genie Space output is consistently truncated. What is the most likely cause?

Accepted Answer

The 'max_tokens' limit is too low for the complexity of the output.. The 'max_tokens' parameter is set to 500, which is relatively small for complex SQL generation tasks. If the generated query is long, or if the model provides a detailed explanation along with the SQL, it will hit this limit and truncate the response. Increasing the 'max_tokens' limit will allow the model to provide complete responses, ensuring that the SQL is valid and the context provided is not cut off mid-sentence.

Answer

The temperature is too low, causing the model to finish early.

Answer

The model version is incompatible with the SQL Warehouse.

Answer

The SQL Warehouse is timing out during the query generation.

Question 4

An administrator needs to secure a table containing sensitive customer data. Which TWO options represent valid ways to restrict access using Unity Catalog?

Accepted Answer

Use GRANT SELECT on the table to specific users or groups.. Unity Catalog provides robust security primitives including standard SQL GRANT/REVOKE commands and dynamic masking. By using these features, administrators can define who sees what data without altering the underlying data files. These two methods are the standard, best-practice approaches for ensuring that sensitive data is protected while maintaining a clean, performant analytics environment for end users.

Answer

Delete the sensitive rows from the table manually.

Answer

Rotate the workspace passwords weekly.

Answer

Disable the table for all users except the owner.

Question 5

Which THREE features are provided by Unity Catalog to enhance data governance?

Accepted Answer

Centralized access control using standard SQL.. Unity Catalog is the centralized governance layer for Databricks. It provides three critical capabilities: a unified namespace for data across workspaces, centralized access control using standard SQL GRANT/REVOKE syntax, and automated data lineage tracking. These features ensure that data is secure, discoverable, and auditable across an entire organization. For analysts, this simplifies the process of finding data and ensures that security policies are consistently applied, regardless of the workspace or tool being used.

Answer

Direct management of physical cloud infrastructure servers.

Answer

Automatic generation of Power BI dashboards.

Question 6

An analyst runs a long-running query that fails with an 'Out of Memory' (OOM) error. Which approach is the most effective way to resolve this without changing the underlying data structure?

Accepted Answer

Resize the SQL Warehouse to a larger instance type. OOM errors in Databricks SQL often occur due to large joins or aggregations that exceed available executor memory. Using hints, such as a broadcast join hint, can instruct the engine to handle data movement differently. If a specific join is causing the OOM, forcing a broadcast join (for small tables) or increasing the memory of the SQL warehouse can provide the necessary resources to complete the task.

Answer

Increase the number of partitions to 10,000

Answer

Use a broadcast hint for large-to-large table joins

Answer

Reduce the number of columns selected in the query

Question 7

An analyst is running queries on a shared Databricks SQL Warehouse. Which TWO actions improve query performance by reducing the impact of high concurrency?

Accepted Answer

Enable Result Set Caching on the SQL Warehouse.. Managing concurrency in Databricks SQL involves both infrastructure configuration and query optimization techniques. By using materialized views or caching strategies, you reduce the compute load on the warehouse. These strategies prevent redundant processing of complex logic, ensuring that concurrent users receive faster responses without requiring the warehouse to constantly re-compute heavy analytical joins and aggregations on raw tables.

Answer

Use SELECT * exclusively in all production dashboards.

Answer

Increase the warehouse size to 4XL regardless of query complexity.

Answer

Always use ORDER BY in every subquery.

Question 8

Which option is recommended to ensure that a SQL Warehouse automatically terminates when no queries are being executed, thereby minimizing unnecessary costs?

Accepted Answer

Setting 'Auto-stop' in the Warehouse configuration.. Auto-stop is a fundamental cost-management feature for Databricks SQL. By configuring a reasonable auto-stop period, analysts can ensure that compute resources are released when idle. This is a critical best practice for maintaining a cost-efficient data platform, particularly for development or staging environments where queries may be sporadic. Proper use of auto-stop directly impacts the overall cloud bill and is an essential configuration step for every SQL Warehouse instance.

Answer

Manual shutdown via the CLI.

Answer

Using a Cron job to delete the warehouse.

Answer

Reducing the cluster size to zero.

Question 9

You want to create a temporary view that is only available for the duration of the current SQL session. Which command achieves this?

Accepted Answer

CREATE TEMPORARY VIEW view_name AS SELECT * FROM table. Temporary views are essential for breaking down complex analytical tasks into manageable chunks without cluttering the persistent metadata layer. By scoping the view to the session, you ensure that temporary tables do not interfere with other users or permanent database objects. This is a standard best practice for modularizing complex data preparation scripts before inserting them into final reporting tables.

Answer

CREATE VIEW view_name AS SELECT * FROM table

Answer

CREATE LOCAL VIEW view_name AS SELECT * FROM table

Answer

CREATE SESSION TABLE view_name AS SELECT * FROM table

Question 10

Refer to the exhibit. What is the most appropriate action to resolve this access issue?

Accepted Answer

Request the workspace administrator to grant appropriate privileges in Unity Catalog.. Refer to the exhibit. This error indicates a failure in the Unity Catalog permission model. When a user lacks the necessary 'USE' or 'SELECT' grants on a catalog, they are blocked from accessing any data within it. Understanding how Unity Catalog manages access at the catalog, schema, and table levels is vital for analysts to troubleshoot access issues and comply with organizational security policies.

Answer

Change the cluster configuration to use a different Spark version.

Answer

Move the data to a local file in DBFS.

Answer

Re-create the table in a different schema.

Question 11

A data analyst is working in a notebook and notices that the query results are inconsistent compared to an earlier run, despite no code changes. What is the most likely cause?

Accepted Answer

The underlying data files are being modified by a concurrent process.. Inconsistent results often stem from the underlying data being updated concurrently without proper versioning or isolation. Databricks provides ACID transactions via Delta Lake. If the analyst is querying raw files instead of Delta tables, or if the table is being updated by another process, the results might vary. Understanding how Delta Lake handles snapshot isolation is crucial for ensuring that analytical reports remain consistent and reproducible over time.

Answer

The notebook is not using a SQL Warehouse.

Answer

The cluster has automatically terminated and restarted.

Answer

The notebook needs more memory to process the dataset.

Question 12

An analyst is using Databricks SQL and wants to optimize query performance for large-scale joins. Which TWO actions should the analyst perform to improve the performance of join operations involving large Delta tables?

Accepted Answer

Run ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS join_key. For large joins, Databricks SQL relies on statistics and efficient data layout. Collecting accurate table statistics allows the Cost-Based Optimizer (CBO) to select the most efficient join type, such as Broadcast or Shuffle Hash. Additionally, Z-Ordering on join keys ensures that related data is physically colocated, drastically reducing data shuffling across the cluster nodes during execution, which leads to faster query runtimes and better resource utilization.

Answer

Manually partition the table by the join key column

Answer

Increase the number of partitions to the maximum allowed

Answer

Set the table format to Parquet instead of Delta

Question 13

When reviewing a query profile, an analyst sees a 'Broadcast Hash Join'. What does this tell the analyst about the data being joined?

Accepted Answer

One of the tables is small enough to fit in memory.. A Broadcast Hash Join indicates that one side of the join is small enough to be sent to all executor nodes. This is an extremely efficient join type as it eliminates shuffling. For analysts, recognizing this confirms that the optimizer has successfully identified a small table, which is a positive performance indicator. If a join is expected to be small but isn't broadcast, the analyst may need to check table statistics.

Answer

The join is extremely memory-intensive.

Answer

The query is performing a cross join.

Answer

The data is skewed and needs re-partitioning.

Question 14

An analyst wants to see the unique values in a column named 'category'. Which SQL command should they use?

Accepted Answer

SELECT DISTINCT category FROM table. The DISTINCT keyword is the standard SQL method for returning unique values from a result set. This is a common requirement for initial data exploration, such as identifying categories, regions, or product lines within a dataset. Understanding how to use DISTINCT effectively allows analysts to quickly grasp the breadth of data in a table before performing deeper analysis or aggregation.

Answer

SELECT UNIQUE category FROM table

Answer

SELECT GROUP category FROM table

Answer

SELECT ONLY category FROM table

Question 15

Which Databricks SQL feature should an analyst use to view the execution plan and understand the data flow, including the number of rows processed and the time taken at each stage of a query?

Accepted Answer

The Query Profile UI. The Query Profile (or Query History UI) in Databricks SQL is the standard tool for monitoring and tuning queries. It provides a visual representation of the execution DAG (Directed Acyclic Graph), showing details like row counts, time spent in each operation, and potential bottlenecks, which is crucial for identifying why a specific query might be slow or inefficient.

Answer

The DESCRIBE DETAIL command

Answer

The SHOW TABLES command

Answer

The EXPLAIN command in a notebook