Databricks-Certified-Data-Engineer-Associate Sample Questions Answers

 To return the number of null values in the member_id column, the best option is to use the count_if function, which counts the number of rows that satisfy a given condition. In this case, the condition is that the member_id column is null. The other options are either incorrect or not supported by Spark SQL. Option A will return the number of non-null values in the member_id column. Option B will not work because there is no count_null function in Spark SQL. Option D will not work because there is no null function in Spark SQL. Option E will not work because there is no count_null function in Spark SQL. References:

Built-in Functions - Spark SQL, Built-in Functions

count_if - Spark SQL, Built-in Functions

Databricks Repos is a visual Git client and API in Databricks that supports common Git operations such as commit, pull, push, branch management, and visual comparison of diffs when committing1. However, merge is not supported in the Git dialog2. You need to use the Repos UI or your Git provider to merge branches3. Merge is a way to combine the commit history from one branch into another branch1. During a merge, a merge conflict is encountered when Git cannot automatically combine code from one branch into another. Merge conflicts require manual resolution before a merge can be completed1. References: 4: Run Git operations on Databricks Repos4, 1: CI/CD techniques with Git and Databricks Repos1, 3: Collaborate in Repos3, 2: Databricks Repos - What it is and how we can use it2.

Databricks Repos is a visual Git client and API in Databricks that supports common Git operations such as commit, pull, push, merge, and branch management. However, to clone a remote Git repository to a Databricks repo, you must use the Databricks UI or API. You cannot clone a Git repo using the CLI through a cluster’s web terminal, as the files won’t display in the Databricks UI1. References: 1: Run Git operations on Databricks Repos | Databricks on AWS2

To create a table in Databricks using data from an SQLite database, the correct syntax involves specifying the format of the data source. The format in the case of using JDBC (Java Database Connectivity) with SQLite is specified by the org.apache.spark.sql.jdbc format. This format allows Spark to interface with various relational databases through JDBC. Here is how the command should be structured:

CREATE TABLE jdbc_customer360

USING org.apache.spark.sql.jdbc

OPTIONS (

url " jdbc:sqlite:/customers.db " ,

dbtable " customer360 "

)

The USING org.apache.spark.sql.jdbc line specifies that the JDBC data source is being used, enabling Spark to interact with the SQLite database via JDBC.

Option B changes duplicate detection from all-column comparison to comparison on the two required business keys. With no subset argument, dropDuplicates() considers every column, so records that differ in event_timestamp are not duplicates. Databricks documents that passing a list such as [ " user_id " , " event_type " ] makes those columns the duplicate-comparison subset while returning a DataFrame with the original schema. groupBy(...).count() aggregates the data and returns counts instead of one complete event row. Dropping event_timestamp and then calling dropDuplicates() still compares every remaining column, not only the two specified keys. Calling distinct() before selecting the keys also performs distinctness on complete rows and then discards the non-key columns. Therefore, df.dropDuplicates([ " user_id " , " event_type " ]) is the precise change, making option B correct.

he approach that can be used to identify the owner of new_table is to review the Owner field in the table’s page in Data Explorer. Data Explorer is a web-based interface that allows users to browse, create, and manage data objects such as tables, views, and functions in Databricks1. The table’s page in Data Explorer provides various information about the table, such as its schema, partitions, statistics, history, and permissions2. The Owner field shows the name and email address of the user who created or owns the table3. The data engineer can use this information to contact the table owner and request for permission to access the table.

The other options are not correct or reliable for identifying the owner of new_table. Reviewing the Permissions tab in the table’s page in Data Explorer can show the users and groups who have access to the table, but not necessarily the owner4. Reviewing the Owner field in the table’s page in the cloud storage solution can be misleading, as the owner of the data files may not be the same as the owner of the table5. There is a way to identify the owner of the table, as explained above, so option E is false.

1: Data Explorer | Databricks on AWS

2: Table details | Databricks on AWS

3: Set owner when creating a view in databricks sql - Databricks - 9978

4: Table access control | Databricks on AWS

5: External tables | Databricks on AWS

To find the owner of a table in Databricks, one can utilize the Data Explorer feature. The Data Explorer provides detailed information about various data objects, including tables. By navigating to the specific table ' s page in Data Explorer, a data engineer can review the Owner field, which identifies the individual or role that owns the table. This information is crucial for obtaining the necessary permissions or for any administrative actions related to the table.

