Courseiva

Databricks Certified Data Analyst Associate (Databricks-DA-Assoc) — Questions 226–291

291 questions total · 4 pages · All types, answers revealed

Page 3

Page 4 of 4

226
MCQmedium

An analyst wants to publish a dashboard for the executive team. The dashboard contains sensitive data that should only be accessible to specific users. What is the correct way to handle access control?

A.Hard-code the security logic into the SQL queries.
B.Export the dashboard as a PDF and email it to the team.
C.Use the 'Share' button to grant 'Can View' access to authorized groups.
D.Make the dashboard public and add a password to the title.
AnswerC

The 'Share' functionality in Databricks allows for precise, role-based access control. By sharing the dashboard with specific groups, the analyst ensures that authentication and authorization are handled by the platform's security framework, keeping data access centralized, auditable, and restricted to only those who strictly require it.

Why this answer

Managing dashboard permissions via the Workspace sharing settings is the standard approach to secure sensitive data. By assigning 'Can View' or 'Can Edit' roles based on user groups, the analyst ensures that only authorized individuals see the dashboard. This is a fundamental skill for data governance, ensuring that insights remain secure and compliant while still being available to the relevant stakeholders within the organization.

Exam trap

Candidates often confuse workspace-level permissions with object-level permissions, incorrectly assuming that granting access to a folder automatically grants access to all individual dashboards contained within the workspace without manual intervention.

227
MCQeasy

Which clause is used in Databricks SQL to filter results after an aggregation has been performed?

A.WHERE
B.GROUP BY
C.HAVING
D.FILTER
AnswerC

The HAVING clause filters records after the GROUP BY operation has aggregated the data. It is the correct syntax for applying conditions to metrics like SUM, AVG, or COUNT, enabling analysts to isolate specific groups based on calculated thresholds rather than row-level values found in the original source tables.

Why this answer

The HAVING clause is specifically designed to filter groups created by the GROUP BY clause. Unlike the WHERE clause, which filters rows before aggregation, HAVING operates on the resulting aggregated data. Mastering this distinction is fundamental for writing reports that require thresholds on calculated metrics, such as identifying products with total sales greater than a specific monetary value in a monthly summary.

Exam trap

Candidates frequently try to use the WHERE clause to filter aggregated results, confusing row-level filtering with group-level filtering after a GROUP BY operation.

228
MCQeasy

A data analyst is creating a view in Databricks SQL that joins a fact table with several dimension tables. The analyst wants to ensure that the view always returns the latest data and that any changes to the underlying tables are immediately reflected. Which type of view should the analyst create?

A.A temporary view
B.A streaming view
C.A standard view
D.A materialized view
AnswerC

A standard view in Databricks SQL is a saved query that runs against the underlying tables each time it is queried. It always returns the current data because it does not store results. Therefore, any changes to the base tables are immediately visible, satisfying the requirement for up-to-date data.

Why this answer

A standard view is a saved query that executes on demand, so it always reflects the current state of the underlying tables. Materialized views store data and require refreshes, temporary views are session-bound, and streaming views are for continuous processing. Thus, a standard view best meets the need for immediate data visibility and persistence.

Exam trap

The trap here is confusing materialized views with standard views, thinking that a materialized view automatically updates, when it actually requires a refresh to reflect changes.

229
MCQmedium

A data analyst runs a Databricks SQL query that joins a 40 GB Delta table to a 12 GB Delta table and the Query Profile shows two Exchange nodes surrounding a SortMergeJoin. The analyst wants to confirm whether the shuffle is the dominant cost before rewriting the query. Which Query Profile metric should the analyst inspect first to quantify the shuffle's impact?

A.Peak memory usage of the driver process
B.Rows read from the smaller table's scan operator
C.Number of files scanned in the Delta table directory
D.Shuffle write bytes and shuffle read bytes on the Exchange nodes
AnswerD

The Exchange nodes are the shuffle boundaries, so their shuffle write and read byte counts directly measure how much data Spark serialized, spilled, and pulled across the network. These values quantify the redistribution cost that dominates a SortMergeJoin with two Exchanges, letting the analyst confirm whether shuffle volume is the bottleneck before rewriting the query.

Why this answer

The Exchange operators are the shuffle stages that redistribute rows by join key before the SortMergeJoin, so their shuffle write and read byte counts are the direct measurement of redistribution cost. Inspecting those byte totals tells the analyst whether the shuffle dominates runtime and justifies alternatives such as broadcast join or pre-partitioning.

Exam trap

The trap here is assuming any single operator's row count explains join slowness, when the Exchange byte metrics are what actually size the shuffle.

230
Multi-Selecthard

A data analyst is configuring an AI/BI Genie Space for a marketing team. The team frequently asks questions that require joining multiple tables, such as 'Which campaigns generated the most leads?' and 'What is the conversion rate by channel?' The analyst notices that Genie sometimes produces incorrect joins, leading to inflated numbers. Which TWO actions should the analyst take to improve Genie's join accuracy? (Choose two.)

Select 2 answers
A.Define foreign key relationships in the Unity Catalog
B.Enable caching for the tables used in the Genie Space
C.Increase the compute cluster size for the Genie Space
D.Provide sample queries that demonstrate correct joins
E.Restrict the Genie Space to a single table to avoid joins
AnswersA, D

Defining foreign key relationships in Unity Catalog provides Genie with explicit join paths between tables. This helps Genie understand how tables relate, reducing the chance of incorrect joins. When Genie knows that 'campaign_id' in the leads table references 'campaign_id' in the campaigns table, it can generate the correct join condition, avoiding Cartesian products or mismatched keys.

Why this answer

To improve Genie's join accuracy, the analyst should define foreign key relationships in Unity Catalog and provide sample queries with correct joins. These actions give Genie the necessary metadata and examples to understand table relationships and generate accurate SQL, reducing errors like Cartesian products or mismatched keys.

Exam trap

The trap here is focusing on performance optimizations like cluster size or caching, which do not address the semantic gap causing incorrect joins.

231
MCQhard

A Databricks SQL dashboard uses a counter visualization to display total revenue for the current month. The analyst notices that the counter shows a value even when the underlying query returns zero rows for the selected filter. Which behavior explains this and what should the analyst do to make the counter reflect the empty result set correctly?

A.The counter is using a COUNT(*) aggregation, which returns 0 for empty sets; the analyst should switch to SUM(revenue).
B.The dashboard cache is serving a stale result; the analyst should disable caching for the warehouse.
C.The counter is aggregating a column that contains NULLs; the analyst should wrap the measure in COALESCE to force a zero.
D.The counter is configured to display the last non-null value or a default when the result set is empty; the analyst should adjust the counter's empty-state or default-value setting.
AnswerD

Counter visualizations in Databricks SQL can carry display defaults or retain a prior value when the underlying dataset returns no rows, which is why a stale or placeholder number appears. The fix is in the visualization configuration, not the SQL, so the analyst should inspect and clear the default or empty-state behavior so the counter shows no data or an explicit zero as intended.

Why this answer

When a counter's dataset returns no rows, the aggregate value is NULL, and Databricks SQL counters can be configured with a default or fallback display that produces the misleading number. The correct remedy is to review the counter's visualization settings, including any default value or empty-state option, and align them with the intended behavior so an empty result is shown honestly rather than replaced by a placeholder.

Exam trap

The trap here is jumping to SQL-level fixes like COALESCE or COUNT(*) when the symptom originates in the visualization's empty-state or default-value configuration.

232
MCQmedium

A data analyst needs to query a table that contains sensitive customer financial data. Company policy requires that analysts see only masked values for account numbers in query results, and the masking must apply regardless of which tool or user queries the table. Which Databricks capability should be used to enforce this consistently at the data layer?

A.A view that filters rows before returning results
B.Column masks applied to the table
C.Workspace-level notebook permissions
D.Cluster access mode configuration
AnswerB

Column masks are table-level security features that evaluate a masking expression each time the column is read, returning masked values for unauthorized users. Because the mask is attached to the table itself, it applies no matter which client, notebook, or dashboard issues the query, satisfying the requirement that masking be enforced consistently at the data layer for all users.

Why this answer

Column masks attach a masking expression directly to a table column, so the transformation is evaluated at query time for every read, independent of the client or user. This data-layer enforcement is exactly what the policy demands: unauthorized analysts receive masked account numbers whether they query via a notebook, the SQL editor, or a BI tool, and authorized users can still see real values.

Exam trap

The trap here is confusing access control with value masking: permissions and views restrict who or what rows are returned, but only a column mask rewrites the actual values for unauthorized readers on every query path.

233
MCQmedium

A data analyst runs a query against a large Delta table partitioned by date. The query filters on a timestamp column rather than the date partition column. How does Databricks handle this query execution?

A.The query automatically re-partitions the Delta table on the fly to match the timestamp filter.
B.The engine evaluates the timestamp column to prune partitions directly mapped to timestamps.
C.The query optimizer performs a full scan of all partitions because partition pruning requires filters on the exact partition columns.
D.Databricks converts the timestamp into the partition column format and successfully prunes partitions.
AnswerC

Partition pruning is strictly tied to the columns specified in the PARTITIONED BY clause. Without a direct equality or range filter on those specific partition columns, the query engine must scan every file in the storage directory.

