Courseiva

Databricks Certified Data Analyst Associate (Databricks-DA-Assoc) — Questions 76–150

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

Page 1

Page 2 of 4

Page 3
76
Multi-Selectmedium

Which TWO of the following are best practices for writing instructions in an AI/BI Genie space?

Select 2 answers
A.Provide clear, concise business logic definitions
B.Include examples of expected natural language questions
C.Include historical SQL queries for every possible outcome
D.Include sensitive security credentials for authentication
E.Use long-form essay style descriptions for all tables
AnswersA, B

Concise business logic helps the model understand precisely how metrics should be calculated. When rules are ambiguous, the model may guess, leading to inaccurate data. Providing clean definitions ensures consistency in reporting and reduces the need for users to verify the math behind the AI's answers.

Why this answer

Effective Genie instructions are concise, specific, and structured. They should prioritize clarity to prevent ambiguity and ensure the model consistently follows business rules. By focusing on explicit examples and avoiding irrelevant information, developers help the LLM maintain high accuracy and reduce the risk of hallucination, which is vital for building trust with business users who rely on the provided insights.

Exam trap

Candidates often assume writing vague, conversational prose or extremely lengthy descriptions works best, overlooking the need for concise logic and concrete examples.

77
MCQmedium

Which audit log category is most useful for identifying unauthorized attempts to access sensitive tables?

A.Cluster-level Spark logs.
B.Unity Catalog audit logs.
C.Job run output logs.
D.Workspace-level login logs.
AnswerB

Unity Catalog audit logs provide a comprehensive record of all metadata and data access events. These logs capture who requested data, which table they accessed, and whether the request was granted or denied. This makes them the primary source for security investigations and auditing of data access activities within the workspace.

Why this answer

The 'Unity Catalog' audit log category records all actions performed within the metastore, including access attempts. By monitoring this log, security analysts can identify unauthorized 'SELECT' or 'DESCRIBE' attempts that failed due to permission denials. This is critical for detecting potential internal threats or misconfigured access policies that could lead to unauthorized data exposure.

Exam trap

Test-takers often choose generic cluster or workspace logs instead of identifying Unity Catalog audit logs, which specifically track data access and permission denials.

78
MCQeasy

Who is the primary intended audience for an AI/BI Genie space in a Databricks environment?

A.Data engineers building ETL pipelines
B.Business analysts and non-technical stakeholders
C.Machine Learning engineers for model training
D.System administrators for managing clusters
AnswerB

Genie is built to empower non-technical users to query data using natural language. It removes the technical barrier to entry, allowing business users to get insights quickly and independently, which is the primary value proposition of the tool for the enterprise-wide democratization of data.

Why this answer

Genie spaces are specifically designed to bridge the gap between technical data infrastructure and business users. By providing a natural language interface, they allow non-technical stakeholders to ask questions and explore data without needing to write SQL or understand complex data schemas. This democratizes access to data and reduces dependency on data teams for basic reporting.

Exam trap

Candidates often think AI/BI Genie is built exclusively for data engineers to write complex ETL code, forgetting it targets business users.

79
MCQmedium

A Genie Space is producing inaccurate results when asked about 'Customer Lifetime Value'. The analyst realizes the AI model is misinterpreting the calculation logic. What is the most effective way to improve the AI's accuracy for this specific business term?

A.Modify the underlying Delta table data to include a pre-computed column for every possible metric.
B.Update the Genie Space instructions with clear, natural language definitions for the calculation.
C.Restrict access to the Genie Space so only the senior data architect can run queries.
D.Rename all columns in the Unity Catalog table to match the exact wording of the user's questions.
AnswerB

The instructions field acts as a system prompt, guiding the Genie model on how to correctly interpret domain-specific terminology. By providing clear definitions, you reduce ambiguity, ensuring the AI generates SQL that aligns with the organization's standardized business metrics, which significantly improves the reliability of the generated insights.

Why this answer

Providing clear instructions in the Genie Space instructions field allows developers to define business-specific logic that the AI might not inherently understand. By embedding these definitions directly into the Genie configuration, you constrain the AI's query generation process to adhere to pre-approved business formulas. This is essential for ensuring that multiple users get consistent answers for complex metrics, regardless of how they phrase their natural language questions.

Exam trap

Candidates often attempt to retrain or fine-tune the underlying LLM model. They overlook that Genie Spaces are designed to be guided by explicit instructions rather than requiring low-level model adjustments.

80
MCQmedium

When designing an AI/BI Genie Space, how can an analyst prevent the model from accessing PII (Personally Identifiable Information) columns in a sensitive table?

A.Add a disclaimer in the system instructions to 'not use PII'.
B.Use a specialized, private LLM that has no knowledge of PII.
C.Create a filtered view in Unity Catalog that excludes sensitive columns.
D.Set the temperature to 0 to minimize hallucinations involving PII.
AnswerC

This is the most secure approach. By creating a view that explicitly selects only the non-sensitive columns, you guarantee that the LLM cannot 'see' or include PII in its SQL generation. This fulfills data governance requirements and protects sensitive information regardless of what the user asks the model.

Why this answer

The best approach to prevent PII exposure is to create a secure view in Unity Catalog that excludes the PII columns and point the Genie Space to that view instead of the raw table. This effectively enforces data masking and access control at the data layer. By only exposing the non-sensitive data required for analysis, you maintain privacy compliance while still allowing the AI to generate useful insights for the business.

Exam trap

Candidates mistakenly believe they can hide PII columns using Genie system instructions, ignoring the fact that prompt instructions can be bypassed or fail at the data layer.

81
MCQhard

A data analyst writes a complex Spark SQL query involving multiple window functions, CTEs, and aggregations. To debug the query performance, the analyst wants to inspect the logical optimization phases applied by the Catalyst optimizer. Which SQL command can the analyst use to view the unoptimized logical plan, optimized logical plan, and physical execution plan?

A.DESCRIBE EXTENDED table_name;
B.SHOW QUERY PLAN SELECT ...;
C.EXPLAIN EXTENDED SELECT ...;
D.ANALYZE TABLE table_name COMPUTE STATISTICS;
AnswerC

EXPLAIN EXTENDED prints every stage Catalyst produces: the parsed logical plan, the analysed logical plan with names resolved, the optimised logical plan after rules such as predicate pushdown and column pruning, and the physical plan with chosen operators such as join strategies. With adaptive query execution, the final plan can change at runtime, so the Databricks query profile shows what actually ran.

Why this answer

Spark SQL provides the EXPLAIN command to inspect the underlying plans generated by the Catalyst optimizer. By specifying the EXTENDED or FORMATTED modifier, analysts can review parsed logical plans, analyzed logical plans, optimized logical plans, and final physical execution plans.

Exam trap

Candidates often select the 'EXPLAIN' command alone, failing to realize that the basic command only provides the physical plan, omitting the critical logical optimization phases required for debugging complex queries.

82
MCQmedium

What is the primary function of the 'VACUUM' command in Databricks SQL data modeling?

A.To shrink the transaction log file size.
B.To permanently remove files no longer referenced by the table state.
C.To re-index the table for faster performance.
D.To force a merge of all Delta logs into a single Parquet file.
AnswerB

VACUUM identifies files that are no longer part of the current version of the Delta table (or within the retention threshold) and deletes them from cloud storage. This is the primary mechanism for reclaiming storage space and ensuring the storage directory only contains necessary, active data files for the table.

Why this answer

The VACUUM command is essential for storage management and compliance. It removes old, unreferenced data files that are no longer part of the current table state beyond a specified retention period. This is vital for managing cloud storage costs and ensuring that data is physically purged, which helps companies meet regulatory requirements like GDPR by permanently deleting sensitive data that is no longer needed in the historical logs.

Exam trap

Candidates often mistakenly believe VACUUM is required to improve query performance. While it deletes files, its primary purpose is storage management and compliance, not immediate query speed optimization.

83
MCQmedium

Refer to the exhibit. The execution plan shows a SortMergeJoin with two Exchange nodes. What is the primary cause of the performance impact here?

A.The join keys are not sorted, forcing a full scan of both tables.
B.The tables are being re-shuffled to align rows on the join key, causing network overhead.
C.The optimizer is unable to choose a broadcast join because both tables are too large.
D.The cluster is running out of memory due to the high number of partitions.
AnswerB

Exchange nodes in a Spark execution plan represent a shuffle, which involves moving data over the network to ensure that rows with the same join key are processed on the same node. This is a heavy operation that occurs when tables are not already partitioned by the join column.

Why this answer

The presence of two Exchange nodes indicates that the data is being repartitioned across the cluster to align join keys on the same worker nodes. This full shuffle is costly in terms of network I/O. Recognizing this pattern helps analysts understand why large joins can be slow and justifies investigating whether tables can be pre-partitioned to avoid these runtime exchanges.

Exam trap

Candidates often misinterpret Exchange nodes in execution plans as caching layers rather than recognizing them as heavy network shuffles during sort-merge joins.

84
MCQmedium

Which of the following describes the purpose of 'Data Skipping' in Databricks SQL when querying Delta tables?

A.It deletes old data files to save storage space
B.It uses min/max statistics to skip irrelevant files
C.It caches frequently used tables in the browser
D.It allows queries to run without reading any files
AnswerB

Data Skipping leverages min/max statistics stored in the Delta transaction log to identify which files contain data that falls outside the range of the query's filters. Files that cannot possibly contain matching records are skipped, reducing the total I/O load and dramatically increasing query performance for large datasets.

Why this answer

Data Skipping is a performance optimization where the Delta engine uses file-level statistics (min/max values) to prune files that do not contain data relevant to the query's filter conditions. By avoiding the I/O of reading irrelevant data files, the engine significantly speeds up queries. This is a primary benefit of the Delta format and is automatically handled by the engine during the planning phase of a SQL query.

Exam trap

Candidates often confuse Data Skipping with full table indexing or caching, incorrectly believing that the engine loads all data into memory before filtering, rather than pruning files based on stats.

85
MCQhard

An analyst needs to combine data from a Delta table in the 'dev' catalog with a CSV file uploaded to a volume. Which feature allows the analyst to manage both in a single query?

A.Unity Catalog Volumes
B.External locations
C.Databricks File System (DBFS) mounting
D.Manual file upload via a web browser
AnswerA

Volumes allow for the management of unstructured data (files) within the Unity Catalog's governed space. By using volumes, an analyst can access these files via SQL and join them with Delta tables, providing a unified and secure approach to handling both structured and semi-structured data within Databricks.

Why this answer

Unity Catalog Volumes enable analysts to manage non-tabular data, such as CSV or JSON files, alongside traditional Delta tables. This unification simplifies the data integration process. By treating files in volumes as first-class objects within the catalog, analysts can use SQL to query and join disparate data sources, reducing the complexity of data pipelines and making it easier to perform multi-source analyses within a secure, governed environment.

Exam trap

Candidates often choose traditional DBFS or external cloud storage paths instead of Unity Catalog Volumes, forgetting that volumes are specifically designed to govern non-tabular files in a single unified namespace.

86
MCQmedium

