Databricks-Certified-Professional-Data-Engineer Databricks Certified Data Engineer Professional Exam Questions and Answers

This is the correct answer because it describes how data will be filtered when a query is run with the following filter: longitude < 20 and longitude > -20. The query is run on a Delta Lake table that has the following schema: user_id LONG, post_text STRING, post_id STRING, longitude FLOAT, latitude FLOAT, post_time TIMESTAMP, date DATE. This table is partitioned by the date column. When a query is run on a partitioned Delta Lake table, Delta Lake uses statistics in the Delta Log to identify data files that might include records in the filtered range. The statistics include information such as min and max values for each column in each data file. By using these statistics, Delta Lake can skip reading data files that do not match the filter condition, which can improve query performance and reduce I/O costs. Verified References: [Databricks Certified Data Engineer Professional], under “Delta Lake” section; Databricks Documentation, under “Data skipping” section.

Option B correctly fills in the blank to meet the specified requirements. Option B uses the “cloudFiles.schemaLocation” option, which is required for the schema detection and evolution functionality of Databricks Auto Loader. Additionally, option B uses the “mergeSchema” option, which is required for the schema evolution functionality of Databricks Auto Loader. Finally, option B uses the “writeStream” method, which is required for the incremental processing of JSON files as they arrive in a source directory. The other options are incorrect because they either omit the required options, use the wrong method, or use the wrong format. References:

Configure schema inference and evolution in Auto Loader : https://docs.databricks.com/en/ingestion/auto-loader/schema.html

Write streaming data : https://docs.databricks.com/spark/latest/structured-streaming/writing-streaming-data.html

The best practice in such scenarios is to ensure that production data is handled securely and with proper access controls. By granting only read access to production data in development and testing environments, it mitigates the risk of unintended data modification. Additionally, maintaining isolated databases for different environments helps to avoid accidental impacts on production data and systems.

This is the correct answer because it is where in the Spark UI one can diagnose a performance problem induced by not leveraging predicate push-down. Predicate push-down is an optimization technique that allows filtering data at the source before loading it into memory or processing it further. This can improve performance and reduce I/O costs by avoiding reading unnecessary data. To leverage predicate push-down, one should use supported data sources and formats, such as Delta Lake, Parquet, or JDBC, and use filter expressions that can be pushed down to the source. To diagnose a performance problem induced by not leveraging predicate push-down, one can use the Spark UI to access the Query Detail screen, which shows information about a SQL query executed on a Spark cluster. The Query Detail screen includes the Physical Plan, which is the actual plan executed by Spark to perform the query. The Physical Plan shows the physical operators used by Spark, such as Scan, Filter, Project, or Aggregate, and their input and output statistics, such as rows and bytes. By interpreting the Physical Plan, one can see if the filter expressions are pushed down to the source or not, and how much data is read or processed by each operator. Verified References: [Databricks Certified Data Engineer Professional], under “Spark Core” section; Databricks Documentation, under “Predicate pushdown” section; Databricks Documentation, under “Query detail page” section.

For secure, air-gapped Databricks deployments , the recommended practice is to host dependency files such as .whl packages in Unity Catalog Volumes — a managed storage layer governed by Unity Catalog.

Once stored in a volume, these files can be safely referenced from cluster-scoped init scripts , which automatically execute installation commands (e.g., pip install /Volumes/catalog/schema/path/PyYAML.whl) during cluster startup.

This ensures consistent environment setup across clusters and compliance with data governance rules.

User directories (A) lack enterprise security controls; private repositories (C) are not viable in air-gapped setups; and Git repos (D) do not trigger package installation. Therefore, B is the correct and officially approved method.

Databricks provides system tables under the system.billing and system.access schemas for auditing and cost attribution across SQL Warehouses. The best practice is to build a dashboard in Databricks SQL using these system tables and configure it with a daily refresh schedule . This ensures automated, up-to-date reporting of compute consumption, user activity, and cost distribution. Sharing this dashboard with executives provides transparency without requiring them to run queries manually. This approach aligns with Databricks’ operational guidance on workspace observability and cost governance , making B the correct answer.

Unity Catalog in Databricks provides the ability to attach tags (key-value metadata pairs) to securable objects such as catalogs, schemas, tables, volumes, and functions. Tags are critical for governance, compliance, and automation, as they allow organizations to track metadata like sensitivity, ownership, business purpose, and retention policies directly at the object level.

According to the official Databricks SQL reference for Unity Catalog, the correct way to programmatically add tags to a table is by using the ALTER TABLE … SET TAGS command. The syntax is:

ALTER TABLE table_name SET TAGS ( ' tag_name ' = ' tag_value ' , ...);

This command can be used within ETL workflows or jobs to automatically apply metadata during or after ingestion, ensuring that governance and compliance rules are embedded in the pipeline itself.

Option A is correct because it uses the supported syntax for applying tags.

Option B (APPLY TAGS) is not valid SQL in Unity Catalog and is not recognized by Databricks.

Option C confuses COMMENT with TAGS. While COMMENT can add descriptive text to a table, it does not handle tags.

Option D (SET TAGS FOR) is not a valid SQL construct in Databricks for applying tags.

Thus, Option A is the only valid and documented way to programmatically set tags on a table in Unity Catalog.