Why this answer

Databricks performs partition pruning based on the partition columns defined in the table metadata. When filtering on a timestamp column that differs from the partition column, the query engine cannot prune partitions effectively, resulting in a full table scan of all files unless liquid clustering or secondary indexing is applied. This optimization is critical for maintaining high query performance on large datasets.

Exam trap

Candidates assume the query optimizer can automatically derive partition pruning from any datetime attribute, missing that filters must target the exact partition column.

234
MCQmedium

A data analyst is troubleshooting a slow-running SQL query against a massive Delta table in Databricks. The query frequently scans the entire table despite filtering on a high-cardinality timestamp column. Which approach will most effectively reduce the data scanned by eliminating full-table reads?

A.Run an OPTIMIZE command with a ZORDER BY clause on the timestamp column to co-locate related data and improve data skipping.
B.Increase the cluster size to a driver instance with more memory to hold the entire uncompressed Delta table in cache.
C.Execute a VACUUM command with a retention threshold of zero hours to purge old data files immediately.
D.Convert the Delta table format to standard Parquet files to take advantage of native Apache Spark partitioning.
AnswerA

Z-Ordering clusters data with similar values into the same file spaces. This enables the Delta Lake file-skipping mechanism to bypass irrelevant files entirely when queries apply range filters on the target timestamp column, directly lowering overall scan volume and query duration.

Why this answer

Z-Ordering co-locates related data based on specified columns, significantly improving data skipping for queries with equality or range filters. When combined with correct partitioning, it minimizes the amount of data scanned from cloud storage, drastically reducing query latency and execution costs in Databricks environments.

Exam trap

Candidates often suggest partitioning by high-cardinality columns like timestamps, which is a major anti-pattern that creates too many small files and metadata overhead, drastically hurting performance.

235
MCQeasy

A data analyst runs a query in Databricks SQL that returns the total sales per region. The query is slow, and the analyst notices that the execution plan shows a full table scan on a large Delta table. The analyst wants to reduce the amount of data read. Which action is most likely to improve performance?

A.Use a broadcast hint to force a broadcast join.
B.Cache the table using CACHE TABLE before running the query.
C.Add a WHERE clause on a partitioned column to enable partition pruning.
D.Increase the cluster size to add more worker nodes.
AnswerC

If the table is partitioned on a column used in a filter, adding a WHERE clause on that column allows Delta to skip entire partitions. This reduces the data read from the full table scan to only relevant partitions, directly addressing the slow performance caused by scanning all data.

Why this answer

Partition pruning is the most direct way to reduce data read. By filtering on a partitioned column, Delta Lake can skip partitions that do not match the filter, turning a full table scan into a targeted read. Other options do not reduce the volume of data read or are inapplicable to an aggregation query.

Exam trap

The trap here is focusing on cluster resources or caching instead of addressing the fundamental issue: reading unnecessary data due to lack of partition pruning.

236
MCQmedium

When designing a star schema in Databricks SQL, why is it recommended to use Delta Lake for both Fact and Dimension tables?

A.It forces the use of Star Schema optimization, which is only supported for Delta format.
B.Delta Lake supports ACID transactions, which are necessary for reliable SCD updates.
C.Delta Lake automatically reorders dimension tables to improve join performance.
D.It is required to store dimensions in the same schema as fact tables for performance.
AnswerB

SCD (Slowly Changing Dimension) patterns require atomic updates to history and status flags. Delta Lake ensures these operations are ACID compliant, meaning they are either fully committed or not committed at all. This prevents partial writes that could invalidate downstream analytical queries or lead to erroneous historical reporting.

Why this answer

Delta Lake provides ACID compliance and time travel, which are critical for maintaining the integrity of star schemas. Analytical workloads often rely on SCD (Slowly Changing Dimension) updates and complex joins. By using Delta Lake, you ensure that dimension updates are consistent and atomic, preventing users from seeing partial updates or corrupted data states, which is essential for accurate business intelligence reporting and historical analysis.

Exam trap

Candidates often choose standard Parquet tables assuming storage format has no impact on data warehouse updates, ignoring the necessity of transaction logs for dimensions.

237
MCQmedium

An analyst is loading a CSV file where one column contains values like 007, 012, and 003. After creating a table through the Add Data UI, those values appear as 7, 12, and 3. The analyst needs the leading zeros preserved for downstream reporting. What should the analyst do?

A.Specify the column as a string type in the schema settings before creating the table.
B.Apply a formatting function after loading that pads the numbers back to three characters.
C.Enable schema evolution so the column can switch types after the first load.
D.Set the file's delimiter to a character that prevents numeric interpretation of the column.
AnswerA

Leading zeros are only meaningful in a string representation. If the column is inferred or declared as an integer, the parser strips the zeros because 007 and 7 are numerically identical. Overriding the inferred type to string preserves the original characters. The Add Data UI and schema editor both allow this type override before the table is created.

Why this answer

Leading zeros carry meaning only in text form, so the column must be declared as a string before the table is created. Letting the reader infer an integer type silently drops the zeros, and no post-load fix reliably restores them. Overriding the type in the schema settings preserves the original values.

Exam trap

The trap here is trusting automatic type inference, which correctly identifies a numeric pattern but destroys leading zeros that matter for identifiers.

238
MCQhard

An analyst is troubleshooting a query that hangs indefinitely during a join. Which TWO metrics in the Query Profile should the analyst examine to diagnose the issue?

A.Shuffle Read/Write bytes.
B.Maximum vs Median rows per task.
C.The number of users logged into the workspace.
D.The total number of tables in the schema.
E.The total size of the database logs.
AnswerA, B

High shuffle bytes indicate that large amounts of data are being exchanged across the network nodes. If a query hangs, it may be due to this massive data movement exceeding the cluster's network or I/O bandwidth, suggesting a need to reduce the data volume or optimize the join strategy.

Why this answer

When a join hangs, it is often due to massive data movement (shuffle) or extreme data skew. Monitoring the shuffle size helps determine if the network or I/O is saturated. Checking the 'Max vs Median' row count helps identify skew.

These metrics are the most reliable indicators of join failure, enabling analysts to decide whether to adjust join keys, use broadcast hints, or address underlying data distribution issues.

Exam trap

Candidates often look at general cluster metrics like total memory instead of join-specific indicators such as shuffle bytes and row disparities.

239
MCQmedium

An analyst has a Delta table `bronze.raw_events` that has accumulated many small files over months of streaming writes. They now need to optimize read performance for downstream dashboards. Which Databricks SQL command should they run?

A.`VACUUM bronze.raw_events;`
B.`ANALYZE TABLE bronze.raw_events COMPUTE STATISTICS;`
C.`OPTIMIZE bronze.raw_events;`
D.`ALTER TABLE bronze.raw_events SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true');`
AnswerC

OPTIMIZE compacts many small files into fewer, larger files, which reduces file-open overhead and improves scan performance for dashboards. This directly addresses the small-file problem created by streaming writes. It is the standard maintenance command for this scenario and does not require changing the table definition or rewriting queries against the table.

Why this answer

The small-file problem from streaming writes is resolved by compaction, and OPTIMIZE performs exactly that by merging small files into larger ones. Statistics gathering and vacuuming address different concerns, and write-time properties only influence future writes rather than existing layout. Compaction is the correct maintenance action for improving scan performance on a fragmented Delta table.

Exam trap

The trap here is confusing VACUUM, which deletes unreferenced old files, with OPTIMIZE, which compacts current small files into larger ones.

240
MCQmedium

Which component of an AI/BI Genie space is responsible for defining the scope of data available to a user and ensuring that the natural language model only references authorized tables?

A.The Unity Catalog Data Explorer integration
B.The Genie space instructions and metadata definition
C.The Databricks SQL Warehouse access policy
D.The workspace-level workspace AI configuration
AnswerB

Instructions and table definitions act as the grounding layer for the Genie space. By specifying allowed tables and providing business context in the instructions, you restrict the LLM to a specific subset of Unity Catalog assets, effectively managing scope and preventing unauthorized query generation across the broader catalog.

Why this answer

Genie spaces rely on the definition of data instructions and schema mappings within the space configuration. By explicitly linking specific tables and columns, the system enforces access control and ensures the LLM does not hallucinate using external data. This metadata-driven approach is critical for maintaining data governance and security while enabling self-service analytics for business users within the Databricks ecosystem.

Exam trap

Candidates often look for a specific 'security tab' or 'access menu' instead of recognizing that the Genie space configuration itself acts as the boundary for data access and LLM scope.

241
MCQmedium

A data analyst is querying a large Delta table containing billions of rows of clickstream data. The table is frequently queried using a timestamp column. To optimize range queries on this timestamp, which Delta Lake feature should the table builder implement?

A.Enabling column mapping to allow renaming columns without rewriting underlying parquet files.
B.Applying Z-Order clustering on the timestamp column to improve multi-dimensional data skipping.
C.Setting the file size compaction threshold to the maximum allowable limit of 1 gigabyte.
D.Adding an explicit check constraint on the timestamp column to reject future dates.
AnswerB

Z-Ordering algorithmically rearranges data within Delta parquet files to collocate similar values. This significantly tightens minimum and maximum statistics stored in the Delta transaction log, enabling the data skipping mechanism to bypass irrelevant files during range queries.