A data analyst needs to run a scheduled SQL query every morning and deliver the result to a finance team as a CSV file in cloud storage. The query logic is already tested in a Databricks SQL query. Which Databricks capability should the analyst use to automate this delivery?

A.A Databricks SQL query schedule with a destination
B.A Databricks SQL alert
C.A Delta Live Tables pipeline
D.A notebook-scoped widget parameter
AnswerA

Scheduling a saved SQL query lets it run at defined times, and configuring a destination delivers results to cloud storage, email, or a dashboard. This directly matches the need to run daily and deliver CSV output to finance. It is the native Databricks SQL automation mechanism for recurring query results.

Why this answer

Databricks SQL supports scheduling saved queries so they execute on a recurring cadence, and a schedule can include a destination such as cloud storage or email. That combination automates both execution and delivery of CSV results, exactly matching the finance team's requirement. Alerts, widgets, and Delta Live Tables address monitoring, interactivity, and pipeline ETL rather than scheduled result delivery.

Exam trap

The trap here is confusing monitoring features such as alerts with delivery features, when only a scheduled query with a destination both runs on a cadence and sends the file.

87
Multi-Selectmedium

Which TWO of the following scenarios are valid use cases for using the COPY INTO command? (Choose two)

Select 2 answers
A.Ingesting data that arrives in files at unpredictable, irregular intervals.
B.Implementing a continuous streaming pipeline with sub-second latency.
C.Loading CSV files into Delta tables while performing basic transformation.
D.Performing complex, multi-stage ELT pipeline orchestration.
E.Ingesting historical data from a database using JDBC connectors.
AnswersA, C

COPY INTO is idempotent, meaning if you run the same command multiple times on the same files, it will not duplicate the data. This makes it perfect for irregular batch uploads where you just want to load everything that has landed since the last successful execution without building streams.

Why this answer

COPY INTO is a powerful, idempotent command for loading data into Delta tables. It is best suited for scenarios where you need to ingest files from cloud storage periodically without setting up complex streaming infrastructure. Understanding when to use COPY INTO versus Auto Loader helps analysts choose the right balance between simplicity and advanced streaming capabilities for their specific data engineering requirements.

Exam trap

Candidates often try to use COPY INTO for complex, real-time streaming transformations, failing to recognize it is optimized for simpler, idempotent file ingestion tasks.

88
MCQeasy

An analyst opens the Databricks SQL editor and wants to browse the tables available in the workspace's Unity Catalog metastore before writing a query. Which workspace object should the analyst use to navigate catalogs, schemas, and tables?

A.The Cluster configuration page
B.The Repos folder in the workspace sidebar
C.The Jobs run history UI
D.The Data Explorer in the Databricks workspace
AnswerD

The Data Explorer is the workspace UI for browsing Unity Catalog objects such as catalogs, schemas, tables, and volumes. It lets an analyst inspect metadata, permissions, and sample data without writing SQL first. In this scenario, it is the intended navigation surface for discovering available tables before composing a query in the SQL editor.

Why this answer

The Data Explorer is the built-in workspace UI for browsing Unity Catalog objects, letting analysts navigate catalogs and schemas, inspect table metadata, and preview data. Before writing SQL, an analyst can confirm table names and columns there. The other listed surfaces handle Git integration, compute configuration, or job monitoring, none of which expose the metastore hierarchy needed for table discovery.

Exam trap

The trap here is assuming any workspace sidebar item that lists files or objects can browse Unity Catalog tables, when only the Data Explorer presents the catalog-schema-table hierarchy.

89
MCQmedium

A data analyst runs COPY INTO to load new CSV files from an S3 path into a Delta table every night. One night the job completes successfully but the row count does not change, even though new files were placed in the source path. The files have the same names as files loaded previously. What is the most likely cause?

A.The CSV files were written with a different compression codec, so COPY INTO could not parse them.
B.COPY INTO skips files whose paths were already loaded unless the load is forced or the files are modified.
C.The Delta table's transaction log has reached its retention limit and stopped accepting new commits.
D.Auto Loader had already claimed the files, so COPY INTO is blocked from reading them.
AnswerB

COPY INTO tracks which file paths it has already ingested and by default does not reload them, even if the underlying object was replaced with new content. Because the new files reused previous names, their paths matched already-processed entries, so the command silently skipped them. This idempotent behavior is intentional but surprises analysts who overwrite files.

Why this answer

COPY INTO is idempotent by design: it records the paths it has loaded and skips them on subsequent runs unless the files are new or the load is explicitly forced. Because the new files reused prior names, their paths matched the load history, so no rows were added despite a successful job status.

Exam trap

The trap here is assuming that replacing a file's contents under the same path will cause COPY INTO to reload it, when path-based tracking means it is skipped.

90
MCQmedium

A data analyst runs a query in Databricks SQL that joins a large fact table to a small dimension table. The query spills to disk and is slow. The analyst adds a /*+ BROADCAST(dim) */ hint to the query. What is the primary effect of this hint on the query execution?

A.It forces the optimizer to broadcast the dimension table to all executor nodes, avoiding a shuffle of the large fact table.
B.It caches the dimension table in Delta cache to speed up subsequent reads.
C.It partitions the fact table by the join key before the join to co-locate matching rows.
D.It sorts both tables by the join key before merging them, ensuring a sort-merge join.
AnswerA

The BROADCAST hint instructs Spark to replicate the small dimension table to every executor, so the join can be performed locally without shuffling the large fact table. This reduces network I/O and often eliminates disk spills, improving performance. The hint is appropriate when one side of the join is small enough to fit in memory.

Why this answer

The BROADCAST hint tells the optimizer to replicate the small dimension table to all executors, so the join with the large fact table can be done locally without shuffling the fact table. This reduces network traffic and avoids disk spills, making the query faster. The hint is effective when the broadcasted table is small enough to fit in memory.

Exam trap

The trap here is assuming that the BROADCAST hint partitions or sorts the large table, when it actually replicates the small table to avoid shuffling the large one.

91
MCQmedium

A data analyst is querying a Delta table that is frequently updated with new transactions. The analyst needs to ensure that a report always reflects the most recent committed data, even if a write operation is in progress. Which feature of Delta Lake should the analyst rely on?

A.Z-order clustering
B.Optimistic concurrency control
C.Snapshot isolation
D.Time travel
AnswerC

Snapshot isolation ensures that each query sees a consistent snapshot of the table as of the latest committed version, even if concurrent writes are occurring. This means the analyst always reads the most recent committed data without being affected by uncommitted changes. Delta Lake provides this by default through its transaction log, making it the correct choice for reliable, up-to-date reads.

Why this answer

Delta Lake's snapshot isolation guarantees that every query reads a consistent snapshot of the table as of the latest committed transaction. This ensures the analyst sees the most up-to-date data even during concurrent writes. Other features like time travel or Z-order serve different purposes and do not provide the required freshness guarantee.

Exam trap

The trap here is assuming that time travel automatically gives the latest data, when it actually requires specifying a historical version.

92
MCQmedium

An analyst needs to create a new table in a Unity Catalog schema to store aggregated results, and the table should be managed by Unity Catalog so that storage lifecycle and access are handled by the platform. Which SQL statement correctly creates a managed Delta table named `summary` in the `analytics` schema of the `reporting` catalog?

A.CREATE TABLE reporting.analytics.summary (id INT, total DOUBLE);
B.CREATE MANAGED TABLE reporting.analytics.summary (id INT, total DOUBLE);
C.CREATE EXTERNAL TABLE reporting.analytics.summary (id INT, total DOUBLE) LOCATION '/mnt/data/summary';
D.CREATE TABLE reporting.analytics.summary USING DELTA LOCATION 'dbfs:/user/hive/warehouse/summary';
AnswerA

In Unity Catalog, CREATE TABLE with a three-part name creates a managed table by default when no LOCATION is specified. Unity Catalog manages the storage location and lifecycle, which matches the requirement. The statement is syntactically valid and uses the correct catalog.schema.table namespace for the reporting catalog and analytics schema.

Why this answer

In Unity Catalog, a table created with CREATE TABLE and a three-part name but no LOCATION is managed by Unity Catalog, which controls its storage and lifecycle. External tables require a LOCATION and are not managed by the platform. The MANAGED keyword is not part of the syntax, and legacy dbfs paths are not the Unity Catalog managed pattern.

Exam trap

The trap here is inventing a MANAGED keyword or adding a LOCATION, when managed versus external is determined by whether a location is specified.

93
MCQeasy

A data analyst needs to query a Delta table named sales and retrieve only the rows where the sale_date is in the year 2023. The table is partitioned by sale_date. Which SQL statement will most efficiently return the required data?

A.SELECT * FROM sales WHERE cast(sale_date as string) LIKE '2023%'
B.SELECT * FROM sales WHERE year(sale_date) = 2023
C.SELECT * FROM sales WHERE sale_date LIKE '2023%'
D.SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01'
AnswerD

This range predicate on the partition column allows the optimizer to perform partition pruning, scanning only the partitions for 2023. It avoids applying a function to the column, so the filter can be pushed down to the file level. This is the most efficient way to retrieve the desired rows.

Why this answer

A range predicate directly on the partition column allows Databricks to prune partitions, reading only the data for 2023. Applying a function such as year() or cast() to the column disables pruning and forces a full scan. Therefore, the range condition is the most efficient.

Exam trap

The trap here is using a function on the partition column, which looks intuitive but disables partition pruning and causes a full table scan.

94
MCQhard

A data analyst is working with a Delta table that contains a column 'status' with values 'active', 'inactive', and 'pending'. The analyst wants to enforce that only these three values can be inserted or updated. Which Databricks SQL feature should the analyst use?

A.CHECK constraint on the status column.
B.Column mask that filters out invalid statuses.
C.NOT NULL constraint on the status column.
D.Generated column that maps status to an integer.
AnswerA

A CHECK constraint allows defining a Boolean expression that must be true for all rows. By specifying status IN ('active','inactive','pending'), the analyst enforces the allowed values. Delta Lake supports CHECK constraints, and they are enforced on write operations, ensuring data integrity. This is the correct way to restrict column values to a specific set.

Why this answer

A CHECK constraint is the appropriate feature to enforce that the status column only contains a specific set of values. It is evaluated on insert and update, rejecting rows that violate the condition. This ensures data integrity directly in the table definition.

Other options like NOT NULL, generated columns, or column masks do not restrict the domain of values for a column.

Exam trap

The trap here is confusing access control features like column masks with data validation constraints.

95
MCQmedium

Which THREE actions are required to successfully secure an external location in Unity Catalog?

A.Create a Storage Credential in Databricks.
B.Grant the 'READ FILES' or 'WRITE FILES' privilege on the external location.
C.Create an External Location object in Unity Catalog.
D.Manually update the cluster's Spark configuration.
E.Provide all users the 'Owner' role on the metastore.
AnswerA, B, C

A Storage Credential object is required to store the cloud provider credentials that allow Databricks to access your storage account. This is the first step in the process, as the External Location depends on this credential to perform any read or write operations against the underlying cloud storage provider.

Why this answer