The provided MERGE statement is a classic implementation of a Type 2 SCD in a data warehousing context. In this approach, historical data is preserved by keeping old records (marking them as not current) and adding new records for changes. Specifically, when a match is found and there ' s a change in the address, the existing record in the customers table is updated to mark it as no longer current ( current = false ), and an end date is assigned ( end_date = staged_updates.effective_date ). A new record for the customer is then inserted with the updated information, marked as current. This method ensures that the full history of changes to customer information is maintained in the table, allowing for time-based analysis of customer data.

This is the correct answer because Databricks has a feature called Auto Optimize, which automatically optimizes the layout of Delta Lake tables by coalescing small files into larger ones and sorting data within each file by a specified column. However, Auto Optimize also considers the trade-off between file size and merge performance, and may choose a smaller target file size to reduce the duration of merge operations, especially for streaming workloads that frequently update existing records. Therefore, it is possible that Auto Optimize has autotuned to a smaller target file size based on the characteristics of the streaming production job. Verified References: [Databricks Certified Data Engineer Professional], under “Delta Lake” section; Databricks Documentation, under “Auto Optimize” section. https://docs.databricks.com/en/delta/tune-file-size.html#autotune-table ' Autotune file size based on workload '

This is the correct answer because the code uses the dropDuplicates method to remove any duplicate records within each batch of data before writing to the orders table. However, this method does not check for duplicates across different batches or in the target table, so it is possible that newly written records may have duplicates already present in the target table. To avoid this, a better approach would be to use Delta Lake and perform an upsert operation using mergeInto. Verified References: [Databricks Certified Data Engineer Professional], under “Delta Lake” section; Databricks Documentation, under “DROP DUPLICATES” section.

According to Databricks Delta Lake optimization documentation, Liquid Clustering is a next-generation file organization capability that automatically manages file co-location without requiring explicit partitioning or manual Z-ORDERing. When combined with Predictive Optimization , Databricks automatically maintains clustering across frequently filtered or queried columns, adapting dynamically as query workloads evolve.

This approach eliminates the need for manual maintenance (such as periodic OPTIMIZE or Z-ORDER commands) while improving query performance on large tables—particularly for high-ingest streaming workloads.

Delta caching (B) only improves performance for cached queries and does not address file layout issues, and (D) handles file size optimization but not clustering. Thus, C is the most efficient, modern, and low-maintenance solution recommended by Databricks.

Exact extract: “dropDuplicates with watermark performs stateful deduplication on the keys within the watermark delay.”

Exact extract: “Records older than the event-time watermark are considered late and may be dropped.”

https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.broadcast.html

The provided PySpark code performs the following operations:

Reads Data from silver_customer_sales Table:

The code starts by accessing the silver_customer_sales table using the spark.table method.

Groups Data by customer_id:

The .groupBy( " customer_id " ) function groups the data based on the customer_id column.

Aggregates Data:

The .agg() function computes several aggregate metrics for each customer_id:

F.min( " sale_date " ).alias( " first_transaction_date " ): Determines the earliest sale date for the customer.

F.max( " sale_date " ).alias( " last_transaction_date " ): Determines the latest sale date for the customer.

F.mean( " sale_total " ).alias( " average_sales " ): Calculates the average sale amount for the customer.

F.countDistinct( " order_id " ).alias( " total_orders " ): Counts the number of unique orders placed by the customer.

F.sum( " sale_total " ).alias( " lifetime_value " ): Calculates the total sales amount (lifetime value) for the customer.

Writes Data to gold_customer_lifetime_sales_summary Table:

The .write.mode( " overwrite " ).table( " gold_customer_lifetime_sales_summary " ) command writes the aggregated data to the gold_customer_lifetime_sales_summary table.

The mode( " overwrite " ) specifies that the existing data in the gold_customer_lifetime_sales_summary table will be completely replaced by the new aggregated data.

Conclusion:

When this code is executed, it reads all records from the silver_customer_sales table, performs the specified aggregations grouped by customer_id, and then overwrites the entire gold_customer_lifetime_sales_summary table with the aggregated results. Therefore, option D accurately describes this process: " The gold_customer_lifetime_sales_summary table will be overwritten by aggregated values calculated from all records in the silver_customer_sales table as a batch job. "

This is the correct answer because it is the REST API call that can be used to review the notebooks configured to run as tasks in a multi-task job. The REST API is an interface that allows programmatically interacting with Databricks resources, such as clusters, jobs, notebooks, or tables. The REST API uses HTTP methods, such as GET, POST, PUT, or DELETE, to perform operations on these resources. The /jobs/get endpoint is a GET method that returns information about a job given its job ID. The information includes the job settings, such as the name, schedule, timeout, retries, email notifications, and tasks. The tasks are the units of work that a job executes. A task can be a notebook task, which runs a notebook with specified parameters; a jar task, which runs a JAR uploaded to DBFS with specified main class and arguments; or a python task, which runs a Python file uploaded to DBFS with specified parameters. A multi-task job is a job that has more than one task configured to run in a specific order or in parallel. By using the /jobs/get endpoint, one can review the notebooks configured to run as tasks in a multi-task job. Verified References: [Databricks Certified Data Engineer Professional], under “Databricks Jobs” section; Databricks Documentation, under “Get” section; Databricks Documentation, under “JobSettings” section.

Exact extract: “select() expects column names or Column expressions.”

Exact extract: “When using strings directly, Spark SQL interprets them as literal column names.”

Exact extract: “Python string operations, such as " colname " *3, return repeated strings, not column expressions.”

The expression 3* " heartrate " is Python string multiplication, which evaluates to " heartrateheartrateheartrate " . The select() method interprets this as a literal column name. Since there is no column with that name in the DataFrame schema, Spark raises AnalysisException saying it cannot resolve that column. To correctly multiply a column by a scalar, one must use the column expression form:

