Courseiva

Databricks Certified Data Analyst Associate (Databricks-DA-Assoc) — Questions 151–225

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

Page 2

Page 3 of 4

Page 4
151
MCQeasy

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

A.The Control Plane
B.The Databricks Workspace
C.The Data Plane
D.The Unity Catalog
AnswerB

The Databricks Workspace is the user-facing environment for creating and managing data assets. It serves as the primary hub where analysts develop notebooks, manage permissions, and track experiments, offering a cohesive experience for team-based data analysis and collaborative data science projects within an organization.

Why this answer

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

Exam trap

Candidates frequently confuse the 'Workspace' with the 'SQL Warehouse' or 'Compute' clusters. They mistake the compute engine for the interface where users actually organize and manage their data assets.

152
MCQeasy

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

A.Pie chart
B.Histogram
C.Scatter plot
D.Funnel chart
AnswerB

A histogram effectively groups continuous data into discrete 'bins' and represents the count of observations within each bin as a bar. This visualization is the industry standard for analyzing the shape, central tendency, and dispersion of a single numerical variable, making it perfect for identifying patterns like normal distributions.

Why this answer

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

Exam trap

Candidates often confuse histograms with bar charts when trying to display continuous numerical distributions, ignoring the need for data binning.

153
MCQmedium

A data analyst needs to optimize query performance for a large sales table that is frequently filtered by 'region_id'. Which physical data modeling strategy should be implemented to minimize data scanning?

A.Implement a primary key constraint on the region_id column.
B.Execute the ANALYZE TABLE command periodically without any data clustering.
C.Apply Z-Ordering on the region_id column.
D.Change the file format to CSV to allow easier manual partitioning.
AnswerC

Z-Ordering maps multi-dimensional data to one dimension while preserving locality. By clustering data with similar 'region_id' values into the same files, the engine can utilize min-max statistics to skip entire files that do not contain the requested region, drastically reducing I/O and improving overall query latency.

Why this answer

Z-Ordering is a technique to co-locate related information in the same set of files, significantly reducing the amount of data read during filter operations. By applying Z-Ordering on the 'region_id' column, the Databricks engine can skip irrelevant files more effectively during query execution. This strategy is essential for large-scale datasets where traditional partitioning alone may lead to excessive file fragmentation or suboptimal data distribution across the cluster nodes.

Exam trap

Candidates often confuse partitioning strategies with Z-Ordering, recommending partitioning for columns with high cardinality like IDs instead of applying Z-Ordering.

154
MCQhard

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

A.The temperature is too low, causing the model to finish early.
B.The 'max_tokens' limit is too low for the complexity of the output.
C.The model version is incompatible with the SQL Warehouse.
D.The SQL Warehouse is timing out during the query generation.
AnswerB

The 'max_tokens' parameter restricts the total length of the model's response. When this limit is reached, the model simply stops, leading to truncated output. For complex analytical queries that involve joins, aggregations, and explanations, 500 tokens is often insufficient, necessitating an increase in the limit to ensure complete outputs.

Why this answer

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

Exam trap

Candidates often blame network latency or database timeout settings when outputs are cut off, missing the token generation threshold constraint in the configuration.

155
MCQmedium

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

A.Use GRANT SELECT on the table to specific users or groups.
B.Delete the sensitive rows from the table manually.
C.Implement column masking policies on sensitive columns.
D.Rotate the workspace passwords weekly.
E.Disable the table for all users except the owner.
AnswerA, C

Standard SQL access controls allow administrators to explicitly define who can read a table. This is the fundamental mechanism for data governance in Unity Catalog, ensuring that only authorized users or service principals can execute queries against the sensitive data, adhering to the principle of least privilege.

Why this answer

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

Exam trap

Candidates frequently select broad workspace-level admin roles or native cloud storage policies instead of focusing on Unity Catalog native primitives like GRANT and column masking.

156
Multi-Selectmedium

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

Select 3 answers
A.Centralized access control using standard SQL.
B.Automated data lineage capture.
C.Direct management of physical cloud infrastructure servers.
D.A unified namespace for data assets.
E.Automatic generation of Power BI dashboards.
AnswersA, B, D

Unity Catalog enables administrators to manage permissions using standard SQL statements like GRANT and REVOKE. This unified approach makes security management consistent across different workspaces, reducing the complexity and manual overhead associated with managing user rights in a distributed data environment, while ensuring granular control over sensitive data assets.

Why this answer

Unity Catalog is the centralized governance layer for Databricks. It provides three critical capabilities: a unified namespace for data across workspaces, centralized access control using standard SQL GRANT/REVOKE syntax, and automated data lineage tracking. These features ensure that data is secure, discoverable, and auditable across an entire organization.

For analysts, this simplifies the process of finding data and ensures that security policies are consistently applied, regardless of the workspace or tool being used.

Exam trap

Candidates often select features like 'data ingestion' or 'query optimization,' which are not core governance features of Unity Catalog, leading to incorrect selections in multi-choice questions.

157
MCQmedium

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

A.Increase the number of partitions to 10,000
B.Use a broadcast hint for large-to-large table joins
C.Resize the SQL Warehouse to a larger instance type
D.Reduce the number of columns selected in the query
AnswerC

Resizing the SQL Warehouse to a larger instance type provides more RAM per node, which is the most direct solution for OOM errors occurring during memory-intensive operations like large joins or complex aggregations. It allows the engine to handle larger intermediate datasets without needing to spill to disk.

Why this answer

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

Exam trap

Candidates mistakenly suggest changing the data storage format or reducing the number of rows, which are inefficient compared to simply scaling the compute resources for the query.

158
MCQmedium

An analyst needs to query a table that has many small files resulting from frequent streaming ingestions. Which command is most appropriate to consolidate these files into larger, more efficient files for future analytical queries?

A.VACUUM table_name
B.OPTIMIZE table_name
C.REFRESH TABLE table_name
D.ALTER TABLE table_name REORGANIZE
AnswerB

OPTIMIZE performs file compaction in Delta Lake. It takes small, fragmented files and rewrites them into larger, optimized files. This process significantly improves read performance by reducing the number of files the query engine needs to scan, making it the correct solution for small file ingestion issues.

Why this answer

The OPTIMIZE command is the standard way to compact small files in Delta tables. It merges multiple small files into larger files, which reduces metadata overhead and improves read performance. By combining this with Z-Ordering, the data layout is optimized for common query patterns, ensuring that the SQL engine reads only the necessary data blocks during execution, which is vital for efficient data warehousing.

Exam trap

Candidates often pick commands like VACUUM or REORG, which serve different purposes. They fail to realize that OPTIMIZE is specifically designed to handle file compaction for performance improvement in Delta tables.

159
MCQmedium

Which of the following describes the purpose of 'Query History' in Databricks SQL?

A.To permanently store query results in cold storage.
B.To monitor query performance and troubleshoot errors.
C.To manage user access permissions for tables.
D.To automatically optimize slow SQL code.
AnswerB

Query History offers detailed insights into query execution, including latency, status, and resource usage. This allows analysts to effectively identify bottlenecks, troubleshoot failed queries, and review the execution plan, which is essential for ongoing optimization and maintaining high performance for business-critical analytical reporting and dashboards.

Why this answer

Query History provides a comprehensive log of all queries executed against the SQL Warehouse. It includes critical information such as execution time, user identity, and status. For analysts, this tool is indispensable for identifying slow-running queries, debugging execution errors, and auditing data access.

By reviewing the query profile, analysts can gain deep insights into how their code is performing, which is vital for continuous performance tuning and optimizing SQL code in production.

Exam trap

Candidates often mistake Query History for a table creation log or a cluster configuration tool, rather than recognizing it as a monitoring interface for execution metrics and performance troubleshooting.

160
MCQhard

Refer to the exhibit. Given the provided JSON configuration for a Databricks cluster, what is the primary use case for this resource?

A.Running automated production ETL jobs.
B.Interactive data analysis and notebook development.
C.Long-running streaming data ingestion.
D.Batch processing of large ML models.
AnswerB

The all-purpose cluster type is designed for interactive development in notebooks. It allows users to start, stop, and restart clusters to run ad-hoc queries and perform data exploration. This configuration is standard for analytical tasks where developers need a responsive environment to test code and visualize findings in real-time.

Why this answer

Refer to the exhibit. The configuration shows an all-purpose cluster with autoscaling enabled. All-purpose clusters are primarily used for interactive development and data exploration within notebooks.

Because they consume more DBU resources compared to job clusters, setting an idle termination limit is a cost-optimization best practice. Understanding cluster types is fundamental for managing platform costs and ensuring that resources are allocated appropriately based on the specific requirements of the workload being executed.

Exam trap

Candidates frequently confuse all-purpose clusters with job clusters, assuming the configuration is for production pipelines when the presence of autoscaling and idle termination clearly points to interactive, exploratory development use cases.

161
Multi-Selectmedium

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

Select 2 answers
A.Enable Result Set Caching on the SQL Warehouse.
B.Use SELECT * exclusively in all production dashboards.
C.Convert complex frequently-queried tables into Materialized Views.
D.Increase the warehouse size to 4XL regardless of query complexity.
E.Always use ORDER BY in every subquery.
AnswersA, C

Result set caching stores the output of repeated queries in the warehouse's local storage. When a subsequent user runs an identical query, the warehouse returns the cached result immediately without re-executing the query logic. This drastically reduces latency and saves compute resources during high-concurrency periods for frequent dashboards.

Why this answer

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

Exam trap

Candidates often suggest scaling out compute clusters manually or rewriting queries, ignoring built-in declarative features like Result Set Caching and Materialized Views designed for concurrency.

162
MCQmedium

A data analyst builds an AI/BI Genie Space over a Unity Catalog table named sales.transactions. Business users report that when they ask questions such as "total revenue by region," Genie returns figures that include cancelled orders, which should be excluded. The analyst wants Genie to always apply this filter. What should the analyst do?

A.Create a SQL warehouse that only returns rows where status is not cancelled.
B.Create a view in Unity Catalog that filters out cancelled orders, and point the Genie Space at that view.
C.Ask business users to include "excluding cancelled orders" in every question.
D.Add an instruction to the Genie Space telling it to filter out cancelled orders.
AnswerB

