Courseiva

Databricks Certified Data Analyst Associate (Databricks-DA-Assoc) — Questions 1–75

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

Page 1 of 4

Page 2
1
MCQhard

Refer to the exhibit. An analyst is reviewing a cluster configuration JSON. If the cluster is currently idle, what happens after 40 minutes of inactivity?

A.The cluster scales down to 2 workers.
B.The cluster remains idle at 2 workers.
C.The cluster terminates.
D.The cluster scales up to 8 workers.
AnswerC

The configuration sets 'autotermination_minutes' to 30. This parameter dictates that the cluster will shut down after 30 minutes of inactivity. Since 40 minutes is greater than 30, the cluster will have successfully terminated, stopping the compute costs associated with the idle nodes in the environment.

Why this answer

Databricks clusters with auto-termination enabled will shut down after the specified inactivity threshold is met. In this configuration, the 'autotermination_minutes' is set to 30. Therefore, once the cluster remains idle for 30 minutes, it will automatically terminate.

By the 40-minute mark, the cluster has already completed the termination sequence, ensuring no additional costs are incurred for idle resources beyond the defined grace period.

Exam trap

Candidates often assume the cluster stays active for the full duration or wait for a manual shutdown, forgetting that auto-termination initiates the shutdown process immediately upon reaching the inactivity threshold.

2
MCQmedium

A data analyst joined a new Databricks workspace and sees a catalog named 'sales_prod' containing schemas 'bronze', 'silver', and 'gold'. The analyst needs to query only the 'gold' schema tables and must not see or query the 'bronze' or 'silver' tables. Which Unity Catalog object should the workspace administrator grant to satisfy this least-privilege requirement?

A.USE CATALOG on sales_prod and USE SCHEMA plus SELECT on the gold schema
B.USE SCHEMA on bronze and silver only
C.SELECT on the sales_prod catalog
D.ALL PRIVILEGES on the sales_prod catalog
AnswerA

Unity Catalog privileges are hierarchical: USE CATALOG lets the analyst traverse the catalog, and USE SCHEMA plus SELECT on gold grants read access to that schema's tables only. Without USE SCHEMA or SELECT on bronze and silver, the analyst cannot see or query those tables, achieving least privilege exactly as required.

Why this answer

Unity Catalog enforces a privilege hierarchy where traversing a catalog requires USE CATALOG and reading a schema's tables requires USE SCHEMA and SELECT on that schema. Granting USE CATALOG on sales_prod and USE SCHEMA plus SELECT on gold gives read access to only the gold tables, while the absence of grants on bronze and silver keeps them hidden.

Exam trap

The trap here is assuming a single broad grant at the catalog level can be scoped down, when Unity Catalog privileges only widen access and never restrict it.

3
MCQhard

A data analyst runs a query in Databricks SQL that aggregates sales data by product category. The analyst notices that the query results include a category value of NULL. The analyst wants to exclude rows where the category is NULL from the aggregation. Which SQL clause should be added to the query?

A.WHERE category IS NOT NULL
B.ORDER BY category NULLS LAST
C.GROUP BY category HAVING COUNT(*) > 0
D.HAVING category IS NOT NULL
AnswerA

The WHERE clause filters rows before aggregation, so adding WHERE category IS NOT NULL removes rows with NULL category from the input to the aggregation. This ensures the aggregation does not include NULL as a group. It is the correct way to exclude NULLs before grouping.

Why this answer

To exclude NULL categories before aggregation, use a WHERE clause with IS NOT NULL. This filters out rows with NULL category before grouping, so the aggregation does not include a NULL group. HAVING filters after aggregation and is less efficient, while ORDER BY only sorts.

Exam trap

The trap here is using HAVING instead of WHERE to filter NULLs; HAVING filters after aggregation and does not prevent the aggregation from processing NULL rows, which can be less efficient and may still include the NULL group if not properly filtered.

4
MCQhard

Refer to the exhibit. A developer is configuring a Genie Space. The model is still including discounts in its revenue calculations despite the instruction. What is the most likely cause?

A.The temperature is set too low.
B.The model lacks sufficient metadata or schema constraints on the 'discount' column.
C.The Genie Space requires a higher temperature setting to understand complex exclusions.
D.The 'data_sources' array is missing the 'discounts' table.
AnswerB

Genie models prioritize schema information and column names. If the 'discount' column is clearly labeled, the model might automatically include it because the instruction 'ignore discounts' is too vague. The developer should rename the column or add explicit logic in a view to ensure the model cannot reach that data.

Why this answer

The AI model is likely relying on the underlying schema metadata or the actual data distribution more heavily than the provided system instructions. When instructions are contradictory to standard SQL column names or common business logic, the model requires more explicit guidance. Improving the precision of the instructions, such as specifying columns to exclude, or modifying the view definition, is necessary to override the model's default behavior.

Exam trap

Candidates often assume the model has 'common sense' about business terms, failing to realize that without explicit instructions or schema constraints, the model defaults to standard SQL logic.

5
MCQmedium

A data analyst is building a Databricks SQL dashboard that tracks daily active users across four global regions. The dashboard has a single dataset query, and the analyst wants the region filter to apply to all visualizations on the dashboard without editing each widget's query. Which dashboard feature should the analyst configure?

A.A Unity Catalog row filter function attached to the underlying table.
B.A separate query snippet embedded in each visualization's SQL that hardcodes the region list.
C.A dashboard-level parameter with the keyword {{region}} applied in the dataset query.
D.A scheduled refresh on the dashboard set to run every five minutes.
AnswerC

Databricks SQL dashboards support parameters that can be bound to a dataset query using the {{parameter_name}} syntax. A dashboard-level parameter created from the dashboard UI is shared across all visualizations that reference the same dataset, so changing its value re-executes the dataset and updates every widget that depends on it. This is the intended mechanism for cross-widget filtering without duplicating filter logic in each visualization.

Why this answer

Dashboards in Databricks SQL let analysts define parameters that can be referenced in dataset queries with the {{name}} syntax. A dashboard-level parameter appears as a control for viewers and, because multiple visualizations can share one dataset, changing the parameter re-runs the dataset and updates every widget bound to it. This satisfies the requirement of a single region filter driving all visualizations without editing each widget's SQL.

Exam trap

The trap here is confusing a dashboard parameter, which is an interactive control shared across widgets, with table-level row filters or refresh schedules that have nothing to do with user-driven filtering.

6
MCQeasy

A data analyst needs to quickly upload a small CSV file from their local machine into Databricks to explore its contents. They want to avoid writing code and prefer a graphical interface. Which Databricks feature should they use?

A.The Add Data UI
B.The Spark DataFrame API
C.The Databricks CLI
D.The DBFS CLI
AnswerA

The Add Data UI in Databricks provides a point-and-click interface for uploading files from a local machine. It supports CSV, JSON, and other formats, automatically infers schema, and creates a table. This is ideal for quick, one-time uploads without coding. It also allows previewing and editing column types before creating the table, making it suitable for analysts who prefer a visual approach.

Why this answer

The Add Data UI is specifically designed for uploading local files through a graphical interface, automatically creating tables and inferring schema. It requires no coding and is perfect for quick exploration. Other options involve command-line or programmatic interfaces, which are not as user-friendly for ad-hoc uploads.

Exam trap

The trap here is confusing the Add Data UI with command-line tools like the Databricks CLI or DBFS CLI, which also allow file uploads but require coding or terminal commands.

7
MCQmedium

A data analyst needs to ingest a large volume of CSV files from an external S3 bucket into a Delta table. Which method provides the most efficient, fault-tolerant, and incremental loading approach in Databricks?

A.Use the COPY INTO command with a static file path.
B.Manually read files into a DataFrame and use write.mode('append').
C.Implement Auto Loader using cloudFiles source.
D.Use the Databricks SQL 'Import' wizard via the UI.
AnswerC

Auto Loader uses cloud-native notifications and directory listing to detect new files efficiently. By managing its own checkpointing, it ensures exactly-once semantics and handles schema evolution automatically. It is the industry standard for production-grade ingestion pipelines in Databricks, providing superior performance and reliability compared to manual file-based ingestion methods.

Why this answer

Auto Loader is specifically designed to process files as they arrive in cloud storage. It maintains state information in a checkpoint location, ensuring that only new or modified files are ingested during subsequent runs. This pattern is critical for production pipelines where data volume scales over time, as it avoids full scans of the source directory, significantly reducing latency and compute costs while ensuring idempotent processing for reliability.

Exam trap

Candidates often choose COPY INTO for continuous, large-scale ingestion, failing to recognize that Auto Loader is the superior, stateful, and fault-tolerant choice for cloud file streams.

8
MCQeasy

Which feature in Databricks allows an analyst to automatically send a dashboard snapshot to a stakeholder's email on a recurring schedule?

A.Alerts
B.Dashboard Subscriptions
C.Query Snapshots
D.Data Refresh Jobs
AnswerB

Dashboard subscriptions allow analysts to schedule the delivery of dashboard snapshots via email. This automates the reporting process, ensuring that stakeholders receive the latest data regularly. It is the correct feature for the scenario, providing a convenient way to share insights without requiring the recipients to manually check the dashboard.

Why this answer

The 'Subscription' feature in Databricks SQL is specifically designed for this purpose. It automates the distribution of dashboard results, which is a key requirement for keeping stakeholders informed without manual intervention. This feature ensures that critical business insights are delivered consistently, increasing the visibility of data-driven findings and ensuring that team members stay updated even if they do not log into the Databricks workspace daily.

Exam trap

Test-takers frequently confuse dashboard subscriptions with manual sharing or scheduled job notebook runs, missing the native subscription feature designed for recurring emails.

9
MCQmedium

A data analyst is designing a star schema in Databricks SQL to optimize query performance for a large sales dataset. Which strategy most effectively minimizes data shuffling during join operations between a large fact table and a small dimension table?

A.Apply the CLUSTER BY clause on the primary key of the fact table.
B.Use the Z-ORDER BY clause on the dimension table's primary key.
C.Leverage the BROADCAST join hint on the dimension table.
D.Convert the fact table into a temporary view before joining.
AnswerC

The broadcast hint forces the optimizer to send a copy of the smaller dimension table to every executor node. This prevents the large fact table from being repartitioned or shuffled across the network, significantly reducing join execution time. This is the optimal configuration for star schemas in Databricks SQL environments.

Why this answer

Utilizing the broadcast join strategy is essential when joining a massive fact table with a significantly smaller dimension table. By distributing the small table to all worker nodes, Databricks eliminates the need for expensive network shuffles of the fact table rows. This approach is fundamental for maintaining low latency in BI dashboards where users require sub-second query responses on complex star schema structures.

Exam trap

Candidates often suggest partitioning or Z-Ordering for every scenario. They miss that the broadcast join is specifically intended to eliminate shuffling by moving small tables instead of large ones.

10
Multi-Selecthard