Securing an external location in Unity Catalog involves creating a Storage Credential, linking that credential to an External Location, and granting specific permissions on that location. This layered approach ensures that access is controlled at both the cloud identity level and the Databricks governance level, allowing administrators to audit all access to external storage paths through Unity Catalog logs.

Exam trap

Students often forget that securing an external location requires both a storage credential and an explicit file-level privilege grant like READ FILES.

96
MCQeasy

An analyst is creating a Databricks SQL dashboard and wants to give viewers a single numeric value that stands out, such as total revenue for the current quarter. Which visualization type should the analyst use?

A.Counter
B.Heatmap
C.Box plot
D.Table
AnswerA

A counter visualization displays one aggregated numeric value prominently, which is exactly what is needed to highlight a single KPI such as total revenue for the quarter. It is designed for single-value metrics and updates automatically when the underlying query result changes, making it ideal for dashboard headline figures.

Why this answer

A single aggregated KPI is best represented by a counter, which renders one number with strong visual emphasis. Tables show detail, heatmaps show patterns across two dimensions, and box plots show distribution, so none of them present a lone value as clearly as a counter does.

Exam trap

The trap here is reaching for a table because it can technically contain the number, when a counter is the visualization purpose-built for a single highlighted value.

97
MCQmedium

A data analyst is designing a table to store customer orders. The table will be frequently queried by order_date and customer_id. The analyst wants to optimize query performance for these filters. Which physical data modeling technique should the analyst use?

A.Partition the table by order_date.
B.Use Z-ORDER BY on order_date and customer_id.
C.Use Liquid Clustering on order_date and customer_id.
D.Create a materialized view that aggregates orders by order_date and customer_id.
AnswerC

Liquid Clustering is a physical data modeling technique that automatically clusters data based on specified columns. It supports multiple columns and incrementally maintains clustering as data changes, optimizing queries that filter on those columns. It is defined at table creation and is more flexible than partitioning.

Why this answer

Liquid Clustering is designed to optimize query performance on multiple columns by clustering data dynamically. It is a table-level property that can be applied to new or existing tables and supports incremental clustering as data is added. Unlike partitioning, it handles high-cardinality columns well and does not require manual maintenance, making it ideal for optimizing filters on both order_date and customer_id.

Exam trap

The trap here is thinking that partitioning or Z-ORDER BY alone can optimize multiple columns, when Liquid Clustering is specifically designed for multi-column clustering without the drawbacks of partitioning.

98
MCQeasy

Which visualization type is most appropriate for displaying the distribution of a single continuous numerical variable, such as transaction amounts, in a Databricks Dashboard?

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

Histograms aggregate continuous numerical data into distinct intervals or bins, plotting frequency counts on the vertical axis. This makes them the definitive choice for analyzing the statistical distribution and spread of metrics like transaction amounts.

Why this answer

Histograms are specifically designed to display the frequency distribution of continuous numerical variables by grouping data into contiguous bins. Choosing the correct visualization ensures that stakeholders can instantly identify data skewness, central tendencies, and outliers without misinterpreting the underlying statistical distribution.

Exam trap

Candidates often confuse continuous numerical distributions with categorical data, mistakenly choosing bar charts instead of histograms.

99
MCQeasy

A data analyst runs the following command in a Databricks SQL editor connected to a Unity Catalog workspace: ```sql DROP TABLE IF EXISTS analytics.events.raw_clicks; ``` What is the result of this statement if `analytics.events.raw_clicks` is a managed Delta table?

A.The table metadata, data files, and any associated directories are permanently removed from the metastore and cloud storage.
B.The table is renamed to a recycle-bin schema and can be restored by any workspace user within 30 days.
C.The command fails because Databricks SQL does not permit dropping tables that other users may be querying.
D.Only the table metadata is removed; the underlying Parquet data files remain in cloud storage for later recovery.
AnswerA

For a managed table, Databricks owns the lifecycle of both metadata and data. Dropping it removes the catalog entry and deletes the underlying data files from the managed storage location. This is the defining behavior that separates managed from external tables and is why the statement succeeds without any additional file cleanup step from the analyst.

Why this answer

Dropping a managed Delta table in Unity Catalog removes both the catalog metadata and the underlying data files because Databricks owns the data lifecycle. External tables behave differently: their files persist. Analysts must therefore treat DROP on a managed table as destructive and irreversible through normal query tools, which is why knowing the table type before running DDL matters.

Exam trap

The trap here is assuming that DROP only removes metadata, which is true for external tables but false for managed tables where Databricks also deletes the data files.

100
MCQmedium

Which DDL statement is used to remove a table's data and metadata permanently from the catalog?

A.TRUNCATE TABLE table_name
B.DELETE FROM table_name
C.DROP TABLE table_name
D.REMOVE TABLE table_name
AnswerC

DROP TABLE is the DDL command that permanently removes both the metadata of the table from the Unity Catalog and the underlying data files from the storage location. It is the correct operation for fully decommissioning a table that is no longer needed in the environment.

Why this answer

The DROP TABLE command is the definitive way to delete both the table metadata and the underlying data files. In a production environment, this is a destructive action that requires careful consideration. Knowing the difference between TRUNCATE (which clears data but keeps the schema) and DROP (which removes everything) is critical for maintaining data integrity and preventing the accidental loss of important organizational assets.

Exam trap

Candidates often confuse DROP TABLE with TRUNCATE TABLE, forgetting that DROP completely removes the table metadata and catalog entry along with the underlying files.

101
MCQmedium

A business user asks a Genie Space, 'Show me the revenue by product.' The model returns an error. What is the most common reason for this failure in a properly configured environment?

A.The user does not have access to the underlying SQL Warehouse.
B.The model cannot distinguish between multiple columns that could mean 'revenue'.
C.The Genie Space is currently set to 'read-only' mode.
D.The user is using the wrong language for the query.
AnswerB

When a schema contains multiple columns like 'net_revenue', 'gross_revenue', and 'total_amount', the model cannot guess which one the user intended without further instructions. This ambiguity is the leading cause of query generation failures. Clear, descriptive comments in the catalog are required to disambiguate these field definitions for the model.

Why this answer

The most common failure in Genie occurs when the model cannot uniquely identify the requested columns or the business logic for 'revenue' is ambiguous. If multiple columns could represent revenue or if the product identifier is unclear, the model will struggle. Clarifying the schema via table comments and standardizing the definition of key metrics in the data layer is the standard fix for this ambiguity issue.

Exam trap

Candidates often assume the model failed because it is 'not smart enough,' rather than identifying that ambiguous column names and lack of clear metadata are the actual culprits.

102
MCQeasy

Which primary resource must be granted to users for them to successfully interact with an AI/BI Genie Space to generate insights?

A.CAN_EDIT permission on the Genie Space.
B.CAN_USE permission on the Genie Space and SELECT access on underlying data.
C.Global admin access within the Databricks Workspace.
D.Access to the underlying SQL Warehouse only.
AnswerB

The CAN_USE permission is the standard requirement for end-users to interact with the interface. Combined with SELECT access on the Unity Catalog objects, this configuration ensures the model can retrieve data to answer questions while maintaining strict adherence to the organization's existing data security and governance framework.

Why this answer

Users require explicit access permissions to both the Genie Space itself and the underlying data assets (tables or views) it exposes. Without sufficient SELECT privileges on the underlying Unity Catalog assets, the Genie engine cannot execute the SQL generated to answer natural language questions. Managing these permissions centrally through Unity Catalog ensures that data governance and security policies are consistently applied, even when users are interacting via natural language interfaces.

Exam trap

Candidates often think that giving a user access to the Genie space is sufficient, forgetting that the user must also have direct SELECT permissions on the underlying data.

103
MCQhard

An analyst has a Databricks SQL dashboard with a table visualization showing order details, including an order_date column. A stakeholder wants to see only orders from the last 30 days but also wants the ability to change the date range without editing the query. The analyst adds a date-range parameter to the query and a parameter widget to the dashboard. After publishing, the stakeholder reports that changing the parameter does not affect the table. Which action should the analyst take to resolve this?

A.Verify that the parameter is referenced in the query's WHERE clause using the correct parameter syntax and that the query was saved and the dashboard refreshed after the change.
B.Change the parameter's data type from DATE to STRING so that the date-range selection is passed correctly to the query.
C.Recreate the table visualization from scratch and re-add the parameter widget, because parameters only apply to newly created visualizations.
D.Convert the parameter to a dashboard-level filter by adding the order_date field to the dashboard filters and removing the parameter widget.
AnswerA

A parameter only affects results if the query actually uses it in a filter, such as in the WHERE clause with the correct syntax. If the parameter was defined but not referenced, or the dashboard was not refreshed after saving, the widget will not change the output. Confirming the reference and refreshing the dashboard ensures the parameter is wired to the query and the latest version is published.

Why this answer

For a parameter to affect a visualization, the query must reference it, typically in a WHERE clause, and the dashboard must reflect the saved query version. If the parameter is defined but unused, or the dashboard was not refreshed after saving, changing the widget value will not alter results. Verifying the reference and refreshing addresses the most common causes of this symptom.

Exam trap

The trap here is assuming the parameter mechanism itself is broken and switching to a different filter type, when the usual cause is that the parameter is not referenced in the query or the dashboard was not refreshed.

104
MCQmedium

What is the primary benefit of using a 'Materialized View' in Databricks SQL for frequent, expensive queries?

A.It bypasses the need for Unity Catalog permissions.
B.It automatically scales the cluster to infinite size.
C.It stores precomputed results to improve read performance.
D.It eliminates the need for any data partitioning.
AnswerC

By storing the results of the query, a materialized view allows the engine to return data immediately without re-executing complex logic or aggregations. This is highly effective for dashboarding and reporting scenarios where the same complex query is run repeatedly by multiple users throughout the business day.

Why this answer

Materialized views precompute query results, allowing for faster read performance by storing the output rather than recalculating it on every request. This is critical for data analysts working with large, complex datasets, as it significantly reduces latency and compute costs for repetitive dashboards or reports. By shifting the workload from query time to refresh time, analysts can provide consistent and fast performance for their business stakeholders.

Exam trap

Candidates often confuse materialized views with standard views or caching mechanisms, assuming they update dynamically on every single read rather than being precomputed and periodically refreshed.

105
MCQeasy

An analyst is building a dimensional model in Databricks SQL and needs to create a table that stores slowly changing dimension type 2 (SCD2) history for customers. The table must track valid_from and valid_to timestamps and a current flag. Which table type in Databricks SQL is best suited for this purpose?

A.A Delta table with change data feed enabled
B.A temporary view that joins the current customer table with a history table
C.A managed table with partitioning by customer_id
D.A Delta table with appropriate columns and MERGE operations to maintain history
AnswerD

To implement SCD2, you need a table that includes columns like valid_from, valid_to, and is_current, and you use MERGE operations to insert new versions and expire old ones. Delta Lake supports ACID transactions and MERGE, making it ideal for maintaining SCD2 history. This approach gives full control over the history tracking logic.

Why this answer