Genie queries the tables and views configured for the space. By replacing the base table with a view that already excludes cancelled orders, every generated SQL statement inherits the filter, so business users get correct revenue without needing to phrase their questions differently. This is the cleanest way to enforce a permanent business rule.

Why this answer

The most reliable way to enforce a permanent business rule is to define it in the data layer. A Unity Catalog view that filters out cancelled orders ensures every Genie-generated query respects the rule. Instructions and user prompts are not guaranteed to add the filter, and compute resources do not apply data logic.

Exam trap

The trap here is assuming that adding a natural-language instruction to the Genie Space will always cause Genie to append the desired filter to every generated query.

163
MCQmedium

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

A.Manual shutdown via the CLI.
B.Setting 'Auto-stop' in the Warehouse configuration.
C.Using a Cron job to delete the warehouse.
D.Reducing the cluster size to zero.
AnswerB

The 'Auto-stop' setting is specifically designed to terminate inactive SQL Warehouses after a set period of idle time. This feature is the most efficient and reliable method to ensure compute costs are minimized, as it automates the shutdown process without needing any manual action from the end-user.

Why this answer

Auto-stop is a fundamental cost-management feature for Databricks SQL. By configuring a reasonable auto-stop period, analysts can ensure that compute resources are released when idle. This is a critical best practice for maintaining a cost-efficient data platform, particularly for development or staging environments where queries may be sporadic.

Proper use of auto-stop directly impacts the overall cloud bill and is an essential configuration step for every SQL Warehouse instance.

Exam trap

Candidates often suggest manually stopping the warehouse or using cluster termination, overlooking the specific 'Auto-stop' configuration designed for SQL Warehouses to manage costs.

164
MCQmedium

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

A.Change the cluster configuration to use a different Spark version.
B.Request the workspace administrator to grant appropriate privileges in Unity Catalog.
C.Move the data to a local file in DBFS.
D.Re-create the table in a different schema.
AnswerB

Unity Catalog uses a centralized grant-based permission model. The administrator must explicitly grant the 'USE CATALOG' privilege to the user for the 'sales_data' catalog. This is the correct procedure for resolving access errors, ensuring that security remains intact while allowing the analyst to perform their required data operations.

Why this answer

Refer to the exhibit. This error indicates a failure in the Unity Catalog permission model. When a user lacks the necessary 'USE' or 'SELECT' grants on a catalog, they are blocked from accessing any data within it.

Understanding how Unity Catalog manages access at the catalog, schema, and table levels is vital for analysts to troubleshoot access issues and comply with organizational security policies.

Exam trap

Candidates often suggest rewriting the SQL query or recreating the table, missing that the underlying root cause is a missing Unity Catalog privilege grant.

165
MCQmedium

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

A.CREATE VIEW view_name AS SELECT * FROM table
B.CREATE TEMPORARY VIEW view_name AS SELECT * FROM table
C.CREATE LOCAL VIEW view_name AS SELECT * FROM table
D.CREATE SESSION TABLE view_name AS SELECT * FROM table
AnswerB

The TEMPORARY keyword limits the visibility of the view to the current SQL session. Once the session ends, the view is automatically dropped. This is perfect for complex queries requiring intermediate steps that do not need to be shared or persisted across the broader organizational data environment.

Why this answer

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

Exam trap

Candidates might select GLOBAL TEMPORARY VIEW, which persists across sessions, failing to satisfy the specific requirement for a view limited strictly to the current session duration.

166
MCQmedium

A data analyst is designing a Delta table in Databricks SQL to store clickstream events. The table will be queried primarily by filtering on event_date and then by user_id. The analyst wants to optimize data skipping for both columns without over-partitioning. Which approach should the analyst use?

A.Partition the table by event_date and use Z-ORDER on user_id.
B.Use Liquid Clustering on event_date and user_id.
C.Create a materialized view that pre-aggregates clicks by event_date and user_id.
D.Partition the table by user_id and use Z-ORDER on event_date.
AnswerB

Liquid Clustering allows incremental clustering on multiple columns without rewriting existing data, and it automatically optimizes data layout for both event_date and user_id. It avoids over-partitioning and supports efficient data skipping for filters on either column. This is the recommended approach for multi-dimensional clustering in Delta Lake, especially when query patterns involve multiple columns and data volume is large.

Why this answer

Liquid Clustering on event_date and user_id provides automatic, incremental clustering that optimizes data skipping for filters on either column without the drawbacks of over-partitioning. It is designed for multi-column clustering and adapts as data changes, making it ideal for clickstream analysis where queries filter by date and user. Partitioning or Z-ORDER alone would not achieve the same balanced performance.

Exam trap

The trap here is assuming that partitioning by a high-cardinality column like user_id is beneficial, when it actually causes small file and metadata issues.

167
MCQhard

Refer to the exhibit. An analyst notices that widget 2 is displaying incorrect trends. Based on the configuration provided, what is the most likely cause?

A.The widget type is set to 'line' instead of 'bar'.
B.The X-axis for widget 2 is not properly aligned to the date range.
C.The query for widget 2 does not aggregate data to match the X-axis.
D.The 'revenue' column is incompatible with the 'line' chart type.
AnswerC

If the query does not group by the 'date' column, the visualization may attempt to plot raw, un-aggregated data points, leading to a jagged or incorrect trend line. For a time-based chart, the SQL query must include an appropriate 'GROUP BY' clause to ensure the visualization displays the correct trend.

Why this answer

The configuration shows that both widgets share the same X-axis ('date'), but they are visualizing different metrics ('sales' vs 'revenue'). If the data is aggregated at different levels or filtered differently, the trends might appear conflicting. Identifying mismatches between data sources and visualization axes is a common troubleshooting step for analysts, ensuring that the dashboard accurately reflects the underlying business logic and prevents misleading interpretations of the reported data.

Exam trap

Candidates often look for complex syntax errors in the SQL code rather than checking whether the underlying aggregation levels match the visualization axes.

168
MCQhard

An analyst notices that the Genie space is consistently ignoring a specific column during query generation. What is the most effective way to force the model to consider this column?

A.Rename the column to start with 'Required_'
B.Add a column comment in Unity Catalog and mention it in instructions
C.Hard-code the column in the SQL Warehouse settings
D.Delete and recreate the Genie space
AnswerB

Combining Unity Catalog table comments with explicit Genie instructions provides a two-pronged approach for grounding. The LLM prioritizes information found in metadata, and the instructions provide the necessary behavioral context to ensure the column is utilized effectively during query generation, solving the issue of it being ignored.

Why this answer

LLMs can sometimes overlook columns if they aren't described clearly in the metadata or instructions. Providing explicit, high-quality descriptions in the table metadata (within Unity Catalog) or adding a specific note in the Genie space instructions ensures the model recognizes the column's semantic value and usage context, forcing it to include the information in its reasoning process during user interactions.

Exam trap

Candidates frequently try to fix missing column issues by changing user prompts, ignoring that the model needs descriptive metadata (comments) to understand the semantic value of a column.

169
MCQmedium

A data engineer has created a Delta table named `sales_summary` and needs to ensure that downstream analysts can only read rows where the `region` column matches their assigned territory. Which Databricks feature should be implemented to enforce this restriction securely at the row level?

A.Apply dynamic views using standard Spark SQL case statements that evaluate session user functions.
B.Implement Unity Catalog row filters using a SQL function that evaluates the current user.
C.Grant SELECT privileges on filtered partitions using Unity Catalog folder-level access controls.
D.Configure cluster-level environment variables to filter data frames automatically upon session start.
AnswerB

Unity Catalog row filters attach a SQL function to the table that evaluates the invoking user's identity, returning only rows matching their assigned territory. Enforcement occurs at query time in the governance layer, so analysts cannot bypass the restriction through direct table reads.

Why this answer

Row filters in Unity Catalog allow administrators and data owners to apply SQL-based filtering logic directly to tables, ensuring users only see authorized rows. This mechanism is critical for maintaining data governance, security, and compliance across diverse enterprise teams accessing shared datasets in Databricks.

Exam trap

Candidates often think custom view definitions or manual user-filtering clauses in queries are sufficient, ignoring native row-level security enforcement.

170
MCQeasy

A data analyst is reviewing audit logs in Databricks to investigate who accessed a sensitive table 'main.finance.payroll'. The analyst needs to identify the user, the timestamp, and the action performed. Which audit log service should the analyst query to find this information?

A.The 'unityCatalog' audit log service
B.The 'jobs' audit log service
C.The 'notebook' audit log service
D.The 'clusters' audit log service
AnswerA

The 'unityCatalog' audit log service records events related to Unity Catalog, including data access, privilege changes, and table operations. It captures the user, timestamp, and action for queries against tables like 'main.finance.payroll'. This is the correct service to query for access details.

Why this answer

Unity Catalog audit logs capture data access events, including user, timestamp, and action, for tables governed by Unity Catalog. The 'unityCatalog' service is the correct source for investigating access to 'main.finance.payroll'.

Exam trap

The trap here is confusing cluster or job logs with data access logs; only Unity Catalog logs record table-level access details.

171
MCQmedium

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

A.The notebook is not using a SQL Warehouse.
B.The underlying data files are being modified by a concurrent process.
C.The cluster has automatically terminated and restarted.
D.The notebook needs more memory to process the dataset.
AnswerB

If the data is not stored as a Delta table, or if the process bypasses transaction logs, concurrent writes will cause inconsistent reads. Delta Lake solves this by providing snapshot isolation, which guarantees that once a query starts, it reads a consistent version of the data, regardless of concurrent modifications.

Why this answer

Inconsistent results often stem from the underlying data being updated concurrently without proper versioning or isolation. Databricks provides ACID transactions via Delta Lake. If the analyst is querying raw files instead of Delta tables, or if the table is being updated by another process, the results might vary.

Understanding how Delta Lake handles snapshot isolation is crucial for ensuring that analytical reports remain consistent and reproducible over time.

Exam trap

Candidates often blame the notebook environment or caching, ignoring that concurrent writes to the underlying data source are the most frequent cause of inconsistent results.

172
MCQmedium

A data analyst is running a complex aggregate query in Databricks SQL that frequently times out before finishing. The underlying Delta table contains hundreds of gigabytes of historical log data partitioned by date. What is the most effective Databricks SQL query optimization technique to apply directly within the SQL statement?

