Databricks-DA-Assoc Practice Test 4 — 10 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.