Delta tables with SCD2 columns and MERGE operations are the standard way to implement slowly changing dimensions type 2 in Databricks SQL. Delta Lake's ACID transactions and MERGE support allow you to update existing rows (expire old records) and insert new versions atomically. Other options like change data feed or temporary views do not provide the necessary persistent history structure.

Exam trap

The trap here is thinking that Change Data Feed automatically provides SCD2 history; it only records changes and does not maintain valid_from/valid_to or current flags.

106
MCQmedium

A data analyst is preparing an AI/BI Genie Space for a sales team. The team often asks questions like 'Show me the top 10 customers by revenue' and 'What were the total sales last quarter?' The analyst wants to ensure that Genie understands that the 'revenue' column in the sales table is calculated as 'quantity * unit_price' and that 'last quarter' refers to the most recent completed fiscal quarter. Which feature should the analyst use to provide this business context?

A.Set up a scheduled job to refresh the data and update statistics
B.Configure the Genie Space with instructions and sample queries
C.Add table and column descriptions in the Unity Catalog
D.Create a view in Databricks SQL that pre-calculates revenue and filters for last quarter
AnswerB

Genie Spaces allow analysts to provide natural language instructions that define business terms and calculations, such as 'revenue = quantity * unit_price' and 'last quarter = most recent completed fiscal quarter'. Sample queries further guide the model by showing expected SQL patterns. This combination ensures Genie correctly interprets the team's questions.

Why this answer

Genie Spaces are configured with natural language instructions and sample queries to teach the model business-specific terminology and calculations. This allows Genie to correctly interpret terms like 'revenue' as a formula and 'last quarter' as a relative time period, enabling accurate responses to ad-hoc questions.

Exam trap

The trap here is assuming that metadata descriptions in Unity Catalog are sufficient to convey business logic, but they are static and cannot encode dynamic calculations or relative time definitions.

107
MCQmedium

You are auditing access to several AI/BI Genie Spaces. You discover that a group of users can ask questions but cannot see the underlying source tables in the Catalog Explorer. Is this a functional issue?

A.Yes, users must have direct access to source tables to use Genie.
B.No, this is a secure configuration that follows the principle of least privilege.
C.No, but it will prevent the Genie from generating any results.
D.Yes, the users will be unable to run any queries through Genie.
AnswerB

The current configuration is optimal. By granting access to the Genie Space but not the raw catalog objects, you ensure users can gain insights without exposing the underlying data architecture. This limits the attack surface and prevents users from running ad-hoc, potentially inefficient queries directly against the source tables.

Why this answer

This is expected behavior and a sound security practice. Genie acts as an intermediary; users do not need to be able to browse or query the source tables directly to use the Genie interface. By decoupling the interface from direct data access, you simplify the user experience and maintain stricter control over how data is accessed, preventing users from bypassing the Genie's curated, secure environment.

Exam trap

Candidates frequently assume that users must have direct SELECT permissions on underlying tables to successfully query them through a Genie space, confusing direct and indirect access.

108
MCQhard

An analyst is loading Parquet files from cloud storage into a Delta table using COPY INTO. The source directory contains files with several different schemas because upstream teams added columns over time. The analyst wants the table to accept the union of all columns without manual intervention. Which COPY INTO behavior should the analyst configure?

A.Enable overwrite mode so each run replaces the table with the latest file's schema.
B.Create the target table with a manually defined superset schema containing every possible column.
C.Use a permissive mode that silently drops columns not present in the target table schema.
D.Set mergeSchema to true in the COPY INTO options so new columns are added to the target table.
AnswerD

COPY INTO supports a mergeSchema option that automatically adds columns present in the source files but missing from the target table. This is exactly the union-of-columns behavior the analyst wants, and it avoids manual ALTER TABLE steps. Without it, files containing extra columns would be rejected or would fail schema matching.

Why this answer

The mergeSchema option in COPY INTO adds any columns found in the source files to the target table, producing the union of columns across heterogeneous files. This matches the requirement to accept new columns without manual ALTER TABLE statements, while retaining all previously loaded data.

Exam trap

The trap here is confusing overwrite behavior with schema merging, when overwrite replaces data and mergeSchema extends the table definition.

109
MCQmedium

A data analyst notices that a query involving a large join between two tables is consistently slow. The analyst suspects that one of the tables is significantly skewed. Which tool in the Databricks SQL query profile is most effective for confirming this skew?

A.The Query History list view.
B.The SQL Query Profile 'Metrics' tab.
C.The Cluster Usage dashboard.
D.The Table details metadata panel.
AnswerB

The Query Profile metrics provide deep visibility into task-level statistics, including the minimum, maximum, and median rows processed per task. If the maximum row count significantly exceeds the median, it confirms data skew, allowing the analyst to implement partitioning strategies like salting to balance the workload across executors.

Why this answer

The Query Profile provides visual metrics to identify bottlenecks. By examining the 'Max vs Median' row count metrics per task, an analyst can see if specific executors are processing significantly more data than others, indicating skew. Understanding data distribution is critical in Databricks, as skew often leads to 'straggler' tasks that delay entire query execution, regardless of overall cluster size or compute power.

Exam trap

Candidates tend to look at cluster-level CPU utilization or overall duration metrics, missing task-level distribution details required to isolate specific data skew issues.

110
MCQmedium

A data analyst is configuring an AI/BI Genie Space to allow business users to query sales data. The analyst needs to ensure the Space uses the most performant and governed data source available in the Unity Catalog. Which configuration should the analyst prioritize?

A.Link the Genie Space directly to a JSON file located in an external cloud storage bucket.
B.Upload a static CSV file containing sales history directly into the Genie Space interface.
C.Configure the Genie Space to use a certified, high-performance Delta table registered in Unity Catalog.
D.Point the Genie Space to a temporary View that contains no documented column descriptions or metadata.
AnswerC

Using a certified Delta table provides the necessary schema metadata and performance optimizations that the Genie AI engine requires to interpret natural language queries correctly. This approach ensures that data governance policies are consistently applied, and the AI produces reliable, audit-ready insights based on trusted organizational data assets.

Why this answer

Genie Spaces rely on Delta tables registered in Unity Catalog to provide natural language insights. By pointing the Genie Space to a curated, high-quality Delta table rather than a raw landing zone table, the analyst ensures that the AI model produces accurate, governed results. This setup is crucial for maintaining data integrity and performance while enabling self-service analytics for non-technical stakeholders who interact with the Genie interface.

Exam trap

Candidates mistakenly suggest creating new views or performing complex data engineering to fix AI inaccuracies, failing to realize that Genie's natural language instructions are specifically designed to handle business logic definitions.

111
Multi-Selecthard

A data analyst is tuning an AI/BI Genie space used by the finance department. Users report that Genie sometimes returns plausible but incorrect aggregations, and that questions referencing 'net revenue' produce inconsistent results. The analyst wants to improve grounding and reliability. Which TWO actions should the analyst take? (Choose two.)

Select 2 answers
A.Remove all tables except one from the Genie space so the model has fewer choices.
B.Add a general instruction in the Genie space that defines net revenue as gross revenue minus returns and discounts.
C.Add example SQL queries to the Genie space that demonstrate correct net revenue calculations against the finance tables.
D.Switch the Genie space to use a larger SQL warehouse so the model has more compute for reasoning.
E.Restrict the space to read-only access for all users so no one can modify the underlying data.
AnswersB, C

General instructions are the mechanism for encoding business definitions that apply across many questions. Defining net revenue explicitly removes ambiguity and steers the model toward a consistent formula. This directly addresses the inconsistent results users reported, because the model no longer has to guess which columns or operations constitute net revenue. It is a targeted, supported way to improve grounding.

Why this answer

Inconsistent results for a business term like net revenue are best resolved by encoding the definition explicitly and demonstrating it. General instructions provide the textual rule, while example SQL queries show the exact joins and arithmetic. Together they ground the model's interpretation and reduce plausible-but-incorrect outputs.

Compute scaling and access restrictions do not address semantic ambiguity.

Exam trap

The trap here is treating inconsistent business-term interpretation as a compute or permissions issue, when the real fix is supplying explicit definitions and worked examples.

112
MCQmedium

An analyst needs to display a comparison between actual and target sales figures in a dashboard. The data exists in two separate tables. Which approach is best to display this comparison in a single visualization?

A.Create two separate visualizations and place them side-by-side on the dashboard.
B.Use a JOIN operation in the SQL query to combine the tables before visualization.
C.Use the visualization's 'secondary data source' feature to link the tables.
D.Import both tables into the dashboard's local memory and filter them.
AnswerB

Joining tables via SQL is the standard way to prepare data for reporting. It allows the analyst to create a consolidated view of actual versus target data. This efficient approach keeps the dashboard lightweight and allows the visualization tool to render the comparison easily without unnecessary complexity or redundant processing.

Why this answer

Combining data from disparate tables within the SQL layer is the recommended best practice for dashboarding in Databricks. By performing a JOIN within the SQL query, the analyst delivers a pre-aggregated, clean dataset to the visualization engine. This approach simplifies the visualization setup, minimizes the processing load on the visualizer, and ensures high performance, which is critical for complex comparative analyses in business intelligence.

Exam trap

Candidates frequently attempt to merge disparate tables directly within the dashboard visualization settings instead of handling the logic in SQL.

113
MCQmedium

An analyst needs to perform a join between a very large table and a small lookup table. Which optimization strategy should the analyst ensure is utilized to maximize performance?

A.Shuffle Hash Join
B.Sort Merge Join
C.Broadcast Hash Join
D.Cartesian Product Join
AnswerC

Broadcast hash joins significantly improve performance by sending the small table to every node. This eliminates the need for shuffling the large table, which is the primary cause of latency in distributed joins. This strategy is the standard best practice for joining large datasets with small lookup tables.

Why this answer

Broadcast joins are the most efficient way to join a small table with a large table. By broadcasting the small table to all worker nodes, Databricks avoids expensive data shuffling, which is the most common bottleneck in distributed joins. Utilizing the query optimizer's ability to perform broadcast joins is a key skill for Databricks SQL analysts, as it drastically reduces network traffic and query latency in large-scale data processing.

Exam trap

Candidates might suggest creating expensive indexes or partitioning the massive table, overlooking that broadcasting the small lookup table eliminates the shuffle bottleneck entirely.

114
MCQhard

An analyst is designing a Delta table in Databricks SQL that will store customer transactions. The table must enforce that the 'transaction_amount' is always positive and that 'customer_id' is not null. The analyst wants to ensure that any future inserts or updates that violate these rules are rejected. Which approach should the analyst use?

A.Use a MERGE statement with a condition that only updates rows where 'transaction_amount' > 0 and 'customer_id' IS NOT NULL.
B.Add a column mask that returns NULL for 'transaction_amount' when it is not positive.
C.Define a CHECK constraint on the table for 'transaction_amount > 0' and a NOT NULL constraint on 'customer_id'.
D.Create a view on top of the table that filters out rows where 'transaction_amount' is not positive or 'customer_id' is null.
AnswerC

Delta Lake supports CHECK constraints and NOT NULL constraints. CHECK constraints enforce a boolean expression on new data, and NOT NULL ensures a column cannot contain nulls. These constraints are enforced on all writes, including inserts, updates, and merges, and will reject violating rows. This directly meets the requirement to reject invalid data at write time.