Why this answer

Delta Lake features like Z-Ordering colocate related information in the same set of files based on specified columns. However, for efficient range filtering on high-cardinality timestamps, Liquid Clustering or traditional Z-Ordering combined with proper partitioning or data skipping statistics ensures the engine reads minimal files.

Exam trap

Exam takers frequently confuse standard partition columns with Z-Order clustering, forgetting that multi-dimensional data skipping relies on specific clustering strategies.

242
MCQeasy

Which Databricks component is the primary interface for collaborative, interactive data analysis and visualization?

A.Databricks SQL Warehouse
B.Databricks Notebooks
C.Delta Live Tables
D.Unity Catalog
AnswerB

Notebooks are the primary interface for collaborative data science and analysis. They allow multiple users to edit code simultaneously, document findings with Markdown, and generate charts directly from query results. This collaborative capability is essential for data analysts who need to build and share reproducible analytical pipelines and reports.

Why this answer

Databricks Notebooks are the core collaborative environment where users write code, execute queries, and create visualizations. They support polyglot programming (SQL, Python, R, Scala) and are integrated directly with the platform's compute resources. For analysts, notebooks provide a persistent workspace to document workflows, share insights with stakeholders, and execute complex data transformations, making them the most important tool for iterative data analysis and team collaboration within the Databricks environment.

Exam trap

Candidates mistakenly select SQL Warehouses or Jobs when asked for the primary interface where code is interactively written, visualized, and shared.

243
MCQeasy

An analyst has a Databricks SQL dashboard with a table visualization showing the top 20 customers by lifetime value. The stakeholder wants the same numbers represented as proportional horizontal bars so differences between customers are easier to compare visually. Which visualization type should the analyst choose?

A.Line chart
B.Pie chart
C.Scatter plot
D.Bar chart
AnswerD

A bar chart places each customer on a categorical axis and encodes lifetime value as bar length, which makes ranking and magnitude comparisons immediate. When oriented horizontally, long customer names remain readable on the y-axis. Databricks SQL visualizations support horizontal bar charts directly, and the built-in sorting lets the analyst order customers by value so the top 20 are clearly ranked.

Why this answer

A bar chart is the standard choice for comparing a numeric measure across discrete categories. Each customer gets a bar whose length equals lifetime value, so ranking and relative differences are immediately visible, and a horizontal orientation keeps long customer names legible. Databricks SQL's visualization editor supports horizontal bar charts and sorting, making it straightforward to present the top 20 customers in descending order.

Exam trap

The trap here is reaching for a pie chart because the request mentions proportions, when the real task is comparing many discrete categories by magnitude, which bars handle far better.

244
MCQmedium

When importing data into Databricks, what is the primary benefit of using Delta Lake over standard Parquet files?

A.Delta Lake files require less storage space than Parquet.
B.Delta Lake supports ACID transactions and schema enforcement.
C.Delta Lake is faster for reading large tables than Parquet.
D.Delta Lake is natively supported by all external BI tools.
AnswerB

Delta Lake provides ACID transactions, which allow multiple readers and writers to interact with the data without corruption. It also enforces schema constraints, ensuring that new data matches the table structure. This makes it far more robust than standard Parquet, which lacks these native management features for distributed data.

Why this answer

Delta Lake adds a transaction log (the _delta_log folder) to Parquet files, enabling ACID transactions and time travel. This prevents data corruption during concurrent writes and allows users to query previous versions of data. This is a foundational concept for data reliability, as it ensures that analytical queries are always executed against a consistent and version-controlled state, which is impossible with standard, non-transactional Parquet files in a distributed system.

Exam trap

Candidates frequently confuse Delta Lake with simple storage formats like Parquet or Avro, overlooking that Delta's primary value proposition is the transaction log enabling ACID properties and concurrency.

245
MCQmedium

When analyzing query execution metrics in Databricks, what does 'Task Duration' represent?

A.The time taken to compile the SQL query.
B.The time elapsed for a single core to process a task.
C.The total time the cluster was running.
D.The latency added by network overhead only.
AnswerB

Task Duration reflects the time spent by a specific core on a specific partition of the data. By comparing the durations across different tasks, an analyst can detect stragglers. If most tasks finish quickly but one takes much longer, it indicates an imbalance in data partitioning or workload distribution.

Why this answer

Task Duration measures the time taken by individual executor nodes to perform their assigned portion of the work. It is distinct from total query time because it highlights parallel execution efficiency. Understanding this is vital because if one task is much longer than others, it indicates data skew or uneven distribution, helping analysts identify the root cause of performance bottlenecks that are hidden within the overall query execution time.

Exam trap

Candidates often confuse task duration with total wall-clock query execution time or cluster uptime, misunderstanding that it tracks individual core processing duration.

246
Multi-Selecthard

An analyst is optimizing query performance in Databricks SQL. Which TWO of the following actions directly improve query execution speed by reducing the amount of data scanned during query execution?

Select 2 answers
A.Implementing Z-Ordering on frequently filtered columns.
B.Increasing the SQL Warehouse cluster size.
C.Using partitioned columns in the WHERE clause.
D.Enabling query result caching for all users.
E.Updating statistics using the ANALYZE TABLE command.
AnswersA, C

Z-Ordering co-locates related data in the same set of files, which allows the query engine to skip large amounts of irrelevant data. When combined with data skipping, this significantly reduces the number of files scanned, leading to faster query performance for analytical workloads filtering on those specific columns.

Why this answer

Data skipping and partition pruning are critical performance optimizations in Databricks SQL. By leveraging the Delta Lake transaction log and metadata, Databricks skips unnecessary files and partitions, drastically reducing I/O requirements. These techniques are fundamental for large-scale data analysis, as they allow queries to target only the specific subsets of data required to satisfy filters, significantly lowering latency and improving overall warehouse performance for end-users.

Exam trap

Candidates often select 'indexing' or 'caching' as performance solutions, which are not standard optimization techniques for reducing data scan size in Delta Lake environments.

247
MCQeasy

What is the purpose of the 'Account Admin' role in Databricks?

A.To manage SQL queries for a specific user.
B.To configure metastores, identities, and settings.
C.To monitor the performance of all SQL Warehouses.
D.To create tables in the metastore.
AnswerB

Account Admins are responsible for the highest-level configuration tasks, such as creating and managing Unity Catalog metastores, provisioning users via SCIM, and defining global security settings. They act as the governance leads who establish the environment's security perimeter, ensuring that all workspaces and catalogs comply with organizational security and operational requirements.

Why this answer

The Account Admin role is the highest level of privilege in Databricks, providing control over the entire account including metastore creation, user provisioning, and billing. This role is responsible for the foundational security configuration of the environment. Because of its power, this role should be restricted to a very small number of individuals to prevent unauthorized changes to security or governance policies.

Exam trap

Candidates confuse the Account Admin role with Workspace Admins or metastore-specific owners, missing the global scope of account-level configurations.

248
MCQeasy

An analyst is writing a query in Databricks and needs to reference a temporary view that is scoped only to their current interactive notebook session. Which command or syntax ensures the view is automatically dropped when the session ends?

A.CREATE GLOBAL TEMP VIEW view_name AS SELECT ...
B.CREATE TABLE view_name AS SELECT ...
C.CREATE TEMP VIEW view_name AS SELECT ...
D.CREATE MANAGED VIEW view_name AS SELECT ...
AnswerC

Creating a standard temporary view binds its existence directly to the current SparkSession. It provides a convenient way to encapsulate complex subqueries and joins for the duration of an interactive notebook session without writing permanent metadata to the catalog.

Why this answer

Spark SQL supports various view scopes for different collaboration needs. A standard temporary view is tied to the lifecycle of the active SparkSession. Once the notebook session terminates, the temporary view is automatically cleaned up, preventing namespace pollution in shared database catalogs.

Exam trap

Candidates often confuse 'TEMP VIEW' with 'GLOBAL TEMP VIEW', incorrectly believing that a standard temporary view is visible across different Spark sessions or notebooks, which leads to 'table not found' errors.

249
MCQmedium

Refer to the exhibit. The table 'sales' is partitioned by 'region' and 'order_date'. How does the Databricks SQL engine process this query to ensure optimal performance?

A.It performs a full table scan and filters the results in memory
B.It uses partition pruning to skip files in partitions not matching the filters
C.It re-indexes the table columns to facilitate faster lookup
D.It automatically broadcasts the table to all nodes for faster processing
AnswerB

The engine uses the filters on 'region' and 'order_date' to perform partition pruning. It only scans the storage directories corresponding to 'North' and dates from 2023 onwards. This significantly limits the volume of data read, resulting in much faster query execution and reduced I/O overhead.

Why this answer

Databricks SQL utilizes partition pruning to ignore directories that do not match the WHERE clause filters. By identifying that the query filters on both 'region' and 'order_date', the engine restricts the scan to specific subdirectories on storage. This significantly reduces the amount of I/O required, which is the primary driver of performance in large-scale data lake queries, as it avoids reading irrelevant data from the storage layer.

Exam trap

Candidates mistakenly think the engine scans all directories and filters rows afterward, ignoring how directory-level partition pruning works in Delta Lake.

250
MCQmedium