Which TWO of the following are benefits of using Unity Catalog with Databricks SQL?

Select 2 answers
A.Centralized access control for all data assets.
B.Automatic translation of SQL to Python code.
C.Data lineage tracking from source to dashboard.
D.Increased query execution speed for simple SELECTs.
E.Ability to bypass authentication for local files.
AnswersA, C

Unity Catalog enables administrators to define permissions in one place, which are then enforced across all workspaces and SQL Warehouses. This centralization eliminates the complexity of managing separate access lists for different environments and ensures consistent security policies throughout the organization's data lifecycle and analytical operations.

Why this answer

Unity Catalog provides a unified governance layer for data and AI assets across the Databricks workspace. It enables fine-grained access control, centralized auditing, and simplified data discovery. By implementing Unity Catalog, organizations can ensure compliance and security while allowing analysts to collaborate effectively.

This is a critical component of modern data platforms, providing the necessary visibility and control required by data teams to manage data assets securely at scale.

Exam trap

Candidates often select features like 'Auto-scaling' or 'Query Caching' as primary benefits of Unity Catalog, confusing resource management with the governance and lineage capabilities that Unity provides.

11
Multi-Selectmedium

A data analyst is new to a Databricks workspace and needs to work with data stored in Unity Catalog. The analyst wants to understand which statements about Unity Catalog are accurate. (Choose two.)

Select 2 answers
A.Unity Catalog access control can be applied at the catalog, schema, table, and column levels, including row and column filters.
B.Unity Catalog provides a centralized governance model with a three-level namespace of catalog, schema, and table or view.
C.Unity Catalog metadata is stored separately in each workspace, so grants must be repeated for every workspace.
D.Unity Catalog can only be queried from notebooks and not from Databricks SQL warehouses or dashboards.
E.Unity Catalog requires that all tables be stored as external tables in cloud object storage rather than managed tables.
AnswersA, B

Unity Catalog supports fine-grained privileges at multiple levels, and it also supports row filters and column masks attached to tables. This lets administrators grant broad access to a catalog or schema while restricting sensitive rows or columns for specific users or groups. The unified privilege model means analysts see only the data they are entitled to, regardless of the query tool they use.

Why this answer

Unity Catalog centralizes governance with a three-level namespace and supports privileges from the catalog level down to columns, including row filters and column masks. These two characteristics let an analyst reference data with fully qualified names and trust that access rules are enforced consistently. The other statements misstate storage requirements, metadata placement, or the range of supported query interfaces.

Exam trap

The trap here is mixing up Unity Catalog with the older workspace-local Hive metastore, leading to false beliefs that grants are per-workspace or that only notebooks can query governed data.

12
MCQmedium

An analyst wants to create a view named `main.sales.high_value` that returns only rows from `main.sales.orders` where `total_amount > 1000`, and wants the view to reflect future updates to the base table automatically. Which statement should the analyst run?

A.CREATE MATERIALIZED VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000;
B.CREATE VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000;
C.CREATE TEMPORARY VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000;
D.CREATE OR REPLACE VIEW main.sales.high_value AS SELECT * FROM main.sales.orders WHERE total_amount > 1000 WITH SCHEMA EVOLUTION;
AnswerB

A standard CREATE VIEW stores only the SQL definition, and each query re-executes it against the base table, so results always reflect the latest committed data. This matches the requirement for a live, filtered projection without storage or refresh overhead. The view inherits access through the base table, so the analyst needs SELECT on the orders table.

Why this answer

Because the analyst wants results that always reflect the current state of the base table, a standard view is correct. A view persists only the query definition, and Databricks re-evaluates it on every read, so inserts or updates to the orders table appear immediately. Materialized views cache results and need refreshes, while temporary views vanish at session end, so neither matches the persistent, always-current requirement.

Exam trap

The trap here is confusing materialized views, which cache results and require refresh, with standard views that always read live data.

13
MCQmedium

A data analyst is designing a dimension table in Databricks SQL that will be used in a star schema. The table contains a natural business key (e.g., product_code) and a surrogate key (e.g., product_sk). The analyst wants to ensure that the surrogate key is unique and automatically generated for each new row, while also enforcing that the natural key is unique. Which approach best achieves these requirements?

A.Define product_sk as BIGINT and set a default value using the UUID() function, then add a PRIMARY KEY constraint on product_sk and a UNIQUE constraint on product_code.
B.Use a Delta table with a CHECK constraint that validates product_code IS NOT NULL, and generate product_sk using the ROW_NUMBER() window function in a view.
C.Define product_sk as STRING and use the MONOTONICALLY_INCREASING_ID() function during inserts to populate it, then add a PRIMARY KEY constraint on product_sk.
D.Define product_sk as GENERATED ALWAYS AS IDENTITY and add a PRIMARY KEY constraint on product_sk and a UNIQUE constraint on product_code.
AnswerD

Using GENERATED ALWAYS AS IDENTITY automatically generates unique surrogate keys. Adding a PRIMARY KEY on product_sk enforces uniqueness and non-nullability, while a UNIQUE constraint on product_code enforces the natural key's uniqueness. This combination meets both requirements and is supported in Delta Lake tables.

Why this answer

The requirement is for an automatically generated, unique surrogate key and a unique natural key. The IDENTITY column property in Delta Lake automatically generates unique, sequential values for new rows. Combining it with a PRIMARY KEY constraint on the surrogate key and a UNIQUE constraint on the natural key enforces both uniqueness rules.

Other options either misuse functions, lack persistence, or do not enforce uniqueness correctly.

Exam trap

The trap here is assuming that any function that generates numbers, such as MONOTONICALLY_INCREASING_ID(), guarantees uniqueness and persistence in a table, when in fact it does not.

14
MCQhard

Refer to the exhibit. The 'sales_data' table is growing rapidly. You notice queries filtering by 'event_date' are fast, but queries filtering by 'id' are slow. What is the most effective data modeling change to optimize for 'id' lookups?

A.Re-partition the table by 'id' instead of 'event_date'.
B.Use Z-Ordering or Liquid Clustering on the 'id' column.
C.Create a secondary table for 'id' lookups that is partitioned by 'id'.
D.Change the table type to a standard Parquet table without partitioning.
AnswerB

Z-Ordering or Liquid Clustering allows the engine to organize data within the existing partitions to facilitate efficient skipping on the 'id' column. This keeps the physical folder structure manageable based on 'event_date' while providing high-performance access paths for queries filtering by specific 'id' values without creating excess files.

Why this answer

The table is currently partitioned by 'event_date', which creates a folder structure that only aids queries filtering on dates. Partitioning by a high-cardinality column like 'id' is a major anti-pattern as it leads to the 'small file problem'. Instead, the analyst should retain the partitioning on 'event_date' but apply Z-Ordering or Liquid Clustering on the 'id' column to facilitate efficient data skipping without creating excessive directory partitions.

Exam trap

Candidates often suggest partitioning by high-cardinality columns like 'id', which leads to the 'small file problem' and degrades performance rather than improving it.

15
MCQeasy

Refer to the exhibit. An analyst receives this error when attempting to select from a table in Databricks SQL. What is the most likely cause?

A.The table schema is corrupted and requires a REPAIR TABLE command.
B.The analyst is missing the USE CATALOG or USE SCHEMA statement.
C.The SQL Warehouse is stopped and needs to be restarted.
D.The table has too many partitions, causing a timeout.
AnswerB

The error occurs because the SQL Warehouse cannot locate the object within the default scope. Setting the session context with USE CATALOG or USE SCHEMA ensures the engine looks in the intended location. Alternatively, using the fully qualified path catalog.schema.table resolves the lookup failure by providing the complete address.

Why this answer

The error indicates a Namespace resolution issue. In Databricks SQL, tables are organized in a three-level namespace: catalog.schema.table. If the current session context is not set to the correct catalog and schema, or if the user lacks the necessary privileges to see the object, the engine cannot resolve the path.

Correcting the context or using a fully qualified name resolves this common accessibility error.

Exam trap

Candidates often assume the error is related to insufficient user permissions, ignoring the simpler and more common issue of failing to define the active namespace context.

16
MCQmedium

A data analyst is building an AI/BI Genie space for the marketing team. The team needs to explore data from a Unity Catalog table named `campaign_metrics` and also wants Genie to understand that the column `roi` should always be calculated as `(revenue - cost) / cost`, regardless of how users phrase their questions. The analyst has already added the table to the Genie space. Which action should the analyst take to ensure Genie consistently applies this calculation?

A.Use the Genie space's 'Sample Questions' feature to add a question that asks for ROI, which will train the model to compute it correctly.
B.Enable the 'SQL Warehouse' setting in the Genie space to automatically infer calculated columns from the table schema.
C.Add a SQL expression in the Genie space's 'Instructions' section defining `roi` as `(revenue - cost) / cost`.
D.Create a view in Unity Catalog that calculates `roi` and add that view to the Genie space instead of the base table.
AnswerC

Instructions in a Genie space are used to provide business logic, definitions, and calculations that guide the model. By adding a SQL expression that defines `roi` as `(revenue - cost) / cost`, the analyst ensures Genie consistently applies this formula whenever users ask about ROI, even if they use different phrasing. This is the intended mechanism for enforcing such calculations.

Why this answer

The correct approach is to add a SQL expression in the Instructions section of the Genie space. Instructions allow analysts to define business terms, calculations, and logic that Genie should follow. By specifying `roi` as `(revenue - cost) / cost`, the analyst ensures consistent application across user queries.

Other options either do not enforce the calculation or misinterpret Genie features.

Exam trap

The trap here is assuming that adding a view or sample question will automatically enforce a calculation, when only the Instructions section provides prescriptive business logic.

17
MCQhard

An analyst is performing a join between a large fact table and a small dimension table. To ensure optimal performance in Databricks SQL, which join type is preferred when the dimension table fits in memory?

A.Sort-merge join
B.Broadcast hash join
C.Cartesian join
D.Full outer join
AnswerB

A broadcast join broadcasts the smaller table to every executor, allowing the join to occur locally at each node without shuffling the large fact table. This significantly reduces data movement across the network, which is the primary bottleneck in distributed join operations within Databricks SQL environments.

Why this answer

When one side of a join is significantly smaller than the other, the engine can use a broadcast join. By sending the smaller table to all worker nodes, the engine avoids a costly shuffle of the larger fact table. This is the most efficient way to perform joins in a distributed environment, as it minimizes network traffic and accelerates data processing speeds significantly.

Exam trap

Candidates often confuse shuffle hash joins or sort-merge joins with broadcast joins, overlooking the distinct performance advantage of broadcasting a small dimension table that fits in memory.

18
MCQhard

A data analyst is using the Databricks SQL Connector for Python to query a large Delta table. The query returns millions of rows, and the analyst wants to process the results in batches to avoid memory issues. Which method should the analyst use to fetch rows in chunks?

A.cursor.fetchone()
B.cursor.fetchall()
C.cursor.fetchmany(size)
D.cursor.execute() with a LIMIT clause
AnswerC