Auto Loader is a tool that can incrementally and efficiently process new data files as they arrive in cloud storage without any additional setup. Auto Loader provides a Structured Streaming source called cloudFiles, which automatically detects and processes new files in a given input directory path on the cloud file storage. Auto Loader also tracks the ingestion progress and ensures exactly-once semantics when writing data into Delta Lake. Auto Loader can ingest various file formats, such as JSON, CSV, XML, PARQUET, AVRO, ORC, TEXT, and BINARYFILE. Auto Loader has support for both Python and SQL in Delta Live Tables, which are a declarative way to build production-quality data pipelines with Databricks. References: What is Auto Loader?, Get started with Databricks Auto Loader, Auto Loader in Delta Live Tables

A SQL Serverless Warehouse is the most suitable option for running SQL-based exploratory data analysis at scale while minimizing operational and cost overhead. Serverless SQL warehouses are fully managed by Databricks , meaning Databricks automatically provisions and scales compute resources, applies Photon acceleration , and optimizes query execution without requiring the user to manage infrastructure. This makes them ideal for workloads involving large numbers of ad hoc or interactive SQL queries , which is common during EDA. Because the data resides in a custom-defined cloud network , serverless SQL warehouses can still securely access governed data when Unity Catalog and networking requirements are satisfied. All-purpose clusters (option A) are designed for collaborative development and mixed workloads, not cost-efficient high-concurrency SQL querying. Classic SQL warehouses (option D) are legacy and lack the performance and cost optimizations of newer serverless offerings. Pro SQL warehouses (option B) require user-managed sizing decisions, whereas serverless warehouses optimize both performance and cost automatically. Databricks documentation recommends SQL Serverless Warehouses for fast, scalable, and cost-effective SQL analytics.

=========

Databricks separates workspace-management services from data processing. Notebook source files stored as workspace objects are managed in the Databricks control plane and are encrypted at rest. When the scheduled job starts, Databricks orchestrates the run from the control plane, but the notebook commands execute on compute resources in the compute plane. The compute plane is where Spark transformations, Python code, and other data-processing operations run. Unity Catalog governs data and AI assets but is not the execution environment or the general storage location for workspace notebook source. Delta Lake is a table-storage layer and does not execute notebook code. Option D is also incorrect because Databricks does not describe workspace notebook source as unencrypted. Therefore, option C accurately represents the separation between notebook storage and workload execution.

The MERGE command can be used to upsert data from a source table, view, or DataFrame into a target Delta table. It allows you to specify conditions for matching and updating existing records, and inserting new records when no match is found. This way, you can avoid writing duplicate records into a Delta table1. The other commands (DROP, IGNORE, APPEND, INSERT) do not have this functionality and may result in duplicate records or data loss234. References: 1: Upsert into a Delta Lake table using merge | Databricks on AWS 2: SQL DELETE | Databricks on AWS 3: SQL INSERT INTO | Databricks on AWS 4: SQL UPDATE | Databricks on AWS

To include a property indicating that a table contains personally identifiable information (PII), the TBLPROPERTIES keyword is used in SQL to add metadata to a table. The correct syntax to define a table property for PII is as follows:

CREATE TABLE customersInFrance

USING DELTA

TBLPROPERTIES ( ' PII ' = ' true ' )

AS

SELECT id,

firstName,

lastName

FROM customerLocations

WHERE country = ' FRANCE ' ;

The TBLPROPERTIES ( ' PII ' = ' true ' ) line correctly sets a table property that tags the table as containing personally identifiable information. This is in accordance with organizational policies for handling sensitive information.

The OPTIMIZE command in Delta Lake is designed to compact small files into larger files to improve query performance and reduce file system overhead. Importantly, OPTIMIZE is considered an idempotent operation when run on unchanged data. This means that once files have been compacted optimally, running OPTIMIZE again does not produce additional changes unless new small files have been added to the table. Databricks ensures that file compaction is efficient and avoids unnecessary rewrites, which helps control compute costs and maintain stable performance. While features like ZORDER can be applied during OPTIMIZE to improve data skipping, re-running the same command without new data will not further reorganize files. Options A, B, and C incorrectly imply additional restructuring or changes on repeated execution. According to Delta Lake best practices, OPTIMIZE should be run periodically after data ingestion events rather than repeatedly on unchanged datasets. This behavior reinforces predictable performance and efficient resource utilization in Databricks environments.

=========

A table update trigger is designed for this dependency. Databricks Lakeflow Jobs can monitor one or more supported Unity Catalog tables and start a job when the monitored tables are updated. Supported changes include data updates, merges, and deletes, so the Silver pipeline can begin only after the Bronze table receives new or changed data. This event-driven approach avoids polling and does not require a continuously running cluster. A continuous trigger keeps the job running rather than reacting to a completed table update. A scheduled trigger runs according to time, regardless of whether the Bronze table changed. A file arrival trigger monitors new files in a Unity Catalog storage location, not updates to an upstream table. Therefore, option A directly implements the required table-to-pipeline orchestration.