A data analyst is configuring an AI/BI Genie Space for a customer support dataset. The analyst notices that when users ask "how many open tickets per agent," Genie sometimes counts all tickets instead of only open ones. The analyst wants to fix this. What should the analyst do?

A.Add a comment to the status column listing all possible values.
B.Create a view that filters tickets to only those with status = 'Open' and add it to the Genie Space.
C.Add an instruction that defines "open tickets" as tickets where status = 'Open'.
D.Rename the status column to ticket_status.
AnswerC

Genie relies on instructions to resolve ambiguous business terms. By explicitly defining "open tickets" as tickets with status = 'Open', the analyst gives Genie the filter logic. This directly addresses the misinterpretation and ensures the generated SQL includes the correct WHERE clause for that term.

Why this answer

The most direct fix is to add an instruction defining "open tickets" as tickets where status = 'Open'. This teaches Genie the business term and ensures it applies the correct filter when generating SQL, without restricting the data for other questions.

Exam trap

The trap here is choosing a view that permanently filters the data, which would prevent answering questions about closed tickets, instead of using an instruction to define the term.

251
MCQhard

A data analyst runs a query on a Delta table with a WHERE clause on a timestamp column, but the query scans the entire table. The analyst checks the table's metadata and sees that the column is not a partition column but is frequently used in filters. The analyst wants to enable data skipping to avoid full scans. Which action should the analyst take?

A.Set spark.sql.parquet.filterPushdown to true to enable filter pushdown on the timestamp column.
B.Run OPTIMIZE with ZORDER on the timestamp column to co-locate related data and improve data skipping.
C.Convert the table to a partitioned table using the timestamp column as the partition key.
D.Enable Delta Lake change data feed on the table to track changes to the timestamp column.
AnswerB

Z-ordering on a frequently filtered column rearranges data files so that related values are clustered together. This allows Delta Lake to skip files using min/max statistics, even if the column is not a partition. Running OPTIMIZE with ZORDER on the timestamp column will improve data skipping for queries filtering on that column.

Why this answer

Z-ordering on a frequently filtered column clusters data so that min/max statistics can be used to skip files. This is ideal for high-cardinality columns like timestamps where partitioning would create too many small files. OPTIMIZE with ZORDER rearranges data without changing the table structure.

Exam trap

The trap here is confusing partitioning with Z-ordering; partitioning on a timestamp column creates many small partitions, while Z-ordering provides data skipping without partitioning overhead.

252
MCQmedium

A data analyst is running a complex, resource-intensive query inside the Databricks SQL query editor that frequently times out before returning results. Which administrative feature should the analyst or workspace admin leverage to manage compute resources and prevent long-running queries from blocking other users?

A.Increase the maximum number of concurrent users in the SQL warehouse cluster settings.
B.Configure the automated query timeout setting on the SQL warehouse to automatically abort queries exceeding a specific duration.
C.Convert the query into a Spark Structured Streaming job using the Databricks notebook interface.
D.Manually restart the underlying Apache Spark driver node using the cluster UI restart button.
AnswerB

The SQL warehouse automated query timeout setting aborts statements exceeding a defined duration, freeing compute so long-running queries cannot monopolise resources and block other users. This directly addresses the timeout symptom while enforcing resource governance at the warehouse level.

Why this answer

Databricks SQL warehouses support query timeout limits and auto-stopping features that prevent runaway queries from consuming cluster resources indefinitely. Configuring these timeouts ensures cluster availability and protects shared analytical environments from deadlocks and excessive consumption, making it a critical skill for efficient query execution management.

Exam trap

Candidates often suggest increasing the warehouse size (scaling up). While this might help, the administrative best practice for runaway queries is setting explicit timeouts to protect shared resources.

253
MCQeasy

What is the primary purpose of the Databricks Catalog Explorer?

A.To manage the deployment and monitoring of automated machine learning models.
B.To browse and manage metadata for catalogs, schemas, tables, and volumes.
C.To monitor the health and logs of active Spark clusters.
D.To develop and execute Python scripts in interactive notebooks.
AnswerB

Catalog Explorer provides a visual interface to explore the Unity Catalog hierarchy, including catalogs, schemas, tables, and volumes. It enables users to inspect table schemas, view data samples, check permissions, and track lineage, which is the core function of the tool for data analysts navigating the data lakehouse.

Why this answer

Catalog Explorer is a unified interface within Databricks for managing and exploring the data ecosystem. It allows users to browse schemas, tables, and views, manage data permissions, and view data lineage. For a data analyst, it is the central hub for understanding data assets, verifying table metadata, and ensuring that the necessary access rights are in place before starting analysis, making it an essential tool for data discovery and governance.

Exam trap

Candidates frequently confuse Catalog Explorer with a data transformation tool or a query editor, forgetting that its primary purpose is metadata management, discovery, and governance.

254
MCQhard

Refer to the exhibit. An analyst receives this error when attempting to query a table. Which step should they take to fix this?

A.Increase the SQL Warehouse size to handle larger metadata lookups.
B.Check the Catalog Explorer to confirm the correct fully qualified table name.
C.Switch the notebook language to Python to access the table.
D.Request Workspace Admin privileges to access all system tables.
AnswerB

Catalog Explorer allows users to navigate the data hierarchy and identify the exact path to a table. By finding the table in the explorer, the analyst can determine its actual catalog, schema, and name, ensuring they use the correct fully qualified path in their query to resolve the reference error.

Why this answer

The error indicates a Namespace resolution issue. In Unity Catalog, objects must be referenced using the three-level namespace: catalog.schema.table. The error shows that the system is looking in 'main.default', but the table might be in a different catalog or schema.

The analyst needs to verify the correct path using the Catalog Explorer to ensure they are referencing the qualified table name correctly in their SQL query.

Exam trap

Candidates often try to debug the code syntax or the table schema, ignoring the most common cause of 'table not found' errors in Unity Catalog: using an incomplete or incorrect three-level namespace.

255
MCQmedium

A data analyst is building a Databricks SQL dashboard to track daily sales. They need to ensure that the dashboard automatically updates every hour to reflect the latest ingestion from Delta tables. Which mechanism should the analyst configure to achieve this requirement?

A.Enable the 'Auto-refresh' toggle within the browser settings of the dashboard viewer.
B.Configure a SQL warehouse to 'Auto-stop' every hour to trigger a metadata update.
C.Configure a schedule on the dashboard using a SQL warehouse to run at the desired frequency.
D.Create a Job in the Workflows UI that explicitly opens the dashboard URL every hour.
AnswerC

Scheduling is the native Databricks feature that allows dashboards to run queries at defined intervals. This approach ensures that the SQL warehouse executes the underlying queries, updates the cache, and refreshes the visualizations, providing consistent and automated data delivery to users without requiring manual refreshes or browser activity.

Why this answer

Databricks SQL dashboards include a built-in schedule feature that allows analysts to define an interval for automated refreshes. By configuring a schedule, the dashboard queries run automatically in the background, ensuring stakeholders view current data without manual intervention. This feature is vital for operational reporting where timeliness is critical for business decision-making and performance monitoring across the organization.

Exam trap

Candidates often think dashboards update in real time automatically without configuring an explicit schedule tied to a running SQL warehouse.

256
Multi-Selecthard

A data analyst is using the Databricks Add Data UI to import a CSV file from cloud storage into a Delta table. The analyst wants to ensure the import process handles the data correctly. Which two actions can the analyst perform directly in the Add Data UI? (Choose two.)

Select 2 answers
A.Set up a scheduled job to automatically ingest new files from the same location.
B.Enable schema evolution for future files.
C.Define a custom schema using DDL statements.
D.Specify a custom delimiter for the CSV file.
E.Preview the data and manually adjust column types before creating the table.
AnswersD, E

The Add Data UI allows the analyst to specify a custom delimiter if the CSV file does not use a comma. This is useful for files that use semicolons, tabs, or other separators. The UI provides an option to set the delimiter during the import process.

Why this answer

The Add Data UI allows analysts to preview data, adjust column types, and specify a custom delimiter during import. It does not support scheduling, DDL schema definition, or schema evolution, which are features of other tools like Auto Loader or Jobs.

Exam trap

The trap here is assuming the Add Data UI can handle ongoing ingestion or complex schema definitions, when it is limited to interactive, one-time imports with basic adjustments.

257
MCQhard

A dashboard author wants viewers to click a bar in a Databricks SQL chart and have the dashboard display a second chart filtered to that bar's category. Which feature should the author configure to achieve this interactive behavior?

A.A scheduled refresh on the dashboard so the second chart reloads on an interval
B.A separate query per category added as individual visualizations to the dashboard
C.Dashboard cross-filtering or a dashboard-level filter driven by the selected value
D.A parameter default value set to a specific category in the second chart's query
AnswerC

Databricks SQL dashboards support interactive filtering where selecting a value in one visualization, such as a bar's category, can drive a dashboard filter that updates other visualizations. Configuring this cross-filter or selection-driven filter lets viewers drill into a category and see the second chart update accordingly, matching the requested behavior.

Why this answer

Interactive cross-chart behavior comes from dashboard filtering driven by a selected value, so clicking a bar's category updates the other chart. Scheduled refresh only reloads data, a parameter default is static, and separate per-category queries create static charts, none of which respond to a viewer's selection.

Exam trap