fetchmany(size) returns the next set of rows up to the specified size, enabling batch processing. This method is ideal for large result sets because it allows the analyst to iterate over chunks, reducing memory overhead. It is part of the Python DB API 2.0 specification and supported by the Databricks SQL Connector.

Why this answer

The fetchmany(size) method allows fetching a specified number of rows at a time, enabling batch processing of large result sets. This is the standard way to avoid loading all rows into memory. Other methods either fetch all rows, one row, or require manual query modifications.

Exam trap

The trap here is assuming that fetchall() is acceptable for large datasets or that fetchone() is efficient, when actually fetchmany() is designed for batch processing.

19
MCQmedium

Refer to the exhibit. An analyst is reviewing the configuration JSON for a Genie Space. Why is the 'instructions' field critical in this scenario?

A.It automatically generates a visualization for the user whenever a query is run.
B.It ensures the AI correctly implements the required join logic between the two datasets.
C.It forces the user to provide a region ID every time they ask a question.
D.It caches the results of the query to improve performance for future users.
AnswerB

Explicitly stating the join requirements prevents the AI from making assumptions about column relationships. In complex schemas, the model might otherwise choose the wrong column to join on, leading to inaccurate results. This configuration enforces consistent, correct business logic across all user queries within the Genie Space.

Why this answer

The 'instructions' field serves as a critical bridge between natural language and technical SQL execution. Without this specific guidance, the Genie model might attempt to join tables incorrectly or ignore necessary filtering logic. By explicitly defining the join strategy, the analyst ensures that the AI consistently uses the standard business join keys, preventing the generation of incorrect, unoptimized, or cross-joined results that would compromise data accuracy.

Exam trap

Candidates often believe the instructions field is only for 'formatting' the output. They fail to realize it is a functional tool to define complex SQL join logic that the AI cannot infer.

20
Multi-Selectmedium

A Databricks workspace administrator wants to optimize costs and manage resources effectively. Which TWO of the following capabilities allow for automatic cluster termination and scaling?

Select 2 answers
A.Auto-termination
B.Auto-scaling
C.Cluster Tagging
D.Delta Cache
E.Instance Pools
AnswersA, B

Auto-termination is a critical cost-saving feature that monitors cluster inactivity. When no jobs are running for a specified period, the cluster automatically terminates, ensuring that users are not billed for compute resources that are idle. This is essential for managing budgets effectively in a shared cloud environment.

Why this answer

Databricks provides built-in mechanisms to manage compute costs through Auto-termination and Auto-scaling. Auto-termination automatically shuts down clusters after a period of inactivity, preventing wasteful billing. Auto-scaling allows the cluster to resize itself based on the current workload requirements, adding or removing workers dynamically.

These features are fundamental for maintaining a cost-efficient data platform while ensuring sufficient performance for varying data volumes during analysis.

Exam trap

Candidates often select 'Auto-scaling' and 'Auto-stop' as synonyms, or confuse them with 'Spot Instances'. They fail to recognize these as two distinct cost-saving mechanisms for cluster lifecycle management.

21
Multi-Selecthard

A data analyst is building a dashboard in Databricks SQL that includes a visualization showing sales by product category. The analyst wants to ensure that the dashboard is interactive and allows viewers to filter the data by date range and product category. Which TWO of the following actions should the analyst take to enable this interactivity? (Choose two.)

Select 2 answers
A.Embed the dashboard in an iframe and use JavaScript to pass filter values.
B.Add a date range parameter to the dashboard and reference it in the query's WHERE clause.
C.Add a dropdown list parameter for product category and use it in the query to filter the data.
D.Use a dashboard-level filter that applies to all visualizations and set it to a fixed date range.
E.Create a separate visualization for each product category and add them all to the dashboard.
AnswersB, C

Adding a date range parameter and referencing it in the query's WHERE clause allows viewers to select a date range that dynamically filters the data. This is a standard method to enable interactivity in Databricks SQL dashboards. The parameter must be added to the dashboard so viewers can adjust it, and the query must use it to filter results.

Why this answer

To enable interactive filtering by date range and product category, the analyst should use parameters. A date range parameter and a dropdown list parameter for product category, both referenced in the query, allow viewers to dynamically filter the data. This is the standard and most efficient method in Databricks SQL dashboards.

Exam trap

The trap here is thinking that creating multiple static visualizations or using fixed filters achieves interactivity, when in fact parameters are required.

22
MCQmedium

An analyst wants to view the query profile for a specific query that was run recently. Where should they look in the Databricks SQL UI?

A.The Catalog Explorer
B.The SQL Editor
C.The Query History tab
D.The SQL Warehouse settings page
AnswerC

The Query History tab in the Databricks SQL UI logs all queries executed on the warehouse. By selecting a specific query ID, users can access the Query Profile, which displays the execution graph, metrics, and bottlenecks, allowing for granular analysis of how the engine processed the data during runtime.

Why this answer

The Query History interface is the primary tool for performance troubleshooting. It provides a visual representation of the query plan, including task breakdown, data read/write volumes, and execution time per operator. Understanding this UI is vital for a Data Analyst to identify bottlenecks such as data skew or excessive shuffling, enabling them to optimize slow queries effectively and improve the efficiency of their SQL workloads.

Exam trap

Candidates often look inside individual notebook outputs or cluster logs instead of the dedicated Query History tab within the Databricks SQL UI to inspect detailed query execution plans.

23
MCQhard

A data analyst is querying a Unity Catalog managed table named sales in the analytics catalog and the marketing schema. A query fails with an error indicating the table cannot be found, even though the analyst can see the table in Catalog Explorer. The analyst is connected to a SQL Warehouse in the same workspace. Which action is most likely to resolve the access issue?

A.Restart the SQL Warehouse so it picks up the latest Unity Catalog metadata
B.Verify that the analyst has USE CATALOG on analytics, USE SCHEMA on marketing, and SELECT on the sales table
C.Ask a workspace administrator to enable table access control on the all-purpose cluster
D.Convert the managed table to an external table so the analyst can read the underlying files
AnswerB

In Unity Catalog, accessing a table requires a chain of privileges: USE CATALOG on the catalog, USE SCHEMA on the schema, and SELECT on the table. Seeing the table in Catalog Explorer does not guarantee these privileges are granted to the user. If any link in the chain is missing, the query fails even though the object is visible, making this the most likely resolution.

Why this answer

Unity Catalog enforces a privilege hierarchy: USE CATALOG on the catalog, USE SCHEMA on the schema, and SELECT on the table. Visibility in Catalog Explorer does not imply these grants. Restarting the warehouse, converting the table to external, or enabling legacy cluster table access control do not address missing Unity Catalog privileges.

Exam trap

The trap here is equating visibility in Catalog Explorer with query access, when Unity Catalog still requires the full USE CATALOG, USE SCHEMA, and SELECT chain.

24
Multi-Selecthard

An analyst is preparing a dashboard and needs to ensure that the queries powering it are as performant as possible. Which THREE techniques should the analyst use to optimize these SQL queries in Databricks?

Select 3 answers
A.Regularly run OPTIMIZE on the underlying tables
B.Always use SELECT * to fetch all available columns
C.Compute statistics using the ANALYZE command
D.Z-Order tables on frequently filtered or joined columns
E.Convert all Delta tables to CSV format for speed
AnswersA, C, D

Regular optimization compacts small files created by ingestion processes into larger files. This reduction in the total number of files directly decreases metadata overhead and scan times, leading to more responsive queries, which is a fundamental requirement for interactive dashboard performance in Databricks SQL.

Why this answer

Optimizing dashboards requires a combination of efficient data layout, metadata management, and query-level tuning. Using the 'OPTIMIZE' command keeps files consolidated, while 'ANALYZE' provides the CBO with the statistics it needs for optimal plan selection. Finally, using 'Z-Ordering' on high-cardinality columns frequently appearing in filters or joins ensures the query engine can skip large volumes of irrelevant data, directly leading to faster dashboard response times.

Exam trap

Candidates often confuse VACUUM with OPTIMIZE or assume Z-Ordering alone is enough without running ANALYZE to update the Cost-Based Optimizer's statistics for best performance.

25
MCQhard

An analyst is reviewing a slow query and identifies that the 'FileScan' stage is taking most of the time. Which THREE factors could be causing this inefficiency?

A.The table contains a high number of small files.
B.The query lacks filters on partition columns.
C.The query uses a cross join.
D.The data is not Z-Ordered on filtered columns.
E.The query is using a User-Defined Function (UDF).
AnswerA, B, D

A high number of small files increases metadata processing time, as the system must open, read, and close many files to retrieve even a small amount of data. This 'small file problem' is a common cause of slow query performance and can be mitigated by using the OPTIMIZE command.

Why this answer

Slow FileScans usually point to I/O-related issues. If too many small files are present, the metadata overhead becomes significant. Without proper pruning, the system scans unnecessary data.

Z-Ordering or partitioning issues cause the engine to read more data than required. Addressing these factors is vital for analysts, as they directly impact the 'Data Skipping' efficiency of the Delta Lake engine, which is the cornerstone of high-performance analytics in Databricks.

Exam trap

Students often overlook metadata overhead caused by tiny files, focusing only on compute sizing instead of file management and partition pruning factors.

26
MCQmedium

When creating a dashboard, an analyst needs to compare two different categories side-by-side. Which visualization is best suited for this task?

A.Heatmap
B.Grouped Bar Chart
C.Bubble Chart
D.Stacked Area Chart
AnswerB

A grouped bar chart explicitly places bars for different categories next to each other for every data point or category. This makes it the standard choice for side-by-side comparisons, allowing the viewer to immediately see which category has a higher or lower value without needing to interpret complex visual encodings.

Why this answer

A grouped bar chart is the most effective visualization for comparing distinct categories side-by-side. It allows the viewer to easily perceive differences in magnitude between related items. This is a common requirement in business reporting, where analysts must compare performance across regions, products, or time periods.

By selecting the right visualization, the analyst reduces the cognitive effort required for the audience to extract insights from the data.

Exam trap

Candidates often choose a stacked bar chart or a line chart when comparing distinct categories, which makes it significantly harder for the reader to compare the exact values side-by-side.

27
MCQeasy

Which of these is a benefit of using a 'parameter' in a Databricks SQL query?

A.It permanently changes the data in the underlying table.
B.It forces every user to run the exact same query.
C.It allows users to dynamically filter results without changing the code.
D.It creates a copy of the database for each user session.
AnswerC

Parameters provide a way for end-users to change query criteria via a UI dropdown or text box. This makes the dashboard interactive without requiring the user to know SQL or modify the source code, which is the primary objective of creating self-service BI tools in the Databricks environment.

Why this answer

Parameters enable dynamic query execution. By allowing users to provide input at runtime, the same query can serve multiple analytical needs. This reduces the need to create dozens of near-identical queries, simplifying the codebase and making the dashboard much more interactive.