A.Increase the overall timeout limit within the cluster configuration settings.
B.Include a explicit WHERE clause filtering on the date partition column to leverage partition pruning.
C.Convert the Delta table format into a standard Parquet format inside an external Hive metastore.
D.Wrap the main aggregation query inside a recursive common table expression.
AnswerB

Adding a WHERE clause on the date partition column lets Databricks SQL prune irrelevant partitions, reading only the required date range instead of scanning hundreds of gigabytes. This reduces I/O and prevents the query from timing out.

Why this answer

Applying a partition filter using the date column allows Databricks SQL to prune irrelevant files instantly, significantly reducing the data scan volume and preventing timeouts. This practice ensures queries execute efficiently over large historical datasets without needing constant infrastructure scaling or manual intervention.

Exam trap

Candidates often try to enable caching or rewrite the entire query logic. They overlook the most fundamental performance gain, which is simply using partition pruning to reduce the data scanned.

173
Multi-Selectmedium

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

Select 2 answers
A.Run ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS join_key
B.Manually partition the table by the join key column
C.Apply ZORDER BY on the columns used in the JOIN clause
D.Increase the number of partitions to the maximum allowed
E.Set the table format to Parquet instead of Delta
AnswersA, C

Computing statistics for specific columns provides the CBO with cardinalities and distribution information. This data allows the query engine to accurately estimate the size of the tables and choose the most efficient join algorithm, preventing suboptimal execution plans that lead to excessive shuffling and memory issues.

Why this answer

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

Exam trap

Candidates often select incorrect optimization techniques like partitioning on high-cardinality columns or assume auto-optimization handles everything without needing manual statistics collection via ANALYZE TABLE commands for the CBO.

174
MCQeasy

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

A.SHOW TABLES IN default LIKE 'sales';
B.DESCRIBE EXTENDED sales;
C.SELECT * FROM sales WHERE row_number <= 10;
D.SELECT * FROM sales LIMIT 10;
AnswerD

This statement returns the first 10 rows from the sales table, allowing the analyst to inspect column names and sample values without scanning the entire table. LIMIT restricts the result set to 10 rows, which is efficient for a quick preview. It is the standard way to preview data in Databricks SQL and works on Delta tables without extra configuration.

Why this answer

The analyst needs to preview actual rows from the sales table to understand its structure and content. Using SELECT * with LIMIT 10 retrieves a small sample efficiently. Other options return metadata or invalid syntax, none of which provide the required row-level data.

This approach is standard for initial data exploration in Databricks SQL.

Exam trap

The trap here is confusing metadata commands like DESCRIBE with data-returning queries, assuming they provide sample rows.

175
Multi-Selectmedium

A data analyst is preparing a Delta table in Unity Catalog for a dashboard that must return results quickly and consistently. The analyst needs to reduce the number of small files and improve data skipping on a frequently filtered column. (Choose two.)

Select 2 answers
A.Increase the SQL warehouse cluster size so more workers can scan small files in parallel.
B.Run OPTIMIZE on the table to compact small files into larger ones.
C.Run ZORDER BY on the frequently filtered column to co-locate related data.
D.Convert the table to a view so queries always compute results from the latest data.
E.Run VACUUM with a retention of zero hours to remove old files and speed up reads.
AnswersB, C

OPTIMIZE rewrites many small files into fewer, larger files, which reduces per-file overhead during reads. For a dashboard that scans the table repeatedly, fewer files mean less metadata and I/O work. This directly addresses the small-file problem described in the scenario and is a standard performance maintenance operation for Delta tables.

Why this answer

OPTIMIZE compacts small files into larger ones, reducing read overhead, while ZORDER BY co-locates related values so Delta can skip files using column statistics. Together they address both the small-file problem and the data-skipping requirement. VACUUM only deletes unreferenced files, views do not change storage layout, and larger clusters do not fix inefficient file organization.

Exam trap

The trap here is treating VACUUM or cluster scaling as performance tuning for file layout, when VACUUM only deletes old files and scaling does not change physical data organization.

176
MCQmedium

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

A.The join is extremely memory-intensive.
B.One of the tables is small enough to fit in memory.
C.The query is performing a cross join.
D.The data is skewed and needs re-partitioning.
AnswerB

The optimizer selects a broadcast join when it determines that one table is small enough to be replicated across all nodes. This avoids shuffling the larger table, which drastically speeds up execution. If the analyst sees this, it confirms the optimizer is leveraging the table size effectively.

Why this answer

A Broadcast Hash Join indicates that one side of the join is small enough to be sent to all executor nodes. This is an extremely efficient join type as it eliminates shuffling. For analysts, recognizing this confirms that the optimizer has successfully identified a small table, which is a positive performance indicator.

If a join is expected to be small but isn't broadcast, the analyst may need to check table statistics.

Exam trap

Candidates assume a Broadcast Hash Join means both tables are large, missing the fact that it is only triggered when one table is small.

177
MCQeasy

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

A.SELECT UNIQUE category FROM table
B.SELECT DISTINCT category FROM table
C.SELECT GROUP category FROM table
D.SELECT ONLY category FROM table
AnswerB

The DISTINCT keyword correctly instructs the SQL engine to scan the specified column and return only unique values, discarding duplicates. This is the standard, optimized syntax in Databricks SQL for identifying unique entries within a dataset, providing a clear and concise way to summarize categorical data attributes.

Why this answer

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

Exam trap

Candidates sometimes choose GROUP BY or COUNT when they simply need a list of unique values, forgetting that DISTINCT is the most direct syntax.

178
MCQeasy

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

A.The DESCRIBE DETAIL command
B.The Query Profile UI
C.The SHOW TABLES command
D.The EXPLAIN command in a notebook
AnswerB

The Query Profile UI is designed exactly for this purpose. It visualizes the execution plan, displaying metrics like row counts per operator, duration of stages, and memory usage. It allows analysts to pinpoint bottlenecks in their SQL queries and optimize them effectively using the provided detailed insights.

Why this answer

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

Exam trap

Candidates frequently confuse the Query Profile with the SQL Warehouse logs or the cluster event logs, failing to identify the specific UI tool designed for visual query execution analysis.

179
Multi-Selectmedium

Which THREE of the following are valid methods to optimize the performance of a slow-running SQL query in Databricks?

Select 3 answers
A.Using Z-Ordering on high-cardinality columns.
B.Manually rewriting every query to use nested subqueries.
C.Executing ANALYZE TABLE to update statistics.
D.Partitioning tables on low-cardinality columns.
E.Increasing the query timeout setting to 24 hours.
AnswersA, C, D

Z-Ordering co-locates related information in files, allowing the Databricks SQL engine to skip irrelevant data during scans. This is particularly effective for columns used frequently in WHERE clauses, as it narrows the data read operation to only the relevant files, drastically improving query execution speed.

Why this answer

Performance optimization in Databricks involves a combination of data organization, compute resource management, and query plan refinement. By using techniques like Z-Ordering, partitioning, and leveraging the cost-based optimizer, analysts can significantly reduce query latency. These methods are essential for managing large-scale datasets, ensuring that compute resources are utilized effectively, and providing timely insights to business stakeholders who rely on the performance of analytical dashboards and reports.

Exam trap

Candidates often choose high-cardinality columns for partitioning instead of low-cardinality ones, leading to excessive small files and degraded query performance.

180
MCQeasy

A data analyst is creating a Databricks SQL dashboard to display a single key performance indicator: the total number of active subscriptions for the current day. The value must be prominent and readable at a glance, with no axes or categories. Which visualization type should the analyst choose?

A.Counter
B.Bar chart
C.Line chart
D.Pie chart
AnswerA

A counter visualization displays a single aggregated value prominently, which is exactly what is needed for a KPI such as today's active subscription count. It has no axes or category encodings, so it presents the number clearly for at-a-glance reading. This makes it the appropriate choice for a single-value dashboard widget focused on one metric without additional dimensions.

Why this answer

A counter visualization is purpose-built to display one aggregated numeric value without axes, categories, or series. For a dashboard KPI such as the current day's active subscription count, it presents the figure prominently and is immediately readable. Other chart types require dimensions or series that do not exist in this single-value scenario, so they would add unnecessary complexity.

Exam trap

The trap here is assuming any chart type can display a single value, when only a single-value visualization like a counter avoids axes and categories entirely.

181
MCQmedium

A data analyst has a Delta table `events` in Unity Catalog and needs to see the history of operations performed on it, including which user ran each operation and when, in order to audit recent changes. Which command should the analyst run?

A.DESCRIBE DETAIL events;
B.SELECT * FROM events VERSION AS OF 0;
C.SHOW TABLES IN main.default;
D.DESCRIBE HISTORY events;
AnswerD

DESCRIBE HISTORY returns the Delta transaction log entries for the table, including version, timestamp, operation, operation parameters, user identity, and other metadata. This directly satisfies the requirement to audit who changed the table and when, and it is the canonical Delta Lake command for table history inspection in Databricks.

Why this answer

Delta Lake records every transaction in its log, and DESCRIBE HISTORY exposes that log as a table with version, timestamp, operation, and user columns. This is the intended command for auditing who changed a table and when. The other options either list schema objects, describe storage metadata, or read data at a point in time, none of which provide the operation audit trail.

Exam trap

The trap here is confusing table history with table detail or time travel, which look similar but serve very different audit purposes.

182
MCQeasy

A data analyst wants to share a Unity Catalog table with an external partner using Delta Sharing. The partner uses a non-Databricks client that supports the Delta Sharing protocol. Which Unity Catalog object must the analyst create to enable the partner to access the shared data?

A.A share
B.A recipient
C.A provider
D.A storage credential
AnswerA

A share is the Unity Catalog object that packages tables to be shared via Delta Sharing. The analyst creates a share, adds the table to it, and then grants the recipient access to the share. The recipient can then use the Delta Sharing protocol to read the shared data. This is the correct object to create for sharing data externally.

Why this answer

To share a table with an external partner, the analyst must create a share in Unity Catalog, add the table to the share, and then grant the recipient access to the share. The recipient can then use the Delta Sharing protocol to read the data. A share is the container for shared data, while a recipient identifies the external party.

Exam trap

The trap here is confusing the roles of share, recipient, and provider in Delta Sharing, and thinking that a recipient or provider is the object that directly holds the shared data.