The trap here is confusing data-refresh scheduling with interactivity, when only selection-driven dashboard filtering reacts to a viewer clicking a chart element.

258
Multi-Selecthard

Which THREE actions occur when using Auto Loader with schema evolution enabled? (Choose three)

Select 3 answers
A.New columns found in the source files are automatically added to the target table.
B.Existing columns with data type conflicts cause the pipeline to stop immediately.
C.The schema is inferred from the metadata of the cloud storage provider.
D.A 'rescued data' column is created to store data that does not match the inferred schema.
E.The inferred schema is saved to a checkpoint location to maintain consistency across restarts.
AnswersA, D, E

Auto Loader detects new columns in source data and can automatically update the target table's schema. This prevents pipeline failure when source data expands, allowing the table to evolve alongside the data, which is a key advantage of the Delta Lake schema evolution capabilities provided during ingestion.

Why this answer

Schema evolution is a powerful feature of Auto Loader that allows pipelines to adapt to changes in upstream data without requiring manual code updates. By understanding how the schema is managed, detected, and reconciled, analysts can build flexible ingestion systems that reduce maintenance overhead. This is critical in modern data environments where upstream sources frequently add or rename columns, requiring the platform to dynamically update downstream tables to accommodate the new structure.

Exam trap

Candidates mistakenly believe Auto Loader automatically deletes unparseable files or alters original source files, when it actually captures corrupted data in a rescued data column.

259
MCQmedium

An analyst is configuring an AI/BI Genie Space and wants to ensure that the model only uses a specific SQL warehouse for query execution. Where should the analyst configure this setting?

A.In the Databricks workspace admin console, under the Genie tab.
B.In the SQL query editor of the Genie Space, by adding a USE WAREHOUSE statement.
C.In the Genie Space settings, under the SQL warehouse section.
D.In the Unity Catalog metastore settings.
AnswerC

The Genie Space settings include a section to select the SQL warehouse that the Space will use for query execution. By specifying a warehouse here, the analyst ensures that all queries generated by Genie run on that warehouse. This is the correct place to configure the compute resource, providing control over performance and cost.

Why this answer

To specify the SQL warehouse for a Genie Space, the analyst must use the Genie Space settings. There, they can select the desired warehouse from available options. This configuration ensures that all natural language queries are executed on the chosen warehouse, providing control over performance, cost, and permissions.

Exam trap

The trap here is thinking that the SQL warehouse can be set via a SQL statement or in global admin settings, when it is actually configured per Genie Space.

260
Multi-Selectmedium

Which THREE of the following steps are necessary to create an interactive dashboard in Databricks SQL? (Choose three)

Select 3 answers
A.Write a SQL query that includes filter parameters.
B.Define the visualization type in the SQL query code.
C.Add the visualization to a dashboard canvas.
D.Configure dashboard filters to link to parameters.
E.Upload a CSV file containing the final report data.
AnswersA, C, D

To make a dashboard interactive, the underlying SQL queries must be parameterized. By using syntax like '{{ parameter_name }}', the analyst creates placeholders that the dashboard interface can dynamically replace with user-selected values, enabling the interactivity required for meaningful data exploration and ad-hoc analysis by the end-users.

Why this answer

Building an interactive dashboard involves creating the underlying queries, selecting appropriate visualizations, and adding parameters for end-user interaction. Mastering this workflow is the core competency of a Databricks Data Analyst. These steps create a robust, self-service environment where users can filter and analyze data dynamically, reducing the burden on analysts to generate custom reports and empowering stakeholders to find their own answers to business questions.

Exam trap

Candidates often forget the step of configuring the filter link, believing that simply adding a parameter to the query is sufficient to make it appear and function in the dashboard interface.

261
MCQeasy

An analyst is writing a query in the Databricks SQL editor and needs to reference a temporary view that was created earlier in the same interactive session. How are temporary views scoped within Databricks SQL environments?

A.They are globally visible to all users across the entire Databricks workspace indefinitely.
B.They persist permanently inside the default Hive metastore schema until manually dropped by an admin.
C.They are automatically replicated to all attached SQL warehouses for cross-warehouse analytics.
D.They are scoped strictly to the current interactive session and drop automatically upon disconnection.
AnswerD

Temporary views in Databricks SQL exist only within the session that created them, so they cannot be referenced by other users or sessions. They vanish automatically when the session disconnects, which is exactly the scoping behaviour the analyst needs when referencing the view later in the same interactive session.

Why this answer

Temporary views in Databricks are scoped to the specific session or notebook in which they are defined. They disappear automatically once the session terminates, ensuring temporary scratchpads do not pollute global catalog namespaces while allowing complex multi-step queries within a single workflow.

Exam trap

Candidates often confuse temporary views with global temporary views or permanent tables, expecting them to persist across different interactive sessions or user disconnects.

262
MCQeasy

An analyst has a CSV file at `abfss://raw@storage.dfs.core.windows.net/exports/2024_orders.csv` and wants to query it directly from Databricks SQL without loading it into a Delta table. Which approach is appropriate?

A.Create a foreign table with CREATE FOREIGN TABLE and query it with standard SQL.
B.Use a read_files table-valued function in a query, for example SELECT * FROM read_files('abfss://raw@storage.dfs.core.windows.net/exports/2024_orders.csv', format => 'csv');
C.Run COPY INTO with the file path as the target and then query the resulting table.
D.Create a temporary view with CREATE TEMPORARY VIEW csv_data AS SELECT * FROM 'abfss://raw@storage.dfs.core.windows.net/exports/2024_orders.csv';
AnswerB

The read_files table-valued function lets Databricks SQL read files directly from cloud storage without creating a table or view. It supports CSV, JSON, Parquet, and other formats, and you can pass options such as header and delimiter. This satisfies the requirement to query the CSV in place without loading it into Delta.

Why this answer

The read_files table-valued function is designed to query external files in place, returning a relation you can select from without creating a persistent object. It supports CSV and other formats and accepts options for headers and delimiters. COPY INTO writes into Delta tables, foreign tables are not a Databricks SQL construct, and a temporary view cannot select from a bare storage path without a file-reading function.

Exam trap

The trap here is assuming a raw cloud storage path can be selected from directly or loaded with COPY INTO when the goal is to query without loading.

263
MCQhard

Refer to the exhibit. An analyst is troubleshooting a performance issue where frequent small inserts into a Delta table result in degraded query performance over time. The exhibit shows the configuration applied. What is the expected behavior of these properties?

A.It forces all writes to be synchronous, ensuring immediate data consistency across all cluster nodes.
B.It automatically triggers a FULL OPTIMIZE command every hour regardless of file counts.
C.It improves read performance by reducing the number of small files created during ingestion.
D.It forces the table to use a liquid clustering layout for all future data partitions.
AnswerC

By enabling these properties, the table writer attempts to optimize file sizes during the write phase, and the background process cleans up lingering small files. This significantly improves read performance because the query engine needs to scan fewer files, which reduces I/O overhead and speeds up overall data retrieval.

Why this answer

These properties enable Delta Lake's auto-optimization features. 'optimizeWrite' dynamically adjusts the write size to produce larger, more efficient files, while 'autoCompact' automatically merges small files into larger ones during background processes. This combination is critical for sustaining query performance in streaming or high-frequency update environments, preventing the 'small-file problem' that leads to excessive metadata overhead and slow scan times in Databricks SQL.

Exam trap

Candidates often fear that 'Auto-Optimize' will negatively impact write latency. They fail to recognize that the primary goal is to prevent metadata degradation and improve long-term read performance.

264
MCQmedium

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?

A.Counter visualization
B.Bar chart
C.Table
D.Pie chart
AnswerA

A counter visualization in Databricks SQL is designed to display a single numeric value prominently. It can be configured to show a target value and indicate whether the current value meets, exceeds, or falls short of the target. This makes it ideal for executive dashboards where quick assessment of KPI status is needed.

Why this answer

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.

Exam trap

The trap here is assuming any chart that can show a number works, but the counter is purpose-built for single KPI display with target tracking.

265
MCQhard

Which capability is provided by Databricks SQL 'Alerts' when a threshold is triggered?

A.Automatically restarting the SQL Warehouse.
B.Sending notifications via integrated channels.
C.Rolling back the last data transaction.
D.Purging the SQL result cache.
AnswerB

Alerts are specifically designed to monitor query output and send notifications through channels like email, Slack, or Webhooks when specific conditions are met. This enables proactive monitoring of business data, ensuring stakeholders are promptly informed of critical threshold breaches without needing to manually refresh or monitor a dashboard.

Why this answer

Alerts in Databricks SQL are designed to monitor specific query results and trigger notifications based on defined conditions. When the threshold is met, the system can send notifications via email or external integrations like Slack or Webhooks. This functionality is vital for business monitoring, enabling teams to respond quickly to changes in data, such as sudden drops in performance or reaching critical business KPIs without manual intervention.

Exam trap

Candidates often assume Alerts trigger automated data pipelines or complex workflows, rather than recognizing they are primarily notification mechanisms for threshold-based monitoring of query results.

266
MCQmedium

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?

A.Grant the stakeholders 'Can Manage' permissions on the notebook.
B.Export the dashboard as a static PDF document.
C.Use Databricks Dashboards to share visual insights while keeping data access restricted.
D.Upload the underlying data files to a public cloud storage bucket.
AnswerC