It empowers the end-user to drill down into the specific segments they are interested in, which is a key driver for self-service analytics adoption within organizations.

Exam trap

Candidates often assume parameters permanently alter the underlying database table or require rewriting the core SQL code every time the value changes.

28
MCQeasy

Which option is best for sharing a dashboard with internal stakeholders who do not have access to the Databricks workspace?

A.Create a temporary personal access token (PAT) for each stakeholder to login.
B.Configure an email subscription to send the dashboard periodically as a snapshot.
C.Publicly host the dashboard URL on the company intranet.
D.Manually export the dashboard as a CSV file to a shared file server.
AnswerB

Email subscriptions allow analysts to schedule the delivery of dashboard snapshots to a distribution list. This method is the secure and intended way to provide insights to users without requiring them to log into the Databricks platform, facilitating broader organizational visibility into critical business metrics and KPIs.

Why this answer

Databricks provides a subscription feature to email dashboard snapshots (PDFs or screenshots) to stakeholders. This ensures that users outside the immediate Databricks environment can consume relevant business insights without needing to manage complex identity and access management for the workspace. This is a key requirement for democratization of data, ensuring that executives and non-technical staff stay informed on KPIs regularly.

Exam trap

Candidates often assume external stakeholders need full workspace accounts to view dashboards, forgetting about scheduled snapshot email subscriptions.

29
MCQmedium

Which Databricks feature provides a detailed, lineage-based view of how data flows from source to destination?

A.Databricks SQL Audit Logs
B.Unity Catalog Data Lineage
C.Delta Live Tables Pipeline Monitoring
D.Cluster Event Logs
AnswerB

Unity Catalog automatically captures lineage information for queries, jobs, and tables. It provides a visual and programmatic way to explore how data is derived, identifying the source tables, intermediate transformations, and destination tables. This is critical for data governance, impact analysis, and maintaining high trust in the platform's analytical outputs.

Why this answer

Data lineage in Unity Catalog tracks the relationships between data assets, transformations, and end-users. This visibility is essential for impact analysis, debugging, and compliance. Understanding lineage helps analysts understand the origin of their data and the downstream effects of any changes they make, ensuring that business stakeholders always have confidence in the data's quality, provenance, and the transformation logic applied throughout the analytical pipeline.

Exam trap

Candidates confuse table history or audit logs with data lineage, overlooking the specific visual mapping capabilities provided by Unity Catalog.

30
Multi-Selecthard

A data analyst is setting up a recurring ingestion job that must load only new files from a cloud storage directory into a Delta table, must track which files have already been processed, and must fail clearly if the source schema changes unexpectedly. Which TWO capabilities should the analyst rely on? (Choose two.)

Select 2 answers
A.Mounting the storage location and using spark.read on the directory with overwrite mode.
B.CREATE TABLE AS SELECT on the source directory rebuilds the table from all files on every run.
C.A scheduled notebook that lists the directory and compares filenames to a manually maintained table.
D.Auto Loader with a checkpoint location tracks processed files and supports schema enforcement or evolution policies.
E.COPY INTO maintains a load history so previously ingested file paths are skipped on subsequent runs.
AnswersD, E

Auto Loader uses a checkpoint directory to record which files it has consumed, enabling incremental processing of new arrivals. Its schema handling can be configured to fail on unexpected changes or to evolve, which matches the requirement to fail clearly when the source schema shifts. This makes it suitable for the recurring job described.

Why this answer

Both COPY INTO and Auto Loader maintain ingestion state so only new files are processed, and both can be configured to surface unexpected schema changes rather than silently accepting them. COPY INTO uses table-level load history, while Auto Loader uses a checkpoint directory with an explicit schema policy. Either satisfies the incremental and schema-safety requirements.

Exam trap

The trap here is reaching for a manual notebook or full-table rebuild when the managed ingestion tools already provide file tracking and schema policy handling.

31
Multi-Selectmedium

A data platform administrator needs to configure secure data access policies in Unity Catalog. Which TWO actions can the administrator perform to restrict access to sensitive columns within a Delta table? (Choose TWO)

Select 2 answers
A.Grant SELECT privilege on the specific sensitive columns to authorized users while withholding it on other columns in the table.
B.Apply a Unity Catalog column mask using a SQL function that transforms or redacts sensitive values based on the querying user's identity.
C.Modify the cloud storage bucket permissions in AWS S3 or Azure Blob Storage to block read access to individual table files containing sensitive columns.
D.Enable row-level security filters on the table to automatically exclude rows containing sensitive attribute values from query results.
E.Create a static clone of the table using the CLONE command and delete the sensitive columns from the cloned copy for general users.
AnswersA, B

Unity Catalog allows administrators to grant or revoke SELECT privileges at the individual column level. This capability ensures that users only query the specific attributes they are authorized to see, preventing unauthorized exposure of sensitive enterprise data fields.

Why this answer

Unity Catalog supports fine-grained governance through column-level access controls and dynamic masking functions. Administrators can restrict column visibility directly by revoking SELECT privileges on specific columns or by applying SQL-based masking functions that evaluate user attributes at query time. These native capabilities ensure sensitive information remains protected across all notebooks, dashboards, and SQL queries without duplicating physical data assets.

Exam trap

Candidates often assume that column-level security requires row-level filtering or physical data duplication, missing that Unity Catalog provides native SQL-based masking and privilege controls directly on the schema objects.

32
MCQeasy

Which feature in Databricks SQL allows an analyst to view the history and performance metrics of previously executed queries?

A.The Data Explorer.
B.The Query History.
C.The Cluster Metrics tab.
D.The Workspace Audit Logs.
AnswerB

Query History provides a centralized view of all queries executed in the workspace. It includes status, duration, user, and access to the Query Profile, which makes it the essential tool for tracking query performance, investigating failures, and analyzing the historical impact of changes to SQL queries and data models.

Why this answer

The Query History interface is the primary tool for reviewing past executions. It allows analysts to search, filter, and inspect the performance of queries run across the workspace. This is important for identifying long-running queries, diagnosing failures, and comparing performance over time, which supports the iterative process of optimizing data models and SQL code to ensure consistent, reliable, and performant data delivery for the organization.

Exam trap

Candidates confuse the Query History interface with the Delta Lake table history or workspace audit logs when searching for past execution metrics.

33
Multi-Selectmedium

Which THREE of the following are valid sources for importing data into a Databricks Delta table? (Choose three)

Select 3 answers
A.S3, ADLS, and GCS cloud object storage.
B.Local files stored on the Databricks Driver node.
C.External relational databases via JDBC/ODBC connectors.
D.Direct memory buffers from the user's browser.
E.Streaming services like Apache Kafka or Amazon Kinesis.
AnswersA, C, E

These cloud object storage services are the native foundation for data lakes in Databricks. They are the most common sources for large-scale data ingestion and are fully supported by Auto Loader, COPY INTO, and standard Spark read APIs, making them the primary choice for enterprise data warehousing strategies.

Why this answer

Databricks provides flexible connectivity to ingest data from diverse sources into the Lakehouse. Whether the data is in cloud storage, a relational database, or a streaming message broker, the platform offers optimized connectors to load this information into Delta format. Mastering these integration points allows analysts to architect robust data pipelines that can pull information from virtually any enterprise system for unified analysis and reporting within a single environment.

Exam trap

Candidates often mistakenly include internal Databricks features like 'Delta Live Tables' or 'Unity Catalog' as data sources, failing to distinguish between data ingestion sources and governance tools.

34
MCQhard

Refer to the exhibit. You are attempting to enable Change Data Feed (CDF) on an existing Delta table but receive this error. Why is this error occurring?

A.The table is not in the Delta format.
B.The table requires column mapping to track physical schema evolution for CDF.
C.The table has too many rows to enable CDF.
D.CDF is only supported on tables with primary keys.
AnswerB

CDF tracks changes to rows over time. If a table allows schema evolution (renaming columns), there must be a way to map the old column name to the new column name in the files. Column mapping enables this decoupling, which is essential for CDF to function correctly during schema changes.

Why this answer

Change Data Feed (CDF) tracks row-level changes, which requires a stable mapping between column names and their underlying physical file data. If the table was created without column mapping, it cannot safely track changes if column names are altered. Setting 'delta.columnMapping.mode' to 'name' decouples the column names from their physical file identifiers, which is a prerequisite for the metadata tracking required by the CDF feature.

Exam trap

Candidates often assume that CDF can be enabled on any existing table without configuration changes. They miss that column mapping is a mandatory architectural prerequisite for tracking schema evolution in CDF.

35
MCQhard

A data analyst is investigating a query that uses a window function with PARTITION BY and ORDER BY, and the query is slow. The analyst suspects that data skew is causing some partitions to be much larger than others. Which approach should the analyst take to diagnose the skew in the Query Profile?

A.Check the 'Shuffle Read Size' per task to see if some tasks read significantly more data than others.
B.Look at the 'Peak Execution Memory' to identify tasks using more memory.
C.Review the 'Number of Output Rows' for each task to see if some produce more rows.
D.Examine the 'Spill (Disk) Size' to see if any tasks spilled to disk.
AnswerA

In the Query Profile, the task-level metrics for a shuffle stage show the amount of data each task reads. If data is skewed, a few tasks will have much larger Shuffle Read Size than others, indicating that those partitions are handling more data. This is a direct way to identify skew in window functions, which often require shuffling data by the partition key.

Why this answer

Data skew in window functions is often revealed by uneven shuffle read sizes across tasks. The Query Profile provides task-level metrics, and a significant disparity in Shuffle Read Size indicates that some partitions are much larger, causing skew. This allows the analyst to confirm the skew and then apply mitigation techniques like salting or repartitioning.

Exam trap

The trap here is focusing on memory or spill metrics to diagnose skew, when the most direct indicator is the uneven distribution of data read during shuffle.

36
MCQhard

A data analyst has a Databricks SQL dashboard that reads a table in the 'finance' catalog. The dashboard works for the analyst but shows an error for a colleague who has SELECT on the table. The colleague lacks USE CATALOG on 'finance' and USE SCHEMA on the containing schema. What is the most likely cause of the error the colleague sees?

A.The dashboard was created with the analyst's credentials embedded, so it always runs as the analyst
B.The colleague is connected to a different SQL warehouse that cannot reach the finance catalog
C.The table is a view that references tables the colleague cannot access
D.The colleague cannot traverse the catalog and schema, so Unity Catalog denies resolution of the table even though SELECT is granted
AnswerD

Unity Catalog requires USE CATALOG on the parent catalog and USE SCHEMA on the parent schema in addition to SELECT on the table. Without those traversal privileges, the colleague cannot resolve the table's fully qualified name, so the dashboard query fails despite the SELECT grant, which explains the error exactly.

Why this answer