================

The best practice is to use " Complete " as output mode instead of " append " when working with aggregated tables. Since gold layer is work final aggregated tables, the only option with output mode as complete is option E.

Auto Loader provides a Structured Streaming source called cloudFiles that can process new data files as they arrive in cloud storage without any additional setup. Auto Loader uses a scalable key-value store to track ingestion progress and ensure exactly-once semantics. Auto Loader can ingest various file formats and load them into Delta Lake tables. Auto Loader is recommended for incremental data ingestion with Delta Live Tables, which extends the functionality of Structured Streaming and allows you to write declarative Python or SQL code to deploy a production-quality data pipeline. References: What is Auto Loader?, What is Auto Loader? | Databricks on AWS, Solved: How does Auto Loader ingest data? - Databricks - 5629

When migrating to Delta Live Tables (DLT) with a data pipeline that involves different programming languages across various data layers, the migration does not require unifying the pipeline into a single language. Delta Live Tables support multi-language pipelines, allowing data engineers and data analysts to work in their preferred languages, such as Python for data engineering tasks (raw, bronze, and silver layers) and SQL for data analytics tasks (gold layer). This capability is particularly beneficial in collaborative settings and leverages the strengths of each language for different stages of data processing.

 A serverless SQL endpoint is a compute resource that is automatically managed by Databricks and scales up or down based on the workload. A serverless SQL endpoint can be used to run queries and dashboards without requiring manual configuration or management. A serverless SQL endpoint is only active when it is needed and shuts down automatically when idle, minimizing the total running time and cost. A serverless SQL endpoint can be created and assigned to a dashboard using the Databricks SQL UI or the SQL Analytics API. References:

Create a serverless SQL endpoint

Assign a SQL endpoint to a dashboard

SQL Analytics API

Auto Loader is a Structured Streaming source that incrementally and efficiently processes new data files as they arrive in cloud storage. It supports both Python and SQL in Delta Live Tables, which are ideal for building streaming data pipelines. Auto Loader can handle near real-time ingestion of millions of files per hour and provide exactly-once guarantees when writing data into Delta Lake. Auto Loader is not designed for dashboard, machine learning, serverless, or batch workloads, which have different requirements and characteristics. References: What is Auto Loader?, Delta Live Tables

Databricks intentionally stops existing streaming reads and writes after an external Delta table is converted to a Unity Catalog managed table. This protects data consistency because the table’s managed storage location and access path have changed. The documented recovery action is to restart the stream with the same configuration. The existing checkpoint can then resume processing from the last committed offset, and supported path-based access is redirected to the converted managed table. REFRESH TABLE invalidates cached metadata but does not repair a stopped Structured Streaming query or modify its checkpoint. Deleting the checkpoint would discard progress information and could cause unnecessary reprocessing or duplicate output. Additional permissions are not the indicated solution when the failure is specifically caused by managed-table conversion. Therefore, option A is correct.

================

A materialized view is appropriate because it stores the results of a defining query and refreshes them as source data changes. Expensive joins and aggregations can therefore be computed before BI users execute their queries, improving response time and providing a consistent definition of business metrics. This aligns with the Gold layer, which normally contains highly refined, aggregated, and business-oriented datasets designed for reporting and analytics. A view over raw Bronze tables would expose unvalidated source data and repeatedly execute its underlying transformations. A streaming table is more suitable for incremental data processing than for presenting precomputed business metrics. CSV files would not provide the managed table semantics, centralized definitions, or efficient query experience required. Therefore, option A best satisfies the Gold-layer requirements.

================

 In a medallion architecture, a common data design pattern for lakehouses, data flows from Bronze to Silver to Gold layer tables, with each layer progressively improving the structure and quality of data. Bronze tables store raw data ingested from various sources, while Silver tables apply minimal transformations and cleansing to create an enterprise view of the data. Silver tables can also join and enrich data from different Bronze tables to provide a more complete and consistent view of the data. Therefore, option D is the correct answer, as Silver tables contain a more refined and cleaner view of data than Bronze tables. Option A is incorrect, as it is the opposite of the correct answer. Option B is incorrect, as Silver tables do not necessarily contain aggregates, but can also store detailed records. Option C is incorrect, as Silver tables may contain less data than Bronze tables, depending on the transformations and cleansing applied. Option E is incorrect, as Silver tables may contain more data than Bronze tables, depending on the joins and enrichments applied. References: What is a Medallion Architecture?, Transforming Bronze Tables in Silver Tables, What is the medallion lakehouse architecture?