Databricks Dashboards provide a controlled interface for stakeholders to consume insights. By sharing the dashboard, the analyst enables access to the visualizations while the underlying data security is managed by Unity Catalog. This ensures that users see only what they are authorized to see, maintaining the integrity of data governance policies.

Why this answer

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.

Exam trap

Candidates often suggest granting read access to raw data or exporting CSVs, failing to realize that Databricks Dashboards allow sharing insights securely without exposing the underlying storage layer.

267
MCQmedium

An analyst is using the Spark DataFrame API to read a large JSON dataset. The dataset contains nested fields that are causing schema inference to fail. Which approach best resolves this?

A.Use the option 'mergeSchema' set to true.
B.Define a schema explicitly using StructType.
C.Convert the JSON to CSV before reading it.
D.Increase the number of partitions during read.
AnswerB

Explicitly defining the schema using StructType ensures that Spark knows exactly how to map the nested JSON fields to the DataFrame columns. This eliminates the need for Spark to perform a full file scan for inference, preventing errors on complex datasets and ensuring consistent data types for downstream analytical tasks.

Why this answer

When dealing with complex, nested JSON, relying on automated schema inference often fails or results in poor performance. Manually defining a schema using StructType allows the analyst to explicitly control data types, including handling nested structures. This approach is more robust for production-grade pipelines where data consistency is required, as it prevents schema mismatch errors during ingestion and improves overall query performance and type safety within the Spark environment.

Exam trap

Candidates often think schema inference handles nested JSON automatically, but complex or deep nesting typically causes inference failures or incorrect data types, requiring an explicit StructType definition.

268
MCQmedium

A data analyst needs to ingest a daily batch of comma-separated value (CSV) files stored in cloud object storage into a Databricks Delta table. The files contain a header row, use a semicolon (;) as the delimiter, and occasionally include embedded newlines within quoted fields. Which approach ensures the parser correctly handles the multi-line fields and missing values?

A.Use spark.read.table() with default parameters because Spark automatically infers delimiters and multiline configurations for all incoming CSV files.
B.Apply the spark.read.format("csv").option("delimiter", ";").option("header", "true").option("multiLine", "true").load(path) command.
C.Convert the CSV files into Parquet format using a local Python script outside Databricks before uploading them to the workspace file system.
D.Execute a standard SQL COPY INTO command without specifying any options, as Delta tables automatically resolve non-standard delimiters.
AnswerB

Setting the delimiter option to a semicolon, enabling header parsing, and setting multiLine to true allows Spark to correctly interpret complex rows. This combination handles formatting variations common in enterprise data dumps without dropping records.

Why this answer

Configuring the Spark CSV reader with explicit options like multiLine set to true ensures that records containing embedded newlines are not split prematurely across rows. Data analysts frequently encounter messy raw external datasets where standard single-line parsing fails, leading to corrupted row counts and schema mismatches. Explicitly defining formatting options guarantees reliable ingestion before downstream business intelligence reporting takes place.

Exam trap

Candidates frequently overlook the 'multiLine' option. They assume standard CSV parsing handles embedded newlines automatically, which leads to corrupted data rows and schema errors during the ingestion process.

269
MCQeasy

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

A.MEAN()
B.AVERAGE()
C.AVG()
D.TOTAL_AVG()
AnswerC

AVG() is the standard SQL function used to calculate the mean of numeric values in a column. It ignores NULL values by default and returns the arithmetic average of the set, making it the correct choice for calculating averages in Databricks SQL for reporting and data aggregation purposes.

Why this answer

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.

Exam trap

Candidates sometimes confuse aggregate functions like AVG() with scalar or window functions, or misspell standard SQL aggregation keywords.

270
MCQhard

An analyst runs a notebook against an all-purpose cluster and notices that the first cell, which reads a large Delta table, takes several minutes while subsequent similar queries finish quickly. The analyst wants to understand why this happens and ensure the same quick response on later runs. Which explanation best describes the underlying behavior?

A.The first query compiled a query plan that Databricks permanently stored in the table metadata
B.The first query used a different execution engine, and later queries switched to Photon
C.The Delta table was automatically optimized after the first read, rewriting all files
D.The cluster had to start and initialize, and Delta caching kept data in memory for later queries
AnswerD

When an all-purpose cluster starts, it must provision resources and initialize the Spark and Databricks runtime, which delays the first query. As the first read scans the Delta table, Databricks caches the underlying files on the cluster's local SSD, so subsequent queries against the same data skip remote storage reads and complete much faster. This explains both the initial delay and the later speedup.

Why this answer

The slow first cell reflects two overlapping costs: the all-purpose cluster must start and initialize before any query runs, and the initial scan reads Delta files from remote storage. Once that scan completes, Databricks caches the data on local SSD, so later queries that touch the same files avoid remote reads. Warm compute plus caching explains the dramatic speedup on subsequent runs.

Exam trap

The trap here is attributing the speedup to table optimization or an engine change, when the real cause is cluster startup latency combined with automatic disk caching of Delta data during the first read.

271
MCQeasy

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

A.Local workspace-level user management.
B.Account-level identity provisioning using SCIM.
C.Database-level authentication via JDBC drivers.
D.Private Link connectivity for all users.
AnswerB

SCIM provisioning at the account level ensures that identities are synchronized from your identity provider directly to the Databricks account. This is the prerequisite for Unity Catalog because it provides a unified identity namespace, allowing for consistent access control policies across all connected workspaces and catalogs within the metastore.

Why this answer

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.

Exam trap

Candidates frequently confuse local workspace-level user management with the account-level SCIM provisioning required for Unity Catalog governance.

272
MCQmedium

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?

A.Create a view that pre-calculates year-over-year growth and profit margin
B.Add instructions in the Genie Space that define the formulas for year-over-year growth and profit margin
C.Train a custom machine learning model to predict the metrics
D.Restrict the Genie Space to only answer questions that include the exact metric names
AnswerB

Adding natural language instructions that specify the exact formulas, such as 'year-over-year growth = (current quarter revenue - same quarter last year revenue) / same quarter last year revenue' and 'profit margin = net income / revenue', ensures Genie applies these calculations consistently. This approach handles various phrasings and combinations with other dimensions because Genie uses the definitions to generate SQL dynamically.

Why this answer

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.

Exam trap

The trap here is assuming that a pre-calculated view is sufficient, but it cannot handle the diverse and dynamic questions executives might ask.

273
Multi-Selecthard

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?

Select 2 answers
A.Shuffle Read/Write bytes are significantly higher than the input data size.
B.The 'Scan' node shows high throughput and low execution time.
C.Task duration distribution shows one task taking much longer than others.
D.The query shows a high number of file metadata operations.
E.The query plan indicates a broadcast hash join is being used.
AnswersA, C

High shuffle volumes compared to input data often suggest unnecessary data movement or poor join strategies. If the shuffle size is much larger than the original input, the query is likely re-distributing data inefficiently, which adds substantial network latency and increases the risk of executor memory errors.

Why this answer

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.

Exam trap

Candidates often look only at total query execution time instead of specific distributed metrics like shuffle read/write bytes and skewed task durations to diagnose bottlenecks.

274
MCQmedium

A data analyst notices that a query performing a join between a large fact table and a small dimension table is slow. The analyst wants to ensure that the small table is broadcasted to all executors to avoid a shuffle. Which configuration should the analyst adjust to increase the likelihood of a broadcast join?

A.spark.sql.autoBroadcastJoinThreshold
B.spark.sql.adaptive.enabled
C.spark.sql.shuffle.partitions
D.spark.sql.broadcastTimeout
AnswerA

This configuration sets the maximum size (in bytes) of a table that can be broadcasted in a join. By increasing this threshold, the analyst allows larger tables to be considered for broadcast, which can eliminate the shuffle of the large table. In this scenario, if the small dimension table's size is below the threshold, it will be broadcasted, improving join performance.

Why this answer

The key to encouraging a broadcast join is to ensure the small table's size is below the autoBroadcastJoinThreshold. By increasing this threshold, the analyst allows Spark to consider the small table for broadcast, eliminating the shuffle of the large table and significantly speeding up the join.

Exam trap

The trap here is thinking that enabling adaptive query execution alone guarantees a broadcast join, when it still depends on the autoBroadcastJoinThreshold setting.

275
MCQeasy

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?

A.GRANT SELECT ON TABLE quarterly_sales TO `colleague@company.com`;
B.GRANT ALL PRIVILEGES ON SCHEMA sales_schema TO `colleague@company.com`;
C.GRANT READ ACCESS ON CATALOG main TO `colleague@company.com`;
D.GRANT USE TABLE ON quarterly_sales TO `colleague@company.com`;
AnswerA

Granting the SELECT privilege directly on a specific table provides precise access control. This ensures the recipient can query only the targeted dataset while adhering strictly to the principle of least privilege across the schema.

Why this answer

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.

Exam trap

Candidates often confuse object-level privileges with administrative commands or forget that parent containers like schemas and catalogs require traversal permissions (USAGE) in addition to the table-level SELECT privilege.

276
MCQmedium

