Databricks-DA-Assoc Practice Test 2 — 30 Questions

Question 1

An analyst is creating a dashboard in Databricks SQL and wants to display the number of active users per day over the last 30 days. The data is stored in a Delta table with a timestamp column. Which visualization type should the analyst use to best show the trend over time?

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

Accepted Answer

Line chart. A line chart is the most appropriate visualization for showing trends over time. It connects data points to illustrate changes and patterns, making it easy to interpret the daily active user count over the 30-day period. Other chart types are better suited for different analytical purposes.

Answer

Scatter plot

Answer

Bar chart

Answer

Pie chart

Question 2

When sharing an analysis with stakeholders, what is the best practice for ensuring they can view the results without needing access to the underlying raw data?

Accepted Answer

Use Databricks Dashboards to share visual insights while keeping data access restricted.. To share results without exposing raw data, analysts should use Databricks Dashboards or materialized views. These tools allow for the creation of curated visualizations that present calculated results while keeping the raw data access restricted. This adheres to security best practices, ensuring that stakeholders see only the insights they need, while the underlying tables remain governed by strict Unity Catalog access policies that protect sensitive information from unauthorized exposure.

Answer

Grant the stakeholders 'Can Manage' permissions on the notebook.

Answer

Export the dashboard as a static PDF document.

Answer

Upload the underlying data files to a public cloud storage bucket.

Question 3

A data analyst needs to create a view in Databricks SQL that combines data from two tables and applies a filter. The view should be accessible to other users in the same Unity Catalog schema. Which SQL statement should the analyst use?

Accepted Answer

CREATE VIEW my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;. The CREATE VIEW statement creates a persistent, virtual view that other users can access if they have permissions. It dynamically reflects changes in the underlying tables and does not store data. A table would duplicate data and not stay current. A materialized view adds unnecessary complexity, and a temporary view is not shared across users.

Answer

CREATE MATERIALIZED VIEW my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;

Answer

CREATE TEMPORARY VIEW my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;

Answer

CREATE TABLE my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;

Question 4

Which function is used to calculate the average of a column in a SQL query?

Accepted Answer

AVG(). Aggregate functions like AVG() are fundamental building blocks in data analysis. They allow analysts to summarize large datasets into meaningful metrics, such as calculating average sale price or average response time. Learning to apply these functions correctly within a SELECT statement is the first step toward building effective analytical dashboards and reports in any SQL-based environment.

Answer

MEAN()

Answer

AVERAGE()

Answer

TOTAL_AVG()

Question 5

Which identity management approach is required to utilize Unity Catalog for centralized governance across multiple Databricks workspaces?

Accepted Answer

Account-level identity provisioning using SCIM.. Unity Catalog requires that all users and groups are managed at the account level rather than individual workspace levels. By using an Account-level metastore, Databricks ensures a single source of truth for identities and permissions. This centralized model simplifies auditing, security, and data sharing, as permissions propagate consistently across all workspaces linked to that specific metastore.

Answer

Local workspace-level user management.

Answer

Database-level authentication via JDBC drivers.

Answer

Private Link connectivity for all users.

Question 6

A financial analyst is building an AI/BI Genie Space to allow executives to query quarterly earnings data. The executives often ask for metrics like 'year-over-year growth' and 'profit margin'. The analyst wants to ensure that Genie correctly calculates these metrics regardless of how the question is phrased. Which approach should the analyst take?

Accepted Answer

Add instructions in the Genie Space that define the formulas for year-over-year growth and profit margin. Providing instructions that define the formulas for year-over-year growth and profit margin allows Genie to understand and apply these calculations consistently. This method is flexible and handles various phrasings, ensuring accurate results for executives' ad-hoc questions without being limited to pre-defined views or exact terminology.

Answer

Create a view that pre-calculates year-over-year growth and profit margin

Answer

Train a custom machine learning model to predict the metrics

Answer

Restrict the Genie Space to only answer questions that include the exact metric names

Question 7

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 8

A data analyst needs to grant a colleague read access to a specific table named 'quarterly_sales' within Unity Catalog without exposing other tables in the same schema. Which SQL command should the analyst execute?

Accepted Answer

GRANT SELECT ON TABLE quarterly_sales TO `colleague@company.com`;. Granting granular access in Unity Catalog is performed using standard SQL GRANT syntax. To allow a user to read data from a specific table, the SELECT privilege must be granted directly on that table securable object while ensuring parent container traversal permissions are present.

Answer

GRANT ALL PRIVILEGES ON SCHEMA sales_schema TO `colleague@company.com`;

Answer

GRANT READ ACCESS ON CATALOG main TO `colleague@company.com`;

Answer

GRANT USE TABLE ON quarterly_sales TO `colleague@company.com`;

Question 9

A data analyst is building a dashboard in Databricks SQL and needs to give viewers the ability to change the date range and a region filter without editing the underlying query. The dashboard should refresh results based on the viewer's selections. Which feature should the analyst use?

Accepted Answer

Dashboard parameters that are referenced in the dataset queries. Dashboard parameters are the built-in way to let viewers supply values such as a date range or region that are injected into dataset queries. They provide interactive controls without editing SQL and refresh results on selection. Materialized views, Delta Live Tables, and notebook widgets address performance, pipeline refresh, or notebook interactivity, not dashboard viewer filtering.

Answer

A materialized view that pre-aggregates the data by date and region

Answer

A Delta Live Tables pipeline that refreshes the dashboard dataset hourly

Answer

A notebook widget embedded in the dashboard as a separate visualization

Question 10

Which TWO of the following are true regarding Databricks SQL Warehouses?

Accepted Answer

They are specifically optimized for SQL-based analytical queries.. SQL Warehouses are specialized compute resources optimized for SQL workloads. Understanding the difference between these and general-purpose clusters is crucial for analysts, as warehouses offer features like serverless startup and auto-stop, which significantly optimize cost and performance. These warehouses enable the platform's BI and dashboarding capabilities, making them a cornerstone of the data analyst's toolkit for delivering consistent, reliable reports to business stakeholders.

Answer

They must be manually started every time a user runs a query.

Answer

They support the same multi-language notebook features as all-purpose clusters.

Answer

They are only available for administrative users.

Question 11

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?

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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