Cron syntax is a tool that can be used to represent and submit a complex run schedule programmatically. Cron syntax is a string of six fields that specify the frequency, date, and time of a job run. For example, the cron expression 0 0 12 * * ? means run the job at 12:00 PM every day. The data engineer can use the Databricks REST API to create or update a job with a cron schedule. The data engineer can also use the Databricks CLI to create or update a job with a cron schedule by using a JSON file that contains the cron expression. The other tools are either invalid or not suitable for representing and submitting a complex run schedule programmatically. References: Schedule a job, Jobs API, Databricks CLI, Cron expressions

The Databricks web application is the user interface that allows you to create and manage workspaces, clusters, notebooks, jobs, and other resources. It is hosted completely in the control plane of the classic Databricks architecture, which includes the backend services that Databricks manages in your Databricks account. The other options are part of the compute plane, which is where your data is processed by compute resources such as clusters. The compute plane is in your own cloud account and network. References: Databricks architecture overview, Security and Trust Center QUESTION NO: 4

Which of the following benefits of using the Databricks Lakehouse Platform is provided by Delta Lake?

A. The ability to manipulate the same data using a variety of languages

B. The ability to collaborate in real time on a single notebook

C. The ability to set up alerts for query failures

D. The ability to support batch and streaming workloads

E. The ability to distribute complex data operations

Answer: D

Delta Lake is the optimized storage layer that provides the foundation for storing data and tables in the Databricks lakehouse. Delta Lake is fully compatible with Apache Spark APIs, and was developed for tight integration with Structured Streaming, allowing you to easily use a single copy of data for both batch and streaming operations and providing incremental processing at scale1. Delta Lake supports upserts using the merge operation, which enables you to efficiently update existing data or insert new data into your Delta tables2. Delta Lake also provides time travel capabilities, which allow you to query previous versions of your data or roll back to a specific point in time3. References: 1: What is Delta Lake? | Databricks on AWS 2: Upsert into a table using merge | Databricks on AWS 3: [Query an older snapshot of a table (time travel) | Databricks on AWS]

Learn more

The data engineering team can use the spark.table method to access the Delta table sales in PySpark. This method returns a DataFrame representation of the Delta table, which can be used for further processing or testing. The spark.table method works for any table that is registered in the Hive metastore or the Spark catalog, regardless of the file format1. Alternatively, the data engineering team can also use the DeltaTable.forPath method to load the Delta table from its path2. References: 1: SparkSession | PySpark 3.2.0 documentation 2: Welcome to Delta Lake’s Python documentation page — delta-spark 2.4.0 documentation

Auto Loader in Databricks utilizes Spark Structured Streaming for processing data incrementally. This allows Auto Loader to efficiently ingest streaming or batch data at scale and to recognize new data as it arrives in cloud storage. Spark Structured Streaming provides the underlying engine that supports various incremental data loading capabilities like schema inference and file notification mode, which are crucial for the dynamic nature of data lakes.

For large-scale batch ETL workloads , Databricks documentation recommends using job clusters that are created specifically for a job run and terminated automatically when the job completes. A job cluster with autoscaling enabled provides the best balance of performance, reliability, and cost efficiency for long-running nightly pipelines. Autoscaling allows the cluster to dynamically add or remove worker nodes based on the workload demands, ensuring sufficient parallelism to process very large JSON datasets efficiently while avoiding overprovisioning when demand decreases. This is especially important when processing raw data into Delta tables, where shuffle-heavy transformations and writes benefit from multiple workers. Single-node clusters (option B) are not suitable for very large volumes of data and risk excessive runtimes or job failures. High-concurrency clusters (option C) are optimized for interactive and concurrent SQL queries, not long-running batch ETL jobs. Always-on all-purpose clusters (option D) increase costs unnecessarily and are intended for interactive development, not scheduled production pipelines. Databricks best practices clearly position autoscaling job clusters as the preferred solution for reliable, cost-effective batch ETL in production on Databricks.

The command databricks bundle deploy deploys the bundle’s local files, artifacts, and resource definitions to the configured remote Databricks workspace. Databricks documents this command as the deployment operation for Declarative Automation Bundles, and a specific target can be selected with the -t or --target option. The validate command checks whether the bundle configuration is valid but does not create or update workspace resources. The run command executes a resource that has already been deployed; it is not the deployment step. The init command creates a new bundle project from a template, while bundle deployment is not a valid command combination. Because the question asks for the single command that sends local bundle assets to the target workspace, option C is correct.

