Download Exam Databricks-Certified-Professional-Data-Engineer Practice Test Questions with 100% Verified Answers [Q36-Q58]

QUESTION 36
A data ingestion task requires a one-TB JSON dataset to be written out to Parquet with a target part-file size of 512 MB. Because Parquet is being used instead of Delta Lake, built-in file-sizing features such as Auto-Optimize & Auto-Compaction cannot be used.
Which strategy will yield the best performance without shuffling data?

Set spark.sql.files.maxPartitionBytes to 512 MB, ingest the data, execute the narrow transformations, and then write to parquet.

Set spark.sql.shuffle.partitions to 2,048 partitions (1TB*1024*1024/512), ingest the data, execute the narrow transformations, optimize the data by sorting it (which automatically repartitions the data), and then write to parquet.

Set spark.sql.adaptive.advisoryPartitionSizeInBytes to 512 MB bytes, ingest the data, execute the narrow transformations, coalesce to 2,048 partitions (1TB*1024*1024/512), and then write to parquet.

Ingest the data, execute the narrow transformations, repartition to 2,048 partitions (1TB* 1024*1024/512), and then write to parquet.

Set spark.sql.shuffle.partitions to 512, ingest the data, execute the narrow transformations, and then write to parquet.

For this scenario where a one-TB JSON dataset needs to be converted into Parquet format without employing Delta Lake’s auto-sizing features, the goal is to avoid unnecessary data shuffles and yet ensure optimal file sizes for the output Parquet files. Here’s a breakdown of why option A is most suitable:
Setting maxPartitionBytes: The spark.sql.files.maxPartitionBytes configuration controls the size of blocks that Spark reads from the data source (in this case, the JSON files) but also influences the output size of files when data is written without repartition or coalesce operations. Setting this parameter to 512 MB directly addresses the requirement to manage the output file size effectively.
Data Ingestion and Processing:
Ingesting Data: Load the JSON dataset into a DataFrame.
Applying Transformations: Perform any required narrow transformations that do not involve shuffling data (like filtering or adding new columns).
Writing to Parquet: Directly write the transformed DataFrame to Parquet files. The setting for maxPartitionBytes ensures that each part-file is approximately 512 MB, meeting the requirement for part-file size without additional steps to repartition or coalesce the data.
Performance Consideration: This approach is optimal because:
It avoids the overhead of shuffling data, which can be significant, especially with large datasets.
It directly ties the read/write operations to a configuration that matches the target output size, making it efficient in terms of both computation and I/O operations.
Alternative Options Analysis:
Option B and D: Involves repartitioning, which would trigger a shuffle of the data, contradicting the requirement to avoid shuffling for performance reasons.
Option C: Uses coalesce, which is less intensive than repartition but can still lead to uneven partition sizes and does not directly control the output file size as effectively as setting maxPartitionBytes.
Option E: Setting shuffle partitions to 512 doesn’t directly control the output file size for writing to Parquet and could lead to smaller files depending on the dataset’s partitioning post-transformations.
Reference
Apache Spark Configuration
Writing to Parquet Files in Spark

QUESTION 37
The team has decided to take advantage of table properties to identify a business owner for each table, which of the following table DDL syntax allows you to populate a table property identifying the business owner of a table CREATE TABLE inventory (id INT, units FLOAT)

SET TBLPROPERTIES business_owner = ‘supply chain’
CREATE TABLE inventory (id INT, units FLOAT)

TBLPROPERTIES (business_owner = ‘supply chain’)

CREATE TABLE inventory (id INT, units FLOAT)
SET (business_owner = ‘supply chain’)

CREATE TABLE inventory (id INT, units FLOAT)
SET PROPERTY (business_owner = ‘supply chain’)

CREATE TABLE inventory (id INT, units FLOAT)
SET TAG (business_owner = ‘supply chain’)

QUESTION 38
The security team is exploring whether or not the Databricks secrets module can be leveraged for connecting to an external database.
After testing the code with all Python variables being defined with strings, they upload the password to the secrets module and configure the correct permissions for the currently active user. They then modify their code to the following (leaving all other variables unchanged).

Which statement describes what will happen when the above code is executed?

The connection to the external table will fail; the string “redacted” will be printed.

An interactive input box will appear in the notebook; if the right password is provided, the connection will succeed and the encoded password will be saved to DBFS.

An interactive input box will appear in the notebook; if the right password is provided, the connection will succeed and the password will be printed in plain text.

The connection to the external table will succeed; the string value of password will be printed in plain text.

The connection to the external table will succeed; the string “redacted” will be printed.

QUESTION 39
You are currently working with the application team to setup a SQL Endpoint point, once the team started consuming the SQL Endpoint you noticed that during peak hours as the number of concur-rent users increases you are seeing degradation in the query performance and the same queries are taking longer to run, which of the following steps can be taken to resolve the issue?