Unity Catalog resolves objects through their full three-part name, which requires USE CATALOG on the catalog and USE SCHEMA on the schema before SELECT on the table is even evaluated. The colleague holds SELECT but lacks the traversal privileges, so name resolution fails and the dashboard query errors until those grants are added.

Exam trap

The trap here is assuming SELECT on a table is sufficient, when Unity Catalog also requires traversal privileges on the enclosing catalog and schema.

37
MCQhard

A data analyst has created a Databricks SQL dashboard with a visualization that uses a query with a parameter. The analyst wants to share the dashboard with a colleague who should be able to view the dashboard but not modify the queries or the dashboard itself. The colleague also needs to be able to change the parameter values to explore the data. What should the analyst do?

A.Grant the colleague CAN EDIT permission on the dashboard.
B.Share the underlying query with the colleague and ask them to run it with different parameter values.
C.Grant the colleague CAN MANAGE permission on the dashboard.
D.Grant the colleague CAN VIEW permission on the dashboard and ensure the parameter is not set to a fixed value.
AnswerD

Granting CAN VIEW permission allows the colleague to view the dashboard and interact with parameters, but not edit the queries or dashboard. Parameters that are not fixed can be changed by viewers. This meets the requirements of viewing without modification while allowing parameter exploration.

Why this answer

To allow a colleague to view the dashboard and change parameter values without modifying it, the analyst should grant CAN VIEW permission. This permission level permits viewing and interacting with parameters but restricts editing. Ensuring the parameter is not fixed allows the colleague to adjust it as needed.

Exam trap

The trap here is confusing view and edit permissions, or assuming that sharing the query is equivalent to sharing the dashboard.

38
MCQmedium

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

A.A materialized view that pre-aggregates the data by date and region
B.Dashboard parameters that are referenced in the dataset queries
C.A Delta Live Tables pipeline that refreshes the dashboard dataset hourly
D.A notebook widget embedded in the dashboard as a separate visualization
AnswerB

Dashboard parameters let viewers supply values, such as a date range or region, that are substituted into the dataset queries at run time. This is exactly the interactive filtering behavior required, and it does not require viewers to edit SQL. Parameters can be exposed as widgets on the dashboard, so stakeholders can change the date range and region and see updated results.

Why this answer

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

Exam trap

The trap here is confusing a performance feature like a materialized view with an interactive filtering feature like dashboard parameters.

39
MCQmedium

A data analyst needs to share a frequently updated sales dashboard with business stakeholders who do not have access to the underlying raw tables containing Personally Identifiable Information (PII). Which Databricks SQL feature should be implemented to securely present this summary data?

A.Create static CSV extracts of the summary data and upload them to a shared workspace folder.
B.Grant the stakeholders direct SELECT permissions on the raw tables, relying on them to ignore sensitive columns.
C.Implement dynamic views utilizing column masking and row-level filtering based on user group identities.
D.Duplicate the entire dataset into a separate schema, permanently deleting the sensitive columns from the copy.
AnswerC

Dynamic views evaluate user context at query runtime, seamlessly applying masking functions or filtering rows dynamically. This guarantees that unauthorized users viewing the dashboard only see sanitized or aggregated results while retaining a single source of truth.

Why this answer

Dynamic View Functions with masking policies allow administrators to restrict column-level visibility based on group memberships or user contexts. This ensures stakeholders see aggregated or masked data without requiring separate downstream tables, maintaining strict data governance while enabling seamless visualization sharing.

Exam trap

Candidates frequently confuse dynamic views with static table copies or row-level security implementations, failing to leverage built-in column masking functions for PII protection.

40
MCQhard

A data analyst manages a Delta table gold.orders that is updated by an upstream job every 15 minutes. Analysts run long-running dashboard queries against the table and occasionally see stale results, even seconds after an update. The analyst wants dashboard queries to reflect the latest committed data without restarting the SQL warehouse. Which action should the analyst take?

A.Run OPTIMIZE gold.orders after every upstream commit so the table is compacted and readers pick up new files.
B.Enable the Delta cache on the SQL warehouse and increase the cache size so recent files stay resident.
C.Convert the table to a Parquet table so queries always read the latest files directly from cloud storage.
D.Configure the session to use a fresh snapshot by disabling snapshot reuse, for example by setting the appropriate Spark configuration for the session.
AnswerD

Spark and Databricks SQL can reuse a cached table snapshot within a session for performance. When the underlying Delta table changes, a session that reuses the snapshot may return stale data. Disabling snapshot reuse forces each query to resolve the current table version, so dashboards see the latest committed data without restarting the warehouse.

Why this answer

Snapshot reuse within a Spark or Databricks SQL session caches the table's state for performance, which can cause readers to miss recent commits. Disabling snapshot reuse forces the session to resolve the current Delta version on each query, so dashboards reflect the latest committed data. Cache tuning and OPTIMIZE address performance, not snapshot freshness, and converting formats sacrifices Delta guarantees.

Exam trap

The trap here is attributing stale query results to caching or file layout when the real cause is session-level snapshot reuse of the Delta table version.

41
Multi-Selectmedium

A data analyst is working in a Databricks workspace and needs to ensure that their notebook code is version-controlled and collaborative. Which TWO actions should they take?

Select 2 answers
A.Use the built-in Databricks Repos feature to connect to a Git provider.
B.Copy and paste code into a local text file.
C.Enable 'Collaboration' settings on the notebook to allow multiple users to edit simultaneously.
D.Create a new notebook file for every code change.
E.Download the notebook to their local machine every hour.
AnswersA, C

Databricks Repos provides seamless integration with Git providers like GitHub, GitLab, and Azure DevOps. This allows users to perform standard Git operations like pull, push, and commit directly from the notebook interface. This native integration is the recommended path for managing source code versions within the Databricks ecosystem for data projects.

Why this answer

Integrating version control and collaboration is essential for professional data engineering workflows. By linking notebooks to Git providers, analysts ensure that changes are tracked, auditable, and easily revertible. This standard practice prevents accidental data loss and fosters teamwork, allowing multiple contributors to manage code repositories effectively within the Databricks environment while maintaining a single source of truth for all analytical project documentation and complex transformation logic.

Exam trap

Candidates frequently select manual file exports or third-party version control plugins, overlooking Databricks Repos and native notebook collaboration settings as the built-in enterprise solutions.

42
Multi-Selecthard

Which THREE of the following are essential components of a well-architected AI/BI Genie Space setup?

Select 3 answers
A.Detailed documentation in the Unity Catalog table/column comments.
B.Providing the end-users with direct read access to all raw source tables.
C.Well-defined system instructions within the Genie Space configuration.
D.A dedicated SQL Warehouse with sufficient compute for the expected workload.
E.Granting all users 'owner' privileges on the Genie Space.
AnswersA, C, D

LLMs rely heavily on the metadata provided in the catalog. High-quality, descriptive comments for tables and columns are the primary mechanism by which the model understands the data structure, naming conventions, and business purpose of each field, directly influencing the quality and relevance of the generated SQL queries.

Why this answer

A robust Genie Space setup relies on clear semantic metadata, optimized data access, and controlled user permissions. By defining clear table descriptions in Unity Catalog, you provide the LLM with the context needed for accurate SQL generation. Combining this with granular permissions and well-crafted system instructions ensures the solution is both secure and reliable.

These three pillars create a scalable environment that allows users to self-serve insights while maintaining strict organizational data governance.

Exam trap

Candidates often select user-facing UI features as core architectural components, failing to recognize that backend metadata, system instructions, and compute resources are essential.

43
MCQmedium

An analyst notices that a dashboard visualization is displaying an incorrect aggregation of total revenue. After reviewing the SQL, the analyst confirms the query logic is correct. What is the most likely cause of this discrepancy in the rendered visualization?

A.The SQL warehouse is currently running in 'Serverless' mode.
B.The visualization configuration in the dashboard editor has an unintended aggregation setting.
C.The Unity Catalog metastore is failing to update the table statistics.
D.The dashboard is using a legacy version of the Databricks SQL visualizer.
AnswerB

Databricks visualizers allow users to perform secondary aggregations on the query results. If the user accidentally sets the visualization to 'Average' when they intended to show a 'Sum', the chart will display incorrect totals. This is a common UI configuration error that occurs after the SQL query executes.

Why this answer

Visualizations in Databricks SQL handle data formatting and aggregation settings independently of the raw SQL output. Sometimes, the 'Plot' settings within the visualization editor define a different aggregation (like 'Average' instead of 'Sum') or apply data formatting that masks the true value. Checking these visualization-specific configurations is the final step in troubleshooting data accuracy issues within the dashboarding workflow.

Exam trap

Candidates often assume the discrepancy is due to a data quality issue or a join error in the SQL logic, overlooking that the visualization UI has its own independent aggregation settings.

44
MCQmedium

A data analyst is troubleshooting a performance issue in a notebook. The query runs slowly when processing a large table. Which approach should the analyst take to improve performance?

A.Increase the number of users connected to the workspace.
B.Run the 'OPTIMIZE' command on the Delta table.
C.Delete the table and re-create it without Delta Lake.
D.Force the notebook to run on a single-node cluster.
AnswerB

The OPTIMIZE command compacts small files into larger, more efficient files, significantly improving read performance. It is a standard procedure for data analysts to maintain high performance in Delta Lake tables. This simple action can drastically reduce I/O overhead for analytical queries scanning large volumes of data.

Why this answer

Performance tuning in Databricks often involves examining how data is stored and accessed. Identifying bottlenecks like small files or lack of partitioning is key. By using built-in optimization commands, analysts can improve query speed significantly.

This process is a core skill for any Databricks analyst, as it directly impacts the cost of compute resources and the user experience when working with massive, real-world datasets.

Exam trap

Candidates often confuse the 'OPTIMIZE' command for compaction with 'VACUUM' for old file removal or cluster-level scaling options, missing that small files directly degrade query performance on Delta tables.

45
MCQeasy

Which component of the Databricks Data Intelligence Platform allows users to discover, govern, and share data across the entire organization?

A.Databricks SQL
B.Unity Catalog
C.Delta Live Tables
D.Compute Clusters
AnswerB

Unity Catalog is the primary governance and discovery component in Databricks. It enables administrators to manage access control lists, perform auditing, and document data assets across multiple workspaces. It serves as the single source of truth for metadata, facilitating secure collaboration and compliance across the entire organizational data landscape.

Why this answer

Unity Catalog is the centralized governance solution for data, analytics, and AI on the Databricks platform. It provides a single interface to manage permissions, track data lineage, and discover assets. Mastery of Unity Catalog is essential for any data analyst as it ensures that data access is secure, compliant, and transparent, effectively bridging the gap between raw storage and meaningful, governed business insights for all users.

Exam trap

Candidates often mistake workspace folders or cloud storage buckets for platform-wide governance tools, ignoring Unity Catalog's role as the central cataloging mechanism.

46
MCQeasy

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

A.Run REFRESH TABLE before every query
B.The Delta Lake engine automatically ensures read-consistency
C.Clear the browser cache in the SQL editor
D.Execute the VACUUM command to force an update
AnswerB