Why this answer

Delta Lake's CHECK and NOT NULL constraints are enforced at write time, ensuring that any data violating the rules is rejected. A CHECK constraint on 'transaction_amount > 0' and a NOT NULL constraint on 'customer_id' will prevent invalid inserts or updates, maintaining data integrity directly in the table definition.

Exam trap

The trap here is confusing data validation constraints with data filtering or masking mechanisms that only affect query results.

115
Multi-Selectmedium

Which TWO actions should an analyst take to improve the accuracy of an AI/BI Genie Space when users report that it frequently hallucinates metric definitions?

Select 2 answers
A.Increase the number of tables available to the Genie Space.
B.Provide clear, concise system instructions that define key metrics.
C.Create standardized views in Unity Catalog and point Genie to those.
D.Enable 'auto-discovery' of all columns in the Unity Catalog tables.
E.Use a higher temperature setting to allow for more flexible interpretations.
AnswersB, C

Well-defined system instructions provide the necessary 'guardrails' for the LLM. By explicitly stating how metrics should be calculated, the developer forces the model to follow specific business logic. This drastically reduces the likelihood of the model creating its own interpretations or using incorrect formulas for standard business KPIs.

Why this answer

Improving AI/BI Genie accuracy involves a dual approach: tightening the instruction set and refining the data layer. By providing specific, clear instructions, the analyst reduces ambiguity. Simultaneously, creating curated views in Unity Catalog enforces a 'source of truth,' limiting the model's ability to interpret ambiguous raw columns.

These steps collectively ground the LLM, ensuring that it generates reliable SQL based on verified organizational definitions rather than probabilistic guesses.

Exam trap

Test-takers often select only one corrective action, forgetting that stopping hallucinations requires both clear natural language instructions and curated Unity Catalog views.

116
MCQeasy

A data analyst at a retail company needs a serverless, fully managed SQL environment in Databricks to run BI queries against a gold Delta table. The team has no interest in managing clusters, and the queries must start instantly without a warm-up period. Which Databricks SQL warehouse type should the analyst select?

A.Serverless SQL warehouse
B.All-purpose cluster with the SQL dialect enabled
C.Pro SQL warehouse with Photon enabled
D.Classic SQL warehouse with a fixed cluster size
AnswerA

Serverless SQL warehouses run in the Databricks account's compute plane, so Databricks manages the infrastructure and the warehouse starts in seconds with no cluster configuration. This matches the requirement for a fully managed, instant-start environment for BI queries on a gold Delta table, removing all capacity planning from the analyst's team.

Why this answer

The requirement is a fully managed SQL endpoint that starts immediately and requires no cluster sizing, which is exactly what a serverless SQL warehouse provides. It runs on Databricks-managed compute, scales automatically for BI workloads, and eliminates warm-up and capacity planning. Classic and Pro warehouses and all-purpose clusters all involve customer-managed compute.

Exam trap

The trap here is assuming any SQL warehouse is serverless, when classic and Pro warehouses still run on compute provisioned inside the customer's own cloud account.

117
MCQhard

An analyst queries a view `main.ops.active_shipments` and receives a row-level filtered result, but the view's definition does not contain a WHERE clause. The analyst has SELECT on the view and on the base table. What most likely explains the filtered output?

A.The analyst's group has a column mask applied to the view that removes rows where the masked column is null.
B.A row filter function is attached to the base table, and the view inherits the filter because it reads the base table at query time.
C.The view was created with a row filter using the CREATE VIEW ... WITH ROW FILTER syntax, but the filter text is hidden from SHOW CREATE TABLE output.
D.Delta Lake time travel is enabled on the view, so the analyst is reading an older snapshot that contains fewer rows.
AnswerB

Row filters in Unity Catalog are attached to tables and apply whenever the table is read, including through views. Because a standard view re-executes its definition against the base table, the attached row filter is evaluated and silently restricts rows. The filter is invisible in the view's SQL text, which explains why the view definition appears to lack a WHERE clause.

Why this answer

Row filters in Unity Catalog are attached to tables and evaluated on every read, including reads performed through views. Since a standard view re-runs its definition against the base table, the base table's row filter silently restricts the rows returned, even though the view's own SQL has no WHERE clause. Column masks alter values, not row counts, and time travel returns different table versions rather than predicate-filtered subsets.

Exam trap

The trap here is assuming the filter must be written in the view definition, when Unity Catalog row filters on the base table apply automatically.

118
MCQmedium

Refer to the exhibit. An analyst is attempting to read a CSV file using Spark. Why is the code failing?

A.The cluster is running on a single-node configuration.
B.The file path provided does not exist in the mounted location.
C.Spark does not support reading CSV files directly from DBFS.
D.The user lacks sufficient memory to read the file.
AnswerB

The AnalysisException clearly states that the path does not exist. This indicates that either the mount point /mnt/data is not correctly established or the specific file sales_2023.csv is missing or misspelled in the underlying storage. The analyst must verify the mount point and the file existence.

Why this answer

This error occurs because the specified path in DBFS does not resolve to an actual file. This often happens if the mount point was not created correctly or if the file name was mistyped. Debugging file paths is a fundamental skill for analysts, as it involves verifying storage connectivity and ensuring the file system abstraction layer correctly points to the underlying cloud storage container where the data resides.

Exam trap

Candidates frequently assume the error is related to file permissions or Spark syntax, ignoring the most common issue: a simple typo or incorrect path string in the file system mount point.

119
MCQmedium

What is the primary function of a Storage Credential in Unity Catalog?

A.To encrypt files stored in the S3 bucket.
B.To provide a secure way to access cloud storage locations.
C.To manage user passwords for the Databricks workspace.
D.To define table schema definitions for external tables.
AnswerB

Storage credentials act as a secure bridge, allowing the Databricks platform to assume a managed identity to perform operations on behalf of the user. This architecture prevents the need for embedding raw access keys in notebooks or cluster configurations, significantly reducing the surface area for credential leakage or misuse.

Why this answer

A Storage Credential is an object in Unity Catalog that encapsulates long-term cloud provider credentials (like an AWS IAM role or Azure Service Principal). It allows Databricks to access data in cloud storage without requiring users to configure individual cloud credentials on every cluster. This centralizes security by keeping sensitive cloud keys away from end users and developers.

Exam trap

Many candidates confuse storage credentials with individual user cloud logins, failing to understand that credentials decouple cloud access from end users.

120
MCQmedium

A data analyst must load a Parquet file from an S3 bucket into a Databricks DataFrame, but the bucket is in a different AWS account and requires temporary credentials. The analyst has an AWS access key ID, secret access key, and session token. Which code snippet correctly configures Spark to read the file securely?

A.spark.conf.set("fs.s3.access.key", access_key) spark.conf.set("fs.s3.secret.key", secret_key) spark.conf.set("fs.s3.session.token", session_token) df = spark.read.parquet("s3://bucket/path/file.parquet")
B.spark.conf.set("fs.s3a.access.key", access_key) spark.conf.set("fs.s3a.secret.key", secret_key) spark.conf.set("fs.s3a.session.token", session_token) df = spark.read.parquet("s3://bucket/path/file.parquet")
C.spark.conf.set("fs.s3a.access.key", access_key) spark.conf.set("fs.s3a.secret.key", secret_key) df = spark.read.parquet("s3a://bucket/path/file.parquet")
D.spark.conf.set("fs.s3a.access.key", access_key) spark.conf.set("fs.s3a.secret.key", secret_key) spark.conf.set("fs.s3a.session.token", session_token) df = spark.read.parquet("s3a://bucket/path/file.parquet")
AnswerD

This is correct because S3A requires explicit configuration of access key, secret key, and session token for temporary credentials. Setting these via spark.conf.set ensures the Spark session can authenticate to the cross-account S3 bucket. The s3a:// scheme is appropriate for Hadoop-based access, and this approach avoids hardcoding credentials in code, aligning with Databricks security best practices for external data access.

Why this answer

To read from S3 with temporary credentials, you must set the access key, secret key, and session token using the fs.s3a.* configuration keys and use the s3a:// URI scheme. This ensures Spark authenticates correctly to the cross-account bucket. Omitting the session token or using the wrong scheme will cause failures, so all three elements are essential for a successful read.

Exam trap

The trap here is assuming that access key and secret key alone are sufficient for temporary credentials, forgetting that the session token is also required for S3A authentication.

121
MCQmedium

An analyst wants to visualize trends over time using a Databricks SQL dashboard. Which feature should they use to allow end-users to dynamically filter the dashboard data without modifying the underlying SQL queries?

A.Hard-coding filters in the WHERE clause.
B.Dashboard Parameters.
C.Creating separate queries for every filter combination.
D.Using the LIMIT clause in the query.
AnswerB

Dashboard parameters allow for dynamic input, enabling users to modify query results through a user-friendly interface. This feature decouples the query logic from the filter values, providing a flexible way to explore data without requiring modifications to the SQL code, which improves user experience and dashboard reusability.

Why this answer

Dashboard parameters are the standard mechanism for creating interactive reports in Databricks SQL. By defining parameters in the query definition, analysts can expose these as input fields on the dashboard interface. This enables end-users to change filter criteria—such as date ranges or categories—dynamically.

This approach democratizes data access and reduces the maintenance burden on data analysts, as a single dashboard can now serve multiple user requirements effectively.

Exam trap

Candidates suggest rewriting queries or embedding static filters instead of using dynamic dashboard parameters for interactive user filtering.

122
MCQeasy

A data analyst needs to create a view in Databricks SQL that combines data from two tables and applies a filter. The view should be accessible to other users in the same Unity Catalog schema. Which SQL statement should the analyst use?

A.CREATE MATERIALIZED VIEW my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;
B.CREATE TEMPORARY VIEW my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;
C.CREATE TABLE my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;
D.CREATE VIEW my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;
AnswerD

The CREATE VIEW statement creates a virtual view that can be queried like a table. It stores the query definition, not the data. This is the standard way to create a view in Databricks SQL, and it will be accessible to users with appropriate permissions on the schema. It meets the requirement of combining tables and applying a filter without duplicating data.

Why this answer

The CREATE VIEW statement creates a persistent, virtual view that other users can access if they have permissions. It dynamically reflects changes in the underlying tables and does not store data. A table would duplicate data and not stay current.

A materialized view adds unnecessary complexity, and a temporary view is not shared across users.

Exam trap

The trap here is confusing a standard view with a materialized view or a table, which have different persistence and refresh characteristics.

123
MCQmedium

An analyst wants to include a chart from a Databricks SQL query in a presentation and also allow stakeholders to explore the same query interactively later. Which two-part approach best fits this need?

A.Create a separate notebook for each stakeholder and email the notebook files
B.Download the chart as an image for the presentation, and share the saved query or dashboard for interactive exploration
C.Take a screenshot of the SQL editor and paste it into the slides
D.Copy the query text into the presentation slides and ask stakeholders to run it themselves
AnswerB