A data analyst in the 'marketing' group needs to query the table `main.sales.leads` but should not be able to read the underlying data files directly. The analyst currently has `SELECT` on the table and `READ FILES` on the external location where the table's data resides. Which Unity Catalog privilege should the analyst's `READ FILES` on the external location be removed to enforce least privilege?

A.`SELECT` on the table
B.`BROWSE` on the catalog `main`
C.`READ FILES` on the external location
D.`USAGE` on the schema `main.sales`
AnswerC

Removing `READ FILES` on the external location prevents the analyst from bypassing Unity Catalog table-level controls and reading raw data files directly. The analyst retains `SELECT` on the table, so querying through SQL or DataFrames continues to work. This enforces least privilege because file-level access is no longer needed for the analyst's role, and Unity Catalog manages access at the table level.

Why this answer

To enforce least privilege, the analyst should not have direct file access when table-level access suffices. `READ FILES` on the external location allows bypassing Unity Catalog table permissions, so it must be removed. `SELECT` on the table remains to allow querying, while `USAGE` on the schema and `BROWSE` on the catalog are unrelated to file-level reads.

Exam trap

The trap here is assuming that `READ FILES` is required for querying a table, when in fact `SELECT` on the table is sufficient and `READ FILES` only grants direct file access.

277
MCQmedium

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?

A./*+ MERGE(dimension_table) */
B./*+ SHUFFLE_HASH(dimension_table) */
C./*+ BROADCAST(dimension_table) */
D./*+ CACHE(dimension_table) */
AnswerC

The BROADCAST hint explicitly requests that the Catalyst optimizer send a copy of the specified table to all worker nodes in the cluster. This enables a broadcast hash join, completely eliminating the network shuffle phase and improving performance for joins involving small tables.

Why this answer

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.

Exam trap

Candidates frequently mistake generic Spark configuration settings for SQL-specific hints, or they attempt to use incorrect syntax like '/*+ BROADCASTJOIN */' instead of the standard '/*+ BROADCAST(table) */' syntax.

278
MCQmedium

You are modeling a table where users need to query based on a 'user_id' but also need to perform historical point-in-time analysis. Which feature is most appropriate?

A.Implement a materialized view with a static snapshot.
B.Use Delta Time Travel.
C.Create a new table for every daily load.
D.Store all historical records in a single table with an 'is_current' flag.
AnswerB

Time Travel in Delta Lake allows querying data as of a specific version or timestamp using the 'VERSION AS OF' or 'TIMESTAMP AS OF' clauses. This provides seamless access to historical states without requiring extra storage for snapshots or complex architectural workarounds for point-in-time analytical requirements.

Why this answer

Time Travel is a native feature of Delta Lake that allows users to query previous versions of a table using a timestamp or version number. This is crucial for point-in-time analysis, as it lets analysts reproduce reports or audit changes without needing to maintain manual snapshots or separate history tables. It leverages the transaction log to provide a consistent, historical view of the data efficiently.

Exam trap

Candidates often confuse Time Travel with Change Data Feed (CDF). While both relate to history, they are used for different purposes: Time Travel for state snapshots and CDF for granular row-level changes.

279
MCQhard

A data analyst needs to create a new table that stores only aggregated daily sales totals derived from an existing Unity Catalog table, and the result must be refreshed nightly. They want the simplest object that persists the results and can be queried by other analysts. Which approach should they use?

A.Create a temporary view with `CREATE TEMPORARY VIEW sales.daily_totals AS SELECT ...` and grant other analysts access.
B.Create a view with `CREATE VIEW sales.daily_totals AS SELECT ...` so the aggregation is recomputed on every query.
C.Create an external table at a cloud storage path and populate it with `INSERT INTO` after each aggregation run.
D.Create a materialized view with `CREATE MATERIALIZED VIEW sales.daily_totals AS SELECT ...` and schedule a refresh.
AnswerD

A materialized view stores the computed result and can be refreshed on a schedule, which matches the requirement to persist daily aggregates and update them nightly. Other analysts can query it like a table, and Databricks manages the refresh and incremental maintenance where possible. This is the purpose-built object for persisted, refreshable aggregation results in Databricks SQL.

Why this answer

Persisting aggregated results that refresh on a schedule is precisely what a materialized view provides. It stores computed data, supports scheduled refresh, and is queryable like a table by other users. Plain views recompute each time, temporary views do not persist or share, and external tables require manual orchestration, so the materialized view is the simplest fitting object.

Exam trap

The trap here is assuming a standard view persists results; in fact a view stores only the query text and recomputes the aggregation on every access.

280
MCQmedium

A data analyst runs a Databricks SQL query on a Delta table that has 500,000 small files. The query applies a filter on a high-cardinality column but still scans all files, resulting in slow performance. The analyst wants to reduce the number of files scanned without changing the query logic. Which action should the analyst take?

A.Set spark.sql.adaptive.enabled to false to force a static execution plan.
B.Run OPTIMIZE on the table to compact small files and improve data skipping.
C.Increase the driver node size to handle the large number of files.
D.Add a Z-ORDER BY clause to the query to sort results by the filtered column.
AnswerB

OPTIMIZE compacts small files into larger ones and collects statistics, which enables Delta data skipping. With 500,000 small files, the query must open each file; compaction reduces file count and allows the engine to skip files based on min/max statistics, directly improving scan performance without altering query logic.

Why this answer

Compacting small files with OPTIMIZE reduces the number of files that must be opened and enables Delta data skipping through collected statistics. This directly addresses the root cause: too many small files causing excessive I/O. Other options either disable useful optimizations, misuse syntax, or only mask the problem without reducing file count.

Exam trap

The trap here is assuming that increasing cluster resources or altering query syntax will solve a small-file problem, when the real fix is file compaction and statistics collection.

281
Multi-Selecthard

An analyst is preparing a Genie Space and wants to ensure users get the most accurate answers while maintaining security. Which TWO actions should the analyst take? (Choose two)

Select 2 answers
A.Exclude highly sensitive columns that are not needed for general analysis.
B.Enable 'auto-query' for all users to maximize data exploration.
C.Provide descriptive names and comments for all tables and columns in Unity Catalog.
D.Grant all users the 'Owner' role on the Genie Space to allow for maximum customization.
E.Remove all constraints and indexes to allow the AI to move faster.
AnswersA, C

Minimizing the schema surface area reduces the risk of the model inadvertently exposing sensitive PII or financial data. By strictly limiting the columns available to Genie, the analyst enforces the principle of least privilege, ensuring that users can only query the data they truly need for their business tasks.

Why this answer

Proper curation of the Genie Space is vital for performance and security. By including only the necessary columns and providing explicit instructions, the analyst reduces noise and ensures the AI model operates within the boundaries of the organization's data governance framework. These steps directly impact the quality of the SQL generated and the safety of the information exposed to end-users via the AI interface.

Exam trap

Candidates often focus only on technical performance (like indexing) while ignoring the importance of metadata curation. They forget that the AI model relies heavily on descriptive names to accurately map natural language.

282
Multi-Selecthard

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?

Select 2 answers
A.Using the 'Create or modify table from file upload' feature in the 'Add' menu.
B.Using the Databricks File System (DBFS) CLI to push the file to the workspace.
C.Using the SQL Editor to copy and paste the raw text into a new table.
D.Uploading the file to a Unity Catalog Volume using the Catalog Explorer.
E.Using the Workspace Sidebar 'Import' button to select the local CSV file.
AnswersA, D

The 'Create or modify table from file upload' interface allows users to drag and drop files directly into the browser. This tool automatically infers the schema, provides a data preview, and handles the conversion of the local CSV into a managed Delta table within the selected schema and catalog.

Why this answer

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.

Exam trap

Candidates often assume they must use 'dbutils.fs.put' or write complex code to upload files. They overlook the simple, built-in UI features available directly within the Databricks Catalog Explorer.

283
MCQhard

A data analyst has a notebook that reads a Delta table and produces summary statistics. The analyst wants colleagues to see the latest results in a dashboard without rerunning the notebook manually each time. Which Databricks capability should the analyst use to keep the dashboard current?

A.Add a markdown cell describing how to run the notebook
B.Increase the cluster size used by the notebook
C.Convert the notebook into a Databricks SQL dashboard and schedule the underlying query
D.Export the notebook results to a static PDF for distribution
AnswerC

A Databricks SQL dashboard is backed by saved queries that can be scheduled to refresh, so viewers see updated results without manual notebook runs. Migrating the summary logic into a query and scheduling it keeps the dashboard current automatically. This matches the goal of sharing live results while removing the need for manual execution.

Why this answer

Databricks SQL dashboards are built on saved queries that can be scheduled to refresh, so viewers see current results without anyone rerunning a notebook. Recreating the summary logic as a query and scheduling it automates currency. Documentation, larger clusters, and static exports do not refresh a dashboard, so they fail to meet the requirement of up-to-date shared results.

Exam trap

The trap here is treating a notebook visualization as automatically shared and refreshed, when dashboards require scheduled queries to stay current without manual runs.

284
MCQhard

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?

A.Create a separate view for each user group.
B.Apply Row Filters and Column Masks in Unity Catalog.
C.Use hard-coded SQL 'WHERE' clauses in every notebook.
D.Move sensitive data to a completely separate, private workspace.
AnswerB