================

Option A enables predictive optimization, which is designed to eliminate manual maintenance scheduling for Unity Catalog managed tables. Databricks evaluates table activity and automatically runs operations such as OPTIMIZE, VACUUM, and ANALYZE when beneficial, using serverless jobs compute. Predictive optimization can be enabled at multiple hierarchy levels, allowing tables to inherit the applicable setting. Auto Loader schema evolution handles changes in incoming file schemas but does not schedule table optimization. Liquid clustering improves data organization and query pruning, but it does not independently provide automated stale-file removal. Optimized writes and auto compaction reduce the creation of small files during write operations, but they do not replace adaptive VACUUM scheduling and the broader maintenance capabilities of predictive optimization. Therefore, option A directly satisfies the requirement for platform-managed table maintenance.

================

dbfs:/user/hive/warehouse Thereby showing " dbfs:/user/hive/warehouse/customer360.db

 The location of the customer360 database depends on the value of the spark.sql.warehouse.dir configuration property, which specifies the default location for managed databases and tables. If the property is not set, the default value is dbfs:/user/hive/warehouse. Therefore, the customer360 database will be located in dbfs:/user/hive/warehouse/customer360.db. However, if the property is set to a different value, such as dbfs:/user/hive/database, then the customer360 database will be located in dbfs:/user/hive/database/customer360.db. Thus, more information is needed to determine the correct response.

Option A is not correct, as dbfs:/user/hive/database/customer360 is not the default location for managed databases and tables, unless the spark.sql.warehouse.dir property is explicitly set to dbfs:/user/hive/database.

Option B is not correct, as dbfs:/user/hive/warehouse is the default location for the root directory of managed databases and tables, not for a specific database. The database name should be appended with .db to the directory path, such as dbfs:/user/hive/warehouse/customer360.db.

Option C is not correct, as dbfs:/user/hive/customer360 is not a valid location for a managed database, as it does not follow the directory structure specified by the spark.sql.warehouse.dir property.

Databases and Tables

[Databricks Data Engineer Professional Exam Guide]

Delta Sharing is an open protocol developed by Databricks that enables secure, real-time sharing of data across organizations without requiring recipients to have a Databricks account. It is specifically designed for scenarios where external partners need read-only access to data stored in Delta format. With Delta Sharing, data providers create a share and grant access to recipients via a secure mechanism (such as a profile file or URL-based access), ensuring that the data cannot be modified by the consumer. This approach eliminates the need for data duplication or manual file transfers, maintaining data freshness and governance. Option B is incorrect because Unity Catalog governs internal access within an organization, not external sharing without accounts. Option C is inefficient and breaks data lineage and freshness. Option D violates security best practices by granting excessive permissions. Delta Sharing ensures fine- grained access control, auditability, and secure external collaboration , making it the correct solution for this requirement.

=========

This question’s options do not include the most precise Unity Catalog answer, which would normally be the MANAGE privilege on the schema, because Databricks documents that MANAGE allows a principal to grant and revoke privileges on an object without automatically granting all data-access privileges on that object. However, since MANAGE is not one of the answer choices, the best available answer is D, Schema Owner . Databricks states that schema owners can grant and revoke permissions on the schema, and schema-level administration is a documented responsibility of the schema owner. Option A is incorrect because USE CATALOG and USE SCHEMA only allow object access/navigation and do not confer privilege-management rights. Option B is too broad because a catalog owner governs the entire catalog scope, not just one schema. Option C is too narrow because a table owner controls only one table, not various objects across a schema. So, among the choices provided, Schema Owner is the closest Databricks-aligned answer, though the cleanest real-world implementation would be MANAGE on the schema .

=========

The correct command to grant the necessary permission on the entire database to the new team is to use the GRANT USAGE command. The GRANT USAGE command grants the principal the ability to access the securable object, such as a database, schema, or table. In this case, the securable object is the database customers, and the principal is the group team. By granting usage on the database, the team will be able to see what tables already exist in the database. Option E is the only option that uses the correct syntax and the correct privilege type for this scenario. Option A uses the wrong privilege type (VIEW) and the wrong securable object (CATALOG). Option B uses the wrong privilege type (CREATE), which would allow the team to create new tables in the database, but not necessarily see the existing ones. Option C uses the wrong securable object (CATALOG) and the wrong principal (customers). Option D uses the wrong securable object (team) and the wrong principal (customers). References: GRANT, Privilege types, Securable objects, Principals

 Data Explorer is a graphical interface that allows you to browse, create, and manage data objects such as databases, tables, and views in your workspace. You can also review and modify the permissions on these data objects using Data Explorer. To access Data Explorer, you can click on the Data icon in the sidebar, or use the %sql magic command in a notebook. You can then select a database and a table, and click on the Permissions tab to view and edit the access control lists (ACLs) for the table. You can also use SQL commands such as SHOW GRANT and GRANT to query and modify the permissions on a Delta table. References:

Data Explorer

Access control for Delta tables

SHOW GRANT

[GRANT]

Option D satisfies the requirements because a Lakeflow Connect managed SaaS connector provides source-specific authentication, incremental ingestion, schema evolution, and automated retries as managed capabilities. Managed connectors use serverless infrastructure and publish governed destination tables that can be processed downstream with Lakeflow Spark Declarative Pipelines. This avoids custom code for OAuth token handling, API pagination, CDC state management, retry behavior, and schema-change recovery. Option A still requires the engineering team to implement and maintain CDC and operational logic. Options B and C place most authentication, schema evolution, change tracking, and failure recovery responsibilities in custom notebooks or jobs, conflicting with the minimal-maintenance requirement. Therefore, the fully managed connector described in option D is the only solution that delivers the requested ingestion and operational behavior as an integrated platform capability.

================

 To grant full permissions on a table to a user or a group, you can use the GRANT ALL PRIVILEGES ON TABLE statement. This statement will grant all the possible privileges on the table, such as SELECT, CREATE, MODIFY, DROP, ALTER, etc. Option A is the only code block that follows this syntax correctly. Option B is incorrect, as it does not grant all the possible privileges on the table, but only a subset of them. Option C is incorrect, as it only grants the SELECT privilege on the table, which is not enough to fully manage the project. Option D is incorrect, as it grants the USAGE privilege on the table, which is not a valid privilege for tables. Option E is incorrect, as it grants all the privileges on the table team to the user or group sales, which is the opposite of what the question asks. References: Grant privileges on a table using SQL | Databricks on AWS, Grant privileges on a table using SQL - Azure Databricks, SQL Privileges - Databricks

Option A is the correct answer because it shows the result of an INNER JOIN between the two tables. An INNER JOIN returns only the rows that have matching values in both tables based on the join condition. In this case, the join condition is ON a.customer_id = c.customer_id, which means that only the rows that have the same customer ID in both tables will be included in the output. The output will have four columns: customer_id, name, account_id, and overdraft_amt. The output will have four rows, corresponding to the four customers who have accounts in the account table.

The use of INNER JOIN can be referenced from Databricks documentation on SQL JOIN or from other sources like W3Schools or GeeksforGeeks.

 In Databricks, you can use different languages within the same notebook by using magic commands. Magic commands are special commands that start with a percentage sign (%) and allow you to change the behavior of the cell. To use SQL within a cell of a Python notebook, you can add %sql to the first line of the cell. This will tell Databricks to interpret the rest of the cell as SQL code and execute it against the default database. You can also specify a different database by using the USE statement. The result of the SQL query will be displayed as a table or a chart, depending on the output mode. You can also assign the result to a Python variable by using the -o option. For example, %sql -o df SELECT * FROM my_table will run the SQL query and store the result as a pandas DataFrame in the Python variable df. Option A is incorrect, as it is possible to use SQL in a Python notebook using magic commands. Option B is incorrect, as attaching the cell to a SQL endpoint is not necessary and will not change the language of the cell. Option C is incorrect, as simply writing SQL syntax in the cell will result in a syntax error, as the cell will still be interpreted as Python code. Option E is incorrect, as changing the default language of the notebook to SQL will affect all the cells, not just one. References: Use SQL in Notebooks - Knowledge Base - Noteable, [SQL magic commands - Databricks], [Databricks SQL Guide - Databricks]

Pipeline A has a driver-side failure because collect() transfers every row to the driver process. Databricks states that collect() should be used only when the result is expected to be small. For a 100 GB dataset, the preferred correction is to remove the full collection and use distributed processing, aggregation, sampling, or limit; increasing driver capacity alone is not a durable solution. Pipeline B is an executor-side problem during a shuffle-intensive join. Increasing spark.sql.shuffle.partitions can divide the shuffled data into smaller task partitions, while workers with more memory per core can provide additional executor memory. Increasing the number of executors does not solve Pipeline A’s driver bottleneck, and increasing driver memory does not address Pipeline B’s executor failures. Therefore, option D correctly distinguishes the affected Spark components and appropriate remedies.