Delta Lake's transaction log provides ACID guarantees, ensuring that reads are always consistent with the latest committed version of the table. The engine automatically reconciles the state of the data files with the transaction log, so analysts do not need to perform manual refreshes to view updates.

Why this answer

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

Exam trap

Candidates frequently select options involving manual cache refreshes or manual transaction log polling, not realizing that Delta Lake's ACID-compliant transaction log ensures read consistency automatically for every query execution.

47
MCQhard

A data analyst needs to grant a service principal read access to a specific external location in Unity Catalog so that a scheduled job can read data from an S3 bucket. The analyst has already created a storage credential that references an IAM role with the necessary S3 permissions. Which Unity Catalog object must the analyst grant the service principal access to, in addition to the storage credential?

A.The AWS IAM role
B.The storage credential
C.The S3 bucket policy
D.The external location
AnswerD

In Unity Catalog, an external location object combines a storage path with a storage credential. To read data from the S3 bucket, the service principal must have `READ FILES` on the external location. Granting access to the storage credential alone is insufficient; the external location is the securable object that maps the path to the credential and controls access to the data.

Why this answer

To allow a service principal to read from an external location, the analyst must grant the service principal `READ FILES` on that external location. The external location is the Unity Catalog securable that combines the cloud storage path with the storage credential, and it is the object against which privileges are granted. The storage credential itself is not granted to users.

Exam trap

The trap here is assuming that access to the storage credential is sufficient for data access, when actually the external location is the securable that must be granted.

48
MCQmedium

You are building a Databricks SQL dashboard and have a query that returns columns: order_date (DATE), region (STRING), and total_sales (DECIMAL). You want a single visualization that shows the trend of total_sales over order_date, with a separate line for each region, all on the same chart. Which visualization type should you select?

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

A line chart plots a numeric measure on the Y-axis against a time or ordered dimension on the X-axis, and when a categorical column such as region is added to the series or grouping, it draws a distinct line per region. This directly satisfies the requirement to compare regional sales trends over time on one chart.

Why this answer

The requirement is a temporal trend with one line per region, which is exactly what a line chart provides when a categorical column such as region is used as the series. The other chart types either compare static categories, show parts of a whole, or display point correlations, and none of them express continuous multi-series trends over dates.

Exam trap

The trap here is assuming any chart with a date on the X-axis will show a trend, when only a line chart connects points into continuous series per category.

49
Multi-Selecthard

Which TWO of the following are true about Databricks SQL Alerts? (Choose two)

Select 2 answers
A.Alerts can trigger external workflows using webhooks.
B.Alerts only function if the dashboard is currently open.
C.Alerts can be based on any SQL query result.
D.Alerts automatically delete data that triggers the condition.
E.Alerts are only compatible with Python-based notebooks.
AnswersA, C

Databricks SQL alerts can be configured to send notifications to external systems via webhooks. This is a powerful feature that allows the platform to integrate with other tools like Slack, PagerDuty, or custom internal applications, enabling automated responses to data-driven events directly from the Databricks environment.

Why this answer

Alerts in Databricks SQL are a critical component of a proactive monitoring strategy. By tracking the results of a query, they provide timely notifications that help analysts and business users respond to events immediately. Understanding the configuration of thresholds and notification channels is essential for building a reliable operational dashboard suite that moves beyond passive data reporting to active, event-driven data management.

Exam trap

Candidates often assume alerts can only send notifications internally or are restricted to dashboard canvases, forgetting they support external integrations like webhooks for workflow automation.

50
MCQhard

An analyst is configuring an AI/BI Genie Space and wants to ensure that the model correctly interprets the term 'revenue' as the sum of the 'sales_amount' column. Which action should the analyst take?

A.Create a view that renames 'sales_amount' to 'revenue' and add that view to the Genie Space.
B.Add a column comment to the 'sales_amount' column in Unity Catalog that says 'This is revenue'.
C.In the Genie Space instructions, write a note that says 'revenue means sales_amount'.
D.Add a synonym 'revenue' to the 'sales_amount' column in the Genie Space's semantic configuration.
AnswerD

Adding a synonym maps the business term 'revenue' to the specific column 'sales_amount'. This directly informs the Genie model that when users ask about revenue, it should use that column. The synonym feature is part of the semantic layer that helps translate natural language into accurate SQL, reducing ambiguity and improving answer correctness.

Why this answer

The correct approach is to add a synonym in the Genie Space's semantic configuration. This feature allows analysts to map business terms to specific columns, ensuring the model understands that 'revenue' refers to 'sales_amount'. Synonyms are part of the Space's semantic layer and are used during natural language to SQL translation, leading to more accurate and consistent results.

Exam trap

The trap here is confusing metadata comments or free-text instructions with the dedicated synonym feature that directly maps terms to columns.

51
MCQmedium

A data analyst notices that a query involving a join between a large table and a small lookup table is performing poorly. The analyst wants to optimize the join performance without changing the underlying data. Which technique should they apply?

A.Add an explicit BROADCAST hint to the small table in the JOIN clause.
B.Increase the number of shuffle partitions to 2000.
C.Convert the large table to a Delta table with Z-Ordering.
D.Use the MERGE INTO statement instead of a standard JOIN.
AnswerA

The BROADCAST hint explicitly instructs the Spark catalyst optimizer to perform a broadcast hash join. This eliminates the need for a shuffle exchange, which is the most expensive part of a join, by duplicating the smaller dataset across all executor nodes for local lookup operations.

Why this answer

Broadcasting the small table forces the cluster to send the entire lookup table to every node in the cluster, avoiding a full shuffle of the large table. This technique significantly reduces network overhead during joins when one side fits in memory. Mastering join strategies is essential for analysts to write efficient SQL that minimizes cluster resource consumption and shortens execution times on large datasets.

Exam trap

Candidates often rely on the Catalyst optimizer to automatically broadcast every small table, failing to manually add explicit broadcast hints when needed.

52
Multi-Selectmedium

A data analyst is reviewing a Databricks SQL query that runs slowly and opens the Query Profile. The analyst wants to identify whether an individual task is disproportionately slow compared with its peers, indicating skew. Which TWO areas of the Query Profile should the analyst examine? (Choose two.)

Select 2 answers
A.The version of the Databricks Runtime used by the warehouse
B.The number of rows and bytes written by each shuffle partition
C.The SQL warehouse cluster size configured for the query
D.The total number of stages in the physical plan
E.The duration distribution of tasks within a single stage
AnswersB, E

Per-partition shuffle write sizes reveal imbalance directly: if one partition receives vastly more bytes or rows than its peers, the downstream task processing it will run long. Comparing these counts across partitions confirms skew at the shuffle boundary and points to the join or grouping key that is unevenly distributed.

Why this answer

Skew manifests as an individual task running far longer than its peers because one partition holds disproportionate data. Task duration distribution within a stage exposes the straggler, while per-partition shuffle write rows and bytes show the imbalanced partition that causes it. Together these two views confirm skew rather than a general resource shortage.

Exam trap

The trap here is blaming cluster size for a slow stage, when skew is a data distribution problem that adding executors cannot solve.

53
MCQhard

Refer to the exhibit. This IAM policy is attached to a Storage Credential. A user tries to run 'DROP TABLE' on a table stored in this bucket and fails. Why?

A.The user lacks the 'DROP' privilege in Unity Catalog.
B.The IAM policy does not include 's3:DeleteObject'.
C.The bucket is marked as 'Read Only' in Unity Catalog.
D.The table is owned by the metastore admin.
AnswerB

To successfully drop a table, Databricks must be able to delete the underlying objects in S3. The provided IAM policy only contains read-level permissions ('GetObject', 'ListBucket'). Without the 'DeleteObject' permission, the cloud provider will reject the request, preventing Databricks from completing the deletion process, regardless of internal permissions.

Why this answer

The IAM policy only grants 's3:GetObject' and 's3:ListBucket', which are read-only permissions. The 'DROP TABLE' command in Databricks requires 's3:DeleteObject' permission on the underlying data to remove the files from S3. Because the policy lacks delete permissions, the operation is blocked by the cloud provider, even if the user has the 'DROP' privilege in Databricks.

Exam trap

Test-takers assume Databricks SQL permissions override cloud IAM restrictions, forgetting that cloud-level missing delete policies will block operations.

54
MCQeasy

A business analyst is using an AI/BI Genie Space to explore customer data. They ask, 'What is the average customer age?' The Genie Space returns a result. Which underlying Databricks component does Genie use to execute the generated SQL query?

A.Databricks SQL warehouse
B.Apache Spark cluster
C.Delta Live Tables pipeline
D.Databricks notebook
AnswerA

Genie Spaces use Databricks SQL warehouses to execute the SQL queries generated from natural language. The SQL warehouse provides the compute resources and is optimized for BI workloads. When a user asks a question, Genie translates it into SQL and runs it on the attached SQL warehouse, returning results quickly and efficiently.

Why this answer

AI/BI Genie Spaces rely on Databricks SQL warehouses to execute the SQL queries generated from natural language. The SQL warehouse provides the necessary compute and is optimized for BI workloads, ensuring fast and reliable results. Other components like Spark clusters or notebooks are not used for this purpose.

Exam trap

The trap here is assuming that Genie uses a general-purpose Spark cluster or notebooks, when it specifically leverages SQL warehouses for query execution.

55
MCQmedium

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

A.Delta Lake Time Travel (VERSION AS OF / TIMESTAMP AS OF).
B.Materialized Views.
C.Setting the table to 'Read-Only' during reports.
D.Database snapshots created via manual backups.
AnswerA

Time Travel allows queries to access snapshots of data from specific versions or points in time. This ensures report consistency even when background processes are appending or modifying data, providing a stable, reproducible source for analytical reporting that is essential for maintaining data accuracy over long reporting cycles.

Why this answer

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

Exam trap

Candidates often suggest using temporary views or caching, which do not provide historical data consistency, instead of the native Delta Lake feature designed specifically for historical data access.

56
MCQeasy

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

A.Use the Add Data UI to upload the file and create the table through the guided wizard.
B.Run COPY INTO against the local filesystem path of the downloaded file.
C.Configure an Auto Loader stream pointing at a cloud storage location to ingest the file.
D.Create an external table over the local file so the data stays in place.
AnswerA

The Add Data UI is designed for exactly this case: small local files uploaded through the browser, with a wizard that previews the data, lets the analyst set the delimiter and header options, and creates a Unity Catalog table without writing code. It is the fastest low-configuration path for a one-time small-file load.

Why this answer

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

Exam trap

The trap here is assuming that any ingestion method can read a file sitting on a local laptop, when most Databricks ingestion paths require cloud storage paths.

57
MCQmedium

A data analyst is reviewing audit logs in Databricks to investigate unauthorized access attempts to a sensitive table. Which audit log event type should the analyst filter on to find failed attempts to query the table?