Exporting the visualization as an image satisfies the static presentation need, while sharing the saved query or dashboard gives stakeholders a live, interactive view. This combination covers both requirements without duplicating logic. It is the practical Databricks SQL workflow for mixing static and interactive sharing.

Why this answer

The scenario needs both a static visual for slides and a live, explorable view for stakeholders. Exporting the chart as an image handles the presentation, and sharing the saved query or dashboard provides governed interactivity. Copying code, distributing notebooks, or screenshotting the editor fails to produce a proper chart and does not support shared exploration.

Exam trap

The trap here is assuming one artifact can serve both a static presentation and live exploration, when the practical split is an exported image plus a shared query or dashboard.

124
Multi-Selecthard

You are configuring a Databricks SQL dashboard that includes a filter widget based on a query parameter. Which TWO statements correctly describe how parameters and filter widgets behave in this dashboard? (Choose two.)

Select 2 answers
A.Parameter values are permanently baked into the saved query definition, so changing a filter widget does not alter the SQL that is sent to the warehouse.
B.A filter widget can only be bound to a single visualization, and adding the same parameter to a second chart creates an independent, unlinked control.
C.A dashboard filter widget can be applied to multiple visualizations at once, so changing its value updates all connected queries that reference the same parameter.
D.A parameter referenced in a query with the {{ parameter_name }} syntax creates a filter widget on the dashboard that viewers can use to change the value.
E.Parameters can only accept numeric values, so a filter widget cannot be used to filter a dashboard by a text field such as region or product category.
AnswersC, D

Filter widgets are designed to be shared across visualizations. When several visualizations are based on queries that reference the same parameter name, a single filter widget controls them all, so one selection refreshes every connected chart. This gives consistent, coordinated filtering across the dashboard rather than per-chart controls.

Why this answer

Parameters written with the double-curly-brace syntax become dashboard filter widgets, and a parameter shared by name across queries lets one widget control multiple visualizations. The remaining statements are wrong because parameters are resolved at run time, filter widgets can drive many charts, and parameters support text and date types as well as numbers.

Exam trap

The trap here is assuming a filter widget is tied to one chart or only handles numbers, when parameters are shared by name and support text and date types.

125
Multi-Selecthard

A data analyst is analyzing a slow-running Databricks SQL query. The analyst suspects that the query is suffering from excessive shuffling. Which two actions should the analyst take to reduce shuffle and improve performance? (Choose two.)

Select 2 answers
A.Repartition the large table on a high-cardinality column before the join.
B.Enable Adaptive Query Execution (AQE) to dynamically coalesce shuffle partitions.
C.Increase the number of shuffle partitions by setting spark.sql.shuffle.partitions to a very high value.
D.Use broadcast joins for small tables to avoid shuffling the large table.
E.Cache the large table in memory to avoid shuffling it in subsequent operations.
AnswersB, D

AQE can dynamically coalesce small shuffle partitions into larger ones, reducing the number of tasks and overhead. It also can optimize skew joins. Enabling AQE (spark.sql.adaptive.enabled=true) is a recommended practice to reduce shuffle-related inefficiencies, especially when partition sizes are uneven.

Why this answer

Enabling AQE allows dynamic coalescing of shuffle partitions and skew handling, reducing shuffle overhead. Using broadcast joins for small tables avoids shuffling the large table entirely. Both are effective strategies to minimize shuffle and improve query performance.

Exam trap

The trap here is thinking that increasing shuffle partitions or caching always helps; instead, AQE and broadcast joins are targeted at reducing shuffle volume and overhead.

126
MCQmedium

A data analyst needs to grant a colleague the ability to run an existing Databricks SQL query and view its results, but the colleague must not be able to edit the query text or change its schedule. The query is saved in the workspace. Which permission level should the analyst assign on the query object?

A.CAN EDIT
B.CAN VIEW
C.CAN RUN
D.CAN MANAGE
AnswerC

CAN RUN grants the ability to execute the saved query and see its results without allowing edits to the query text or its schedule. This precisely matches the requirement: the colleague can run the query and view output, but cannot modify it. It is the least-privilege permission that still enables the needed action.

Why this answer

CAN RUN is the least-privilege permission that lets a user execute a saved query and view results while preventing edits to the query text or schedule. CAN VIEW does not grant run capability, while CAN EDIT and CAN MANAGE both allow modifications that the scenario explicitly forbids.

Exam trap

The trap here is choosing CAN VIEW for read-only access when the colleague also needs to execute the query, which requires CAN RUN.

127
MCQmedium

An analyst is optimizing a query that performs multiple aggregations on a Delta table. Which TWO actions can the analyst take to improve query performance via the SQL editor?

A.Use Z-Ordering on columns frequently used in WHERE filters.
B.Enable partition pruning by including partition columns in the WHERE clause.
C.Increase the cluster size to 'Large' for every query execution.
D.Force a Broadcast Join for all small tables.
E.Convert all tables to Parquet format.
AnswerA, B

Z-Ordering co-locates related data in the same set of files, significantly enhancing data skipping capabilities. By clustering data based on frequently filtered columns, the engine can ignore irrelevant files during query execution, leading to faster data retrieval and lower overall compute resource consumption for the query.

Why this answer

Improving performance requires reducing the amount of data read. Using partition pruning allows the engine to skip irrelevant files, while Z-Ordering improves data skipping by clustering related information together. These techniques are fundamental for Databricks SQL performance, as they minimize I/O overhead.

Mastering these adjustments allows analysts to write highly efficient code that scales effectively with growing datasets, reducing both latency and costs for the organization.

Exam trap

Candidates often suggest adding more compute resources or changing the cluster type as a first step, ignoring that query-level optimizations like pruning and Z-Ordering are far more effective.

128
Multi-Selectmedium

A data analyst is new to a Databricks workspace and needs to understand which compute options are available for running SQL and notebooks. Which TWO of the following statements accurately describe Databricks compute in this context? (Choose two.)

Select 2 answers
A.SQL warehouses are optimized for running SQL and serving dashboards and scheduled queries.
B.All-purpose clusters are required to run any query in the Databricks SQL editor.
C.SQL warehouses and all-purpose clusters share the same configuration settings and scaling behavior.
D.A job cluster is created for a specific job run and terminated when the run completes.
E.Serverless compute for notebooks cannot be used with Unity Catalog.
AnswersA, D

SQL warehouses are compute resources specifically tuned for SQL workloads, including BI dashboards, Databricks SQL queries, and scheduled reports. They separate storage from compute and can auto-stop when idle. For an analyst focused on SQL and visualization, a SQL warehouse is the appropriate compute choice, making this statement accurate.

Why this answer

SQL warehouses are purpose-built for SQL, dashboards, and scheduled queries, while job clusters are ephemeral compute created for a specific run and terminated afterward. Together these describe two real compute behaviors an analyst should know. The other statements misstate requirements or capabilities, such as claiming all-purpose clusters are needed for the SQL editor or that serverless cannot use Unity Catalog.

Exam trap

The trap here is assuming there is a single compute type for all Databricks work, when SQL warehouses, job clusters, and all-purpose clusters serve distinct purposes.

129
Multi-Selectmedium

A data analyst is preparing to publish a Databricks SQL dashboard for a team of business users. The analyst wants the dashboard to load quickly and to remain usable as the underlying Delta table grows. Which two practices should the analyst follow? (Choose two.)

Select 2 answers
A.Create the dashboard datasets against a SQL Warehouse that is sized appropriately and has auto-stop configured
B.Store the dashboard datasets as CSV files in a Unity Catalog volume for faster reads
C.Pre-aggregate large fact tables into summary tables or materialized views used by the dashboard datasets
D.Disable the SQL Warehouse auto-stop so the dashboard always has warm compute
E.Grant every viewer CAN MANAGE on the dashboard so they can tune queries themselves
AnswersA, C

Sizing the SQL Warehouse appropriately ensures the dashboard queries have enough compute to return results quickly, and auto-stop controls cost when the dashboard is idle. This directly supports fast loading and sustainable operation as usage grows. The warehouse is the compute that serves dashboard queries, so choosing and configuring it correctly is a core best practice.

Why this answer

Fast, scalable dashboards rely on appropriately sized SQL Warehouse compute with auto-stop and on reducing the data scanned through pre-aggregation such as summary tables or materialized views. Storing datasets as CSV, disabling auto-stop, and granting broad manage permissions do not improve performance and introduce cost or governance problems.

Exam trap

The trap here is thinking that keeping compute always on or moving data to CSV improves dashboard performance, when sizing, auto-stop, and pre-aggregation are the real levers.

130
MCQmedium

When designing a table to support frequent 'MERGE' operations, which data modeling practice will lead to the best performance?

A.Store the data in a CSV file format.
B.Use Z-Ordering on the join keys.
C.Disable the Delta transaction log.
D.Ensure the target table has no indexes or clustering.
AnswerB

When MERGE operations are performed, the engine joins the source and target tables. If the join keys in the target table are Z-Ordered, the engine can efficiently find and update only the relevant files. This significantly reduces the volume of data processed, leading to much faster performance for large-scale operations.

Why this answer

MERGE operations are expensive because they involve reading, joining, and rewriting data. To optimize this, the target table should be well-organized using partitioning and Z-Ordering or Liquid Clustering. This allows the MERGE operation to perform 'data skipping', only loading the relevant files into memory during the join and update process.

Without these optimizations, the engine must perform a full table scan for every single merge statement, leading to significant latency.

Exam trap

Candidates often suggest partitioning by high-cardinality columns for performance. This is a major anti-pattern that leads to the small file problem and degrades MERGE performance significantly.

131
MCQmedium

A data analyst runs a Databricks SQL query that joins a 50 GB fact_sales Delta table with a 200 MB dim_product Delta table. The query takes 15 minutes. The analyst runs EXPLAIN FORMATTED and sees a SortMergeJoin instead of a BroadcastHashJoin. The analyst has already confirmed that the small table is not being broadcast. Which action should the analyst take to improve performance?

A.Add a broadcast hint to the large fact table so it is replicated to all executors.
B.Increase spark.sql.autoBroadcastJoinThreshold to a value larger than 200 MB, such as 250 MB, so the small table can be broadcast.
C.Set spark.sql.autoBroadcastJoinThreshold to -1 to disable broadcasting and force a shuffle hash join.
D.Repartition both tables on the join key before the join to ensure co-located partitions.
AnswerB

The default auto broadcast join threshold is 10 MB, so a 200 MB table is not broadcast. Increasing the threshold above 200 MB allows Spark to broadcast the small table, converting the SortMergeJoin into a BroadcastHashJoin. This eliminates the shuffle of the large fact table and significantly improves performance.

Why this answer

Increasing the auto broadcast join threshold above the size of the small table enables Spark to broadcast it, converting the SortMergeJoin to a BroadcastHashJoin. This avoids shuffling the large fact table, which is the main cost. The default threshold is 10 MB, so a 200 MB table is not broadcast unless the threshold is raised.

Exam trap

The trap here is assuming that broadcast joins are automatically used for any small table, when in fact the default threshold is only 10 MB and must be increased for larger small tables.

132
MCQeasy

An analyst is preparing a report and needs to ensure that the dashboard data is always current. What is the most efficient way to achieve this?

