Databricks-DA-Assoc Practice Test 2 — 25 Questions

Question 1

A data analyst is exploring a large Delta table named sales in the Databricks SQL query editor. Before writing a detailed report, the analyst wants to quickly view the first 10 rows to understand the column names and data types. Which SQL statement should the analyst execute?

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

Accepted Answer

SELECT * FROM sales LIMIT 10;. The analyst needs to preview actual rows from the sales table to understand its structure and content. Using SELECT * with LIMIT 10 retrieves a small sample efficiently. Other options return metadata or invalid syntax, none of which provide the required row-level data. This approach is standard for initial data exploration in Databricks SQL.

Answer

SHOW TABLES IN default LIKE 'sales';

Answer

DESCRIBE EXTENDED sales;

Answer

SELECT * FROM sales WHERE row_number <= 10;

Question 2

An analyst is designing a dashboard in Databricks SQL to monitor key performance indicators (KPIs). The dashboard will be viewed by executives who need to quickly see the current value of each KPI and whether it is meeting its target. Which visualization type is most appropriate for displaying a single KPI value along with its target?

Accepted Answer

Counter visualization. The counter visualization is specifically designed for displaying a single KPI value with optional target comparison. It provides a clear, visual indication of whether the KPI meets its target, making it the best choice for executive dashboards that require quick status assessment.

Answer

Bar chart

Answer

Table

Answer

Pie chart

Question 3

When migrating a reporting workload to Databricks SQL, an analyst needs to ensure that specific query results are consistent across multiple runs, even if the underlying Delta table is being updated. Which feature should the analyst utilize?

Accepted Answer

Delta Lake Time Travel (VERSION AS OF / TIMESTAMP AS OF).. Time Travel allows analysts to query a table as it existed at a specific point in time or version. This is critical for audits and reproducibility, ensuring that reports generated today can be replicated exactly, even if new data is added or old data is deleted. Understanding how to use the 'AS OF' syntax is vital for maintaining high standards of data integrity in analytical reporting workflows within the Databricks environment.

Answer

Materialized Views.

Answer

Setting the table to 'Read-Only' during reports.

Answer

Database snapshots created via manual backups.

Question 4

A data analyst has a 5 MB pipe-delimited text file with a header row on their local laptop and needs to load it into a Databricks Unity Catalog table for a one-time analysis. They want the fastest path with the least configuration. Which approach should they use?

Accepted Answer

Use the Add Data UI to upload the file and create the table through the guided wizard.. For a small local file that needs to become a table once, the Add Data UI is the intended workflow: it uploads the file, previews it, lets the analyst confirm delimiter and header handling, and creates the table. The other approaches assume cloud storage paths or streaming infrastructure that a laptop file simply does not have.

Answer

Run COPY INTO against the local filesystem path of the downloaded file.

Answer

Configure an Auto Loader stream pointing at a cloud storage location to ingest the file.

Answer

Create an external table over the local file so the data stays in place.

Question 5

When querying a Delta table that is updated frequently, what is the most reliable way to ensure an analyst sees the most up-to-date data?

Accepted Answer

The Delta Lake engine automatically ensures read-consistency. Delta Lake maintains a transaction log that ensures ACID compliance. When a query is initiated, the engine reads the latest version of the transaction log to determine which data files are current. Because the transaction log is the source of truth, the engine automatically provides read-consistent snapshots, ensuring that the analyst always queries the current state of the table without needing manual cache refreshes.

Answer

Run REFRESH TABLE before every query

Answer

Clear the browser cache in the SQL editor

Answer

Execute the VACUUM command to force an update

Question 6

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?

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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.