A.`login`
B.`tableAccess`
C.`permissionDenied`
D.`sqlQuery`
AnswerC

`permissionDenied` events are generated when a user attempts an action but lacks the necessary privileges. These events capture failed attempts to access tables, including queries that are denied due to insufficient permissions. Filtering on `permissionDenied` allows the analyst to identify unauthorized access attempts. This is the correct event type for investigating security violations.

Why this answer

To investigate unauthorized access attempts, the analyst should filter audit logs for `permissionDenied` events. These events are generated when a user lacks the required privileges for an action, such as querying a table. Other event types like `tableAccess` or `sqlQuery` focus on successful operations or query details, not permission failures. `login` events are about authentication, not table access.

Exam trap

The trap here is assuming that `tableAccess` logs include failed attempts, when in fact `tableAccess` only records successful accesses; failures are logged as `permissionDenied`.

58
MCQmedium

A data analyst has a Unity Catalog table `main.finance.payroll` that contains a column `ssn` with sensitive data. The analyst wants to allow the HR team to query the table but mask the `ssn` column so that only users in the `hr_admins` group see the actual values; all other users should see `***` instead. Which Unity Catalog feature should the analyst use to achieve this?

A.Column-level masking using a user-defined function (UDF) applied via `ALTER TABLE ... ALTER COLUMN ... SET MASK`.
B.Table ACLs granting SELECT only to `hr_admins` and denying SELECT to others.
C.Dynamic view that selects all columns except `ssn` for non-admin users.
D.Row-level security using a filter expression on the table.
AnswerA

Unity Catalog supports column masks that invoke a user-defined function to transform the column value at query time. The mask function can check the user's group membership and return the original value for members of `hr_admins` and a redacted value for others. This directly meets the requirement of conditional masking based on group membership.

Why this answer

Column masks in Unity Catalog allow conditional redaction of sensitive column values based on the querying user's identity or group memberships. By applying a mask function to the `ssn` column, the analyst can ensure that only members of `hr_admins` see the real values, while others see a masked placeholder. This satisfies the requirement without changing how users query the table.

Exam trap

The trap here is confusing column masking with row-level security, which filters rows rather than altering column values.

59
MCQmedium

An analyst notices that the AI/BI Genie Space is generating queries that take too long to execute. The underlying data is stored in partitioned tables. What is the most effective way to help the Genie generate more performant SQL?

A.Instruct the model to use 'SELECT *' to get all data at once.
B.Modify the system instructions to require filtering on partition keys.
C.Increase the SQL Warehouse cluster size to handle larger queries.
D.Use a different LLM model within the Genie settings.
AnswerB

Explicitly instructing the model to filter by partition keys (like date or region) ensures that every query generated by the AI is optimized for performance. By guiding the model to narrow the scope of the data scan, you prevent full table scans and reduce the load on the warehouse.

Why this answer

Genie models are highly responsive to context. By adding explicit instructions about filtering on partition columns, such as 'always filter by date or region,' the model learns to include these constraints in its generated SQL. This ensures that the generated queries prune data partitions effectively, significantly reducing query runtime and resource consumption on the SQL Warehouse, which directly improves the end-user experience for all business users.

Exam trap

Candidates frequently think that rebuilding the underlying physical tables or adding new indexes is required, ignoring the power of system instructions for guiding LLM query generation.

60
MCQeasy

A data analyst needs to perform a one-time upload of a 15 MB compressed Parquet file from a local laptop into a Unity Catalog volume in a Databricks workspace. The analyst has the Databricks SQL editor open and no cluster running. Which approach should the analyst take to place the file into the volume path?

A.Navigate to the volume in Catalog Explorer, use the Upload to volume action, and select the local Parquet file to transfer it into the volume path.
B.Run COPY INTO from the SQL editor, referencing the local file path as the source and the volume path as the target.
C.Use dbfs cp from a terminal with the Databricks CLI to copy the file into /Volumes/catalog/schema/volume/path.
D.Use the Catalog Explorer's Create table UI to upload the file, selecting the target volume as the destination and the Parquet format.
AnswerA

Catalog Explorer exposes an Upload to volume action on Unity Catalog volumes that transfers local files directly into the volume directory without requiring a running cluster. This is the supported, low-friction path for small one-time uploads and places the file exactly where downstream SQL or notebook code can read it by its volume path.

Why this answer

Unity Catalog volumes are designed to hold non-tabular files, and Catalog Explorer provides an Upload to volume action that pushes a local file directly into the chosen volume directory. This avoids spinning up a cluster and does not require the SQL warehouse, making it the appropriate tool for a small, one-time upload from a laptop into a governed storage location.

Exam trap

The trap here is reaching for cluster-based or DBFS-based tooling such as COPY INTO or dbfs cp when a simple UI upload into a Unity Catalog volume is both supported and sufficient.

61
MCQhard

Refer to the exhibit. A data scientist can query both tables, but the join fails with a permission error. What is the most likely reason?

A.The user lacks SELECT access on the 'prod' catalog.
B.The user lacks USAGE on the 'default' schema.
C.The join condition requires elevated privileges.
D.The tables are in different catalogs.
AnswerB

Unity Catalog requires USAGE privileges on all containers in the hierarchy, including the schema. If the 'data_scientists' group does not have the USAGE privilege on the 'default' schema, they cannot resolve the tables for the join, even if they have individual SELECT access to the tables within that same schema.

Why this answer

In Unity Catalog, the user must have the USAGE privilege on every schema involved in a query, even if they have SELECT access on the tables within those schemas. If the 'default' schema lacks the USAGE grant for the group, the query engine cannot validate the path to the data, causing the join to fail at the permission check level despite table-level SELECT access.

Exam trap

Candidates assume having SELECT on the tables in a join is sufficient, forgetting that USAGE is required on every participating schema.

62
MCQeasy

What is the primary function of the 'Databricks Assistant' within the notebook environment?

A.To automatically execute all notebooks in a production pipeline.
B.To assist in writing, debugging, and explaining notebook code.
C.To manage user permissions across the entire workspace.
D.To store and back up notebook versions automatically.
AnswerB

The Databricks Assistant is an AI-integrated tool that provides suggestions, debugging help, and code explanations directly within the notebook. It enhances developer productivity by accelerating the coding process and providing immediate support for syntax-related issues, making it a powerful tool for analysts at all experience levels to write efficient code.

Why this answer

The Databricks Assistant is an AI-powered coding companion designed to help users write, debug, and optimize code faster. By leveraging context-aware suggestions, it helps analysts become more efficient. Understanding how to use the Assistant is beneficial for streamlining development workflows, as it assists with complex syntax, troubleshooting common errors, and generating boilerplate code, allowing the analyst to focus on the core logic of their data analysis tasks.

Exam trap

Candidates often mistakenly believe the Assistant is for data visualization or automated dashboard generation, failing to recognize its core role as a coding companion for writing, debugging, and explaining code.

63
Multi-Selectmedium

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

Select 2 answers
A.They must be manually started every time a user runs a query.
B.They are specifically optimized for SQL-based analytical queries.
C.They support the same multi-language notebook features as all-purpose clusters.
D.They can automatically scale to handle concurrent query demand.
E.They are only available for administrative users.
AnswersB, D

SQL Warehouses are built specifically to provide high-performance SQL execution, serving as the compute engine for Databricks SQL. They include optimizations such as query result caching and specialized query planning, making them vastly superior to general-purpose clusters for structured SQL queries and interactive BI reporting tasks.

Why this answer

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

Exam trap

Candidates often mistakenly believe SQL Warehouses are general-purpose compute resources, failing to understand they are specialized for SQL analytics and lack the flexibility of general-purpose clusters for Python or Scala code.

64
Multi-Selecthard

Which TWO of the following are valid ways to improve the performance of a slow-loading Databricks SQL dashboard? (Choose two)

Select 2 answers
A.Convert all visualizations into a single complex query.
B.Enable query result caching on the SQL warehouse.
C.Add more users to the dashboard to increase processing power.
D.Optimize the underlying SQL queries using filters and aggregates.
E.Switch the dashboard to manual refresh mode only.
AnswersB, D

Query result caching stores the results of queries so that subsequent executions of the same query retrieve the data directly from the cache. This bypasses the need for the warehouse to re-read and re-process the underlying data, resulting in near-instant load times for repeated dashboard views.

Why this answer

Improving dashboard performance is critical for user adoption. By utilizing query result caching and optimizing the underlying SQL queries, analysts can significantly reduce latency. Caching stores the output of frequent queries, while query optimization ensures that the compute resources are used efficiently.

These techniques are standard operational practices that ensure dashboard responsiveness, even when dealing with massive datasets common in modern data lakehouses.

Exam trap

Candidates often rely solely on upgrading cluster hardware instead of implementing built-in caching and query-level optimizations.

65
MCQmedium

A data analyst has a Databricks SQL query that joins a large fact table to a small dimension table. The query is slow because the dimension table is being shuffled across the cluster. The analyst wants to avoid this shuffle and improve performance. Which SQL hint should they use?

A.REPARTITION
B.SHUFFLEMERGE
C.BROADCAST
D.MERGE
AnswerC

BROADCAST is a Databricks SQL hint that instructs the optimizer to broadcast the smaller table to all nodes, eliminating the shuffle of the larger table. In this scenario, the dimension table is small, so broadcasting it avoids the expensive shuffle and speeds up the join. This is the correct hint to use.

Why this answer

The BROADCAST hint tells the optimizer to send the smaller table to all worker nodes, allowing the join to occur locally without shuffling the larger table. This reduces network overhead and speeds up the query. The other options are either not hints or would increase shuffling.

Exam trap

The trap here is confusing join strategy hints with other SQL operations like MERGE or REPARTITION, which do not address broadcast joins.

66
MCQmedium

An analytics team is building an AI/BI Genie space to allow business users to query sales data using natural language. After setting up the base tables, the initial user questions return inaccurate filter values because the LLM struggles to map colloquial region names to the exact string codes stored in the database. What is the most effective feature within AI/BI Genie to resolve this mapping issue without modifying the underlying physical tables?

A.Create a materialized view containing hardcoded string mappings for every possible regional variation queried by business users.
B.Enable automatic schema inference and let the Genie space rebuild its internal vector embeddings from scratch overnight.
C.Configure table and column descriptions, add explicit instructions, and provide verified queries demonstrating the correct region mappings.
D.Modify the source table column constraints to reject any query that does not use the exact database region code.
AnswerC

Detailed table documentation, clear spatial instructions, and few-shot examples via verified queries provide the LLM with exact patterns to follow. This improves translation accuracy for ambiguous colloquialisms and ensures reliable SQL generation for business users.

Why this answer

Adding instructions, descriptions, and verified queries to the Genie space gives the underlying model precise semantic context about colloquial terms, business definitions, and expected filters. This targeted guidance steers natural language translation toward the correct database columns and values without requiring costly data transformations or restructuring of source tables in the data lakehouse.