183
MCQmedium

An analyst needs to combine data from two tables that share a common key. Which join type returns all rows from both tables, even if there is no match in the other table?

A.INNER JOIN
B.LEFT JOIN
C.FULL OUTER JOIN
D.CROSS JOIN
AnswerC

A FULL OUTER JOIN returns all rows from both tables, combining them where keys match and filling missing values with NULL for rows that have no match. This provides a complete view of all data in both tables, which is the intended behavior for comprehensive reconciliation or merger reports.

Why this answer

A full outer join is the only join type that preserves every record from both source tables, filling in NULL values where matches do not exist. This is essential for comprehensive data auditing and reconciliation tasks where the analyst needs to identify missing records or orphans in either dataset. Understanding the impact of NULLs in the result set is crucial for accurate analysis.

Exam trap

Candidates frequently confuse FULL OUTER JOIN with INNER JOIN or LEFT JOIN, forgetting that only a full outer join preserves all records from both tables regardless of matches.

184
MCQeasy

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

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

A line chart is ideal for displaying trends over time. It plots data points connected by lines, making it easy to see how the number of active users changes from day to day. With dates on the x-axis and user count on the y-axis, the analyst can quickly identify increases, decreases, or patterns over the 30-day period.

Why this answer

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

Exam trap

The trap here is selecting a bar chart because it can show time on the x-axis, but a line chart is superior for trend analysis.

185
MCQmedium

A data analyst needs to share a notebook with a colleague who should be able to run the code but not modify it. Which permission level should the analyst grant to the colleague?

A.Can Manage
B.Can Run
C.Can Edit
D.Can View
AnswerB

The 'Can Run' permission allows a user to attach the notebook to a cluster and execute its cells. It specifically prevents the user from modifying the notebook's code or changing its settings, making it the appropriate choice for analysts who need to run reports without altering the source data processing logic.

Why this answer

The 'Can Run' permission is designed for scenarios where users need to execute code within a notebook without altering the underlying logic. In the Databricks access control model, this permission ensures that the notebook's integrity remains intact while still allowing collaborative execution. It is a critical security practice to follow the principle of least privilege, ensuring users have only the necessary permissions for their specific analytical tasks.

Exam trap

Candidates often select 'Can Edit' or 'Can Manage' out of habit, ignoring the principle of least privilege. They fail to realize 'Can Run' is sufficient for executing code without altering logic.

186
Multi-Selectmedium

An analyst is preparing a Databricks SQL dashboard for an executive review. The dashboard contains several visualizations built from different queries. The analyst wants to ensure that viewers can change a single value, such as a fiscal quarter, and have all relevant visualizations update consistently. Which TWO approaches allow the analyst to achieve this interactive filtering across multiple visualizations? (Choose two.)

Select 2 answers
A.Embed the quarter value directly in each query's WHERE clause and republish the dashboard whenever the quarter changes.
B.Add a dashboard-level filter for the fiscal quarter field, ensuring each visualization's dataset exposes a field with the same name and type so the filter applies to all of them.
C.Create a parameter in each query that references the same parameter name, add a parameter widget to the dashboard, and use it to drive the value across the queries.
D.Use a cross-filter from a table visualization that lists quarters, relying on the dashboard to propagate the selection to all other widgets regardless of their datasets.
E.Duplicate each visualization and create separate copies for each quarter, then instruct viewers to switch between dashboard tabs manually.
AnswersB, C

Dashboard-level filters apply to all visualizations whose datasets contain a matching field. When each dataset exposes a fiscal quarter field with the same name and compatible type, a single filter selection propagates to every relevant widget. This gives viewers one control that updates multiple visualizations consistently, which is exactly the cross-visualization behavior requested for the executive review.

Why this answer

Shared parameters and dashboard-level filters are the two supported mechanisms for driving multiple visualizations from one control. Parameters work when queries reference the same parameter name and a parameter widget is added, while dashboard filters work when datasets expose a matching field. Both give viewers a single selection point that updates all relevant widgets, unlike hardcoding values or duplicating charts.

Exam trap

The trap here is assuming cross-filtering or duplicated charts can uniformly filter every visualization, when shared parameters or dashboard-level filters with matching fields are the mechanisms that actually do so.

187
MCQmedium

A data analyst needs to share a sensitive sales table with the marketing team in Databricks. The marketing team should only see rows where the region column matches 'North America' and should not have access to the credit_card column. Which Unity Catalog feature should the data analyst implement?

A.Create a static clone of the table, drop the restricted column, and grant SELECT permission on the clone to the marketing group.
B.Apply row filters and column masks using SQL functions within Unity Catalog to restrict data visibility dynamically for the marketing group.
C.Configure an access control list on the parent catalog to deny SELECT access on specific columns for the marketing group.
D.Export the filtered subset of data into CSV files and upload them to a secured volume inside the marketing team's schema.
AnswerB

Unity Catalog row filters and column masks dynamically filter rows and redact column values at query time based on user identity or group membership. This approach eliminates data duplication, ensures real-time updates, and provides robust governance security.

Why this answer

Row-level and column-level filtering in Unity Catalog allow administrators and data owners to secure fine-grained access to tables using SQL functions. By applying a dynamic view with conditional logic, users see only permitted rows and columns. This ensures regulatory compliance and data minimization principles are met without duplicating physical storage assets across different business units.

Exam trap

Candidates often incorrectly suggest creating separate physical views or duplicating data for different teams. They overlook that Unity Catalog features allow applying these restrictions directly to the base table object.

188
MCQmedium

Why should an analyst use the 'EXPLAIN' command before running a complex SQL query on a very large dataset?

A.To bypass the authentication requirement for queries.
B.To view the projected cost in US Dollars.
C.To verify the query plan and identify inefficiencies.
D.To automatically apply Z-Ordering to the table.
AnswerC

EXPLAIN outputs the execution plan, showing how the engine intends to scan, join, and aggregate the data. Reviewing this allows analysts to see potential bottlenecks like full table scans or expensive shuffles. By identifying these early, they can adjust the query logic to be more performant before execution.

Why this answer

The EXPLAIN command provides the logical and physical execution plan without actually running the query. For analysts, this is a proactive safety and performance measure. It allows them to spot inefficient patterns, such as unexpected cross joins or massive data shuffles, before consuming expensive compute resources.

This practice is essential for cost control and ensuring that queries are optimized for the scale of the underlying production data.

Exam trap

Candidates assume EXPLAIN is only for debugging errors, ignoring its critical role in performance tuning and cost estimation by revealing the underlying execution plan before runtime.

189
MCQhard

A data analyst at a multinational bank needs to ensure that queries against a Unity Catalog table 'main.risk.transactions' automatically hide the 'customer_ssn' column for users in the 'contractors' group, while all other users see the full data. The analyst wants to implement this without creating separate views for each user group. Which Unity Catalog feature should the analyst use?

A.Row-level security with a filter function
B.Column mask with a masking function
C.Attribute-based access control (ABAC) with tags
D.Dynamic view with a CASE statement
AnswerB

Column masks allow you to apply a function to a column that returns a masked value based on the user's group membership. The analyst can create a mask that returns NULL or a redacted value for the 'contractors' group and the original value for others. This meets the requirement without separate views.

Why this answer

Column masks in Unity Catalog allow you to apply a masking function to a column that evaluates the user's identity and returns a masked value for specific groups. This directly satisfies the requirement to hide 'customer_ssn' for contractors while showing it to others.

Exam trap

The trap here is confusing row-level security with column-level masking; row filters hide rows, not columns.

190
MCQmedium

An organization requires that certain sensitive columns be removed from a table for specific groups of users. Which Databricks feature should be used to enforce this at the data modeling level?

A.Create separate physical tables for each security level.
B.Use Delta Lake dynamic masking.
C.Change the file format to JSON to strip sensitive fields.
D.Hard-code the filtering logic in every user's SQL query.
AnswerB

Dynamic masking allows for the definition of policies that transform or redact column content based on the user's role at query time. This ensures security is enforced consistently across all applications and users, without altering the underlying data on disk, which is vital for maintaining data governance and compliance standards.

Why this answer

Dynamic Data Masking and Row-Level Security are the preferred methods for controlling access to sensitive data within Databricks SQL. By using SQL functions to define masking policies, administrators can ensure that users only see the data they are authorized to view. This approach keeps the underlying data intact while providing secure, role-based access control, ensuring compliance with data privacy regulations without duplicating data for different security profiles.

Exam trap

Candidates often get confused between 'Data Masking' and 'Row Filters'. They might select row-level security when the requirement specifically asks for column-level removal or obscuring of sensitive data attributes.

191
MCQmedium

A data analyst needs to load a daily batch of 500 CSV files from an S3 bucket into a Delta table. The files are consistently formatted, and the analyst wants to ensure that files already processed are not re-imported in subsequent runs. Which approach is most efficient for this idempotency requirement?

A.Using an INSERT INTO statement to append data from a temporary view.
B.Using the MERGE INTO command to check for existing records based on a key.
C.Using the COPY INTO command to load the files from the S3 bucket.
D.Creating a standard SELECT query on the cloud location and appending results.
AnswerC

COPY INTO provides a declarative way to load data while maintaining an internal record of processed files to ensure idempotency. This command simplifies the ingestion process by handling schema mapping and file discovery automatically, allowing analysts to run the same script repeatedly without risking data duplication in the target Delta table.

Why this answer

COPY INTO is the ideal SQL command for idempotent batch loading from cloud object storage. It automatically tracks which files have been processed, preventing duplicate ingestion without requiring complex state management by the user. While Auto Loader is also idempotent, COPY INTO is often preferred for simpler batch SQL workflows where low-latency streaming is not a primary requirement for the analyst.

Exam trap

Candidates often suggest manual filtering or 'spark.read' with path lists. They ignore the built-in idempotency of 'COPY INTO', which natively tracks file state to prevent duplicate data ingestion.

192
MCQmedium

A data analyst is working with a Delta table that contains a column 'sensitive_info' which should be redacted for users in the 'marketing' group. The analyst wants to ensure that users in that group see a masked value while other users see the actual data. Which Databricks feature should the analyst use?

A.Row filters
B.Column masks
C.Dynamic views
D.Table ACLs
AnswerB