A.Manually refresh the dashboard every morning.
B.Set a refresh schedule on the dashboard.
C.Re-run the SQL queries in a notebook and export the results.
D.Ask the SQL warehouse to stay running indefinitely.
AnswerB

Setting a refresh schedule is the native, efficient way to automate data updates in Databricks SQL dashboards. It ensures that the SQL queries are executed at pre-defined intervals, providing up-to-date visualizations for end-users without any manual effort from the analyst, meeting the requirement for efficiency and consistency.

Why this answer

Setting a regular refresh schedule is the correct way to ensure data remains fresh. This automation removes the manual effort of refreshing queries, ensuring that the dashboard always represents the latest state of the business. It is a fundamental operational task that guarantees the dashboard's reliability and relevance, preventing stakeholders from making decisions based on stale or outdated information, which is a major risk in any data-driven environment.

Exam trap

Many candidates mistakenly think they must manually execute queries every day or configure complex orchestration jobs just to keep a dashboard updated.

133
MCQmedium

A data analyst built a Databricks SQL dashboard with a chart showing monthly revenue. During a presentation, an executive asks to see only the months where revenue exceeded $100,000. The analyst wants to apply this condition directly in the visualization without rewriting the underlying query. Which Databricks SQL dashboard feature should the analyst use?

A.Edit the chart's query to include a WHERE clause filtering revenue greater than 100000.
B.Create a new calculated column in the dataset that flags high-revenue months and sort by it.
C.Add a filter to the dashboard and set it to apply only to the revenue chart.
D.Apply a visualization-level sort descending on revenue and manually deselect the bars below the threshold.
AnswerC

Dashboard filters in Databricks SQL operate on the dataset's columns and can be scoped to individual visualizations, so the analyst can add a numeric filter on the revenue column with a condition such as greater than 100000 and apply it only to that chart. This changes the displayed data without editing the query, which is exactly what the scenario requires.

Why this answer

Dashboard filters in Databricks SQL let analysts apply conditions such as revenue greater than a threshold at view time and scope them to specific visualizations. This meets the need to show only high-revenue months without altering the query, and it supports interactive toggling during a presentation. The other approaches either modify the query, add unnecessary columns, or rely on manual steps that are not reproducible.

Exam trap

The trap here is assuming that any change to the displayed data requires editing the underlying query, when Databricks SQL dashboard filters can apply conditions at view time without touching the SQL.

134
MCQmedium

An analyst is configuring an AI/BI Genie Space for a retail dataset. The analyst wants Genie to map the business term "top sellers" to a specific calculation that ranks products by total units sold. Where should the analyst define this mapping?

A.In the Genie Space's instructions, add a definition: "top sellers" means products ranked by total units sold.
B.Create a Unity Catalog function named top_sellers and reference it in the Genie Space.
C.Add a comment to the units_sold column stating that it is used for top sellers.
D.Create a metric view that pre-calculates top sellers and add it to the Genie Space.
AnswerA

Instructions are the correct place to teach Genie business terminology and calculations. By defining "top sellers" there, the analyst ensures Genie interprets that phrase consistently and generates SQL that ranks by units sold. This aligns with how Genie uses natural-language instructions to resolve ambiguous terms without changing the underlying data model.

Why this answer

Genie Space instructions are designed to capture business glossary definitions and calculation rules. Defining "top sellers" as products ranked by total units sold directly in the instructions gives Genie the semantic knowledge to generate correct SQL for that phrase, without altering the data model.

Exam trap

The trap here is thinking that a column comment or a separate function automatically teaches Genie the business term; only explicit instructions create that mapping.

135
MCQmedium

A Data Analyst is configuring an AI/BI Genie Space to allow business users to query sales data. The analyst needs to ensure the model understands specific business logic, such as how to calculate 'Net Revenue' from raw columns. Where should the analyst define this logic to ensure Genie consistently applies these business rules across all user queries?

A.Add the logic as a comment in the user's prompt history.
B.Hardcode the business logic into the Genie Space configuration UI.
C.Define the logic in the SQL view metadata or table comments within Unity Catalog.
D.Require users to provide the formula in every natural language query.
AnswerC

AI/BI Genie leverages metadata defined in Unity Catalog to understand data semantics. By properly commenting tables and views with detailed business descriptions, you provide the context needed for the model to generate accurate SQL. This approach ensures consistent interpretation of metrics across all users accessing the Genie Space.

Why this answer

Defining business logic in the underlying Databricks SQL Warehouse or Unity Catalog perspective is the most reliable way to guide Genie. By providing clear descriptions and semantic definitions within the catalog or view metadata, the LLM-driven engine can correctly interpret business terminology. This ensures that non-technical users receive consistent, accurate results without needing to write complex SQL, directly impacting the trust and adoption of the AI/BI solution within the organization.

Exam trap

Candidates often mistakenly believe business logic should be hardcoded directly into the Genie user interface prompt rather than structured within Unity Catalog metadata or table comments.

136
MCQeasy

A data analyst writes a query in the Databricks SQL editor that returns an error stating the column cannot be resolved, even though the analyst is certain the column exists in the Delta table. The analyst wants to quickly confirm the actual column names and data types of the table before editing the query. Which approach is most efficient?

A.Run DESCRIBE TABLE on the table in the SQL editor
B.Open the table's Delta transaction log JSON files and read the schema
C.Query the table with SELECT * and inspect the result grid columns
D.Recreate the table with a CREATE OR REPLACE statement matching the intended schema
AnswerA

DESCRIBE TABLE returns the column names, data types, and comments for the table, letting the analyst verify exact spelling and casing without leaving the SQL editor. This is the fastest way to confirm the real schema and correct the unresolved column reference, since it queries the metastore metadata directly rather than scanning data.

Why this answer

DESCRIBE TABLE reads table metadata from the metastore and returns column names, data types, and comments without scanning data. That makes it the fastest, cheapest way to confirm exact spelling and casing so the analyst can fix the unresolved column reference in the query.

Exam trap

The trap here is running SELECT * to eyeball column names, which scans data and still hides declared types and comments.

137
MCQmedium

When analyzing query performance, what does a high 'Spill to Disk' metric indicate?

A.The query is successfully using the cache.
B.The cluster has insufficient memory for the operation.
C.The data is highly compressed.
D.The query is finished and writing results to the table.
AnswerB

Spilling happens when a transformation, such as a large-scale shuffle or aggregate, consumes more memory than allocated. The system temporarily moves data to disk to prevent an 'OutOfMemory' crash. This slows the query down significantly, suggesting that the memory allocation per executor should be increased or the query optimized.

Why this answer

Spilling to disk occurs when a query's operations, such as sorts or joins, exceed the available memory in the cluster's executors. This is a major performance bottleneck because disk I/O is exponentially slower than memory. Recognizing this indicator is crucial for analysts because it signals that the current cluster size or query logic needs adjustment to fit the workload comfortably within RAM, significantly improving query speed and stability.

Exam trap

Test-takers frequently attribute disk spilling to slow network latency or poor storage speeds rather than recognizing memory exhaustion.

138
MCQhard

A data analyst is using Databricks SQL to query a table `main.sales.orders` that has a column `credit_card` containing sensitive data. The analyst needs to run a query that aggregates orders by region but must not see the actual credit card numbers. Which Unity Catalog feature should be used to mask the `credit_card` column for this analyst?

A.Granting `SELECT` only on specific columns
B.Using `REDACT` function in the query
C.Column-level dynamic data masking using a user-defined function
D.Row-level security using a dynamic view
AnswerC

Column-level dynamic data masking allows you to apply a function to a column that transforms the data based on the querying user's identity or group. This masks sensitive values like credit card numbers while still allowing aggregations and queries. The analyst can run the aggregation without seeing the actual data. This is the recommended approach in Unity Catalog for column-level security.

Why this answer

Column-level dynamic data masking in Unity Catalog allows administrators to attach a masking function to a column. The function can return a masked value (e.g., '****') for unauthorized users while returning the actual value for authorized users. This enables the analyst to run aggregations without seeing sensitive data.

Other options either do not address column masking or are not supported features.

Exam trap

The trap here is confusing row-level security with column-level masking; row-level security filters rows, while masking transforms column values.

139
MCQmedium

A data analyst is writing a Databricks SQL query that needs to join a large table with a small table and then aggregate the results. To optimize performance, they want to ensure the small table is broadcasted. Which SQL hint syntax should they use in the query?

A.SELECT /*+ BROADCAST(small_table) */ ...
B.SELECT /*+ MAPJOIN(small_table) */ ...
C.SELECT /*+ SHUFFLE_HASH(small_table) */ ...
D.SELECT /*+ BROADCASTJOIN(small_table) */ ...
AnswerA

The correct syntax for a broadcast hint in Databricks SQL is to include /*+ BROADCAST(table_alias) */ immediately after the SELECT keyword. This instructs the optimizer to broadcast the specified table. Using the table alias is important if the table is aliased in the query.

Why this answer

The BROADCAST hint in Databricks SQL is specified as /*+ BROADCAST(table_name) */ after SELECT. It directs the optimizer to broadcast the smaller table, avoiding a shuffle of the larger table. Other hints like MAPJOIN or BROADCASTJOIN are not supported.

Exam trap

The trap here is using hint names from other SQL engines like Hive's MAPJOIN, which are not valid in Databricks SQL.

140
MCQmedium

A data analyst is using Databricks SQL to query a large Delta table. The query is performing slowly because it must scan the entire table to retrieve data for a specific date range. Which action should the analyst take to optimize query performance?

A.Convert the table format from Delta to Parquet to improve raw read throughput.
B.Increase the number of clusters in the SQL Warehouse to enable higher concurrency.
C.Execute an OPTIMIZE command with ZORDER BY on the relevant date columns.
D.Use the CACHE SELECT statement on the entire table to force all data into memory.
AnswerC

The OPTIMIZE command with ZORDER BY reorganizes data layout into smaller, co-located files based on the specified columns. This allows the query engine to utilize metadata for data skipping, drastically reducing the amount of data read from storage. This approach directly addresses the performance bottleneck by pruning unnecessary files before the scan begins.

Why this answer

Implementing Z-Ordering on the partition columns or columns frequently used in WHERE clauses significantly enhances data skipping capabilities. By physically organizing data on disk based on these values, Databricks SQL engine can skip irrelevant files during query execution. This optimization is critical in Databricks SQL environments because it reduces I/O overhead, lowers latency for end-user dashboards, and minimizes the computational resources required to process large-scale analytical workloads efficiently.

Exam trap

Candidates often confuse basic table partitioning with Z-Ordering, or forget that OPTIMIZE must be paired with ZORDER BY to improve date-range data skipping.

141
MCQeasy

Why should an analyst use the 'feedback' feature provided in an AI/BI Genie space interface?

A.To log a support ticket with Databricks Engineering
B.To improve the accuracy of future answers
C.To increase the SQL Warehouse query limit
D.To export the answer as a CSV file
AnswerB

Feedback serves as a direct input to enhance the model's reasoning capabilities within the context of the Genie space. By flagging errors, users help the system learn which interpretations were incorrect, allowing the model to adapt and provide more reliable, accurate answers to similar questions in the future.