Exam trap

Candidates often assume they must perform complex ETL or data cleaning to fix mapping issues, failing to realize that Genie spaces can be guided using semantic metadata and verified query examples.

67
MCQmedium

A data analyst needs to perform ad-hoc SQL queries on a large dataset while ensuring the compute resources automatically terminate when idle to minimize costs. Which compute resource is most appropriate for this task?

A.Job Compute
B.SQL Warehouse
C.All-Purpose Compute
D.Delta Live Tables Pipeline
AnswerB

SQL Warehouses offer managed, auto-scaling compute resources tailored for SQL analytics. They provide serverless or classic scaling options with built-in auto-stop features that terminate idle resources, ensuring cost-efficiency. This aligns perfectly with the analyst's requirement to perform ad-hoc SQL queries while maintaining strict control over compute costs during periods of inactivity.

Why this answer

SQL Warehouses are designed specifically for SQL-based workloads, supporting BI tools and ad-hoc analysis through the SQL editor. They feature auto-stop functionality, which shuts down the cluster after a specified period of inactivity, directly addressing the cost-optimization requirement. Understanding the distinction between SQL Warehouses and All-Purpose clusters is vital for analysts, as Warehouses provide optimized performance for SQL queries and efficient resource management for collaborative reporting environments.

Exam trap

Candidates frequently confuse All-Purpose clusters with SQL Warehouses, failing to recognize that SQL Warehouses are specifically optimized for ad-hoc SQL queries and auto-stop cost management.

68
MCQeasy

When analyzing the execution plan of a query in Databricks SQL, what does a 'BroadcastHashJoin' node typically indicate?

A.The query is performing a cross join between two large tables.
B.The optimizer successfully avoided a network shuffle by sending a small table to all executors.
C.The query is failing because the join keys are not indexed.
D.The data is being read from multiple external storage locations simultaneously.
AnswerB

This join strategy is chosen when one side of the join is small enough to fit into memory on each worker node. By broadcasting this table, the engine eliminates the need to redistribute the larger table across the network, resulting in much faster execution and reduced cluster load.

Why this answer

A BroadcastHashJoin indicates that the query optimizer determined one table was small enough to be duplicated across all nodes. This avoids expensive shuffles by performing the join locally on each partition. Understanding execution plans allows analysts to verify if their queries are executing as intended and helps identify opportunities to apply hints or optimize join operations for better performance on large-scale distributed data processing tasks.

Exam trap

Candidates frequently mistake a BroadcastHashJoin for an indicator of data skew or network bottlenecks, when it actually represents an optimized join strategy.

69
MCQeasy

An analyst is preparing to upload a small local CSV file to a Databricks workspace. Which tool is most appropriate for a quick, one-time upload without requiring infrastructure setup?

A.Auto Loader
B.Databricks Add Data UI
C.COPY INTO command
D.Databricks Connect
AnswerB

The Add Data UI allows users to drag and drop files from a local computer directly into the workspace. It creates a managed table or file path in DBFS, making it the most efficient and straightforward method for a small, one-time upload requirement by a data analyst.

Why this answer

The 'Add Data' UI in Databricks provides a user-friendly interface to upload small files directly from a local machine to DBFS. This approach is ideal for exploratory analysis or small datasets where setting up external storage or complex ingestion pipelines is unnecessary. Understanding this tool allows analysts to quickly pivot from local analysis to platform-based workflows without needing deep knowledge of cloud storage configurations.

Exam trap

Candidates often overcomplicate the solution by suggesting external cloud storage ingestion or CLI tools, failing to recognize the simplicity of the 'Add Data' UI for one-time, small-scale local file uploads.

70
MCQeasy

Which of the following is the primary purpose of examining the 'Query Profile' in Databricks SQL?

A.To change the permissions of the underlying table.
B.To visualize the execution steps and identify bottlenecks.
C.To automatically rewrite SQL for better performance.
D.To view the raw logs of the cluster driver.
AnswerB

The Query Profile provides a graphical representation of the physical query plan. It allows analysts to see the duration of individual operators, data volumes, and task metrics, making it easier to pinpoint specific stages that are causing slow execution or high resource utilization within a complex SQL statement.

Why this answer

The Query Profile is the central diagnostic tool for Databricks SQL. It provides a visual representation of how a query was executed, including the time spent in each stage and where bottlenecks occur. Mastering this tool is vital for data analysts because it transforms abstract performance issues into actionable insights, enabling them to reduce query latency, optimize costs, and effectively communicate performance bottlenecks to data engineers for backend architecture improvements.

Exam trap

Candidates often think the Query Profile is only for viewing the final output or query results, missing its primary value as a visual diagnostic tool for identifying execution bottlenecks.

71
MCQmedium

A data analyst is building a Databricks SQL dashboard that includes a line chart of daily active users over the past year. The stakeholder wants to hover over the line to see the exact date and user count for each point. The analyst notices that the tooltip currently shows only the user count and not the date. What should the analyst do to include the date in the tooltip?

A.Enable the dashboard-level option to show all fields in tooltips, which automatically includes every column returned by the query.
B.Modify the query to concatenate the date and user count into a single string column and use that as the measure in the line chart.
C.Change the visualization type from line chart to a table so that all columns, including the date, are visible without hovering.
D.Add the date column to the visualization's tooltip encoding or ensure it is included as a field in the chart's data so the hover tooltip can display it.
AnswerD

Tooltips in Databricks SQL visualizations display fields that are included in the chart's encoding or data. If the date is only used as the x-axis but not exposed to the tooltip, the hover may show only the measure. Adding the date to the tooltip encoding or ensuring it is part of the chart's fields lets the tooltip render both the date and the user count as the stakeholder requested.

Why this answer

Tooltips reflect the fields encoded in the visualization. If the date is used only for the x-axis and not exposed to the tooltip, hovering shows only the measure. Including the date in the tooltip encoding or ensuring it is part of the chart's fields makes the hover display both the date and the user count, preserving the line chart while meeting the stakeholder's requirement.

Exam trap

The trap here is assuming a dashboard-wide setting can inject all query columns into tooltips, when tooltip content is governed by the visualization's field encodings.

72
MCQhard

Refer to the exhibit. A data analyst attempts to run a query in the SQL editor but receives the error displayed. Which action should the administrator take to resolve this?

A.Restart the SQL Warehouse to refresh the user's session token.
B.Grant the user the 'CAN_USE' permission on the specific SQL Warehouse.
C.Upgrade the user's workspace role to 'Workspace Admin'.
D.Increase the maximum number of clusters in the SQL Warehouse configuration.
AnswerB

The 'CAN_USE' permission is explicitly required for users to execute queries on a SQL Warehouse. By granting this permission in the workspace settings, the administrator enables the user's identity to authenticate with the compute resource, resolving the 403 Forbidden error and allowing the analyst to proceed with their work.

Why this answer

The error indicates a lack of the necessary access control privilege for the specific SQL Warehouse. In Databricks, permissions are managed via Access Control Lists (ACLs). The analyst requires the 'CAN_USE' permission to attach to and execute queries on a warehouse.

Administrators must update the warehouse's permission settings in the Databricks workspace to grant this access level to the user or their associated group.

Exam trap

Candidates often check table-level permissions or cluster instance types instead of inspecting the specific 'CAN_USE' access control list for the SQL Warehouse.

73
MCQeasy

Which Databricks SQL object allows an analyst to save a specific query result set and share it with other users, ensuring they see a snapshot of the data at the time of execution?

A.Dashboard
B.Query
C.Alert
D.SQL Warehouse
AnswerB

A query object allows users to write, execute, save, and share SQL code. When shared, others can view the query and the associated result set. This is the fundamental building block for sharing analytical insights and ensuring that team members have access to the same logic and outputs.

Why this answer

In Databricks SQL, a Query is the primary object for executing SQL statements. When a user runs a query, the results are stored in the results cache. By sharing the query, other users can access the saved results or re-run the query to obtain the latest data.

Understanding how to manage and share query objects is essential for collaborative data analysis and maintaining consistent reporting across organizational teams.

Exam trap

Candidates often confuse 'Query' objects with 'Dashboards' or 'Alerts', failing to realize that a saved query object is the fundamental unit for sharing specific result sets in SQL.

74
MCQmedium

A data analyst has a Delta table silver.customers in Unity Catalog. A new privacy requirement states that analysts must never see the raw email column, but they still need to query all other columns. The analyst has CREATE VIEW and SELECT privileges on the schema. Which approach best enforces the requirement while keeping the table queryable?

A.Add a row filter to the table so rows containing emails are excluded for analysts, and keep base-table SELECT granted.
B.Create a view that selects all columns except email, grant SELECT on the view to the analysts, and revoke SELECT on the base table.
C.Rename the email column to a non-obvious name and rely on security by obscurity to hide it from analysts.
D.Apply a column mask on the email column using a masking function, and leave SELECT on the base table in place.
AnswerB

A view that projects only the non-sensitive columns, combined with revoking direct table access, gives analysts the data they need while preventing them from reading the email column. Because Unity Catalog views can run with the definer's privileges, analysts do not need base-table SELECT, so this cleanly enforces the privacy requirement.

Why this answer

Projecting only the safe columns into a view and revoking direct SELECT on the base table is the canonical least-privilege pattern in Unity Catalog. Analysts query the view, which returns every column except email, while the raw table remains inaccessible. Column masks or row filters do not remove the column from query results in the same unambiguous way.

Exam trap

The trap here is thinking that a column mask or row filter removes a column from visibility, when those features alter values or rows rather than hiding the column entirely.

75
MCQmedium

A data analyst has built a Databricks SQL dashboard with a bar chart that shows total revenue by region. The dashboard has a parameter named `region_param` that allows the viewer to select one or more regions. The analyst wants the bar chart to update automatically whenever the viewer changes the selected regions, without requiring a manual refresh. What should the analyst do?

A.Create a separate query for each region and add them as individual visualizations to the dashboard.
B.Use a dashboard-level filter that applies to all visualizations and set the parameter to a fixed value.
C.Add a filter widget to the dashboard and link it to the parameter.
D.Reference the parameter in the query using the `{{ region_param }}` syntax and ensure the parameter is added to the dashboard.
AnswerD

Databricks SQL parameters are referenced in queries with the `{{ parameter_name }}` syntax. When the parameter is added to the dashboard, changing its value re-executes the query and updates all visualizations that depend on it. This is the correct way to make the bar chart update automatically based on the viewer's selection.

Why this answer

Parameters in Databricks SQL allow dynamic values to be passed into queries. By referencing the parameter with double curly braces in the query and adding it to the dashboard, the visualization will automatically update when the parameter value changes. This is the standard mechanism for interactive dashboards in Databricks SQL.

Exam trap

The trap here is confusing dashboard filters with parameters, assuming that a filter widget alone can drive dynamic query updates.

Page 1 of 4

Page 2

All pages