[Sep 18, 2023] Fully Updated Free Actual Databricks Databricks-Certified-Professional-Data-Engineer Exam Questions
Free Databricks-Certified-Professional-Data-Engineer Questions for Databricks Databricks-Certified-Professional-Data-Engineer Exam [Sep-2023]
To take the Databricks Certified Professional Data Engineer certification exam, candidates must have a solid understanding of data engineering concepts, as well as experience using Databricks. Databricks-Certified-Professional-Data-Engineer exam consists of multiple-choice questions and performance-based tasks, which require candidates to demonstrate their ability to perform specific data engineering tasks using Databricks.
Databricks-Certified-Professional-Data-Engineer certification exam is a comprehensive test that covers all aspects of data engineering with Databricks. Databricks-Certified-Professional-Data-Engineer exam is designed to test the candidate's knowledge of Databricks architecture, data engineering concepts, data processing with Databricks, and data storage with Databricks. Databricks-Certified-Professional-Data-Engineer exam also tests the candidate's ability to design, implement, and maintain data engineering solutions using Databricks.
Databricks Certified Professional Data Engineer is an exam designed for professionals who are willing to demonstrate their expertise in building and managing big data pipelines using Databricks. Databricks is a unified analytics platform that provides a collaborative environment for processing large-scale data. The Databricks Certified Professional Data Engineer exam validates the candidate's ability to design, build, and deploy large-scale data processing solutions using Databricks.
NEW QUESTION # 31
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, cloudfiles.checkpointlocation
- C. checkpointlocation, schemalocation
- D. schemalocation, checkpointlocation
- E. cloudfiles.schemalocation, checkpointlocation
Answer: E
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 # 32
The operations team is interested in monitoring the recently launched product, team wants to set up an email alert when the number of units sold increases by more than 10,000 units. They want to monitor this every 5 mins.
Fill in the below blanks to finish the steps we need to take
* Create ___ query that calculates total units sold
* Setup ____ with query on trigger condition Units Sold > 10,000
* Setup ____ to run every 5 mins
* Add destination ______
- A. Python, Job, Refresh, email address
- B. SQL, Job, Refresh, email address
- C. SQL, Alert, Refresh, email address
- D. SQL, Job, SQL Cluster, email address
- E. Python, Job, SQL Cluster, email address
Answer: C
Explanation:
Explanation
The answer is SQL, Alert, Refresh, email address
Here the steps from Databricks documentation,
Create an alert
Follow these steps to create an alert on a single column of a query.
1.Do one of the following:
*Click Create in the sidebar and select Alert.
*Click Alerts in the sidebar and click the + New Alert button.
2.Search for a target query.
Graphical user interface, text, application Description automatically generated
To alert on multiple columns, you need to modify your query. See Alert on multiple col-umns.
3.In the Trigger when field, configure the alert.
*The Value column drop-down controls which field of your query result is evaluated.
*The Condition drop-down controls the logical operation to be applied.
*The Threshold text input is compared against the Value column using the Condition you specify.
Note
If a target query returns multiple records, Databricks SQL alerts act on the first one. As you change the Value column setting, the current value of that field in the top row is shown beneath it.
4.In the When triggered, send notification field, select how many notifications are sent when your alert is triggered:
*Just once: Send a notification when the alert status changes from OK to TRIGGERED.
*Each time alert is evaluated: Send a notification whenever the alert status is TRIGGERED regardless of its status at the previous evaluation.
*At most every: Send a notification whenever the alert status is TRIGGERED at a spe-cific interval. This choice lets you avoid notification spam for alerts that trigger of-ten.
Regardless of which notification setting you choose, you receive a notification whenever the status goes from OK to TRIGGERED or from TRIGGERED to OK. The schedule settings affect how many notifications you will receive if the status remains TRIGGERED from one execution to the next. For details, see Notification frequency.
5.In the Template drop-down, choose a template:
*Use default template: Alert notification is a message with links to the Alert configuration screen and the Query screen.
*Use custom template: Alert notification includes more specific information about the alert.
a.A box displays, consisting of input fields for subject and body. Any static content is valid, and you can incorporate built-in template variables:
*ALERT_STATUS: The evaluated alert status (string).
*ALERT_CONDITION: The alert condition operator (string).
*ALERT_THRESHOLD: The alert threshold (string or number).
*ALERT_NAME: The alert name (string).
*ALERT_URL: The alert page URL (string).
*QUERY_NAME: The associated query name (string).
*QUERY_URL: The associated query page URL (string).
*QUERY_RESULT_VALUE: The query result value (string or number).
*QUERY_RESULT_ROWS: The query result rows (value array).
*QUERY_RESULT_COLS: The query result columns (string array).
An example subject, for instance, could be: Alert "{{ALERT_NAME}}" changed status to
{{ALERT_STATUS}}.
b.Click the Preview toggle button to preview the rendered result.
Important
The preview is useful for verifying that template variables are rendered cor-rectly. It is not an accurate representation of the eventual notification content, as each alert destination can display notifications differently.
c.Click the Save Changes button.
6.In Refresh, set a refresh schedule. An alert's refresh schedule is independent of the query's refresh schedule.
*If the query is a Run as owner query, the query runs using the query owner's cre-dential on the alert's refresh schedule.
*If the query is a Run as viewer query, the query runs using the alert creator's cre-dential on the alert's refresh schedule.
7.Click Create Alert.
8.Choose an alert destination.
Important
If you skip this step you will not be notified when the alert is triggered.
NEW QUESTION # 33
A data engineer is overwriting data in a table by deleting the table and recreating the table. Another data
engineer suggests that this is inefficient and the table should simply be overwritten instead.
Which of the following reasons to overwrite the table instead of deleting and recreating the table is incorrect?
- A. Overwriting a table allows for concurrent queries to be completed while in progress
- B. Overwriting a table maintains the old version of the table for Time Travel
- C. Overwriting a table is efficient because no files need to be deleted
- D. Overwriting a table results in a clean table history for logging and audit purposes
- E. Overwriting a table is an atomic operation and will not leave the table in an unfinished state
Answer: D
NEW QUESTION # 34
How do you upgrade an existing workspace managed table to a unity catalog table?
- A. Create table table_name format = UNITY as select * from old_table_name
- B. ALTER TABLE table_name SET UNITY_CATALOG = TRUE
- C. Create table table_name as select * from hive_metastore.old_schema.old_table
- D. Create or replace table_name format = UNITY using deep clone old_table_name
- E. Create table catalog_name.schema_name.table_name
as select * from hive_metastore.old_schema.old_table
Answer: E
Explanation:
Explanation
The answer is Create table catalog_name.schema_name.table_name as select * from hive_metastore.old_schema.old_table Basically, we are moving the data from an internal hive metastore to a metastore and catalog that is registered in the Unity catalog.
note: if it is a managed table the data is copied to a different storage account, for a large tables this can take a lot of time. For an external table the process is different.
Managed table: Upgrade a managed to Unity Catalog
External table: Upgrade an external table to Unity Catalog
NEW QUESTION # 35
if you run the command VACUUM transactions retain 0 hours? What is the outcome of this command?
- A. Command will be successful, but historical data will be removed
- B. Command runs successful and compacts all of the data in the table
- C. Command will be successful, but no data is removed
- D. Command will fail if you have an active transaction running
- E. Command will fail, you cannot run the command with retentionDurationcheck enabled
Answer: E
Explanation:
Explanation
The answer is,
Command will fail, you cannot run the command with retentionDurationcheck enabled.
1.VACUUM [ [db_name.]table_name | path] [RETAIN num HOURS] [DRY RUN]
*Recursively vacuum directories associated with the Delta table and remove data files that are no longer in the latest state of the transaction log for the table and are older than a retention threshold. Default is 7 Days.
*The reason this check is enabled is because, DELTA is trying to prevent unintentional deletion of history, and also one important thing to point out is with 0 hours of retention there is a possibility of data loss(see below kb) Documentation in VACUUM https://docs.delta.io/latest/delta-utility.html
https://kb.databricks.com/delta/data-missing-vacuum-parallel-write.html
NEW QUESTION # 36
The data engineering team noticed that one of the job fails randomly as a result of using spot in-stances, what feature in Jobs/Tasks can be used to address this issue so the job is more stable when using spot instances?
- A. Add a retry policy to the task
- B. Use Jobs runs, active runs UI section to monitor and restart the job
- C. Restart the job cluster, job automatically restarts
- D. Use Databrick REST API to monitor and restart the job
- E. Add second task and add a check condition to rerun the first task if it fails
Answer: A
Explanation:
Explanation
The answer is, Add a retry policy to the task
Tasks in Jobs support Retry Policy, which can be used to retry a failed tasks, especially when using spot instance it is common to have failed executors or driver.
NEW QUESTION # 37
What is the type of table created when you issue SQL DDL command CREATE TABLE sales (id int, units int)
- A. Query fails due to missing location
- B. Managed Parquet table
- C. External Table
- D. Managed Delta table
- E. Query fails due to missing format
Answer: D
Explanation:
Explanation
Answer is Managed Delta table
Anytime a table is created without the Location keyword it is considered a managed table, by de-fault all managed tables DELTA tables Syntax CREATE TABLE table_name ( column column_data_type...)
NEW QUESTION # 38
you are currently working on creating a spark stream process to read and write in for a one-time micro batch, and also rewrite the existing target table, fill in the blanks to complete the below command sucesfully.
1.spark.table("source_table")
2..writeStream
3..option("____", "dbfs:/location/silver")
4..outputMode("____")
5..trigger(Once=____)
6..table("target_table")
- A. checkpointlocation, overwrite, True
- B. targetlocation, overwrite, True
- C. checkpointlocation, True, overwrite
- D. checkpointlocation, complete, True
- E. checkpointlocation, True, complete
Answer: D
NEW QUESTION # 39
Which of the below SQL commands creates a session scoped temporary view?
- A. 1.CREATE OR REPLACE LOCAL TEMPORARY VIEW view_name
2.AS SELECT * FROM table_name - B. 1.CREATE OR REPLACE GLOBAL TEMPORARY VIEW view_name
2.AS SELECT * FROM table_name - C. 1.CREATE OR REPLACE TEMPORARY VIEW view_name
2.AS SELECT * FROM table_name
(Correct) - D. 1.CREATE OR REPLACE VIEW view_name
2.AS SELECT * FROM table_name - E. 1.CREATE OR REPLACE LOCAL VIEW view_name
2.AS SELECT * FROM table_name
Answer: C
Explanation:
Explanation
The answer is
1.CREATE OR REPLACE TEMPORARY VIEW view_name
2.AS SELECT * FROM table_name
The default temporary view is session scoped, as soon as the session ends or if a notebook is de-tached session scoped temporary view is dropped.
NEW QUESTION # 40
Which of the below SQL commands create a Global temporary view?
- A. 1.CREATE OR REPLACE VIEW view_name
2. AS SELECT * FROM table_name - B. 1. CREATE OR REPLACE GLOBAL TEMPORARY VIEW view_name
2. AS SELECT * FROM table_name
(Correct) - C. 1. CREATE OR REPLACE LOCAL TEMPORARY VIEW view_name
2. AS SELECT * FROM table_name - D. 1. CREATE OR REPLACE LOCAL VIEW view_name
2. AS SELECT * FROM table_name - E. 1.CREATE OR REPLACE TEMPORARY VIEW view_name
2. AS SELECT * FROM table_name
Answer: B
Explanation:
Explanation
1. CREATE OR REPLACE GLOBAL TEMPORARY VIEW view_name
2. AS SELECT * FROM table_name
There are two types of temporary views that can be created Local and Global
*A session-scoped temporary view is only available with a spark session, so another note-book in the same cluster can not access it. if a notebook is detached and reattached local temporary view is lost.
*A global temporary view is available to all the notebooks in the cluster but if a cluster re-starts a global temporary view is lost.
NEW QUESTION # 41
Kevin is the owner of both the sales table and regional_sales_vw view which uses the sales table as the underlying source for the data, and Kevin is looking to grant select privilege on the view regional_sales_vw to one of newly joined team members Steven. Which of the following is a true statement?
- A. Kevin can not grant access to Steven since he does not have security admin privilege
- B. Kevin can grant access to the view, because he is the owner of the view and the under-lying table
- C. Kevin although is the owner but does not have ALL PRIVILEGES permission
- D. Kevin can not grant access to Steven since he does have workspace admin privilege
- E. Steve will also require SELECT access on the underlying table
Answer: B
Explanation:
Explanation
The answer is, Kevin can grant access to the view, because he is the owner of the view and the un-derlying table, Ownership determines whether or not you can grant privileges on derived objects to other users, a user who creates a schema, table, view, or function becomes its owner. The owner is granted all privileges and can grant privileges to other users
NEW QUESTION # 42
While investigating a data issue, you wanted to review yesterday's version of the table using below command, while querying the previous version of the table using time travel you realized that you are no longer able to view the historical data in the table and you could see it the table was updated yesterday based on the table history(DESCRIBE HISTORY table_name) command what could be the reason why you can not access this data?
SELECT * FROM table_name TIMESTAMP AS OF date_sub(current_date(), 1)
- A. A command VACUUM table_name RETAIN 0 was ran on the table
- B. Time travel is disabled
- C. Time travel must be enabled before you query previous data
- D. You currently do not have access to view historical data
- E. By default, historical data is cleaned every 180 days in DELTA
Answer: A
Explanation:
Explanation
The answer is, VACUUM table_name RETAIN 0 was ran
The VACUUM command recursively vacuums directories associated with the Delta table and re-moves data files that are no longer in the latest state of the transaction log for the table and are older than a retention threshold. The default is 7 Days.
When VACUUM table_name RETAIN 0 is ran all of the historical versions of data are lost time travel can only provide the current state.
NEW QUESTION # 43
Two junior data engineers are authoring separate parts of a single data pipeline notebook. They are working on
separate Git branches so they can pair program on the same notebook simultaneously. A senior data engineer
experienced in Databricks suggests there is a better alternative for this type of collaboration.
Which of the following supports the senior data engineer's claim?
- A. Databricks Notebooks support commenting and notification comments
- B. Databricks Notebooks support the creation of interactive data visualizations
- C. Databricks Notebooks support real-time co-authoring on a single notebook
- D. Databricks Notebooks support the use of multiple languages in the same notebook
- E. Databricks Notebooks support automatic change-tracking and versioning
Answer: C
NEW QUESTION # 44
What is the purpose of gold layer in Multi hop architecture?
- A. Data quality checks and schema enforcement
- B. Eliminate duplicate records
- C. Preserves grain of original data, without any aggregations
- D. Optimized query performance for business-critical data
- E. Optimizes ETL throughput and analytic query performance
Answer: D
Explanation:
Explanation
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 # 45
A data analyst has noticed that their Databricks SQL queries are running too slowly. They claim that this issue
is affecting all of their sequentially run queries. They ask the data engineering team for help. The data
engineering team notices that each of the queries uses the same SQL endpoint, but the SQL endpoint is not
used by any other user.
Which of the following approaches can the data engineering team use to improve the latency of the data
analyst's queries?
- A. They can turn on the Serverless feature for the SQL endpoint and change the Spot In-stance Policy to
"Reliability Optimized" - B. They can increase the maximum bound of the SQL endpoint's scaling range
- C. They can increase the cluster size of the SQL endpoint
- D. They can turn on the Serverless feature for the SQL endpoint
- E. They can turn on the Auto Stop feature for the SQL endpoint
Answer: C
NEW QUESTION # 46
How do you access or use tables in the unity catalog?
- A. catalog_name.schema_name.table_name
- B. schema_name.catalog_name.table_name
- C. catalog_name.database_name.schema_name.table_name
- D. catalog_name.table_name
- E. schema_name.table_name
Answer: A
Explanation:
Explanation
The answer is catalog_name.schema_name.table_name
Graphical user interface, diagram Description automatically generated
Note: Database and Schema are analogous they are interchangeably used in the Unity catalog.
FYI, A catalog is registered under a metastore, by default every workspace has a default metastore called hive_metastore, with a unity catalog you have the ability to create meatstores and share that across multiple workspaces.
Diagram Description automatically generated
NEW QUESTION # 47
You noticed a colleague is manually copying the data to the backup folder prior to running an up-date command, incase if the update command did not provide the expected outcome so he can use the backup copy to replace table, which Delta Lake feature would you recommend simplifying the process?
- A. Use DEEP CLONE to clone the table prior to update to make a backup copy
- B. Cloud object storage retains previous version of the file
- C. Use time travel feature to refer old data instead of manually copying
- D. Use SHADOW copy of the table as preferred backup choice
- E. Cloud object storage automatically backups the data
Answer: C
Explanation:
Explanation
The answer is, Use time travel feature to refer old data instead of manually copying.
https://databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
1.SELECT count(*) FROM my_table TIMESTAMP AS OF "2019-01-01"
2.SELECT count(*) FROM my_table TIMESTAMP AS OF date_sub(current_date(), 1)
3.SELECT count(*) FROM my_table TIMESTAMP AS OF "2019-01-01 01:30:00.000"
NEW QUESTION # 48
What is the main difference between the below two commands?
1.INSERT OVERWRITE table_name
2.SELECT * FROM table
1.CREATE OR REPLACE TABLE table_name
2.AS SELECT * FROM table
- A. Both are same and results in identical outcomes
- B. INSERT OVERWRITE clears historical data versions by de-fault, CREATE OR REPLACE maintains the historical data versions by default
- C. INSERT OVERWRITE replaces data by default, CREATE OR REPLACE replaces data and Schema by default
- D. INSERT OVERWRITE maintains historical data versions by de-fault, CREATE OR REPLACEclears the historical data versions by default
- E. INSERT OVERWRITE replaces data and schema by default, CREATE OR REPLACEreplaces data by default
Answer: C
Explanation:
Explanation
The main difference between INSERT OVERWRITE and CREATE OR REPLACE TABLE(CRAS) is that CRAS can modify the schema of the table, i.e it can add new columns or change data types of existing columns. By default INSERT OVERWRITE only overwrites the data.
INSERT OVERWRITE can also be used to overwrite schema, only when
spark.databricks.delta.schema.autoMerge.enabled is set true if this option is not enabled and if there is a schema mismatch command will fail.
NEW QUESTION # 49
......
Validate your Databricks-Certified-Professional-Data-Engineer Exam Preparation with Databricks-Certified-Professional-Data-Engineer Practice Test: https://www.actual4cert.com/Databricks-Certified-Professional-Data-Engineer-real-questions.html