A data engineer can create a multi-task job in Databricks that consists of multiple tasks that run in a specific order. Each task can have one or more dependencies, which are other tasks that must run before the current task. The Depends On field of a new Databricks Job Task allows the data engineer to specify the dependencies of the task. The data engineer should select a task in the Depends On field when they want the new task to run only after the selected task has successfully completed. This can help the data engineer to create a logical sequence of tasks that depend on each other’s outputs or results. For example, a data engineer can create a multi-task job that consists of the following tasks:

Task A: Ingest data from a source using Auto Loader

Task B: Transform the data using Spark SQL

Task C: Write the data to a Delta Lake table

Task D: Analyze the data using Spark ML

Task E: Visualize the data using Databricks SQL

In this case, the data engineer can set the dependencies of each task as follows:

Task A: No dependencies

Task B: Depends on Task A

Task C: Depends on Task B

Task D: Depends on Task C

Task E: Depends on Task D

This way, the data engineer can ensure that each task runs only after the previous task has successfully completed, and the data flows smoothly from ingestion to visualization.

The other options are incorrect because they do not describe valid scenarios for selecting a task in the Depends On field. The Depends On field does not affect the following aspects of a task:

Whether the task needs to be replaced by another task

Whether the task needs to fail before another task begins

Whether the task has the same dependency libraries as another task

Whether the task needs to use as little compute resources as possible References: Create a multi-task job, Run tasks conditionally in a Databricks job, Databricks Jobs.

An If/else condition task is specifically designed to branch a Lakeflow Job based on a Boolean expression. The condition can reference the upstream value as {{tasks.check_volume.values.record_count}} and compare it with 1000000. The true branch can invoke high_volume_pipeline, while the false branch invokes normal_pipeline. Run if conditions evaluate task outcomes such as success or failure; they do not compare a calculated record count. A For each task iterates over an input array and is not appropriate for selecting one of two execution paths. Retries are intended to recover from unsuccessful task attempts, not to wait for a business metric to cross a threshold. Therefore, option A implements the required value-based branching. This question was retained after comparison with the existing Word question bank.

================

In Delta Live Tables (DLT), when configured to run in Continuous Pipeline Mode, particularly in a production environment, the system is designed to continuously process and update data as it becomes available. This mode keeps the compute resources active to handle ongoing data processing and automatically updates all datasets defined in the pipeline at predefined intervals. Once the pipeline is manually stopped, the compute resources are terminated to conserve resources and reduce costs. This mode is suitable for production environments where datasets need to be kept up-to-date with the latest data.

 A streaming live table or view processes data that has been added only since the last pipeline update. Streaming tables and views are stateful; if the defining query changes, new data will be processed based on the new query and existing data is not recomputed. This is useful when data needs to be processed incrementally, such as when ingesting streaming data sources or performing incremental loads from batch data sources. A live table or view, on the other hand, may be entirely computed when possible to optimize computation resources and time. This is suitable when data needs to be processed in full, such as when performing complex transformations or aggregations that require scanning all the data. References: Difference between LIVE TABLE and STREAMING LIVE TABLE, CREATE STREAMING TABLE, Load data using streaming tables in Databricks SQL.

According to the medallion lakehouse architecture, gold tables are the final layer of data that powers analytics, machine learning, and production applications. They are often highly refined and aggregated, containing data that has been transformed into knowledge, rather than just information. Silver tables, on the other hand, are the intermediate layer of data that represents a validated, enriched version of the raw data from the bronze layer. They provide an enterprise view of all its key business entities, concepts and transactions, but they may not have all the aggregations and calculations that are required for specific use cases. Therefore, gold tables are more likely to contain aggregations than silver tables. References:

What is the medallion lakehouse architecture?

What is a Medallion Architecture?

Option D retains only orders whose customer identifier matches a row in customers_df. Because customers_df contains only active customers, an inner join acts as a membership filter: orders belonging to active customers match and remain, while orders belonging to inactive customers have no corresponding row and are removed. A left join with orders_df on the left would preserve every order, including orders from inactive customers, unless an additional NULL filter were applied. A left join with customers_df on the left changes the driving dataset and can exclude the required order structure or produce different cardinality. A cross join generates the Cartesian product of both DataFrames and is unrelated to key-based filtering. Therefore, the inner join in option D precisely implements the stated active-customer filtering requirement.

In Python, the == operator is used to compare the values of two variables, while the = operator is used to assign a value to a variable. Therefore, option A and E are incorrect, as they use the = operator for comparison. Option B and C are also incorrect, as they compare the review_period variable to a string value  " True " , which is different from the boolean value True. Option D is the correct answer, as it uses the == operator to compare the day_of_week variable to the integer value 1, and the and operator to check if both conditions are true. If both conditions are true, then the final block of the Python program will be executed. References: [Python Operators], [Python If … Else]