Row filters and column masks are built-in Unity Catalog features that enforce fine-grained access control dynamically. They allow administrators to define security rules that automatically filter rows or mask sensitive column values based on the user's role or attributes, providing a robust, scalable, and centralized method for protecting sensitive data assets.

Why this answer

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.

Exam trap

Candidates often suggest creating separate filtered views or duplicate tables for different user groups, which violates governance best practices and creates maintenance overhead.

285
MCQmedium

Refer to the exhibit. Why might the analyst choose to create a new table with 'AS SELECT *' instead of just running OPTIMIZE on the original table?

A.The original table is read-only and cannot be modified
B.It is the only way to delete old data from the table
C.It provides a cleaner way to reorganize data and apply Z-Ordering
D.CTAS automatically creates indexes on the table
AnswerC

Using CTAS allows the analyst to rebuild the table with a clean file layout from the start. By applying Z-Ordering during or immediately after the creation, the analyst ensures the data is optimally structured, which is often more efficient than attempting to reorganize a heavily fragmented, existing table.

Why this answer

Creating a new table via CTAS (Create Table As Select) allows the analyst to re-partition or re-cluster the data from scratch, which is often faster and cleaner than reorganizing an existing, fragmented table. This process also allows for the application of Z-Ordering during the initial creation, ensuring that the new table has the optimal data layout from the first day of its existence.

Exam trap

Candidates assume CTAS is always more expensive or slower than OPTIMIZE, failing to recognize that for highly fragmented tables, a full rewrite is often the cleanest and most efficient path.

286
MCQhard

Refer to the exhibit. A data analyst reviews a physical execution plan where a table is scanned and shuffled multiple times in succession during a self-join operation. What underlying anti-pattern in the query construction likely caused this redundant exchange?

A.The analyst applied a broadcast hint to a table that exceeded the broadcast size configuration threshold.
B.The table was referenced multiple times in complex join logic without caching, causing Spark to recompute its lineage.
C.The partition column data type mismatch forced Catalyst to duplicate the partition directories.
D.Automatic table compaction via OPTIMIZE was running concurrently during the execution phase.
AnswerB

Repeated references to the same table without caching force Spark to re-read and re-shuffle its lineage for each join branch. Persisting or caching the table breaks this recomputation, eliminating the redundant exchanges visible in the physical plan.

Why this answer

Redundant exchanges and scans in an execution plan often occur when a DataFrame or table is referenced multiple times in complex joins or transformations without being cached or persisted. Spark recomputes the lineage from storage for each reference, leading to duplicate scans and shuffles.

Exam trap

Candidates often blame external factors like cluster configuration for performance issues, overlooking the fundamental Spark behavior where un-cached DataFrames are recomputed from their source lineage upon every reference.

287
MCQmedium

A data analyst is modeling a Delta table in Databricks SQL that stores product inventory snapshots. The table has columns snapshot_date (DATE), product_id (STRING), warehouse_id (STRING), and quantity_on_hand (INT). Queries frequently filter on snapshot_date and then join to a product dimension. The analyst wants to minimize the amount of data scanned for date-filtered queries while keeping the table simple and avoiding manual file management. Which approach best achieves this in Databricks SQL?

A.Convert the table to a view that filters snapshot_date to the last 30 days.
B.Use Z-ORDER BY on product_id and warehouse_id when writing the table.
C.Create a Bloom filter index on product_id to speed up date-range queries.
D.Partition the Delta table by snapshot_date using PARTITIONED BY (snapshot_date) in the CREATE TABLE statement.
AnswerD

Partitioning by snapshot_date lets Databricks SQL prune files for date-filtered queries, reducing data scanned. Because Delta Lake manages partition metadata automatically, the analyst avoids manual file management. This directly matches the scenario's need to minimize scanned data for date predicates while keeping the table simple, and it works with subsequent joins to a product dimension.

Why this answer

Partitioning by snapshot_date aligns the physical layout with the dominant filter, enabling file pruning and lower data scanned. Delta Lake manages partitions automatically, so the analyst avoids manual file management. The other choices either target the wrong column, require ongoing maintenance, or change query results, so they do not satisfy the scenario's goals.

Exam trap

The trap here is assuming that any performance feature, such as a Bloom filter or Z-ORDER, will accelerate all filters, when in fact each optimizes specific access patterns and must match the columns used in the WHERE clause.

288
Multi-Selectmedium

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?

Select 2 answers
A.Enforce strict schema validation during ingestion.
B.Optimize data skipping for frequently filtered columns.
C.Automatically resolve high-cardinality partition issues.
D.Implement row-level security policies.
E.Manage concurrent write conflicts in Delta tables.
AnswersB, C

Liquid clustering dynamically organizes data based on the columns specified in the CLUSTER BY clause. This creates metadata that allows the engine to skip unnecessary files during query execution. By focusing on frequently filtered columns, analysts can dramatically improve performance without the management burden of traditional static partitioning.

Why this answer

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.

Exam trap

Candidates often confuse Liquid Clustering with traditional partitioning or Z-Ordering. They struggle to identify that it specifically solves the 'small file' and 'high cardinality' issues that plague static partitioning.

289
MCQeasy

You are a data analyst working in Databricks SQL. Your workspace has Unity Catalog enabled. You need to inspect the metadata of a table named `customers` in the `sales` schema of the `retail` catalog, but you do not want to return any rows of data. Which SQL statement should you use?

A.DESCRIBE TABLE retail.sales.customers;
B.SELECT * FROM retail.sales.customers LIMIT 0;
C.SHOW CREATE TABLE retail.sales.customers;
D.SHOW TABLES IN retail.sales;
AnswerA

DESCRIBE TABLE (or DESC TABLE) returns the column names, data types, and comments for the specified table without scanning or returning any data rows. It works with the fully qualified three-part Unity Catalog namespace catalog.schema.table, so it correctly targets retail.sales.customers and satisfies the requirement to inspect metadata only.

Why this answer

In Databricks SQL with Unity Catalog, DESCRIBE TABLE is the standard command to retrieve column names, types, and comments for a table, and it accepts the three-part namespace catalog.schema.table. The other commands either list table names, run a data query, or return a CREATE statement, none of which match the requirement to inspect column metadata without returning rows.

Exam trap

The trap here is assuming that any command containing the table name will return metadata, when each metadata command returns a different shape of information.

290
MCQhard

A data analyst runs a query that joins a fact table to a dimension table and groups by a dimension attribute. The Query Profile shows a BroadcastHashJoin, yet the query still takes much longer than expected and one stage shows a single task consuming most of its runtime. The dimension table is small, so the analyst suspects the bottleneck is elsewhere. Which explanation best fits the evidence?

A.The grouping key is skewed, so one task in the aggregation stage processes far more rows than the others
B.The fact table lacks statistics, causing Spark to choose a nested loop join
C.The broadcast of the small table is failing and silently falling back to a sort-merge join
D.The dimension table is too large to broadcast and must be repartitioned before joining
AnswerA

A BroadcastHashJoin eliminates shuffle for the join, so the remaining bottleneck is likely the post-join aggregation. If one dimension attribute value is extremely common, the group-by partitions unevenly and a single task handles most rows, producing exactly the long-running straggler task the profile shows despite the efficient join.

Why this answer

Because the broadcast join removes shuffle from the join itself, the remaining slow stage with one dominant task is the aggregation. An unevenly distributed grouping key concentrates most rows into one partition, so that task runs far longer than its peers. Addressing the skew in the group-by, not the join, is what improves runtime.

Exam trap

The trap here is assuming the slow join is the culprit, when an efficient broadcast join can coexist with a skewed aggregation downstream.

291
Multi-Selecthard

You are an analyst in a Unity Catalog-enabled Databricks workspace. A colleague has shared a table `finance.transactions` with you, and you need to confirm what privileges you currently hold on it before running a sensitive query. Which TWO of the following statements about inspecting privileges in Unity Catalog are accurate? (Choose two.)

Select 2 answers
A.You can run SHOW GRANTS ON TABLE finance.transactions to see the privileges granted on that specific table.
B.Privileges granted at the catalog or schema level are inherited by the table, so SHOW GRANTS ON TABLE may not show every effective privilege you hold.
C.You must be the table owner or a metastore admin to run SHOW GRANTS ON TABLE on a table you can query.
D.SHOW GRANTS ON TABLE finance.transactions will also list the privileges granted on the parent catalog and schema automatically.
E.SHOW GRANTS ON TABLE returns only the privileges of the current user and hides grants to other principals for privacy.
AnswersA, B

SHOW GRANTS ON TABLE returns the privileges granted on the specified securable, including which principals hold SELECT, MODIFY, or other rights. It is the direct way to inspect table-level grants in Unity Catalog and works with the three-part catalog.schema.table namespace, so it correctly answers what privileges exist on the transactions table.

Why this answer

Unity Catalog uses a hierarchical privilege model, so inspecting a table involves both the direct grants on the table and the inherited grants from its catalog and schema. SHOW GRANTS ON TABLE reveals direct table grants, while effective access can come from higher levels. The other statements misstate the command's scope, required permissions, or inheritance behavior and would lead an analyst to wrong conclusions about their access.

Exam trap

The trap here is assuming that SHOW GRANTS ON TABLE shows all effective privileges, when inheritance from catalog and schema means you must check multiple levels.

Page 3

Page 4 of 4

All pages