Column masks in Unity Catalog allow dynamic masking of column values based on the user's group membership. The analyst can define a mask function that returns a redacted value for the 'marketing' group and the original value for others. This feature is designed exactly for this scenario, providing row-level and column-level security without altering the underlying data.

Why this answer

Column masks in Unity Catalog are designed to dynamically redact column values based on the user's identity or group membership. They allow the analyst to define a masking expression that applies only to specified users or groups, leaving the original data intact for others. This provides fine-grained security without duplicating data or creating multiple views.

Exam trap

The trap here is confusing row-level security with column-level masking, or assuming that table ACLs can provide granular column masking.

193
MCQeasy

A data analyst runs the following SQL in a Databricks SQL warehouse: CREATE OR REPLACE TEMPORARY VIEW monthly_sales AS SELECT month, SUM(revenue) AS total_revenue FROM sales.orders GROUP BY month; Immediately afterward, the analyst opens a new query tab in the same SQL warehouse session and runs SELECT * FROM monthly_sales; The query fails with a TABLE_OR_VIEW_NOT_FOUND error. What is the most likely reason for the failure?

A.The view definition was persisted in Unity Catalog, but the second tab lacks USE CATALOG privileges on the default catalog.
B.The view was created as a temporary view, which is scoped to the notebook or session that created it, so a new query tab cannot resolve it.
C.The aggregated alias total_revenue conflicts with a reserved keyword, so the view was dropped automatically after creation.
D.The CREATE OR REPLACE TEMPORARY VIEW statement silently failed because the underlying sales.orders table lacks a primary key.
AnswerB

Temporary views in Databricks SQL are session-scoped objects. They exist only for the duration of the session or notebook that created them and are not registered in Unity Catalog. A different query tab or SQL editor session cannot see the view, which explains the resolution failure when the second tab attempts to query monthly_sales.

Why this answer

Temporary views are bound to the session or notebook that creates them and are not stored in Unity Catalog. When the analyst opens a separate query tab, that new session has its own namespace and cannot resolve the temporary view, producing TABLE_OR_VIEW_NOT_FOUND. Persisting the view as a regular view or a managed table would make it accessible across sessions.

Exam trap

The trap here is assuming that any object created with CREATE VIEW, even a TEMPORARY one, becomes a persistent, catalog-registered object visible to all users and sessions.

194
MCQeasy

When importing data into Databricks using the 'Add Data' UI, what is the default file format for the created table if the source is a CSV file?

A.CSV
B.Parquet
C.Delta
D.JSON
AnswerC

Delta Lake is the default table format in Databricks. When you use the UI to import a CSV file, Databricks performs an internal transformation to load that data into a Delta table, enabling all the benefits of the Lakehouse architecture, including ACID guarantees and optimized performance for SQL queries.

Why this answer

Databricks defaults to Delta Lake for all managed table creation because it provides ACID transactions, scalability, and time travel. Even when importing simple CSV files, Databricks automatically converts them into the Delta format to ensure that the data is queryable with high performance and reliability. Recognizing this default behavior is essential for understanding how Databricks standardizes data storage behind the scenes for all analytical workloads.

Exam trap

Candidates often assume the uploaded file retains its original CSV format, failing to realize that Databricks automatically converts all ingested data into Delta format to ensure ACID compliance and performance.

195
MCQhard

Refer to the exhibit. An analyst attempts to query table changes using the table changes function (table_changes()) to track incremental updates for a reporting pipeline, but encounters the error shown in the exhibit. How should the analyst resolve this issue?

A.Run an ALTER TABLE command to set delta.enableChangeDataFeed = true, then re-run the query.
B.Restart the SQL warehouse to clear internal metadata caches preventing change data feed access.
C.Convert the table format from Delta Lake to standard Apache Parquet using a CTAS statement.
D.Execute a VACUUM command with a retention period of zero hours to force immediate log compaction.
AnswerA

The table_changes() function requires Change Data Feed enabled on the source table. Running ALTER TABLE to set delta.enableChangeDataFeed = true activates change tracking, after which the incremental query returns the row-level changes the reporting pipeline needs.

Why this answer

The Change Data Feed (CDF) must be explicitly enabled on a Delta table via table properties either at creation time or through an ALTER TABLE command. Without this configuration, Delta Lake does not record the low-level row insertions, updates, and deletions required for changestream queries.

Exam trap

Candidates often assume Change Data Feed is enabled by default on all Delta tables or attempt to use it without altering table properties first, resulting in query failures.

196
MCQmedium

What is the primary implication of granting a user 'Can Use' permission on a Genie space?

A.The user can modify the Genie space instructions
B.The user gains direct access to the SQL Warehouse
C.The user can ask natural language questions
D.The user becomes the owner of the space
AnswerC

The 'Can Use' permission is designed specifically for end-users who need to perform analysis. It enables them to send prompts to the model and see the generated results, providing a safe, read-only experience that maintains the security boundaries set by the space owner.

Why this answer

The 'Can Use' permission allows a user to interact with the Genie space via natural language. This provides them the ability to ask questions and view results without giving them the rights to modify the space's instructions or configuration. This permission model follows the principle of least privilege, ensuring that users can perform their analysis without the risk of accidentally altering the underlying configuration.

Exam trap

Candidates often assume that a user with 'Can Use' permission also has the ability to edit instructions or modify the underlying configuration of the Genie space, confusing it with administrative rights.

197
MCQmedium

An analyst is creating a visualization of daily sales trends over the past year. They notice that the chart is cluttered and difficult to interpret due to the high volume of individual data points. Which action should the analyst take to improve readability?

A.Change the chart type to a scatter plot.
B.Filter the dataset to only include the last seven days.
C.Change the X-axis grouping from 'Day' to 'Month'.
D.Increase the chart size to fill the entire screen width.
AnswerC

Grouping by month aggregates the daily values into manageable segments, effectively smoothing out the line chart. This reduction in granularity helps the viewer focus on monthly performance patterns and seasonal changes, which provides clearer insights than attempting to interpret 365 individual data points on a single chart view.

Why this answer

Aggregating data by a larger time grain, such as weeks or months, is a best practice when visualizing high-frequency time-series data. This reduces noise and highlights long-term trends rather than daily fluctuations. By adjusting the visualization settings to group data, the analyst makes the insights more accessible to stakeholders, facilitating faster decision-making and preventing cognitive overload caused by displaying hundreds of individual data points simultaneously.

Exam trap

Candidates often try to solve visualization clutter by deleting data points or filtering out rows, which results in inaccurate reporting, rather than simply changing the aggregation interval for better clarity.

198
Multi-Selecthard

Which THREE of the following are benefits of using Delta Lake over standard Parquet files in Databricks?

Select 3 answers
A.ACID transaction support
B.Automatic data compression to non-standard formats
C.Time travel capabilities
D.Schema enforcement and evolution
E.The ability to run queries without a compute engine
AnswersA, C, D

Delta Lake provides Atomicity, Consistency, Isolation, and Durability (ACID) guarantees. This ensures that concurrent reads and writes are handled safely, preventing data corruption and partial writes. This is a fundamental requirement for reliable data warehousing on top of cloud object storage, ensuring users always see consistent data states.

Why this answer

Delta Lake introduces ACID transactions, time travel, and schema enforcement to the data lake, transforming it into a lakehouse. These features are critical for maintaining data integrity in complex production environments. Understanding these benefits allows analysts to make informed decisions about storage formats, ensuring that the data platform remains reliable, scalable, and capable of handling complex analytical requirements without risking data corruption or inconsistency.

Exam trap

Candidates often include features like 'data compression' or 'partitioning' as benefits of Delta Lake, failing to select the core architectural advantages that differentiate it from standard Parquet files.

199
MCQeasy

A data analyst runs `DROP TABLE IF EXISTS main.default.customer_orders;` in a Databricks SQL warehouse. The table is a managed Delta table in Unity Catalog and the analyst's identity has no applicable owner or admin privileges. What happens?

A.The statement succeeds but the table is moved to a recycle bin schema where it can be restored for 30 days by any workspace user.
B.The statement succeeds and only the table's metadata is removed while the underlying Parquet data files remain in the managed storage location.
C.The statement fails with an insufficient privileges error because DROP TABLE requires ownership of the table or MANAGE on the parent schema.
D.The statement succeeds because IF EXISTS implicitly converts the operation into a metadata-only soft delete that any schema user can perform.
AnswerC

Dropping a managed Unity Catalog table requires ownership of the table or the MANAGE privilege on its parent schema, plus USE CATALOG and USE SCHEMA. Because the identity has neither, the statement fails with a permissions error. The IF EXISTS clause only suppresses the not-found error; it does not grant or waive privilege checks.

Why this answer

Removing a managed Unity Catalog table is a privileged DDL operation. The executing identity needs to be the table owner or hold MANAGE on the parent schema, along with USE CATALOG and USE SCHEMA on the enclosing objects. The IF EXISTS qualifier only prevents a not-found error; it does not relax authorization, so the drop is rejected before any data or metadata is touched.

Exam trap

The trap here is assuming IF EXISTS weakens the privilege requirement or that dropping a managed table leaves the data files intact.

200
MCQmedium

You are visualizing server error logs over time. You want to highlight days where the error count exceeds a specific threshold (e.g., 50 errors). Which visualization feature is best for this?

A.Add a trend line to the chart.
B.Use conditional formatting on the data series.
C.Change the chart type to a pie chart.
D.Filter out all days with fewer than 50 errors.
AnswerB

Conditional formatting allows you to define rules, such as changing the color of a bar or line segment when the count exceeds 50. This provides immediate, intuitive visual feedback to the user about which days are problematic, satisfying the requirement to highlight specific data points effectively.

Why this answer

Conditional formatting is the standard tool for data highlighting. By applying rules to the visualization, the analyst can automatically change colors or icons based on the data values. This 'at-a-glance' identification of anomalies is crucial for operational monitoring, as it allows users to pinpoint problematic timeframes immediately, reducing the mean time to detect issues without requiring the user to inspect every individual data point manually.

Exam trap

Candidates often wrongly select manual filters or separate dashboard creation instead of utilizing conditional formatting rules to automatically highlight data anomalies.

201
MCQhard