from pyspark.sql.functions import col

df.select((col( " heartrate " ) * 3).alias( " heartrate_x3 " ))

This ensures Spark evaluates the arithmetic operation on the column instead of misinterpreting the string.

In Databricks Repos, when a user does not have privileges to make changes directly to the main branch of a cloned remote Git repository, the recommended approach is to create a new branch within the Databricks workspace. The developer can then make changes in this new branch, commit those changes, and push the new branch to the remote Git repository. This workflow allows for isolated development without affecting the main branch, enabling the developer to propose changes via a pull request from the new branch to the main branch in the remote repository. This method adheres to common Git collaboration workflows, fostering code review and collaboration while ensuring the integrity of the main branch.

Lakeflow Declarative Pipelines (DLT) supports data quality enforcement using @dlt.expect, @dlt.expect_or_drop, and @dlt.expect_all.

@dlt.expect applies a rule and records whether rows pass or fail the condition but does not drop failing rows . Instead, failing rows can be written to a quarantine table.

@dlt.expect_or_drop enforces that only rows passing the condition flow downstream, dropping bad records automatically.

In this case, the requirement is:

Good rows (reading < 120) go to the silver table .

Bad rows (reading > = 120) go to a quarantine table .

Bad rows should not be included in silver.

The correct implementation is Option A , where:

The silver table uses @dlt.expect to validate reading < 120. These rows flow normally.

The quarantine table applies an expectation for reading > = 120, ensuring bad records are captured separately.

Other options are incorrect:

Option B/D: These either use expect_or_drop incorrectly or apply wrong conditions, leading to dropped rows without quarantining properly.

Option C: Uses expect_or_drop for both tables, which would discard bad rows instead of persisting them into a quarantine table.

Thus, Option A meets the business requirement to split good and bad data streams while ensuring both are captured for auditing and processing.

Databricks documents that expectation decorators are applied to datasets using decorators such as @dp.expect , @dp.expect_or_drop , and related variants. The expect_or_drop behavior drops records that violate the constraint before they are written to the target dataset. That matches the requirement exactly. ( Databricks Documentation )

Option D is the only answer that uses the documented decorator pattern correctly and applies drop semantics for both constraints. Options B and C use expect , which records the violation but does not drop invalid records. Option A is not the documented API pattern for Lakeflow expectations, because expectations are declared as decorators on the table or view definition rather than chained as DataFrame methods. ( Databricks Documentation )

======

QUESTION NO: 36

A data engineer is creating a data ingestion pipeline to understand where customers are taking their rented bicycles during use. The engineer noticed that over time, data being transmitted from the bicycle sensors fails to include key details like latitude and longitude. Downstream analysts need both the clean records and the quarantined records available for separate processing.

The data engineer already has this code:

import dlt

from pyspark.sql.functions import expr

rules = {

" valid_lat " : " (lat IS NOT NULL) " ,

" valid_long " : " (long IS NOT NULL) "

}

quarantine_rules = " NOT({0}) " .format( " AND " .join(rules.values()))

@dlt.view

def raw_trips_data():

return spark.readStream.table( " ride_and_go.telemetry.trips " )

How should the data engineer meet the requirements to capture good and bad data?

A.

@dlt.table(partition_cols=[ " is_quarantined " ])

@dlt.expect_all(rules)

def trips_data_quarantine():

return (

spark.readStream.table( " raw_trips_data " )

withColumn( " is_quarantined " , expr(quarantine_rules))

)

B.

@dlt.table

@dlt.expect_all_or_drop(rules)

def trips_data_quarantine():

return spark.readStream.table( " raw_trips_data " )

C.

@dlt.table(name= " trips_data_quarantine " )

def trips_data_quarantine():

return (

spark.readStream.table( " raw_trips_data " )

filter(expr(quarantine_rules))

)

D.

@dlt.view

@dlt.expect_or_drop( " lat_long_present " , " (lat IS NOT NULL AND long IS NOT NULL) " )

def trips_data_quarantine():

return spark.readStream.table( " ride_and_go.telemetry.trips " )

Answer: A

Databricks documents a quarantine pattern for Lakeflow Spark Declarative Pipelines in which you create a dataset containing both valid and invalid rows, add an is_quarantined flag based on your rule set, and then use that dataset for separate downstream processing paths. The documented pattern uses a boolean quarantine expression and preserves all rows while tracking quality metrics through expectations. ( Databricks Documentation )

Option A is the only choice that matches that documented design: it keeps all records, marks invalid rows with is_quarantined , and applies expectations to capture data quality metrics. Option B drops invalid rows, which fails the requirement to keep quarantined records available. Option C captures only bad rows and loses the clean path. Option D also drops invalid rows and therefore does not preserve both good and quarantined records for separate downstream use. ( Databricks Documentation )

======

QUESTION NO: 42

A data team is implementing an append-only Delta Lake pipeline that needs to handle both batch and streaming data. They want to ensure that schema changes in the source data can be automatically incorporated without breaking the pipeline. Which configuration should the team use when writing data to the Delta table?

A. validateSchema = false

B. ignoreChanges = false

C. overwriteSchema = true

D. mergeSchema = true

Answer: D

Databricks documents mergeSchema as the write option used to enable schema evolution when appending data to Delta tables. This allows new columns in the source to be automatically merged into the target schema rather than causing the write to fail. ( Databricks Documentation )