They can turn on the Serverless feature for the SQL endpoint.

They can increase the maximum bound of the SQL endpoint’s scaling range.

They can increase the cluster size(2X-Small to 4X-Large) of the SQL endpoint.

They can turn on the Auto Stop feature for the SQL endpoint.

They can turn on the Serverless feature for the SQL endpoint and change the Spot In-stance Policy from
“Cost optimized” to “Reliability Optimized.”

QUESTION 40
A Delta Lake table representing metadata about content from user has the following schema:
user_id LONG, post_text STRING, post_id STRING, longitude FLOAT, latitude FLOAT, post_time TIMESTAMP, date DATE Based on the above schema, which column is a good candidate for partitioning the Delta Table?

Date

Post_id

User_id

Post_time

QUESTION 41
A junior data engineer seeks to leverage Delta Lake’s Change Data Feed functionality to create a Type 1 table representing all of the values that have ever been valid for all rows in abronzetable created with the propertydelta.enableChangeDataFeed = true. They plan to execute the following code as a daily job:

Which statement describes the execution and results of running the above query multiple times?

Each time the job is executed, newly updated records will be merged into the target table, overwriting previous values with the same primary keys.

Each time the job is executed, the entire available history of inserted or updated records will be appended to the target table, resulting in many duplicate entries.

Each time the job is executed, the target table will be overwritten using the entire history of inserted or updated records, giving the desired result.

Each time the job is executed, the differences between the original and current versions are calculated; this may result in duplicate entries for some records.

Each time the job is executed, only those records that have been inserted or updated since the last execution will be appended to the target table giving the desired result.

QUESTION 42
A data engineer is running a groupBy aggregation on a massive user activity log grouped by user_id. A few users have millions of records, causing task skew and long runtimes.
Which technique will fix the skew in this aggregation?

Use salting by adding a random prefix to skewed keys before aggregation, then aggregate again after removing the prefix.

Increase the Spark driver memory and retry.

Use reduceByKey instead of groupBy to avoid shuffles.

Filter out the skewed users before the aggregation.

QUESTION 43
A data engineer has created a new cluster using shared access mode with default configurations. The data engineer needs to allow the development team access to view the driver logs if needed.
What are the minimal cluster permissions that allow the development team to accomplish this?

CAN ATTACH TO

CAN MANAGE

CAN VIEW

CAN RESTART

QUESTION 44
Given the following error traceback (from display(df.select(3*”heartrate”))) which shows AnalysisException:
cannot resolve ‘heartrateheartrateheartrate’, which statement describes the error being raised?

There is a type error because a DataFrame object cannot be multiplied.

There is a syntax error because the heartrate column is not correctly identified as a column.

There is no column in the table named heartrateheartrateheartrate.

There is a type error because a column object cannot be multiplied.

QUESTION 45
Which statement describes integration testing?

Validates interactions between subsystems of your application

Requires an automated testing framework

Requires manual intervention

Validates an application use case

Validates behavior of individual elements of your application

QUESTION 46
A DLT pipeline includes the following streaming tables:
Raw_lot ingest raw device measurement data from a heart rate tracking device.
Bgm_stats incrementally computes user statistics based on BPM measurements from raw_lot.
How can the data engineer configure this pipeline to be able to retain manually deleted or updated records in the raw_iot table while recomputing the downstream table when a pipeline update is run?

Set the skipChangeCommits flag to true on bpm_stats

Set the SkipChangeCommits flag to true raw_lot

Set the pipelines, reset, allowed property to false on bpm_stats

Set the pipelines, reset, allowed property to false on raw_iot

QUESTION 47
A nightly job ingests data into a Delta Lake table using the following code:

The next step in the pipeline requires a function that returns an object that can be used to manipulate new records that have not yet been processed to the next table in the pipeline.
Which code snippet completes this function definition?
def new_records():

return spark.readStream.table(“bronze”)

return spark.readStream.load(“bronze”)

return spark.read.option(“readChangeFeed”, “true”).table (“bronze”)

QUESTION 48
Which of the following locations in Databricks product architecture hosts jobs/pipelines and queries?

Data plane

Control plane

Databricks Filesystem

JDBC data source

Databricks web application

QUESTION 49
Assuming that the Databricks CLI has been installed and configured correctly, which Databricks CLI command can be used to upload a custom Python Wheel to object storage mounted with the DBFS for use with a production job?

configure

fs

jobs

libraries

workspace

QUESTION 50
A data architect has heard about lake’s built-in versioning and time travel capabilities. For auditing purposes they have a requirement to maintain a full of all valid street addresses as they appear in the customers table.
The architect is interested in implementing a Type 1 table, overwriting existing records with new values and relying on Delta Lake time travel to support long-term auditing. A data engineer on the project feels that a Type 2 table will provide better performance and scalability.
Which piece of information is critical to this decision?