A data analyst is troubleshooting a slow Databricks SQL query that joins a large fact table with a small dimension table. The query plan shows a broadcast hash join, but the analyst notices that the small table is not being broadcast as expected. Which configuration should the analyst check to ensure the small table is broadcast?

A.spark.sql.adaptive.enabled
B.spark.sql.autoBroadcastJoinThreshold
C.spark.databricks.delta.optimizeWrite.enabled
D.spark.sql.shuffle.partitions
AnswerB

This Spark configuration sets the maximum size in bytes for a table to be considered for broadcasting in a join. If the small table's size exceeds this threshold, it will not be broadcast. The analyst should verify this value and adjust it if necessary to allow the small table to be broadcast, which can improve join performance by avoiding a shuffle.

Why this answer

The broadcast hash join is governed by the autoBroadcastJoinThreshold configuration, which defines the maximum size for a table to be broadcast. If the small table exceeds this threshold, it will not be broadcast. Checking and potentially increasing this threshold can enable the broadcast, improving performance.

Other settings affect different aspects of query execution and do not directly control broadcast eligibility.

Exam trap

The trap here is assuming that enabling adaptive query execution alone guarantees a broadcast join, when the auto-broadcast threshold must also be satisfied.

202
Multi-Selectmedium

Which TWO of the following statements accurately describe the relationship between Databricks SQL Warehouses and Delta Lake?

Select 2 answers
A.SQL Warehouses must always be co-located in the same cloud region as the Delta Lake storage.
B.SQL Warehouses utilize Delta Lake's transaction log to ensure consistent query results.
C.Delta Lake is a compute engine that replaces the need for SQL Warehouses.
D.SQL Warehouses can query Delta tables using optimized Parquet files.
E.SQL Warehouses require all data to be imported into a proprietary internal database format.
AnswersB, D

SQL Warehouses leverage Delta Lake's transaction log to provide snapshot isolation. This ensures that when an analyst queries a table, they see a consistent version of the data, even if other processes are concurrently writing to the same table, thereby maintaining data integrity during complex ad-hoc analytical operations.

Why this answer

Databricks SQL Warehouses provide the compute layer to execute ANSI-compliant SQL against data stored in Delta Lake format. Delta Lake serves as the underlying storage layer, providing ACID transactions and schema enforcement. Analysts must understand this separation, as it allows compute resources to scale independently from storage.

This architecture ensures that SQL Warehouses can efficiently query massive volumes of data while leveraging the reliability and performance optimizations inherent in the Delta Lake storage structure.

Exam trap

Candidates often assume SQL Warehouses store their own data, failing to realize they are strictly compute engines that query data residing in external Delta Lake storage via the transaction log.

203
MCQeasy

What is the primary purpose of adding 'Instructions' to a Genie space in Databricks?

A.To automate the deployment of SQL Warehouses
B.To enable automatic updates for underlying tables
C.To provide context and guidance for query generation
D.To store user credentials for data access
AnswerC

Instructions act as the 'brain' of the Genie space by providing the AI with domain-specific knowledge. This context helps the LLM interpret ambiguous questions correctly, apply standard business metrics, and choose the right columns, which leads to more accurate and relevant responses for end-users.

Why this answer

Instructions serve as a system prompt that guides the AI's behavior, defining business logic, specific terminology, and preferred analytical approaches. By setting these instructions, developers improve the accuracy of query generation, ensuring the AI understands company-specific jargon and data relationships that aren't inherently obvious from table schemas alone. This significantly enhances the user experience and reliability of the natural language interface.

Exam trap

Candidates frequently mistake instructions for data governance or access control settings, rather than recognizing them as the primary mechanism for guiding LLM reasoning and domain-specific terminology mapping.

204
MCQmedium

A data analyst is creating a new AI/BI Genie Space for the sales team. They want to add a trusted table that contains curated sales data. Which of the following is the correct way to add a table to the Genie Space?

A.Use the Databricks SQL query editor to run a CREATE TABLE statement.
B.Use the Databricks CLI to run 'databricks genie add-table' with the table name.
C.In the Genie Space configuration, navigate to the Data tab and add the table from the Unity Catalog.
D.Create a Databricks notebook that reads the table and register it as a temporary view.
AnswerC

The Data tab in the Genie Space configuration is where you add tables and views that the Space can query. Selecting tables from Unity Catalog ensures the Genie model has the necessary metadata and permissions. This is the standard method to provide curated data sources for natural language queries, enabling accurate and secure responses.

Why this answer

To add a table to an AI/BI Genie Space, you must use the Genie Space configuration interface. The Data tab allows you to select tables and views from Unity Catalog, ensuring the Space has access to the correct data sources. This is the only supported method that integrates the table with the natural language query engine, providing accurate and secure responses.

Exam trap

The trap here is assuming that any method of creating or referencing a table, such as SQL DDL or temporary views, will automatically make it available to the Genie Space.

205
MCQmedium

An organization stores sensitive financial records in a Unity Catalog managed Delta table stored in cloud object storage. To comply with corporate security mandates, all data at rest must be encrypted using a customer-managed encryption key rather than the default cloud provider-managed keys. Where must the administrator configure this encryption setting?

A.Inside the Databricks notebook before executing any SQL queries against the sensitive financial Delta table.
B.Within the Unity Catalog metastore root storage configuration using a cloud-provider storage credential linked to a customer-managed key.
C.By modifying the Spark session configuration spark.databricks.io.encryption.enabled to true in the cluster policy.
D.Through the Apache Spark dataframe write option by setting the encryption parameter to customer_managed prior to saving.
AnswerB

Unity Catalog managed tables inherit encryption from the metastore root storage location, so the customer-managed key must be attached there via a storage credential. Table-level or workspace settings cannot override the root storage encryption for managed data at rest.

Why this answer

Customer-managed keys (CMK) for managed storage in Unity Catalog are configured at the storage credential or metastore level when establishing the root storage location. This ensures that all managed tables and volumes created within that storage root automatically leverage the specified encryption key, enforcing enterprise compliance policies transparently across all downstream data assets and analytics workloads.

Exam trap

Candidates often mistakenly believe encryption keys are configured at the individual table or schema level, failing to realize that Unity Catalog root storage encryption must be set at the metastore or storage credential level.

206
Multi-Selectmedium

Which THREE of the following are benefits of using Liquid Clustering instead of traditional partitioning in Databricks SQL?

Select 3 answers
A.It simplifies data layout management by removing the need for manual partitioning.
B.It prevents the creation of small files caused by high-cardinality partitions.
C.It provides faster write throughput by disabling transaction logs during updates.
D.It allows for easier clustering key updates without needing to rewrite the entire table.
E.It forces data to be sorted by every column in the table automatically.
AnswersA, B, D

Liquid Clustering eliminates the rigid structure of traditional partitioning. It allows users to define clustering keys, and the system automatically reorganizes data to optimize for query performance over time. This removes the administrative overhead of re-partitioning tables when business requirements or filter patterns shift significantly over the project lifecycle.

Why this answer

Liquid Clustering provides a flexible alternative to manual partitioning by automatically managing data layout based on query patterns. It eliminates the need for manual 'partition evolution' and solves issues related to skewed data distribution or small file problems. By offloading cluster management to the Databricks engine, analysts can spend more time on business logic and less on the intricacies of physical data file management for large-scale datasets.

Exam trap

Candidates frequently assume Liquid Clustering is a complete replacement for all partitioning. They often fail to recognize that Liquid Clustering is specifically designed to replace manual partitioning, not all storage optimization techniques.

207
Multi-Selecthard

An analyst is preparing a Databricks SQL dashboard for an executive review and needs to ensure the numbers shown are trustworthy and reproducible. The analyst wants to document the data lineage and make the dashboard resilient to schema changes in the source tables. Which TWO actions should the analyst take? (Choose two.)

Select 2 answers
A.Convert every numeric column to a string in the query so formatting is consistent.
B.Use fully qualified three-level namespace names such as catalog.schema.table in the dataset query.
C.Grant the dashboard viewers CAN MANAGE permission on the underlying tables.
D.Create the visualizations from a saved Databricks SQL query that is stored in a Git-backed workspace folder.
E.Enable the dashboard's scheduled refresh to run every minute.
AnswersB, D

Referencing tables with their full catalog.schema.table names removes ambiguity about which object is being read, especially in a Unity Catalog environment with many similarly named tables. It makes the dependency graph explicit for lineage tools and reduces the chance that a session default schema change silently points the query at a different table. This improves resilience against schema and workspace reorganizations.

Why this answer

Trustworthy, reproducible dashboards depend on a versioned, reviewable source of SQL and on unambiguous object references. Storing the query in a Git-backed workspace folder provides history and peer review, while using fully qualified catalog.schema.table names makes dependencies explicit and guards against silent redirection when defaults change. Together these practices support lineage documentation and resilience, unlike refresh tuning, type coercion, or over-permissive grants.

Exam trap

The trap here is assuming that more frequent refreshes or broader permissions improve trustworthiness, when the real drivers are version-controlled SQL and explicit, fully qualified table references.

208
Multi-Selectmedium

An analyst is preparing a report and needs to ensure that sensitive PII columns are not exposed. Which TWO techniques can be used to achieve this in Databricks SQL?

Select 2 answers
A.Apply Dynamic Data Masking to the sensitive columns.
B.Use the GRANT ALL PRIVILEGES command on all tables.
C.Implement Row-Level Security using a filter clause in the view definition.
D.Use the GRANT SELECT command only on non-sensitive columns.
E.Run the query as a superuser to bypass masks.
AnswersA, D

Dynamic data masking allows administrators to redact or hash sensitive data based on the user's role. For example, a partial mask can be applied to email addresses or SSNs, ensuring the raw data is hidden while still allowing analytical processing on the masked values by unauthorized users.

Why this answer

Data governance and security are paramount in Databricks SQL. Using column-level filtering and dynamic data masking ensures that analysts only see the data they are authorized to access. These security controls are enforced at the query plan level by Unity Catalog, ensuring that security policies are consistent regardless of the tool or interface the user is utilizing to connect to the warehouse.

Exam trap

Candidates often select table-level access or row filters instead of column-specific techniques, forgetting that dynamic data masking and selective column GRANT statements specifically protect sensitive PII columns without blocking overall table access.

209
MCQhard

Which of the following is the best strategy for refining Genie space performance when the model consistently struggles with complex joins?