overwriteSchema is used with overwrite operations, not append-style schema evolution. validateSchema and ignoreChanges are not the correct configuration for this requirement. Because the pipeline is append-only and must tolerate source schema changes automatically, mergeSchema = true is the documented choice. ( Databricks Documentation )

======

QUESTION NO: 49

A data engineer needs to install the PyYAML Python package for YAML file processing within their Databricks environment. However, the Databricks workspace is air-gapped and does not have direct internet access to download packages from PyPI. The engineer has already downloaded the required PyYAML wheel ( .whl ) file onto their laptop. The data engineer wants to install the PyYAML package from the local wheel file so that it is automatically available whenever any new cluster is provisioned in their Databricks workspace. Which approach should the data engineer use?

A. Upload the PyYAML.whl file to a Unity Catalog volume. Add the path to the Unity Catalog allowlist if required. Then create a cluster-scoped init script that executes pip install /path/to/PyYAML.whl .

B. Set up a private PyPI repository, register the wheel there, and create a cluster-scoped init script that executes /databricks/python/bin/pip install --index-url=https://{repo-url} PyYAML on the cluster.

C. Upload the PyYAML.whl file under the data engineer’s user home directory in the Workspace, and create a cluster-scoped init script that executes %pip install /path/to/PyYAML.whl on the shared cluster.

D. Add the PyYAML.whl file directly to Databricks Git Repos and assume that any cluster linked to the Repo will automatically have PyYAML installed from that file.

Answer: A

Databricks documents that libraries and init scripts can be sourced from Unity Catalog volumes, and for standard access mode, relevant paths can require Unity Catalog allowlist configuration. Databricks also documents that init scripts run on every cluster startup, which is the mechanism that makes a package automatically available whenever a new cluster is provisioned. ( Databricks Documentation )

Option A fits the air-gapped requirement because it does not depend on internet access and uses a startup-time installation mechanism. Option B still depends on reachable repository infrastructure. Option C is incorrect because %pip is notebook magic, not the correct form inside an init script. Option D is unsupported because storing a wheel in Git Repos does not automatically install it on cluster creation. ( Databricks Documentation )

======

QUESTION NO: 51

A data engineer is tasked with ensuring that a Delta table in Databricks continuously retains deleted files for 15 days instead of the default 7 days, in order to comply with the organization’s data retention policy. Which code snippet correctly sets this retention period for deleted files?

A.

