[UPDATED Dec-2023] Best Value Available Preparation Guide for Databricks-Certified-Professional-Data-Engineer Exam
1 Full Databricks-Certified-Professional-Data-Engineer Practice Test and 60 Unique Questions, Get it Now!
Databricks is a cloud-based data processing and analytics platform that is widely used in the industry. It provides an end-to-end solution for data engineering, machine learning, and business analytics. To validate the skills and expertise of data engineers who work with Databricks, the company offers a certification program known as the Databricks-Certified-Professional-Data-Engineer (Databricks Certified Professional Data Engineer) exam. Databricks Certified Professional Data Engineer Exam certification is designed to help data engineers demonstrate their proficiency in using Databricks to design, build, and maintain data pipelines and data solutions.
Databricks is a leading cloud-based data engineering and analytics platform that enables organizations to process, store, and analyze large volumes of data. The platform offers a comprehensive suite of tools and services that help data engineers and data scientists to collaborate and streamline their workflows. To validate the skills and expertise of data engineers using the Databricks platform, Databricks offers the Databricks-Certified-Professional-Data-Engineer (Databricks Certified Professional Data Engineer) certification.
Databricks-Certified-Professional-Data-Engineer certification is highly sought after by employers as it provides assurance that the candidate has the necessary skills and knowledge to work with Databricks effectively. Databricks Certified Professional Data Engineer Exam certification is recognized as a standard of excellence in the data engineering field and is a valuable asset for professionals looking to advance their careers.
NEW QUESTION # 17
An upstream system has been configured to pass the date for a given batch of data to the Databricks Jobs API as a parameter. The notebook to be scheduled will use this parameter to load data with the following code:
df = spark.read.format("parquet").load(f"/mnt/source/(date)")
Which code block should be used to create the date Python variable used in the above code block?
- A. date = spark.conf.get("date")
- B. input_dict = input()
date= input_dict["date"] - C. date = dbutils.notebooks.getParam("date")
- D. dbutils.widgets.text("date", "null")
date = dbutils.widgets.get("date") - E. import sys
date = sys.argv[1]
Answer: C
Explanation:
Explanation
This is the correct way to get a parameter passed to a notebook by the Databricks Jobs API. The dbutils.notebooks.getParam method returns the value of a parameter passed to a notebook as a string. If no parameter with that name is passed, it returns None by default. You can also specify a default value as a second argument. Verified References: Databricks Certified Data Engineer Professional, under "Databricks Tooling" section; Databricks Documentation, under "Pass parameters to a notebook" section.
NEW QUESTION # 18
A data engineer has configured a Structured Streaming job to read from a table, manipulate the data, and then
perform a streaming write into a new table. The code block used by the data engineer is below:
1. (spark.table("sales")
2. .withColumn("avg_price", col("sales") / col("units"))
3. .writeStream
4. .option("checkpointLocation", checkpointPath)
5. .outputMode("complete")
6. ._____
7. .table("new_sales")
8.)
If the data engineer only wants the query to execute a single micro-batch to process all of the available data,
which of the following lines of code should the data engineer use to fill in the blank?
- A. .processingTime(1)
- B. .processingTime("once")
- C. .trigger(once=True)
- D. .trigger(continuous="once")
- E. .trigger(processingTime="once")
Answer: C
NEW QUESTION # 19
A data architect has designed a system in which two Structured Streaming jobs will concurrently write to a single bronze Delta table. Each job is subscribing to a different topic from an Apache Kafka source, but they will write data with the same schema. To keep the directory structure simple, a data engineer has decided to nest a checkpoint directory to be shared by both streams.
The proposed directory structure is displayed below:
Which statement describes whether this checkpoint directory structure is valid for the given scenario and why?
- A. Yes; Delta Lake supports infinite concurrent writers.
- B. No; only one stream can write to a Delta Lake table.
- C. No; each of the streams needs to have its own checkpoint directory.
- D. Yes; both of the streams can share a single checkpoint directory.
- E. No; Delta Lake manages streaming checkpoints in the transaction log.
Answer: C
Explanation:
Explanation
This is the correct answer because checkpointing is a critical feature of Structured Streaming that provides fault tolerance and recovery in case of failures. Checkpointing stores the current state and progress of a streaming query in a reliable storage system, such as DBFS or S3. Each streaming query must have its own checkpoint directory that is unique and exclusive to that query. If two streaming queries share the same checkpoint directory, they will interfere with each other and cause unexpected errors or data loss. Verified References: [Databricks Certified Data Engineer Professional], under "Structured Streaming" section; Databricks Documentation, under "Checkpointing" section.
NEW QUESTION # 20
Which of the following developer operations in CI/CD flow can be implemented in Databricks Re-pos?
- A. Trigger Databricks Repos API to pull the latest version of code into production folder
- B. Delete a branch
- C. Pull request and review process
- D. Merge when code is committed
- E. Resolve merge conflicts
Answer: A
Explanation:
Explanation
See the below diagram to understand the role Databricks Repos and Git provider plays when building a CI/CD workflow.
All the steps highlighted in yellow can be done Databricks Repo, all the steps highlighted in Gray are done in a git provider like Github or Azure DevOps
NEW QUESTION # 21
What is the purpose of the silver layer in a Multi hop architecture?
- A. Optimized query performance for business-critical data
- B. Replaces a traditional data lake
- C. Refined views with aggregated data
- D. Efficient storage and querying of full, unprocessed history of data
- E. Eliminates duplicate data, quarantines bad data
Answer: E
Explanation:
Explanation
Medallion Architecture - Databricks
Silver Layer:
1. Reduces data storage complexity, latency, and redundency
2. Optimizes ETL throughput and analytic query performance
3. Preserves grain of original data (without aggregation)
4. Eliminates duplicate records
5. production schema enforced
6. Data quality checks, quarantine corrupt data
Exam focus: Please review the below image and understand the role of each layer(bronze, silver, gold) in medallion architecture, you will see varying questions targeting each layer and its purpose.
Sorry I had to add the watermark some people in Udemy are copying my content.
A diagram of a house Description automatically generated with low confidence
NEW QUESTION # 22
As a Data Engineer, you were asked to create a delta table to store below transaction data?
- A. 1.CREATE TABLE USING DELTA transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int) - B. 1.CREATE TABLE transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int)
5.FORMAT DELTA - C. 1.CREATE DELTA TABLE transactions (
2. transactionId int,
3. transactionDate timestamp,
4. unitsSold int) - D. 1.CREATE TABLE transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int)
(Correct) - E. 1.CREATE TABLE transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int)
5.LOCATION DELTA
Answer: A
Explanation:
Explanation
Answer is
1.CREATE TABLE transactions (
2.transactionId int,
3.transactionDate timestamp,
4.unitsSold int)
When creating a table in Databricks by default the table is stored in DELTA format.
NEW QUESTION # 23
You currently working with the marketing team to setup a dashboard for ad campaign analysis, since the team is not sure how often the dashboard should be refreshed they have decided to do a manual refresh on an as needed basis. Which of the following steps can be taken to reduce the overall cost of the compute when the team is not using the compute?
*Please note that Databricks recently change the name of SQL Endpoint to SQL Warehouses.
- A. They can turn on the Serverless feature for the SQL endpoint(SQL Warehouse) and change the Spot Instance Policy from "Reliability Optimized" to "Cost optimized"
- B. They can turn on the Auto Stop feature for the SQL endpoint(SQL Warehouse).
- C. They can decrease the maximum bound of the SQL endpoint(SQL Warehouse) scaling range.
- D. They can turn on the Serverless feature for the SQL endpoint(SQL Warehouse).
- E. They can decrease the cluster size of the SQL endpoint(SQL Warehouse).
Answer: B
Explanation:
Explanation
The answer is, They can turn on the Auto Stop feature for the SQL endpoint(SQL Warehouse).
Use auto stop to automatically terminate the cluster when you are not using it.
NEW QUESTION # 24
You are looking to process the data based on two variables, one to check if the department is supply chain and second to check if process flag is set to True
- A. if department == "supply chain" && process:
- B. if department == "supply chain" & if process == TRUE:
- C. if department == "supply chain" and process:
- D. if department == "supply chain" & process == TRUE:
- E. if department = "supply chain" & process:
Answer: C
NEW QUESTION # 25
You are trying to create an object by joining two tables that and it is accessible to data scientist's team, so it does not get dropped if the cluster restarts or if the notebook is detached. What type of object are you trying to create?
- A. Global Temporary view with cache option
- B. External view
- C. View
- D. Temporary view
- E. Global Temporary view
Answer: C
Explanation:
Explanation
Answer is View, A view can be used to join multiple tables but also persist into meta stores so others can accesses it
NEW QUESTION # 26
You noticed that a team member started using an all-purpose cluster to develop a notebook and used the same all-purpose cluster to set up a job that can run every 30 mins so they can update un-derlying tables which are used in a dashboard. What would you recommend for reducing the overall cost of this approach?
- A. Enable auto termination after 30 mins
- B. Reduce the size of the cluster
- C. Change the cluster all-purpose to job cluster when scheduling the job
- D. Change the cluster mode from all-purpose to single-mode
- E. Reduce the number of nodes and enable auto scale
Answer: C
Explanation:
Explanation
While using an all-purpose cluster is ok during development but anytime you don't need to interact with a notebook, especially for a scheduled job it is less expensive to use a job cluster. Using an all-purpose cluster can be twice as expensive as a job cluster.
Please note: The compute cost you pay the cloud provider for the same cluster type and size be-tween an all-purpose cluster and job cluster is the same the only difference is the DBU cost.
The total cost of cluster = Total cost of VM compute(Azure or AWS or GCP) + Cost per DBU The per DBU cost varies between all-purpose and Job Cluster Here is the recent cost estimate from AWS between Jobs Cluster and all-purpose Cluster, for jobs compute its
$0.15 cents per DBU v$0.55 cents per DBU for all-purpose
Graphical user interface Description automatically generated
How do I check how much the DBU cost for my cluster?
When you click on an exister cluster or when you look at the cluster details you will see this in the top right corner Graphical user interface, text, application, email Description automatically generated
NEW QUESTION # 27
Which of the following SQL command can be used to insert or update or delete rows based on a condition to check if a row(s) exists?
- A. INSERT INTO OVERWRITE table_name
- B. INSERT IF EXISTS table_name
- C. UPDATE table_name
- D. MERGE INTO table_name
- E. COPY INTO table_name
Answer: D
Explanation:
Explanation
here is the additional documentation for your review.
https://docs.databricks.com/spark/latest/spark-sql/language-manual/delta-merge-into.html
1.MERGE INTO target_table_name [target_alias]
2. USING source_table_reference [source_alias]
3. ON merge_condition
4. [ WHEN MATCHED [ AND condition ] THEN matched_action ] [...]
5. [ WHEN NOT MATCHED [ AND condition ] THEN not_matched_action ] [...]
6.
7.matched_action
8. { DELETE |
9. UPDATE SET * |
10. UPDATE SET { column1 = value1 } [, ...] }
11.
12.not_matched_action
13. { INSERT * |
14. INSERT (column1 [, ...] ) VALUES (value1 [, ...])
NEW QUESTION # 28
Each configuration below is identical to the extent that each cluster has 400 GB total of RAM, 160 total cores and only one Executor per VM.
Given a job with at least one wide transformation, which of the following cluster configurations will result in maximum performance?
- A. * Total VMs:2
* 200 GB per Executor
* 80 Cores / Executor - B. * Total VMs: 8
* 50 GB per Executor
* 20 Cores / Executor - C. * Total VMs; 1
* 400 GB per Executor
* 160 Cores / Executor - D. * Total VMs: 4
* 100 GB per Executor
* 40 Cores/Executor
Answer: B
Explanation:
Explanation
This is the correct answer because it is the cluster configuration that will result in maximum performance for a job with at least one wide transformation. A wide transformation is a type of transformation that requires shuffling data across partitions, such as join, groupBy, or orderBy. Shuffling can be expensive and time-consuming, especially if there are too many or too few partitions. Therefore, it is important to choose a cluster configuration that can balance the trade-off between parallelism and network overhead. In this case, having 8 VMs with 50 GB per executor and 20 cores per executor will create 8 partitions, each with enough memory and CPU resources to handle the shuffling efficiently. Having fewer VMs with more memory and cores per executor will create fewer partitions, which will reduce parallelism and increase the size of each shuffle block. Having more VMs with less memory and cores per executor will create more partitions, which will increase parallelism but also increase the network overhead and the number of shuffle files. Verified References: [Databricks Certified Data Engineer Professional], under "Performance Tuning" section; Databricks Documentation, under "Cluster configurations" section.
NEW QUESTION # 29
You are still noticing slowness in query after performing optimize which helped you to resolve the small files problem, the column(transactionId) you are using to filter the data has high cardinality and auto incrementing number. Which delta optimization can you enable to filter data effectively based on this column?
- A. Perform Optimize with Zorder on transactionId
(Correct) - B. Create BLOOM FLTER index on the transactionId
- C. transactionId has high cardinality, you cannot enable any optimization.
- D. Increase the cluster size and enable delta optimization
- E. Increase the driver size and enable delta optimization
Answer: A
Explanation:
Explanation
The answer is, perform Optimize with Z-order by transactionid
Here is a simple explanation of how Z-order works, once the data is naturally ordered, when a flle is scanned it only brings the data it needs into spark's memory Based on the column min and max it knows which data files needs to be scanned.
Table Description automatically generated
Graphical user interface, diagram, application Description automatically generated
NEW QUESTION # 30
You had worked with the Data analysts team to set up a SQL Endpoint(SQL warehouse) point so they can easily query and analyze data in the gold layer, but once they started consuming the SQL Endpoint(SQL warehouse) you noticed that during the peak hours as the number of users increase you are seeing queries taking longer to finish, which of the following steps can be taken to resolve the issue?
*Please note Databricks recently renamed SQL endpoint to SQL warehouse.
- A. They can turn on the Serverless feature for the SQL endpoint(SQL warehouse) and change the Spot Instance Policy from "Cost optimized" to "Reliability Optimized."
- B. They can turn on the Serverless feature for the SQL endpoint(SQL warehouse).
- C. They can increase the cluster size from 2X-Small to 4X-Large of the SQL end-point(SQL warehouse) .
- D. They can turn on the Auto Stop feature for the SQL endpoint(SQL warehouse) .
- E. They can increase the maximum bound of the SQL endpoint(SQL warehouse) 's scaling range.
Answer: E
Explanation:
Explanation
the answer is,
They can increase the maximum bound of the SQL endpoint's scaling range, when you increase the maximum bound you can add more clusters to the warehouse which can then run additional queries that are waiting in the queue to run, focus on the below explanation that talks about Scale-out.
The question is looking to test your ability to know how to scale a SQL Endpoint(SQL Warehouse) and you have to look for cue words or need to understand if the queries are running sequentially or concurrently. if the queries are running sequentially then scale up(Size of the cluster from 2X-Small to 4X-Large) if the queries are running concurrently or with more users then scale out(add more clusters).
SQL Endpoint(SQL Warehouse) Overview: (Please read all of the below points and the below diagram to understand )
1.A SQL Warehouse should have at least one cluster
2.A cluster comprises one driver node and one or many worker nodes
3.No of worker nodes in a cluster is determined by the size of the cluster (2X -Small ->1 worker, X-Small ->2 workers.... up to 4X-Large -> 128 workers) this is called Scale up
4.A single cluster irrespective of cluster size(2X-Smal.. to ...4XLarge) can only run 10 queries at any given time if a user submits 20 queries all at once to a warehouse with 3X-Large cluster size and cluster scaling (min
1, max1) while 10 queries will start running the remaining 10 queries wait in a queue for these 10 to finish.
5.Increasing the Warehouse cluster size can improve the performance of a query, example if a query runs for 1 minute in a 2X-Small warehouse size, it may run in 30 Seconds if we change the warehouse size to X-Small.
this is due to 2X-Small has 1 worker node and X-Small has 2 worker nodes so the query has more tasks and runs faster (note: this is an ideal case example, the scalability of a query performance depends on many factors, it can not always be linear)
6.A warehouse can have more than one cluster this is called Scale out. If a warehouse is con-figured with X-Small cluster size with cluster scaling(Min1, Max 2) Databricks spins up an additional cluster if it detects queries are waiting in the queue, If a warehouse is configured to run 2 clusters(Min1, Max 2), and let's say a user submits 20 queries, 10 queriers will start running and holds the remaining in the queue and databricks will automatically start the second cluster and starts redirecting the 10 queries waiting in the queue to the second cluster.
7.A single query will not span more than one cluster, once a query is submitted to a cluster it will remain in that cluster until the query execution finishes irrespective of how many clusters are available to scale.
Please review the below diagram to understand the above concepts:
SQL endpoint(SQL Warehouse) scales horizontally(scale-out) and vertical (scale-up), you have to understand when to use what.
Scale-out -> to add more clusters for a SQL endpoint, change max number of clusters If you are trying to improve the throughput, being able to run as many queries as possible then having an additional cluster(s) will improve the performance.
Databricks SQL automatically scales as soon as it detects queries are in queuing state, in this example scaling is set for min 1 and max 3 which means the warehouse can add three clusters if it detects queries are waiting.
During the warehouse creation or after you have the ability to change the warehouse size (2X-Small....to
...4XLarge) to improve query performance and the maximize scaling range to add more clusters on a SQL Endpoint(SQL Warehouse) scale-out, if you are changing an existing warehouse you may have to restart the warehouse to make the changes effective.
How do you know how many clusters you need(How to set Max cluster size)?
When you click on an existing warehouse and select the monitoring tab, you can see warehouse utilization information(see below), there are two graphs that provide important information on how the warehouse is being utilized, if you see queries are being queued that means your warehouse can benefit from additional clusters. Please review the additional DBU cost associated with adding clusters so you can take a well balanced decision between cost and performance.
NEW QUESTION # 31
A notebook accepts an input parameter that is assigned to a python variable called department and this is an optional parameter to the notebook, you are looking to control the flow of the code using this parameter. you have to check department variable is present then execute the code and if no department value is passed then skip the code execution. How do you achieve this using python?
- A. 1.if (department is not None)
2. #Execute code
3.else
4. pass - B. 1.if department is not None:
2. #Execute code
3.end:
4. pass - C. 1.if department is not None:
2. #Execute code
3.then:
4. pass - D. 1.if department is not None:
2. #Execute code
3.else:
4. pass
(Correct) - E. 1.if department is None:
2. #Execute code
3.else:
4. pass
Answer: D
Explanation:
Explanation
The answer is,
1.if department is not None:
2. #Execute code
3.else:
4. pass
NEW QUESTION # 32
At the end of the inventory process a file gets uploaded to the cloud object storage, you are asked to build a process to ingest data which of the following method can be used to ingest the data incrementally, the schema of the file is expected to change overtime ingestion process should be able to handle these changes automatically. Below is the auto loader command to load the data, fill in the blanks for successful execution of the below code.
1.spark.readStream
2..format("cloudfiles")
3..option("cloudfiles.format","csv)
4..option("_______", 'dbfs:/location/checkpoint/')
5..load(data_source)
6..writeStream
7..option("_______",' dbfs:/location/checkpoint/')
8..option("mergeSchema", "true")
9..table(table_name))
- A. checkpointlocation, cloudfiles.schemalocation
- B. cloudfiles.schemalocation, checkpointlocation
- C. cloudfiles.schemalocation, cloudfiles.checkpointlocation
- D. schemalocation, checkpointlocation
- E. checkpointlocation, schemalocation
Answer: B
Explanation:
Explanation
The answer is cloudfiles.schemalocation, checkpointlocation
When reading the data cloudfiles.schemalocation is used to store the inferred schema of the incoming data.
When writing a stream to recover from failures checkpointlocation is used to store the offset of the byte that was most recently processed.
NEW QUESTION # 33
Which of the following techniques structured streaming uses to ensure recovery of failures during stream processing?
- A. Write ahead logging and watermarking
- B. Checkpointing and Idempotent sinks
- C. The stream will failover to available nodes in the cluster
- D. Checkpointing and Watermarking
- E. Checkpointing and write-ahead logging
- F. Delta time travel
Answer: E
Explanation:
Explanation
The answer is Checkpointing and write-ahead logging.
Structured Streaming uses checkpointing and write-ahead logs to record the offset range of data being processed during each trigger interval.
NEW QUESTION # 34
Direct query on external files limited options, create external tables for CSV files with header and pipe delimited CSV files, fill in the blanks to complete the create table statement CREATE TABLE sales (id int, unitsSold int, price FLOAT, items STRING)
________
________
LOCATION "dbfs:/mnt/sales/*.csv"
- A. FORMAT CSV
TYPE ( header ="true", delimiter = "|") - B. USING CSV
OPTIONS ( header ="true", delimiter = "|")
(Correct) - C. FORMAT CSV
FORMAT TYPE ( header ="true", delimiter = "|") - D. FORMAT CSV
OPTIONS ( "true","|") - E. USING CSV
TYPE ( "true","|")
Answer: B
Explanation:
Explanation
Answer is
USING CSV
OPTIONS ( header ="true", delimiter = "|")
Here is the syntax to create an external table with additional options
CREATE TABLE table_name (col_name1 col_typ1,..)
USING data_source
OPTIONS (key='value', key2=vla2)
LOCATION = "/location"
NEW QUESTION # 35
......
Get Instant Access to Databricks-Certified-Professional-Data-Engineer Practice Exam Questions: https://evedumps.testkingpass.com/Databricks-Certified-Professional-Data-Engineer-testking-dumps.html