A.Flatten all tables into a single wide table
B.Increase the number of allowed tables
C.Add primary/foreign key definitions and clarify join logic in instructions
D.Switch to a larger, more expensive SQL Warehouse
AnswerC

Defining relationships in Unity Catalog and providing explicit join guidance in the instructions is the industry-standard way to assist LLMs. This provides the 'source of truth' that the AI needs to navigate the relationships between tables, ensuring it can perform complex joins reliably and consistently.

Why this answer

When a Genie space fails on multi-table joins, the underlying issue is often a lack of clear relationship definitions. By updating the table metadata in Unity Catalog (such as primary and foreign key constraints) and using the Genie instructions to explicitly define how tables should be joined, the developer provides the model with the necessary 'map' to build complex queries accurately.

Exam trap

Candidates often try to solve join issues by rewriting the user's natural language question rather than addressing the root cause: the lack of explicit metadata regarding table relationships.

210
MCQmedium

A data analyst is building a Databricks SQL dashboard that includes a parameter for the region. The dashboard should allow viewers to select a region from a dropdown list that is populated dynamically from the distinct values in the sales table's region column. Which feature should the analyst use to implement this?

A.Add a filter widget on the dashboard that uses a SELECT DISTINCT query, and link it to the visualizations.
B.Implement a text input parameter and instruct users to type the region name.
C.Use a static dropdown parameter and manually enter all possible region values.
D.Create a query-based dropdown parameter that runs a SELECT DISTINCT region FROM sales query to populate the list.
AnswerD

Databricks SQL dashboards support query-based parameters, where the list of values is generated by a query. This allows dynamic population from the distinct values in a column. The analyst can configure the parameter to use a query, ensuring the dropdown reflects current data without manual updates.

Why this answer

Databricks SQL dashboards support query-based parameters, which dynamically populate a dropdown from a SQL query. This ensures the list of regions is always up to date. Static lists or text inputs do not provide dynamic population from the data, and filter widgets are not parameters.

Exam trap

The trap here is confusing a filter widget with a parameter; filter widgets are for filtering visualizations, while parameters are reusable variables that can be used across queries and visualizations.

211
MCQhard

A data analyst queries a partitioned Delta table `logs.events` that has a `event_date` column. The query filters `WHERE event_date = '2024-06-01'`. Performance is poor even though the table is partitioned on `event_date`. Which factor most likely explains the poor performance?

A.The table has thousands of small files within the matching partition, so pruning selects the right partition but still reads excessive files.
B.The `event_date` column is stored as a string while the predicate compares to a string literal, forcing a full scan due to implicit casting.
C.The table was partitioned by a high-cardinality column other than `event_date`, such as a unique event identifier.
D.The predicate is wrapped in a non-deterministic function such as `WHERE CAST(event_date AS DATE) = current_date()`, preventing partition pruning.
AnswerA

Partition pruning narrows the scan to the correct partition, but if that partition contains thousands of tiny files from frequent appends, the engine still pays heavy per-file overhead. Pruning and compaction solve different problems. This explanation is consistent with the facts: the partition column matches the predicate, yet the layout inside the partition remains fragmented and slow.

Why this answer

Partition pruning only eliminates partitions that cannot match; it does not fix fragmentation inside a matching partition. If the correct partition contains many small files, the query reads all of them, incurring high overhead. The likely remedy is compaction with OPTIMIZE on that partition, possibly combined with ZORDER, rather than changing the partition column or the predicate form.

Exam trap

The trap here is assuming that correct partition pruning guarantees fast reads, when heavy small-file fragmentation within the selected partition can still dominate query time.

212
MCQmedium

An analyst is optimizing a dashboard that queries a Delta table partitioned by 'event_date'. The query filters on 'event_date' and 'user_id', but is still slow. What is the most effective next step to improve query speed?

A.Enable Liquid Clustering on the 'user_id' column.
B.Change the table format to Parquet to improve scan performance.
C.Increase the cluster's driver node memory.
D.Rewrite the query to use a subquery instead of a JOIN.
AnswerA

Liquid Clustering allows the table to automatically organize data based on query patterns, including 'user_id'. It replaces traditional partitioning and Z-Ordering with a more flexible, adaptive approach that improves performance for high-cardinality filters by clustering relevant data together without requiring manual management of partition columns or Z-Order schemes.

Why this answer

Partitioning by date is good for coarse pruning, but if 'user_id' is the primary filter, the engine still scans all files within the selected dates. Z-Ordering by 'user_id' creates data skipping metadata that allows the engine to skip files not containing the specific user, drastically reducing data read volume. This is a critical optimization for high-cardinality columns used in frequent point-lookups.

Exam trap

Candidates often suggest adding more compute or changing partition columns. They fail to realize that Z-Ordering or Liquid Clustering is required to optimize high-cardinality columns not used in partitioning.

213
MCQmedium

A data analyst needs to inspect the logical and physical execution plans of a slow Spark SQL query to understand how filters and joins are being evaluated. Which command should the analyst execute in a Databricks notebook cell?

A.DESCRIBE EXTENDED table_name;
B.EXPLAIN EXTENDED SELECT * FROM table_name WHERE condition;
C.SHOW QUERY PLAN FOR SELECT * FROM table_name WHERE condition;
D.ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS;
AnswerB

EXPLAIN EXTENDED generates a comprehensive breakdown of the query lifecycle, showing the logical optimizations and physical operators chosen by the Catalyst optimizer. This insight is vital for diagnosing performance bottlenecks and verifying predicate pushdown.

Why this answer

The EXPLAIN command in Spark SQL outputs the execution plans, including parsed logical plan, analyzed logical plan, optimized logical plan, and physical plan. Analyzing these plans helps data analysts identify inefficient join strategies, missing predicate pushdown, and unnecessary full table scans.

Exam trap

Candidates confuse the simple EXPLAIN command with EXPLAIN EXTENDED, failing to realize that the latter is required to see the full logical and physical plan details needed for deep analysis.

214
MCQmedium

An analyst wants to ensure that a Genie space only uses the most recent data for all queries. How can this be achieved?

A.Set the SQL Warehouse to auto-terminate every hour
B.Use instructions to mandate filters on date columns
C.Delete old data from the Unity Catalog tables
D.Rename tables whenever new data arrives
AnswerB

Instructions are the correct place to enforce business rules, including data freshness. By explicitly telling the model to always filter by the most recent partition or date, you ensure that all results provided to users are current, preventing stale data from being reported in the Genie space.

Why this answer

To enforce data freshness, the analyst should instruct the Genie space to use views or filter conditions that point to the latest partitions or versioned tables. By incorporating this logic into the instructions, the developer ensures that the model always applies the necessary filter clauses (like 'WHERE date = CURRENT_DATE') to every generated query, maintaining data integrity without requiring manual input from the users.

Exam trap

Candidates often believe that creating manual data refresh schedules or modifying user query inputs is necessary to enforce data freshness across the board.

215
MCQeasy

Which visualization type is most appropriate for showing the distribution of a single continuous variable, such as the spread of customer ages?

A.Pie Chart
B.Line Chart
C.Histogram
D.Scatter Plot
AnswerC

Histograms are specifically designed to visualize the distribution of continuous numerical data. By binning values and showing counts per bin, they provide a clear view of where data points are concentrated, which is exactly what is needed to understand the spread of variables like customer age.

Why this answer

A histogram is the standard tool for visualizing the distribution of numerical data. It groups continuous values into ranges, or 'bins', and displays the frequency of observations in each bin. This allows analysts to quickly identify the shape of the data, such as whether it is normally distributed, skewed, or contains outliers, which is foundational for exploratory data analysis and effective business reporting.

Exam trap

Test-takers frequently confuse histograms with bar charts, failing to realize that histograms specifically plot continuous numerical data grouped into contiguous bins.

216
Multi-Selecthard

A data analyst is reviewing the Query Profile in Databricks SQL for a query that spills data to disk during a sort operation. Which two metrics should the analyst examine to confirm and diagnose the spill? (Choose two.)

Select 2 answers
A.Spill (Disk) Size
B.Number of Output Rows
C.Total Time Spent in Shuffle
D.Shuffle Read Size
E.Peak Execution Memory
AnswersA, E

Spill (Disk) Size is a metric in the Query Profile that specifically reports the amount of data written to disk due to memory overflow during operations like sort or aggregation. A non-zero value confirms that spilling occurred. The analyst should examine this metric to verify the spill and its magnitude, which helps in tuning memory configurations or query logic.

Why this answer

Spill (Disk) Size directly quantifies data written to disk due to memory overflow, confirming spill. Peak Execution Memory indicates memory pressure that causes spill. Together, they diagnose spill during sort.

Other metrics like shuffle read size or output rows do not directly measure spill and are less relevant for this specific issue.

Exam trap

The trap here is confusing shuffle-related metrics with spill metrics; spill is a distinct phenomenon that can occur without shuffles and is measured by specific spill counters.

217
MCQmedium

A data analyst runs an Apache Spark SQL query against a Delta Lake table and notices that partition pruning is not occurring despite filtering on the partitioned column 'region'. The table definition shows 'region' is stored as a string, but the query passes an integer value. How does this type mismatch impact query execution?

A.Spark automatically casts the column to match the filter type without impacting performance.
B.The query fails immediately with a TypeMismatch analysis exception before any jobs are launched.
C.Catalyst fails to match directory paths to filter values, forcing a full table scan across all partitions.
D.Delta Lake automatically rewrites the underlying table schema to match the incoming query filter type.
AnswerC

When data types do not align precisely between the filter predicate and the partition schema definition, the Catalyst optimizer cannot safely evaluate directory paths against static partition filters. Consequently, the query engine bypasses partition pruning and reads all files, leading to significantly degraded performance.

Why this answer

Type mismatches between predicate filters and partition columns prevent catalyst optimization from pushing down partition filters. Spark attempts implicit casting, which often invalidates static partition pruning mechanisms, forcing the engine to scan every single data file across all partitions, drastically increasing input data volume and slowing down query execution performance.

Exam trap

Candidates overlook data types in filter predicates, assuming implicit casting always preserves partition pruning and optimization efficiencies.

218
MCQhard

