[Aug-2023] Databricks-Certified-Professional-Data-Engineer Questions - Truly Beneficial For Your Databricks Exam
Download Databricks Databricks-Certified-Professional-Data-Engineer Sample Questions
NEW QUESTION # 122
You are noticing job cluster is taking 6 to 8 mins to start which is delaying your job to finish on time, what steps you can take to reduce the amount of time cluster startup time
- A. Use SQL endpoints to reduce the startup time
- B. Use All purpose cluster instead to reduce cluster start up time
- C. Setup a second job ahead of first job to start the cluster, so the cluster is ready with re-sources when the job starts
- D. Reduce the size of the cluster, smaller the cluster size shorter it takes to start the clus-ter
- E. Use cluster pools to reduce the startup time of the jobs
Answer: E
Explanation:
Explanation
The answer is, Use cluster pools to reduce the startup time of the jobs.
Cluster pools allow us to reserve VM's ahead of time, when a new job cluster is created VM are grabbed from the pool. Note: when the VM's are waiting to be used by the cluster only cost incurred is Azure. Databricks run time cost is only billed once VM is allocated to a cluster.
Here is a demo of how to setup and follow some best practices,
https://www.youtube.com/watch?v=FVtITxOabxg&ab_channel=DatabricksAcademy
NEW QUESTION # 123
Which of the following Structured Streaming queries successfully performs a hop from a Silver to Gold table?
- A. 1.(spark.table("sales")
2..writeStream
3..option("checkpointLocation", checkpointPath)
4..outputMode("complete")
5..table("sales") ) - B. 1.(spark.read.load(rawSalesLocation)
2. .writeStream
3. .option("checkpointLocation", checkpointPath)
4. .outputMode("append")
5. .table("uncleanedSales") ) - C. 1.(spark.table("sales")
2..withColumn("avgPrice", col("sales") / col("units"))
3..writeStream
4..option("checkpointLocation", checkpointPath)
5..outputMode("append")
6..table("cleanedSales") ) - D. 1.(spark.readStream.load(rawSalesLocation)
2..writeStream
3..option("checkpointLocation", checkpointPath)
4..outputMode("append")
5..table("uncleanedSales") ) - E. 1.(spark.table("sales")
2..groupBy("store")
3..agg(sum("sales"))
4..writeStream
5..option("checkpointLocation", checkpointPath)
6..outputMode("complete")
7..table("aggregatedSales") )
(Correct)
Answer: E
Explanation:
Explanation
The answer is
1.(spark.table("sales")
2..groupBy("store")
3..agg(sum("sales"))
4..writeStream
5..option("checkpointLocation", checkpointPath)
6..outputMode("complete")
7..table("aggregatedSales") )
The gold layer is normally used to store aggregated data
Review the below link for more info,
Medallion Architecture - Databricks
Gold Layer:
1. Powers Ml applications, reporting, dashboards, ad hoc analytics
2. Refined views of data, typically with aggregations
3. Reduces strain on production systems
4. Optimizes query performance for business-critical 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 # 124
You are working on a process to query the table based on batch date, and batch date is an input parameter and expected to change every time the program runs, what is the best way to we can parameterize the query to run without manually changing the batch date?
- A. Store the batch date in the spark configuration and use a spark data frame to filter the data based on the spark configuration.
- B. Create a dynamic view that can calculate the batch date automatically and use the view to query the data
- C. Manually edit code every time to change the batch date
- D. There is no way we can combine python variable and spark code
- E. Create a notebook parameter for batch date and assign the value to a python variable and use a spark data frame to filter the data based on the python variable
Answer: E
Explanation:
Explanation
The answer is, Create a notebook parameter for batch date and assign the value to a python variable and use a spark data frame to filter the data based on the python variable
NEW QUESTION # 125
A dataset has been defined using Delta Live Tables and includes an expectations clause: CON-STRAINT valid_timestamp EXPECT (timestamp > '2020-01-01') ON VIOLATION DROP ROW What is the expected behavior when a batch of data containing data that violates these constraints is processed?
- A. Records that violate the expectation cause the job to fail.
- B. Records that violate the expectation are added to the target dataset and recorded as invalid in the event log.
- C. Records that violate the expectation are added to the target dataset and flagged as in-valid in a field added to the target dataset.
- D. Records that violate the expectation are dropped from the target dataset and loaded into a quarantine table.
- E. Records that violate the expectation are dropped from the target dataset and recorded as invalid in the event log.
Answer: E
Explanation:
Explanation
The answer is Records that violate the expectation are dropped from the target dataset and recorded as invalid in the event log.
Delta live tables support three types of expectations to fix bad data in DLT pipelines Review below example code to examine these expectations, Diagram Description automatically generated with medium confidence
NEW QUESTION # 126
The data analyst team had put together queries that identify items that are out of stock based on orders and replenishment but when they run all together for final output the team noticed it takes a really long time, you were asked to look at the reason why queries are running slow and identify steps to improve the performance and when you looked at it you noticed all the code queries are running sequentially and using a SQL endpoint cluster. Which of the following steps can be taken to resolve the issue?
Here is the example query
1.--- Get order summary
2.create or replace table orders_summary
3.as
4.select product_id, sum(order_count) order_count
5.from
6. (
7. select product_id,order_count from orders_instore
8. union all
9. select product_id,order_count from orders_online
10. )
11.group by product_id
12.-- get supply summary
13.create or repalce tabe supply_summary
14.as
15.select product_id, sum(supply_count) supply_count
16.from supply
17.group by product_id
18.
19.-- get on hand based on orders summary and supply summary
20.
21.with stock_cte
22.as (
23.select nvl(s.product_id,o.product_id) as product_id,
24. nvl(supply_count,0) - nvl(order_count,0) as on_hand
25.from supply_summary s
26.full outer join orders_summary o
27. on s.product_id = o.product_id
28.)
29.select *
30.from
31.stock_cte
32.where on_hand = 0
- A. Turn on the Auto Stop feature for the SQL endpoint.
- B. Turn on the Serverless feature for the SQL endpoint and change the Spot Instance Pol-icy to "Reliability Optimized."
- C. Increase the cluster size of the SQL endpoint.
- D. Turn on the Serverless feature for the SQL endpoint.
- E. Increase the maximum bound of the SQL endpoint's scaling range.
Answer: C
Explanation:
Explanation
The answer is to increase the cluster size of the SQL Endpoint, here queries are running sequentially and since the single query can not span more than one cluster adding more clusters won't improve the query but rather increasing the cluster size will improve performance so it can use additional compute in a warehouse.
In the exam please note that additional context will not be given instead you have to look for cue words or need to understand if the queries are running sequentially or concurrently. if the que-ries are running sequentially then scale up(more nodes) if the queries are running concurrently (more users) then scale out(more clusters).
Below is the snippet from Azure, as you can see by increasing the cluster size you are able to add more worker nodes.
SQL endpoint scales horizontally(scale-out) and vertically (scale-up), you have to understand when to use what.
Scale-up-> Increase the size of the cluster from x-small to small, to medium, X Large....
If you are trying to improve the performance of a single query having additional memory, additional nodes and cpu in the cluster will improve the performance.
Scale-out -> Add more clusters, 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.
SQL endpoint
A picture containing diagram Description automatically generated
NEW QUESTION # 127
Which of the following locations hosts the driver and worker nodes of a Databricks-managed clus-ter?
- A. JDBC data source
- B. Databricks Filesystem
- C. Data plane
- D. Control plane
- E. Databricks web application
Answer: C
Explanation:
Explanation
See the Databricks high-level architecture
NEW QUESTION # 128
Which of the following describes a scenario in which a data engineer will want to use a Job cluster instead of
an all-purpose cluster?
- A. An ad-hoc analytics report needs to be developed while minimizing compute costs
- B. A Databricks SQL query needs to be scheduled for upward reporting
- C. An automated workflow needs to be run every 30 minutes
- D. A data team needs to collaborate on the development of a machine learning model
- E. A data engineer needs to manually investigate a production error
Answer: C
NEW QUESTION # 129
Which of the following SQL statements can be used to update a transactions table, to set a flag on the table from Y to N
- A. REPLACE transactions SET active_flag = 'N' WHERE active_flag = 'Y'
- B. MERGE transactions SET active_flag = 'N' WHERE active_flag = 'Y'
- C. UPDATE transactions SET active_flag = 'N' WHERE active_flag = 'Y'
- D. MODIFY transactions SET active_flag = 'N' WHERE active_flag = 'Y'
Answer: A
Explanation:
Explanation
The answer is
UPDATE transactions SET active_flag = 'N' WHERE active_flag = 'Y'
Delta Lake supports UPDATE statements on the delta table, all of the changes as part of the update are ACID compliant.
NEW QUESTION # 130
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. cloudfiles.schemalocation, cloudfiles.checkpointlocation
- B. schemalocation, checkpointlocation
- C. cloudfiles.schemalocation, checkpointlocation
- D. checkpointlocation, schemalocation
- E. checkpointlocation, cloudfiles.schemalocation
Answer: C
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 # 131
A dataset has been defined using Delta Live Tables and includes an expectations clause: CON-STRAINT valid_timestamp EXPECT (timestamp > '2020-01-01') ON VIOLATION FAIL What is the expected behavior when a batch of data containing data that violates these constraints is processed?
- A. Records that violate the expectation are dropped from the target dataset and recorded as invalid in the event log.
- B. Records that violate the expectation are added to the target dataset and recorded as invalid in the event log.
- C. Records that violate the expectation are added to the target dataset and flagged as in-valid in a field added to the target dataset.
- D. Records that violate the expectation cause the job to fail
- E. Records that violate the expectation are dropped from the target dataset and loaded into a quarantine table.
Answer: D
Explanation:
Explanation
The answer is Records that violate the expectation cause the job to fail.
Delta live tables support three types of expectations to fix bad data in DLT pipelines Review below example code to examine these expectations, Diagram Description automatically generated with medium confidence
Invalid records:
Use the expect operator when you want to keep records that violate the expectation. Records that violate the expectation are added to the target dataset along with valid records:
SQL
CONSTRAINT valid_timestamp EXPECT (timestamp > '2020-01-01')
Drop invalid records:
Use the expect or drop operator to prevent the processing of invalid records. Records that violate the expectation are dropped from the target dataset:
SQL
CONSTRAINT valid_timestamp EXPECT (timestamp > '2020-01-01') ON VIOLATION DROP ROW Fail on invalid records:
When invalid records are unacceptable, use the expect or fail operator to halt execution immediately when a record fails validation. If the operation is a table update, the system atomically rolls back the transaction:
SQL
CONSTRAINT valid_timestamp EXPECT (timestamp > '2020-01-01') ON VIOLATION FAIL UP-DATE
NEW QUESTION # 132
How are Delt tables stored?
- A. A Directory where parquet data files are stored in Data plane, a sub directory _delta_log where meta data, history and log is stored in control pane.
- B. Data is stored in Data plane and Metadata and delta log are stored in control pane
- C. A Directory where parquet data files are stored, all of the metadata is stored in parquet files
- D. A Directory where parquet data files are stored, a sub directory _delta_log where meta data, and the transaction log is stored as JSON files.
- E. A Directory where parquet data files are stored, all of the meta data is stored in memory
Answer: D
Explanation:
Explanation
The answer is A Directory where parquet data files are stored, a sub directory _delta_log where meta data, and the transaction log is stored as JSON files.
Timeline Description automatically generated
NEW QUESTION # 133
A data engineer needs to dynamically create a table name string using three Python varia-bles: region, store,
and year. An example of a table name is below when region = "nyc", store = "100", and year = "2021":
nyc100_sales_2021
Which of the following commands should the data engineer use to construct the table name in Py-thon?
- A. "{region}{store}_sales_2023"
- B. f"{region}{store}_sales_2023"
- C. "{region}+{store}+"_sales_"+2023"
- D. "{region}+{store}+_sales_+2023"
- E. f"{region}+{store}+_sales_+2023"
Answer: B
NEW QUESTION # 134
Which of the following operations are not supported on a streaming dataset view?
spark.readStream.format("delta").table("sales").createOrReplaceTempView("streaming_view")
- A. SELECT id, count(*) FROM streaming_view GROUP BY id
- B. SELECT sum(unitssold) FROM streaming_view
- C. SELECT id, sum(unitssold) FROM streaming_view GROUP BY id ORDER BY id
- D. SELECT max(unitssold) FROM streaming_view
- E. SELECT * FROM streadming_view ORDER BY id
Answer: E
Explanation:
Explanation
The answer isSELECT * FROM streadming_view order by id Please Note: Sorting with Group by will work without any issues see below explanation for each option of the options, Graphical user interface, text, application Description automatically generated
Certain operations are not allowed on streaming data, please see highlighted in bold.
https://spark.apache.org/docs/latest/structured-streaming-programming-guide.html#unsupported-operations
* Multiple streaming aggregations (i.e. a chain of aggregations on a streaming DF) are not yet supported on streaming Datasets.
* Limit and take the first N rows are not supported on streaming Datasets.
* Distinct operations on streaming Datasets are not supported.
* Deduplication operation is not supported after aggregation on a streaming Datasets.
* Sorting operations are supported on streaming Datasets only after an aggregation and in Complete Output Mode.
Note: Sorting without aggregation function is not supported.
Here is the sample code to prove this,
Setup test stream
Graphical user interface, text, application, email Description automatically generated
Sum aggregation function has no issues on stream
Graphical user interface, application Description automatically generated
Max aggregation function has no issues on stream
Graphical user interface, application Description automatically generated
Group by with Order by has no issues on stream
Group by has no issues on stream
Table Description automatically generated
Order by without group by fails.
Graphical user interface, text, application Description automatically generated
NEW QUESTION # 135
Once a cluster is deleted, below additional actions need to performed by the administrator
- A. Remove logs
- B. Remove virtual machines but storage and networking are automatically dropped
- C. No action needs to be performed. All resources are automatically removed.
- D. Remove networking but Virtual machines and storage disks are automatically dropped
- E. Drop storage disks but Virtual machines and networking are automatically dropped
Answer: C
Explanation:
Explanation
What is Delta?
Delta lake is
* Open source
* Builds up on standard data format
* Optimized for cloud object storage
* Built for scalable metadata handling
Delta lake is not
* Proprietary technology
* Storage format
* Storage medium
* Database service or data warehouse
NEW QUESTION # 136
You are asked to write a python function that can read data from a delta table and return the Data-Frame, which of the following is correct?
- A. Write SQL UDF to return a DataFrame
- B. Write SQL UDF that can return tabular data
- C. Python function cannot return a DataFrame
- D. Python function can return a DataFrame
- E. Python function will result in out of memory error due to data volume
Answer: E
Explanation:
Explanation
The answer is Python function can return a DataFrame
The function would something like this,
1.get_source_dataframe(tablename):
2. df = spark.read.table(tablename)
3.return df
df = get_source_dataframe('test_table')
since there is no action spark returns a Dataframe and assigns to df python variable
NEW QUESTION # 137
Which of the following commands can be used to run one notebook from another notebook?
- A. only job clusters can run notebook
- B. execute.utils.run("full notebook path")
- C. spark.notebook.run("full notebook path")
- D. notebook.utils.run("full notebook path")
- E. dbutils.notebook.run("full notebook path")
Answer: E
Explanation:
Explanation
The answer is dbutils.notebook.run(" full notebook path ")
Here is the full command with additional options.
run(path: String, timeout_seconds: int, arguments: Map): String
1.dbutils.notebook.run("ful-notebook-name", 60, {"argument": "data", "argument2": "data2", ...})
NEW QUESTION # 138
The Delta Live Tables Pipeline is configured to run in Development mode using the Triggered Pipeline Mode.
what is the expected outcome after clicking Start to update the pipeline?
- A. All datasets will be updated once and the pipeline will shut down. The compute resources will be terminated
- B. All datasets will be updated once and the pipeline will shut down. The compute resources will persist to allow for additional development and testing
- C. All datasets will be updated at set intervals until the pipeline is shut down. The compute resources will be deployed for the update and terminated when the pipeline is stopped
- D. All datasets will be updated at set intervals until the pipeline is shut down. The compute resources will persist after the pipeline is stopped to allow for additional development and testing
- E. All datasets will be updated continuously and the pipeline will not shut down. The compute resources will persist with the pipeline
Answer: E
Explanation:
Explanation
The answer is All datasets will be updated once and the pipeline will shut down. The compute re-sources will persist to allow for additional testing.
DLT pipeline supports two modes Development and Production, you can switch between the two based on the stage of your development and deployment lifecycle.
Development and production modes
When you run your pipeline in development mode, the Delta Live Tables system:
*Reuses a cluster to avoid the overhead of restarts.
*Disables pipeline retries so you can immediately detect and fix errors.
In production mode, the Delta Live Tables system:
*Restarts the cluster for specific recoverable errors, including memory leaks and stale credentials.
*Retries execution in the event of specific errors, for example, a failure to start a cluster.
Use the buttons in the Pipelines UI to switch between develop-ment and production modes. By default, pipelines run in development mode.
Switching between development and production modes only controls cluster and pipeline execution behavior.
Storage locations must be configured as part of pipeline settings and are not affected when switching between modes.
Please review additional DLT concepts using below link
https://docs.databricks.com/data-engineering/delta-live-tables/delta-live-tables-concepts.html#delta-live-tables-c
NEW QUESTION # 139
Which of the following is not a privilege in the Unity catalog?
- A. MODIFY
- B. DELETE
- C. EXECUTE
- D. SELECT
- E. CREATE TABLE
Answer: B
Explanation:
Explanation
The Answer is DELETE and UPDATE permissions do not exit, you have to use MODIFY which provides both Update and Delete permissions.
Please note: TABLE ACL privilege types are different from Unity Catalog privilege types, please read the question carefully.
Here is the list of all privileges in Unity Catalog:
Unity Catalog Privileges
https://learn.microsoft.com/en-us/azure/databricks/spark/latest/spark-sql/language-manual/sql-ref-privileges#priv Table ACL privileges
https://learn.microsoft.com/en-us/azure/databricks/security/access-control/table-acls/object-privileges#privileges
NEW QUESTION # 140
One of the team members Steve who has the ability to create views, created a new view called re-gional_sales_vw on the existing table called sales which is owned by John, and the second team member Kevin who works with regional sales managers wanted to query the data in region-al_sales_vw, so Steve granted the permission to Kevin using command GRANT VIEW, USAGE ON regional_sales_vw to [email protected] but Kevin is still unable to access the view?
- A. Table access control is not enabled on the table and view
- B. Kevin needs owner access on the view regional_sales_vw
- C. Kevin needs select access on the table sales
- D. Steve is not the owner of the sales table
- E. Kevin is not the owner of the sales table
Answer: D
Explanation:
Explanation
Ownership determines whether or not you can grant privileges on derived objects to other users, since Steve is not the owner of the underlying sales table, he can not grant access to the table or data in the table indirectly.
Only owner(user or group) can grant access to a object
https://docs.microsoft.com/en-us/azure/databricks/security/access-control/table-acls/object-privileges#a-user-has Data object privileges - Azure Databricks | Microsoft Doc
NEW QUESTION # 141
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. .trigger(continuous="once")
- B. .processingTime("once")
- C. .trigger(once=True)
- D. .trigger(processingTime="once")
- E. .processingTime(1)
Answer: C
NEW QUESTION # 142
What is the purpose of a gold layer in Multi-hop architecture?
- A. Data quality checks and schema enforcement
- B. Optimizes ETL throughput and analytic query performance
- C. Eliminate duplicate records
- D. Powers ML applications, reporting, dashboards and adhoc reports.
- E. Preserves grain of original data, without any aggregations
Answer: D
Explanation:
Explanation
The answer is Powers ML applications, reporting, dashboards and adhoc reports.
Review the below link for more info,
Medallion Architecture - Databricks
Gold Layer:
1.Powers Ml applications, reporting, dashboards, ad hoc analytics
2.Refined views of data, typically with aggregations
3.Reduces strain on production systems
4.Optimizes query performance for business-critical 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.
NEW QUESTION # 143
Which of the following SQL keywords can be used to append new rows to an existing Delta table?
- A. UPDATE
- B. DELETE
- C. INSERT INTO
- D. UNION
- E. COPY
Answer: C
NEW QUESTION # 144
......
Databricks Certified Professional Data Engineer Certification Exam can be attempted by professionals and students who have experience in data engineering, data management, ETL, and data processing. The preparation for the exam can be done via online training courses such as the Databricks Data Engineering Certification Preparation Course, the online Databricks Documentation, and different study materials such as books and videos from verified training providers.
Databricks Certified Professional Data Engineer exam is a practical and hands-on exam that requires candidates to demonstrate their ability to design and implement data pipelines using Databricks. Databricks-Certified-Professional-Data-Engineer exam consists of multiple-choice questions and hands-on exercises that test the candidate's ability to apply their knowledge to real-world scenarios. Databricks-Certified-Professional-Data-Engineer exam is designed to be challenging, but fair, and it is intended to accurately assess a candidate's skills and knowledge.
Truly Beneficial For Your Databricks Exam: https://evedumps.testkingpass.com/Databricks-Certified-Professional-Data-Engineer-testking-dumps.html