Delta Lake time travel does not scale well in cost or latency to provide a long-term versioning solution.

Delta Lake time travel cannot be used to query previous versions of these tables because Type 1 changes modify data files in place.

Shallow clones can be combined with Type 1 tables to accelerate historic queries for long-term versioning.

Data corruption can occur if a query fails in a partially completed state because Type 2 tables requires Setting multiple fields in a single update.

QUESTION 51
Kevin is the owner of the schema sales, Steve wanted to create new table in sales schema called regional_sales so Kevin grants the create table permissions to Steve. Steve creates the new table called regional_sales in sales schema, who is the owner of the table regional_sales

Kevin is the owner of sales schema, all the tables in the schema will be owned by Kevin

Steve is the owner of the table

By default ownership is assigned DBO

By default ownership is assigned to DEFAULT_OWNER

Kevin and Smith both are owners of table

QUESTION 52
A data engineer has configured their Databricks Asset Bundle with multiple targets in databricks.yml and deployed it to the production workspace. Now, to validate the deployment, they need to invoke a job named my_project_job specifically within the prod target context. Assuming the job is already deployed, they need to trigger its execution while ensuring the target-specific configuration is respected.
Which command will trigger the job execution?

databricks execute my_project_job -e prod

databricks job run my_project_job –env prod

databricks run my_project_job -t prod

databricks bundle run my_project_job -t prod

QUESTION 53
Identify one of the below statements that can query a delta table in PySpark Dataframe API

Spark.read.mode(“delta”).table(“table_name”)

Spark.read.table.delta(“table_name”)

Spark.read.table(“table_name”)

Spark.read.format(“delta”).LoadTableAs(“table_name”)

Spark.read.format(“delta”).TableAs(“table_name”)

QUESTION 54
Operations team is using a centralized data quality monitoring system, a user can publish data quality metrics through a webhook, you were asked to develop a process to send messages using a webhook if there is atleast one duplicate record, which of the following approaches can be taken to integrate an alert with current data quality monitoring system

Use notebook and Jobs to use python to publish DQ metrics

Setup an alert to send an email, use python to parse email, and publish a webhook message

Setup an alert with custom template

Setup an alert with custom Webhook destination

Setup an alert with dynamic template

QUESTION 55
Which of the following commands results in the successful creation of a view on top of the delta stream(stream on delta table)?

Spark.read.format(“delta”).table(“sales”).createOrReplaceTempView(“streaming_vw”)

Spark.readStream.format(“delta”).table(“sales”).createOrReplaceTempView(“streaming_vw”)

Spark.read.format(“delta”).table(“sales”).mode(“stream”).createOrReplaceTempView(“streaming_vw”)

Spark.read.format(“delta”).table(“sales”).trigger(“stream”).createOrReplaceTempView(“streaming_vw”)

Spark.read.format(“delta”).stream(“sales”).createOrReplaceTempView(“streaming_vw”)

You can not create a view on streaming data source.

QUESTION 56
A data engineer has created a new cluster using shared access mode with default configurations. The data engineer needs to allow the development team access to view the driver logs if needed.
What are the minimal cluster permissions that allow the development team to accomplish this?

CAN ATTACH TO

CAN MANAGE

CAN VIEW

CAN RESTART

QUESTION 57
The following code has been migrated to a Databricks notebook from a legacy workload:

The code executes successfully and provides the logically correct results, however, it takes over 20 minutes to extract and load around 1 GB of data.
Which statement is a possible explanation for this behavior?

%sh triggers a cluster restart to collect and install Git. Most of the latency is related to cluster startup time.

Instead of cloning, the code should use %sh pip install so that the Python code can get executed in parallel across all nodes in a cluster.

%sh does not distribute file moving operations; the final line of code should be updated to use %fs instead.

Python will always execute slower than Scala on Databricks. The run.py script should be refactored to Scala.

%sh executes shell code on the driver node. The code does not take advantage of the worker nodes or Databricks optimized Spark.

QUESTION 58
A junior data engineer has configured a workload that posts the following JSON to the Databricks REST API endpoint 2.0/jobs/create.

Assuming that all configurations and referenced resources are available, which statement describes the result of executing this workload three times?

Three new jobs named “Ingest new data” will be defined in the workspace, and they will each run once daily.

The logic defined in the referenced notebook will be executed three times on new clusters with the configurations of the provided cluster ID.

Three new jobs named “Ingest new data” will be defined in the workspace, but no jobs will be executed.

One new job named “Ingest new data” will be defined in the workspace, but it will not be executed.

The logic defined in the referenced notebook will be executed three times on the referenced existing all purpose cluster.

Leave a Reply Cancel reply