A data analyst is using a Databricks SQL warehouse to query a table that is protected by row-level security using dynamic views in Unity Catalog. The analyst has SELECT on the view but not on the underlying table. When querying the view, the analyst receives an error about insufficient privileges on the base table. What is the most likely cause?

A.The analyst needs to be granted SELECT on the base table directly to query the view.
B.The view owner does not have SELECT privilege on the underlying table, so the view cannot access it.
C.The view is defined with SQL SECURITY INVOKER, which requires the analyst to have base table access.
D.The view was created without the SECURITY DEFINER clause, so the analyst's permissions are used to access the base table.
AnswerB

In Unity Catalog, views run with the view owner's privileges. If the owner lacks SELECT on the base table, the view cannot retrieve data, causing an error for any user querying the view. The analyst's own privileges are irrelevant because the view executes as the owner. Thus, the owner must have the necessary privileges on the base table.

Why this answer

In Unity Catalog, views execute with the permissions of the view owner, not the querying user. If the view owner lacks SELECT on the underlying table, the view cannot access the data, resulting in a permission error for any user. The analyst's privileges on the view are sufficient, but the owner must have the necessary privileges on the base table.

Exam trap

The trap here is assuming that the querying user needs direct privileges on the base table, when in fact the view owner's privileges are what matter.

219
MCQhard

You want to perform a case-insensitive search for a string in a column. Which function is most efficient to use for this purpose?

A.WHERE col LIKE '%value%'
B.WHERE LOWER(col) = 'value'
C.WHERE col REGEX '(?i)value'
D.WHERE col = CASE_INSENSITIVE('value')
AnswerA

Standard LIKE operator is case-sensitive in most configurations. If the data contains mixed-case entries, this search will fail to return all matches. It does not solve the requirement for case-insensitive searching and will lead to incomplete result sets if the source data is not normalized beforehand.

Why this answer

The question asks for the most *efficient* way to perform a case-insensitive search. Using `LOWER()` forces a full table scan and prevents index utilization (though Spark doesn't have traditional indexes, it still prevents certain optimizations like partition pruning or file skipping). In Spark SQL / Databricks, case-insensitive string matching is often naturally handled by default collations or specific functions, but `LIKE` or `ILIKE` (or `lower()`) are compared differently.

However, looking at standard Databricks Data Analyst objectives, `ILIKE` or `LIKE` with proper collation are preferred for readability, while `LIKE` is often standard. More importantly, option A (`LIKE`) is case-insensitive by default in many SQL dialects, but in Spark SQL, `LIKE` is case-sensitive, whereas `ILIKE` is case-insensitive. Wait, Spark SQL `LIKE` is case-sensitive.

Let's check `rlike`. Actually, the standard built-in operator for case-insensitive matching in Spark SQL / Databricks without transforming the column is `ILIKE`. If `ILIKE` is not listed, let's correct the question options or the correct answer.

Alternatively, `LIKE` is not case-insensitive. Let's fix the correct option to `ILIKE` if present, but since it's not, let's provide a valid Databricks SQL approach or fix option A/B.

Exam trap

Candidates often suggest creating a functional index or using more complex regex functions, forgetting that simple string normalization via LOWER() is the standard, though scan-heavy, approach.

220
MCQeasy

A data analyst needs to grant a team member the ability to view the metadata of a table `main.finance.transactions` in Unity Catalog, but not query the data. Which privilege should the analyst grant?

A.`BROWSE`
B.`MODIFY`
C.`SELECT`
D.`USAGE`
AnswerA

`BROWSE` allows a user to view an object's metadata, such as its schema and properties, without granting access to the underlying data. This matches the requirement to view metadata only. It is a relatively new privilege in Unity Catalog designed for discovery without data access. Granting `BROWSE` on the table or its parent catalog/schema enables the team member to see the table in the UI and describe it.

Why this answer

The `BROWSE` privilege in Unity Catalog is specifically designed to allow users to discover and view metadata of objects without accessing the data. Granting `BROWSE` on the table or its parent objects satisfies the requirement. Other privileges like `SELECT`, `USAGE`, or `MODIFY` either grant data access or are insufficient for metadata visibility.

Exam trap

The trap here is assuming that `USAGE` on a schema grants metadata visibility on tables, when in fact `BROWSE` is the privilege that allows viewing metadata without data access.

221
MCQhard

An analyst is building an AI/BI Genie space over a Unity Catalog schema containing both `orders` and `customers` tables. Business users often ask questions that require joining the two tables on `customer_id`. During testing, Genie frequently generates queries that join on the wrong column or omit the join entirely, producing inflated row counts. The analyst has already verified that both tables are selected and that column names are clear. Which action is most likely to correct Genie's join behavior?

A.Rename `customer_id` in both tables to `cust_key` so the join column is unique across the schema.
B.Add example SQL queries to the Genie space that explicitly join `orders` and `customers` on `customer_id`.
C.Add a general instruction stating that the `orders` and `customers` tables should always be joined when both are referenced.
D.Create a foreign key constraint between `orders.customer_id` and `customers.customer_id` in Unity Catalog.
AnswerB

Example SQL queries are the most direct way to teach Genie a specific join pattern. By showing the exact join condition and table aliases, the model can reproduce that pattern when similar questions arise. Since the analyst already confirmed tables and column names are clear, the missing element is a demonstration of the correct join, making this the targeted fix for inflated row counts.

Why this answer

When Genie produces incorrect joins despite clear table and column names, the most reliable correction is to supply example SQL that demonstrates the intended join condition. Examples give the model a concrete pattern to imitate, including the join key and aliases. General instructions help but lack the precision of a worked query, and metadata constraints are not automatically leveraged during generation.

Exam trap

The trap here is assuming that declarative metadata such as foreign keys or a prose instruction will override Genie's join choices, when explicit example SQL is what reliably anchors the pattern.

222
MCQeasy

A data analyst at a retail company has been asked to build interactive sales reports that refresh automatically each morning and can be shared with regional managers. The analyst wants to use Databricks SQL to create queries, schedule them, and publish visualizations without writing notebook code. Which Databricks component should the analyst use to accomplish this?

A.Databricks Jobs
B.Databricks Marketplace
C.Databricks Repos
D.Databricks SQL
AnswerD

Databricks SQL is the built-in service for SQL-centric analytics. It provides the SQL editor, query scheduling, dashboards, and alerting, and it runs queries on SQL warehouses. It lets an analyst build and share reports without notebook code, and the scheduled refresh delivers the required daily updates to regional managers, so this component directly satisfies every stated requirement.

Why this answer

Databricks SQL is purpose-built for SQL analysts: it includes a query editor, visualization tools, dashboards, alerts, and scheduled refreshes running on SQL warehouses. Because the analyst needs to author queries, schedule them, and publish shareable visualizations without notebook code, this is the only listed component that covers the full workflow end to end.

Exam trap

The trap here is assuming that any scheduling-capable component, such as Jobs, can substitute for the SQL analytics experience, when only Databricks SQL provides the editor, dashboards, and scheduled query refresh together.

223
MCQmedium

What happens if a user submits a question that requires data from a table NOT explicitly defined in the Genie space configuration?

A.The model will try to guess the schema of the table
B.The model will deny the request due to lack of access
C.The model will use the table anyway if the user has access
D.The query will execute but return null results
AnswerB

Genie spaces operate under the constraint of 'allowed_tables.' If the required data is not in this list, the model is effectively blocked from 'seeing' or querying that data. This ensures that the AI's operational scope is strictly limited to the tables defined by the administrator or space owner.

Why this answer

Genie spaces are designed with a security-first approach. If a table is not listed in the space's configuration, the model is physically prevented from accessing it. This ensures that users cannot 'force' the AI to query unauthorized or sensitive data outside the intended analytical scope, protecting the organization from data leakage or unauthorized access to proprietary information stored in other parts of the Unity Catalog.

Exam trap

Candidates often assume the model will attempt to 'guess' or search for data in other tables, failing to recognize that Genie is strictly limited to the tables defined in its configuration.

224
MCQeasy

A data analyst is creating a table in Databricks SQL to store product information. The analyst wants to ensure that the table is automatically optimized for query performance as data is added, without manual intervention. Which table type should the analyst use?

A.A Delta table with Z-ORDER applied on a schedule.
B.A Delta table with liquid clustering enabled on high-cardinality columns.
C.A Delta table partitioned by a low-cardinality column.
D.A Parquet table with manually defined partitions.
AnswerB

Liquid clustering automatically optimizes data layout as new data is added, without requiring manual partitioning or Z-ORDER. It is designed for tables that need continuous optimization. By enabling liquid clustering on columns frequently used in filters, the table maintains good query performance with minimal maintenance, directly meeting the requirement for automatic optimization.

Why this answer

Liquid clustering is a feature in Delta Lake that automatically optimizes data layout as new data is written, without manual intervention. It is ideal for tables where query patterns may change or where continuous optimization is desired. By enabling liquid clustering, the analyst ensures that the table remains performant without needing to manually partition or schedule Z-ORDER operations.

Exam trap

The trap here is assuming that partitioning or scheduled Z-ORDER provides automatic optimization, when they actually require manual setup and maintenance.

225
MCQmedium

An analyst is observing that a specific query is slow because it performs a large shuffle. Which of the following is the most likely cause, and how can it be mitigated?

A.Cause: Lack of partitioning; Mitigation: Increase cluster size
B.Cause: Poor data colocation; Mitigation: Use Z-Ordering
C.Cause: Too many files; Mitigation: Run VACUUM
D.Cause: Column data types; Mitigation: Use VARCHAR instead of STRING
AnswerB

A shuffle is often caused by data being spread randomly across files. Z-Ordering forces related data into the same files based on the specified columns. By colocation, the engine can perform operations on the same node, significantly reducing the amount of data transferred over the network during a shuffle.

Why this answer

Large shuffles occur when data must be redistributed across nodes to perform a join or group by operation. This often happens if the data is not partitioned or clustered effectively on the join keys. To mitigate this, the analyst should ensure that the table is Z-Ordered on the relevant columns, as this colocates the data and allows the engine to perform more efficient local operations instead of cross-node shuffles.

Exam trap

Candidates frequently blame network latency or hardware limits, missing that large shuffles in Spark/Databricks are almost always caused by poor data distribution and lack of colocation on join keys.

Page 2

Page 3 of 4

Page 4

All pages