Why this answer

The feedback mechanism is crucial for the iterative improvement of the Genie space. When users rate answers as 'helpful' or 'not helpful,' they provide signal that Databricks uses to refine the model's performance over time. This continuous learning process ensures that the assistant becomes more accurate, more relevant, and better aligned with the specific business context, ultimately providing higher value to the entire user organization.

Exam trap

Candidates often assume the feedback feature is only for contacting customer support, ignoring its role in improving AI response accuracy over time.

142
MCQeasy

When sharing data with external organizations using Delta Sharing, which component manages the secure exchange of data?

A.Unity Catalog metastore.
B.Workspace-local Hive metastore.
C.Databricks File System (DBFS) Root.
D.User-managed S3 bucket policies.
AnswerA

The Unity Catalog metastore serves as the governance layer that defines and tracks Delta Shares. It holds the metadata about the share, including which tables are included and which recipients have access. Without the metastore, there is no central mechanism to authorize or audit the external sharing of data assets.

Why this answer

Delta Sharing is an open protocol that enables the secure exchange of data across different organizations without requiring them to have accounts in the same Databricks workspace. The Unity Catalog metastore acts as the central control point for these shares. By creating a 'Share' object, an administrator defines exactly which tables are shared, and the recipient uses a token to access that data securely.

Exam trap

Many candidates mistakenly believe that individual clusters, notebooks, or workspace admins manage Delta Sharing instead of recognizing the central role of the Unity Catalog metastore.

143
Multi-Selectmedium

Which THREE features are provided by AI/BI Genie spaces to help business users analyze data independently?

Select 3 answers
A.Conversational natural language querying
B.Automatic chart and visualization generation
C.Interactive model performance feedback
D.One-click deployment of production ETL pipelines
E.Automatic generation of unit tests for Python code
AnswersA, B, C

The core of Genie is the ability to interpret natural language questions and translate them into valid SQL. This removes the barrier of entry for non-technical users, allowing them to ask business questions directly without needing to understand the underlying table structures or complex query syntax.

Why this answer

Genie spaces empower business users by abstracting complex SQL development. They provide a natural language interface for asking questions, automatic visualization of results for immediate insight, and a feedback loop where users can rate answers to refine the model's future performance. These features collectively reduce the burden on data teams while fostering a self-service culture where users can safely interact with governed data assets.

Exam trap

Candidates often overlook the 'feedback' component, focusing only on the query generation aspect, and failing to realize that user ratings are critical for model improvement.

144
MCQmedium

A data analyst has been asked to grant a new team member read access to a specific table named 'customer_orders' in Unity Catalog. The analyst wants to ensure the team member can query the table but cannot see any other tables in the schema. Which SQL command should the analyst use?

A.GRANT SELECT ON SCHEMA main.sales TO `user@example.com`;
B.GRANT SELECT ON TABLE main.sales.customer_orders TO `user@example.com`;
C.GRANT USAGE ON TABLE main.sales.customer_orders TO `user@example.com`;
D.GRANT SELECT ON CATALOG main TO `user@example.com`;
AnswerB

This command grants the SELECT privilege on the specific table 'main.sales.customer_orders' to the user. In Unity Catalog, table-level grants are supported and allow fine-grained access control, enabling the user to query only that table without access to other tables in the schema. This directly satisfies the requirement.

Why this answer

To grant read access to a specific table in Unity Catalog, the SELECT privilege must be granted on that table. This allows the user to query the table's data while leaving other tables inaccessible. Schema- or catalog-level grants are broader and would inadvertently expose additional data, so they are not suitable for this scenario.

Exam trap

The trap here is confusing the USAGE privilege with SELECT for tables, or assuming that schema-level grants are necessary for table access.

145
MCQeasy

A junior data analyst attempts to load a large JSON file into a Databricks DataFrame using spark.read.json(path), but the resulting DataFrame contains numerous null values across critical columns. Upon inspection, the raw JSON records have inconsistent nesting structures and missing attributes. What is the most effective way for the analyst to inspect the inferred schema before transforming the data?

A.Execute the display() function on the raw file path string directly without creating a DataFrame object first.
B.Run the schema property or printSchema() method on the DataFrame to review the hierarchical structure and data types inferred by Spark.
C.Query the system information tables in the Hive metastore using a standard SQL DESCRIBE DATABASE statement.
D.Export the JSON file into Microsoft Excel locally to manually count the frequency of null values in each column.
AnswerB

printSchema() displays the inferred schema, including nested struct fields and data types, revealing why inconsistent nesting produced nulls. Reviewing this hierarchy lets the analyst correct the read options or define an explicit schema before transformation.

Why this answer

Using the printSchema() method on the loaded DataFrame prints out the hierarchical data types and inferred structure determined by Spark during the read operation. Data analysts rely heavily on this method to quickly identify schema evolution issues, unexpected nulls, and nested arrays before building downstream analytical queries and dashboards.

Exam trap

Candidates often try to use 'display()' or 'show()' to inspect schemas. While these show data content, they do not provide the structural metadata or hierarchical types needed to debug schema issues.

146
Multi-Selectmedium

A data analyst is setting up a new Genie space. Which TWO of the following are prerequisites for the successful creation and usage of a Genie space?

Select 2 answers
A.A Unity Catalog-enabled workspace
B.A running Serverless SQL Warehouse
C.A pre-trained custom Large Language Model
D.A local Python environment with Genie SDK
E.A Delta Live Table pipeline with high availability
AnswersA, B

Unity Catalog is mandatory because it provides the unified metadata and governance required for the LLM to discover and reason over tables. Without Unity Catalog, the Genie space cannot resolve table schemas, identify relationships, or enforce the data access controls necessary for safe natural language interactions.

Why this answer

Genie spaces require a foundational environment where data is discoverable and executable. Unity Catalog provides the necessary governance and metadata, while a SQL Warehouse provides the compute resources to execute the generated code. Without these, the Genie space lacks both the data access layer and the processing capability required to answer user questions, making these two components absolute necessities for the feature to function correctly.

Exam trap

Candidates often forget that serverless compute is a mandatory prerequisite, sometimes wrongly assuming that standard SQL warehouses or manual compute provisioning are sufficient for Genie space functionality.

147
Multi-Selecthard

Which THREE of the following are benefits of using the Delta Lake format in Databricks SQL?

Select 3 answers
A.ACID transactions for data integrity
B.Native support for Time Travel
C.Automatic file compaction via OPTIMIZE
D.The ability to run queries without a cluster
E.Automatic conversion of all queries to Python
AnswersA, B, C

ACID compliance ensures that concurrent writes do not corrupt the data. It guarantees that a transaction either succeeds completely or fails entirely, ensuring consistency for analytical queries. This integrity is essential in multi-user environments where multiple processes may be reading and writing data simultaneously.

Why this answer

Delta Lake provides the foundation for reliable, performant SQL querying in Databricks. Key benefits include ACID transactions, which prevent data corruption during concurrent operations; time travel, which allows analysts to query previous versions of data; and data skipping/compaction, which drastically improve query performance. Together, these features enable reliable data warehousing and complex analytics at scale, which are the main reasons why it is the standard format in Databricks SQL environments.

Exam trap

Candidates often include non-Delta features or assume that Delta Lake requires manual indexing or external metadata stores, losing track of the core features like ACID and Time Travel.

148
MCQhard

Refer to the exhibit. An analyst examines the physical execution plan of a Spark SQL join query. Based on the provided plan output, which optimization strategy was automatically applied by Adaptive Query Execution (AQE)?

A.Dynamic partition pruning was used to eliminate scanning partitions on the orders table.
B.AQE dynamically converted a sort-merge join into a broadcast hash join based on runtime size statistics.
C.Skew join optimization was triggered to split the skewed partition keys into multiple sub-tasks.
D.The Catalyst optimizer pushed down aggregations past the join boundary to reduce intermediate data size.
AnswerB

Adaptive Query Execution evaluates runtime size metrics after executing child stages. When the size of the filtered 'products' table fell below the broadcast threshold, AQE replaced the costly sort-merge join with an efficient broadcast hash join, avoiding a heavy shuffle.

Why this answer

Adaptive Query Execution in Databricks dynamically optimizes query plans at runtime based on precise statistics gathered from completed shuffle stages. The exhibit clearly displays a BroadcastHashJoin where the right side of the join was converted from a sort-merge join into a broadcast join using a broadcast exchange stage.

Exam trap

Candidates often assume the optimizer always chooses the most efficient join method, failing to recognize the specific 'BroadcastHashJoin' signature in the plan as a result of runtime AQE optimizations.

149
MCQmedium

An analyst is setting up a Genie Space to handle financial reporting. They want the model to always group results by 'fiscal_quarter'. How should they enforce this requirement?

A.Update the default table sort order in the catalog.
B.Add a constraint to the system instructions.
C.Force users to type 'group by fiscal_quarter' in their prompts.
D.Create a view that excludes the 'date' column.
AnswerB

System instructions are the correct place for business rules. By clearly defining that 'all time-based queries must be aggregated by fiscal_quarter', the model is conditioned to prioritize this column. This ensures that the generated SQL respects the mandatory fiscal reporting standards of the organization without requiring user intervention.

Why this answer

Forcing specific groupings is best achieved through system instructions in the Genie Space configuration. By explicitly stating 'Always group by fiscal_quarter for all time-based queries,' the analyst provides a mandatory rule for the model. This guarantees that every time-series query generated by the AI aligns with the company's fiscal calendar, preventing the model from defaulting to calendar months and ensuring reporting consistency across the organization.

Exam trap

Candidates often look to modify the physical table structure or default database settings rather than using the Genie space configuration options to enforce grouping behavior.

150
Multi-Selecthard

A data analyst is investigating why a specific Spark SQL query takes an exceptionally long time to complete and exhibits signs of severe data skew. Which TWO metrics or behaviors in the Databricks Spark UI typically indicate that data skew is impacting the query? (Choose two)

Select 2 answers
A.The maximum task duration is drastically higher than the 75th and median task durations for a given stage.
B.The total number of input files scanned is zero because all data is successfully pruned by partition filters.
C.Specific tasks show high amounts of spill to disk for memory-heavy operations like aggregations and joins.
D.The driver node runs out of memory during the initial parsing phase before any execution stages are generated.
E.All tasks across all worker nodes complete in nearly identical timeframes with minimal variance.
AnswersA, C

A wide gap between the median task duration and the maximum task duration is a primary hallmark of data skew. Most tasks complete rapidly, but the stage cannot finish until the few tasks processing the skewed partition keys finish executing their workloads.

Why this answer

Data skew occurs when records are unevenly distributed across partitions, causing a few tasks to process the vast majority of data while others finish quickly. In the Spark UI, this manifests as a massive disparity between task duration percentiles and extremely high memory consumption or spill metrics on the tasks handling the skewed keys.

Exam trap

Students often mistake general cluster slowness for data skew, failing to check the specific percentile task duration gaps in the Spark UI.

Page 1

Page 2 of 4

Page 3

All pages