spark.sql( " " "

ALTER TABLE my_table

SET TBLPROPERTIES ( ' delta.deletedFileRetentionDuration ' = ' interval 15 days ' )

" " " )

B.

from delta.tables import *

deltaTable = DeltaTable.forPath(spark, " /mnt/data/my_table " )

deltaTable.deletedFileRetentionDuration = " interval 15 days "

C.

spark.sql( " VACUUM my_table RETAIN 15 HOURS " )

D.

spark.conf.set( " spark.databricks.delta.deletedFileRetentionDuration " , " 15 days " )

Answer: A

Databricks documents delta.deletedFileRetentionDuration as a Delta table property and shows that Delta table properties are modified with SET TBLPROPERTIES . The documented value format is an interval expression such as ' interval 7 days ' , so ' interval 15 days ' is the correct way to set the retention window on the table itself. ( Databricks Documentation )

Option C controls a specific VACUUM run and is shown in hours here, not as a persistent table-level retention setting. Option D sets a Spark configuration rather than the table property required by the question. Option B is not the documented API for setting this Delta retention property. ( Databricks Documentation )

======

QUESTION NO: 52

A data engineer is designing a data pipeline in Databricks that needs to process records from a Kafka stream where late-arriving data is common. Which approach should the data engineer use?

A. Use a watermark to specify the allowed lateness to accommodate records that arrive after their expected window, ensuring correct aggregation and state management.

B. Use an Auto CDC pipeline with batch tables to simplify late data handling.

C. Use batch processing and overwrite the entire output table each time to ensure late data is incorporated correctly.

D. Implement a custom solution using Databricks Jobs to periodically reprocess all historical data.

Answer: A

Databricks and Apache Spark document watermarks as the standard mechanism for handling late-arriving data in Structured Streaming. A watermark defines how long the engine should continue waiting for out-of-order event-time data and helps manage state for aggregations, joins, and deduplication. ( Databricks Documentation )

This directly matches Kafka streaming scenarios where lateness is common. The other options are not the standard streaming solution for event-time late data: Auto CDC is for change data capture use cases, and repeated full historical reprocessing is inefficient and unnecessary when watermarking is the built-in feature designed for this problem. ( Databricks Documentation )

======

The Query Profiler in Databricks SQL and notebooks provides a detailed breakdown of query performance metrics. The “Total wall-clock duration” metric directly represents the total elapsed time from query start to completion, including all execution, planning, and waiting stages. In contrast, “Aggregated task time” reflects the cumulative duration across all parallel tasks, which does not equal the total elapsed wall time since tasks often run concurrently. Using job duration from Spark UI can underestimate or overestimate runtime when queries span multiple jobs. Therefore, the Query Profiler’s total wall-clock duration is the officially documented method to determine actual query execution time.

Maintaining data quality rules in a centralized Delta table allows for the reuse of these rules across multiple DLT (Delta Live Tables) pipelines. By storing these rules outside the pipeline ' s target schema and referencing the schema name as a pipeline parameter, the team can apply the same set of data quality checks to different tables within the pipeline. This approach ensures consistency in data quality validations and reduces redundancy in code by not having to replicate the same rules in each DLT notebook or file.

In Structured Streaming, event-time watermarks control how long the engine waits for late-arriving data before finalizing aggregations. By setting an appropriate watermark, Databricks can handle late data gracefully — incorporating records that arrive within the defined window while discarding excessively delayed events.

This approach ensures accurate aggregations, minimizes state size, and prevents memory leaks.

Manual reprocessing (A) or overwriting entire datasets (B) is inefficient and costly, while Auto CDC (C) is used for change tracking in Delta tables, not for streaming event lateness.

Thus, using watermarking is the recommended and official approach for managing late data in streaming pipelines.

Scheduling a job to execute the data processing pipeline once an hour on a new job cluster is the most cost-effective solution given the scenario. Job clusters are ephemeral in nature; they are spun up just before the job execution and terminated upon completion, which means you only incur costs for the time the cluster is active. Since the total processing time is only 10 minutes, a new job cluster created for each hourly execution minimizes the running time and thus the cost, while also fulfilling the requirement for hourly data updates for the business reporting team ' s dashboards.

This is the correct answer because it describes a limitation of Databricks Secrets. Databricks Secrets is a module that provides tools to store sensitive credentials and avoid accidentally displaying them in plain text. Databricks Secrets allows creating secret scopes, which are collections of secrets that can be accessed by users or groups. Databricks Secrets also allows creating and managing secrets using the Databricks CLI or the Databricks REST API. However, a limitation of Databricks Secrets is that the Databricks REST API can be used to list secrets in plain text if the personal access token has proper credentials. Therefore, users should still be careful with which credentials are stored in Databricks Secrets and which users have access to using these secrets. Verified References: [Databricks Certified Data Engineer Professional], under “Databricks Workspace” section; Databricks Documentation, under “List secrets” section.

This is the correct answer because the code is using the dbutils.secrets.get method to retrieve the password from the secrets module and store it in a variable. The secrets module allows users to securely store and access sensitive information such as passwords, tokens, or API keys. The connection to the external table will succeed because the password variable will contain the actual password value. However, when printing the password variable, the string “redacted” will be displayed instead of the plain text password, as a security measure to prevent exposing sensitive information in notebooks. Verified References: [Databricks Certified Data Engineer Professional], under “Security and Governance” section; Databricks Documentation, under “Secrets” section.

This is the correct answer because it accurately informs this decision. The decision is about where the Databricks workspace used by the contractors should be deployed. The contractors are based in India, while all the company’s data is stored in regional cloud storage in the United States. When choosing a region for deploying a Databricks workspace, one of the important factors to consider is the proximity to the data sources and sinks. Cross-region reads and writes can incur significant costs and latency due to network bandwidth and data transfer fees. Therefore, whenever possible, compute should be deployed in the same region the data is stored to optimize performance and reduce costs. Verified References: [Databricks Certified Data Engineer Professional], under “Databricks Workspace” section; Databricks Documentation, under “Choose a region” section.

In Delta Lake, a shallow clone creates a new table by copying the metadata of the source table without duplicating the data files. When the vacuum command is run on the source table, it removes old data files that are no longer needed to maintain the transactional log ' s integrity, potentially including files referenced by the shallow clone ' s metadata. If these files are purged, the shallow cloned tables will reference non-existent data files, causing them to stop working properly. This highlights the dependency of shallow clones on the source table ' s data files and the impact of data management operations like vacuum on these clones.

This is the correct answer because it will allow the developer to update their local repository with the latest changes from the remote repository and switch to the desired branch. Pulling changes will not affect the current branch or create any conflicts, as it will only fetch the changes and not merge them. Selecting the dev-2.3.9 branch from the dropdown will checkout that branch and display its contents in the notebook. Verified References: [Databricks Certified Data Engineer Professional], under “Databricks Tooling” section; Databricks Documentation, under “Pull changes from a remote repository” section.

This is the correct answer because it describes the behavior of Delta Lake Auto Compaction, which is a feature that automatically optimizes the layout of Delta Lake tables by coalescing small files into larger ones. Auto Compaction runs as an asynchronous job after a write to a table has succeeded and checks if files within a partition can be further compacted. If yes, it runs an optimize job with a default target file size of 128 MB. Auto Compaction only compacts files that have not been compacted previously. Verified References: [Databricks Certified Data Engineer Professional], under “Delta Lake” section; Databricks Documentation, under “Auto Compaction for Delta Lake on Databricks” section.

" Auto compaction occurs after a write to a table has succeeded and runs synchronously on the cluster that has performed the write. Auto compaction only compacts files that haven’t been compacted previously. "

https://learn.microsoft.com/en-us/azure/databricks/delta/tune-file-size

Change Data Feed (CDF) allows downstream consumers to read incremental changes (inserts, updates, deletes) from a Delta table. The documentation explains that when streaming from a Delta table with CDF enabled, developers can use readStream().option( " readChangeFeed " , " true " ) to capture incremental events. For maintaining a derived table with enrichment logic, the recommended practice is to use apply_changes(), which applies CDC semantics (insert/update/delete) correctly to the target streaming table. By joining with the static employee dimension, enriched rows are generated before being merged into the new streaming target. This ensures correctness, scalability, and minimal latency. Batch reads or skipping commits do not maintain correctness for CDC pipelines.

The correct method to define a table using Lakeflow Declarative Pipelines (LDP) is with the @dlt.table decorator, which persists the output as a managed Delta table. When ingesting raw JSON data, spark.read.json() or spark.read.format( " json " ).load() is the standard approach. This reads JSON-formatted files from the source and stores them in Delta format automatically managed by Databricks.

Reference Source: Databricks Lakeflow Declarative Pipelines Developer Guide – “Create tables from raw JSON and Delta sources.”

Delta Lake outperforms legacy Hadoop formats because it leverages Parquet-based storage , data skipping , and file pruning . According to Databricks documentation, Delta Lake automatically stores detailed statistics (min/max values and file-level metadata) in the transaction log. During query planning, the engine uses these statistics to skip entire files that do not match query filters , a process called data skipping and file pruning . Additionally, Delta uses a vectorized Parquet reader , which reduces I/O and CPU overhead. Together, these optimizations allow Delta to scan significantly less data and produce more efficient physical query plans (e.g., Shuffle Hash Join instead of Sort Merge Join). The performance gain is due to efficient data skipping, not the inherent superiority of join type.

Lakeflow Declarative Pipelines (LDP), formerly Delta Live Tables (DLT), supports enforcing data quality using expectations . Expectations can either:

Track violations (expect) → records that do not meet conditions are flagged but still included in the pipeline.

Drop violations (expect_or_drop) → records that do not meet conditions are excluded from downstream tables.

Fail pipeline on violations (expect_or_fail) → records that fail conditions stop the pipeline.

In this scenario, the requirement explicitly states that invalid records (where customer_id is null or amount ≤ 0) must be dropped . According to the official documentation, the correct method is .expect_or_drop( " expectation_name " , " SQL_predicate " ) applied on the streaming input.

Option A is correct: It uses .expect_or_drop directly within the transformation chain for both rules, ensuring records that fail are removed before writing to the silver table.

Option B incorrectly uses @dlt.expect decorators, which only track violations but do not drop invalid rows.

Option C uses .expect, which also only flags rows, not drop them.

Option D uses @dlt.expect_or_drop decorator syntax, which is not supported in Python API; expect_or_drop must be applied as a method on the DataFrame, not as a decorator.

Therefore, the correct solution is Option A , which ensures compliance by enforcing data quality and dropping invalid rows programmatically during ingestion.

Converting the data to Delta Lake may not improve query performance on free text fields with high cardinality, such as the review column. This is because Delta Lake collects statistics on the minimum and maximum values of each column, which are not very useful for filtering or skipping data on free text fields. Moreover, Delta Lake collects statistics on the first 32 columns by default, which may not include the review column if the table has more columns. Therefore, the junior data engineer’s suggestion is not correct. A better approach would be to use a full-text search engine, such as Elasticsearch, to index and query the review column. Alternatively, you can use natural language processing techniques, such as tokenization, stemming, and lemmatization, to preprocess the review column and create a new column with normalized terms that can be used for filtering or skipping data. References:

Optimizations : https://docs.delta.io/latest/optimizations-oss.html

Full-text search with Elasticsearch : https://docs.databricks.com/data/data-sources/elasticsearch.html

Natural language processing : https://docs.databricks.com/applications/nlp/index.html

The code uses the DELETE FROM command to delete records from the users table that match a condition based on a join with another table called delete_requests, which contains all users that have requested deletion. The DELETE FROM command deletes records from a Delta Lake table by creating a new version of the table that does not contain the deleted records. However, this does not guarantee that the records to be deleted are no longer accessible, because Delta Lake supports time travel, which allows querying previous versions of the table using a timestamp or version number. Therefore, files containing deleted records may still be accessible with time travel until a vacuum command is used to remove invalidated data files from physical storage. Verified References: [Databricks Certified Data Engineer Professional], under “Delta Lake” section; Databricks Documentation, under “Delete from a table” section; Databricks Documentation, under “Remove files no longer referenced by a Delta table” section.

Deletion vectors (DVs) in Delta Lake optimize delete operations by marking deleted rows logically in metadata rather than rewriting Parquet files. When a DELETE statement is executed, affected rows are tracked by DVs in the transaction log. The data remains in the underlying files but is filtered out during query reads. This improves performance for frequent deletes and updates since file rewrites are deferred. Physical data removal only occurs when a VACUUM command is later executed. The Databricks documentation confirms: “With deletion vectors, deleted rows are marked in metadata and skipped at read time, avoiding file rewrites.” Thus, rows are marked as deleted in metadata—not in files.

When triggering a job run using the Databricks CLI, the run_id field in the response represents a globally unique identifier for that particular run of the job. This run_id is distinct from the job_id . While the job_id identifies the job definition and is constant across all runs of that job, the run_id is unique to each execution and is used to track and query the status of that specific job run within the Databricks environment. This distinction allows users to manage and reference individual executions of a job directly.

The Databricks documentation recommends applyInPandas() for complex per-group operations where maintaining internal state within each group is necessary. When using applyInPandas(), Spark provides all records for each grouping key as a Pandas DataFrame to the function, allowing efficient vectorized operations with local state management. This approach ensures high performance and scalability while maintaining logical isolation between groups. In contrast, SCALAR and SCALAR_ITER UDFs operate on individual rows or batches and cannot maintain inter-row state effectively. grouped_agg UDFs are limited to computing aggregates and do not support complex multi-row transformations. Therefore, applyInPandas() is the correct and Databricks-recommended solution for stateful per-group time-series computations.

Exact extract: “A shallow clone creates a copy of the metadata that references the source data files; it is fast and inexpensive.”

Exact extract: “A deep clone copies the data.”

This is the correct answer because it indicates a bottleneck caused by code executing on the driver. A bottleneck is a situation where the performance or capacity of a system is limited by a single component or resource. A bottleneck can cause slow execution, high latency, or low throughput. A production cluster has 3 executor nodes and uses the same virtual machine type for the driver and executor. When evaluating the Ganglia Metrics for this cluster, one can look for indicators that show how the cluster resources are being utilized, such as CPU, memory, disk, or network. If the overall cluster CPU utilization is around 25%, it means that only one out of the four nodes (driver + 3 executors) is using its full CPU capacity, while the other three nodes are idle or underutilized. This suggests that the code executing on the driver is taking too long or consuming too much CPU resources, preventing the executors from receiving tasks or data to process. This can happen when the code has driver-side operations that are not parallelized or distributed, such as collecting large amounts of data to the driver, performing complex calculations on the driver, or using non-Spark libraries on the driver. Verified References: [Databricks Certified Data Engineer Professional], under “Spark Core” section; Databricks Documentation, under “View cluster status and event logs - Ganglia metrics” section; Databricks Documentation, under “Avoid collecting large RDDs” section.

In a Spark cluster, the driver node is responsible for managing the execution of the Spark application, including scheduling tasks, managing the execution plan, and interacting with the cluster manager. If the overall cluster CPU utilization is low (e.g., around 25%), it may indicate that the driver node is not utilizing the available resources effectively and might be a bottleneck.

The error being raised is an AnalysisException, which is a type of exception that occurs when Spark SQL cannot analyze or execute a query due to some logical or semantic error 1 . In this case, the error message indicates that the query cannot resolve the column name ‘heartrateheartrateheartrate’ given the input columns ‘heartrate’ and ‘age’. This means that there is no column in the table named ‘heartrateheartrateheartrate’, and the query is invalid. A possible cause of this error is a typo or a copy-paste mistake in the query. To fix this error, the query should use a valid column name that exists in the table, such as ‘heartrate’. References:  AnalysisException

Lakeflow Declarative Pipelines provide the expect_all method for programmatically applying multiple data quality expectations at once. The documentation explains that @dlt.expect_all accepts a dictionary of expectation names mapped to SQL constraints, allowing rules to be dynamically loaded from metadata such as JSON files. This ensures that pipelines remain maintainable and scalable without needing to hardcode individual @dlt.expect decorators. The event logs will track each expectation’s pass and fail counts individually, making it auditable. Other options are incorrect: invoking an external API introduces unnecessary complexity, individual decorators require hardcoding, and SQL constraints cannot dynamically reference external JSON.

https://www.databricks.com/blog/2020/08/31/introducing-the-databricks-web-terminal.html

The code is using %sh to execute shell code on the driver node. This means that the code is not taking advantage of the worker nodes or Databricks optimized Spark. This is why the code is taking longer to execute. A better approach would be to use Databricks libraries and APIs to read and write data from Git and DBFS, and to leverage the parallelism and performance of Spark. For example, you can use the  Databricks Connect  feature to run your Python code on a remote Databricks cluster, or you can use the  Spark Git Connector  to read data from Git repositories as Spark DataFrames.

This is the correct answer because it exemplifies best practices for implementing this system. By isolating tables in separate databases based on data quality tiers, such as bronze, silver, and gold, the data engineering team can achieve several benefits. First, they can easily manage permissions for different users and groups through database ACLs, which allow granting or revoking access to databases, tables, or views. Second, they can physically separate the default storage locations for managed tables in each database, which can improve performance and reduce costs. Third, they can provide a clear and consistent naming convention for the tables in each database, which can improve discoverability and usability. Verified References: [Databricks Certified Data Engineer Professional], under “Lakehouse” section; Databricks Documentation, under “Database object privileges” section.

Given the information that the model is registered with MLflow and assuming predict is the method used to apply the model to a set of columns, we use the model.predict() function to apply the model to the DataFrame df using the specified columns . The model.predict() function is designed to take in a DataFrame and a list of column names as arguments, applying the trained model to these features to produce a predictions column. When working with PySpark, this predictions column needs to be selected alongside the customer_id to create a new DataFrame with the schema customer_id LONG, predictions DOUBLE .

The key requirement is to detect when the daily average of percent_null is greater than 5% for three consecutive days .

Option A only checks the last 24 hours , not consecutive days . It would trigger too frequently and cause spam.

Option C calculates an average across all records in the last 3 days , but this could be skewed by one high or low day — it does not ensure consecutive daily violations .

Option D simply counts days where the threshold was exceeded, but it does not guarantee that those days were consecutive . This could incorrectly trigger on non-adjacent violations.

Option B is correct:

It aggregates hourly values into daily averages .

It checks that the last 3 consecutive days all had averages above 5%.

It avoids redundant alerts by using Notification Frequency: Just once.

This matches Databricks Lakehouse Monitoring best practices , where SQL alerts should be designed to aggregate metrics to the correct granularity (daily here) and ensure consecutive threshold violations before triggering.

Reference (Databricks Lakehouse Monitoring, SQL Alerts Best Practices):

Use DATE_TRUNC to compute metrics at the correct time granularity.

To detect consecutive-day issues, filter the last N daily aggregates and check conditions across all rows.

Always configure alerts with controlled notification frequency to prevent alert fatigue.

In the Spark UI ' s Storage tab, an indicator that a cached table is not performing optimally would be the presence of the _disk annotation in the RDD Block Name. This annotation indicates that some partitions of the cached data have been spilled to disk because there wasn ' t enough memory to hold them. This is suboptimal because accessing data from disk is much slower than from memory. The goal of caching is to keep data in memory for fast access, and a spill to disk means that this goal is not fully achieved.

In Databricks and Delta Lake, transactions are indeed ACID-compliant, but this compliance is limited to single table transactions. Delta Lake does not inherently enforce foreign key constraints, which are a staple in relational database systems for maintaining referential integrity between tables. This means that when migrating workloads from a relational database system to Databricks Lakehouse, engineers need to reconsider how to maintain data integrity and relationships that were previously enforced by foreign key constraints. Unlike traditional relational databases where foreign key constraints help in maintaining the consistency across tables, in Databricks Lakehouse, the data engineer has to manage data consistency and integrity at the application level or through careful design of ETL processes.

Databricks Asset Bundles (DABs) allow jobs, clusters, and permissions to be defined as code in YAML configuration files. According to the Databricks documentation on job permissions and bundle deployment, when defining permissions within a job resource, they must be scoped directly under that specific job’s definition. This ensures that permissions are applied only to the intended job resource and not inadvertently propagated to other jobs or resources.

In this scenario, the data engineer must grant the data-engineers group CAN_MANAGE access, allowing them to configure, edit, and manage the job, while the auditors group should only have CAN_VIEW, giving them read-only access to see configurations and results without the ability to modify or execute. Importantly, no additional groups should be granted permissions, in order to follow the principle of least privilege.

Options A and B introduce unnecessary or unintended groups (like admin-team in A) or define permissions outside of the job scope (as in B). Option C improperly separates the permissions block outside the job resource, which is not aligned with Databricks bundle best practices.

Option D is the correct approach because it defines the job resource my-job with its name, tasks, clusters, and the exact intended permissions (CAN_MANAGE for data-engineers and CAN_VIEW for auditors). This aligns with Databricks’ principle of least privilege and ensures compliance with governance standards in Unity Catalog-enabled workspaces.

Databricks Catalog Explorer provides a feature called AI Generate that automatically produces intelligent comments for columns. This feature uses metadata such as column names, types, patterns, and sampled values to generate human-readable documentation. According to the documentation, this is the recommended method to rapidly enrich schema metadata and improve data discoverability, especially at enterprise scale. Unlike DESCRIBE HISTORY or DESCRIBE TABLE, which only surface technical schema details, AI Generate directly produces business-oriented descriptions. PySpark statistical functions (df.describe) only return numeric statistics and cannot generate descriptive metadata. Thus, AI Generate in Catalog Explorer is the correct approach.

Delta Lake automatically collects statistics on the first 32 columns of each table. These statistics help optimize query performance through data skipping , which allows Databricks to scan only relevant parts of a table.

This feature significantly improves query efficiency, especially when dealing with large datasets.

Why Other Options Are Incorrect:

Option A: Views do not cache the most recent versions of the source table; they are recomputed when queried.

Option C: Z-ORDER can be applied to any data type, including strings, to optimize read performance.

Option D: Delta Lake does not enforce primary or foreign key constraints.

This is the correct answer because it meets the requirements of maintaining a full record of all values that have ever been valid in the source system and recreating the current table state with only the most recent value for each record. The code ingests all log information into a bronze table, which preserves the raw CDC data as it is. Then, it uses merge into to perform an upsert operation on a silver table, which means it will insert new records or update or delete existing records based on the change type and the pk_id columns. This way, the silver table will always reflect the current state of the source table, while the bronze table will keep the history of all changes. Verified References: [Databricks Certified Data Engineer Professional], under “Delta Lake” section; Databricks Documentation, under “Upsert into a table using merge” section.

When a Databricks job runs multiple tasks with dependencies, the tasks are executed in a dependency graph. If a task fails, the downstream tasks that depend on it are skipped and marked as Upstream failed. However, the failed task may have already committed some changes to the Lakehouse before the failure occurred, and those changes are not rolled back automatically. Therefore, the job run may result in a partial update of the Lakehouse. To avoid this, you can use the  transactional writes  feature of Delta Lake to ensure that the changes are only committed when the entire job run succeeds. Alternatively, you can use the  Run if  condition to configure tasks to run even when some or all of their dependencies have failed, allowing your job to recover from failures and continue running. References:

transactional writes : https://docs.databricks.com/delta/delta-intro.html#transactional-writes

Run if : https://docs.databricks.com/en/workflows/jobs/conditional-tasks.html

Partitioning a Delta Lake table improves query performance by organizing data into partitions based on the values of a column. In the given schema, the date column is a good candidate for partitioning for several reasons:

Time-Based Queries : If queries frequently filter or group by date, partitioning by the date column can significantly improve performance by limiting the amount of data scanned.

Granularity : The date column likely has a granularity that leads to a reasonable number of partitions (not too many and not too few). This balance is important for optimizing both read and write performance.

Data Skew : Other columns like post_id or user_id might lead to uneven partition sizes (data skew), which can negatively impact performance.

Partitioning by post_time could also be considered, but typically date is preferred due to its more manageable granularity.

The MANAGE privilege in Unity Catalog provides the ability to grant and revoke privileges on the specified object (in this case, a catalog) without giving full administrative access or ownership.

This is the Databricks-recommended approach for delegating governance responsibilities while preserving the principle of least privilege .

By contrast, the ALL PRIVILEGES option grants excessive access (including read and write permissions), and metastore admin status provides global control over all catalogs—far exceeding the requirement. The MANAGE privilege enables the finance team lead to control access to objects within finance_data responsibly while limiting overall administrative exposure.