In the Medallion Architecture promoted by Databricks, the Bronze layer represents the raw ingestion layer where data is stored in its original form with minimal transformation. The primary goal is to ensure data fidelity and traceability , not to apply business logic or heavy transformations. Typical operations in the Bronze layer include schema inference (or enforcement), ingestion of raw data, and the addition of metadata columns such as ingestion timestamp, load date/time, and process identifiers. These metadata fields help track lineage, enable debugging, and support incremental processing downstream. Extensive data cleansing (A), aggregations (B), and business transformations (C) are intentionally deferred to the Silver and Gold layers , where data is refined and structured for analytics. By keeping Bronze data close to the source and append-only, organizations maintain a reliable audit trail and can reprocess data if needed. Therefore, adding operational metadata like load timestamps and process IDs is the correct characteristic of Bronze layer transformations.

=========

When Automatically detect column types is disabled during file upload, Databricks does not infer specialized data types from the CSV values. Instead, every uploaded column is created as STRING. This behavior prevents values that resemble numbers, dates, or Boolean values from being automatically assigned potentially incorrect types. The engineer can subsequently transform or cast individual columns into the required target types as part of a controlled Silver-layer process. Option B is incorrect because partial numeric inference does not occur when automatic detection is disabled. Option C is incorrect because Databricks can create the table without manually supplied types by using STRING for every column. Option D is also incorrect because disabling detection determines the schema behavior for the table creation operation. Therefore, option A accurately describes the result.

================

Lakeflow Jobs provides task-level Run if conditions such as All succeeded, All done, At least one failed, and All failed. No single Run if condition directly represents “A succeeded and B failed” when Task C depends directly on both tasks. The required outcome can be modeled with two guarded dependency branches: one gateway proceeds only after Task A succeeds, and another proceeds only after Task B fails. Task C then uses All succeeded against those two successful gateway tasks. A negative dependency is not a supported dependency type. Task values pass arbitrary application values and do not automatically represent task outcomes. Notebook widgets are task parameters rather than dependency-state conditions. Therefore, the two-chain configuration described in option B correctly represents the required conjunction.

================

In Databricks Asset Bundles , the databricks.yml file located at the repository root serves as the single authoritative configuration file for the entire project. This file defines key elements such as the bundle name, deployment targets (for example, dev, test, prod), workspace configurations, permissions, and resource definitions including jobs and pipelines. Databricks documentation specifies that there must be one primary databricks.yml file at the root , ensuring consistency and simplifying deployment processes. However, to support modularity and maintainability, this root configuration file can reference additional YAML files using the include mapping. This allows teams to organize configurations across directories while still maintaining a single source of truth. Option A is incorrect because multiple root-level configuration files are not supported as independent authorities. Option C is invalid since configuration files are not restricted to hidden directories. Option D introduces ambiguity and contradicts the requirement for a single authoritative configuration. Therefore, using one root-level configuration file with optional includes is the correct and recommended approach.

For small-scale, ad hoc Python workloads that run frequently and should start quickly and stop without leaving idle infrastructure running, serverless compute is the best fit. Databricks documentation explains that serverless compute allows users to run workloads without provisioning compute resources manually , and Databricks automatically allocates and manages the required resources. This improves startup and scaling speed and minimizes idle time, which directly matches the requirement that compute should “wind down quickly” after the work completes. Databricks also recommends serverless compute broadly when the workload is supported because it is the simplest and most reliable option. An all-purpose cluster is better suited to interactive collaborative development and can remain running longer than needed. A job cluster is ephemeral and often a good batch choice, but it still requires more explicit configuration than serverless and is not as optimized for minimal operational overhead. A SQL warehouse is intended for SQL workloads, not general ad hoc Python script execution. Therefore, serverless compute is the most appropriate choice.

A data lakehouse is a data management architecture that combines the flexibility, cost-efficiency, and scale of data lakes with the data management and ACID transactions of data warehouses, enabling business intelligence (BI) and machine learning (ML) on all data12. One of the key features of a data lakehouse is that it supports ACID-compliant transactions, which means that it ensures data integrity, consistency, and isolation across concurrent read and write operations3. This feature results in improved data quality over a traditional data lake, which does not support transactions and may suffer from data corruption, duplication, or inconsistency due to concurrent or streaming data ingestion and processing . References: 1: What is a Data Lakehouse? - Databricks 2: What is a Data Lakehouse? Definition, features & benefits. - Qlik 3: ACID Transactions - Databricks : [Data Lake vs Data Warehouse: Key Differences] : [Data Lakehouse: The Future of Data Engineering]