Databricks Data Engineer Professional Practice Exam

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?

• date = spark.conf.get("date")

• input_dict = input()
  date= input_dict["date"]

• import sys
  date = sys.argv[1]

• date = dbutils.notebooks.getParam("date")

• dbutils.widgets.text("date", "null")
  date = dbutils.widgets.get("date")

Answer explanation

In https://docs.databricks.com/en/notebooks/notebook-workflows.html#dbutilsnotebook-api the "run Example" is an equivalent use-case as this.

dbutils.widgets.text("date", "null")
date = dbutils.widgets.get("date")

The Databricks workspace administrator has configured interactive clusters for each of the data engineering groups. To control costs, clusters are set to terminate after 30 minutes of inactivity. Each user should be able to execute workloads against their assigned clusters at any time of the day.
Assuming users have been added to a workspace but not granted any permissions, which of the following describes the minimal permissions a user would need to start and attach to an already configured cluster.

• "Can Manage" privileges on the required cluster

• Workspace Admin privileges, cluster creation allowed, "Can Attach To" privileges on the required cluster

• Cluster creation allowed, "Can Attach To" privileges on the required cluster

• "Can Restart" privileges on the required cluster

• Cluster creation allowed, "Can Restart" privileges on the required cluster

Answer explanation

Can restart is is minimum permission to attach and start the cluster. For more information.

Read this page https://docs.databricks.com/en/security/auth-authz/access-control/cluster-acl.html

When scheduling Structured Streaming jobs for production, which configuration automatically recovers from query failures and keeps costs low?

• Cluster: New Job Cluster;
  Retries: Unlimited;
  Maximum Concurrent Runs: Unlimited

• Cluster: New Job Cluster;
  Retries: None;
  Maximum Concurrent Runs: 1

• Cluster: Existing All-Purpose Cluster;
  Retries: Unlimited;
  Maximum Concurrent Runs: 1

• Cluster: New Job Cluster;
  Retries: Unlimited;
  Maximum Concurrent Runs: 1

• Cluster: Existing All-Purpose Cluster;
  Retries: None;
  Maximum Concurrent Runs: 1

Answer explanation

Maximum concurrent runs: Set to 1. There must be only one instance of each query concurrently active. Retries: Set to Unlimited.

https://docs.databricks.com/en/structured-streaming/query-recovery.html

The data engineering team has configured a Databricks SQL query and alert to monitor the values in a Delta Lake table. The recent_sensor_recordings table contains an identifying sensor_id alongside the timestamp and temperature for the most recent 5 minutes of recordings.
The query on the left is used to create the alert:

The query is set to refresh each minute and always completes in less than 10 seconds. The alert is set to trigger when mean (temperature) > 120. Notifications are triggered to be sent at most every 1 minute.
If this alert raises notifications for 3 consecutive minutes and then stops, which statement must be true?

• The total average temperature across all sensors exceeded 120 on three consecutive executions of the query

• The recent_sensor_recordings table was unresponsive for three consecutive runs of the query

• The source query failed to update properly for three consecutive minutes and then restarted

• The maximum temperature recording for at least one sensor exceeded 120 on three consecutive executions of the query

• The average temperature recordings for at least one sensor exceeded 120 on three consecutive executions of the query

Answer explanation

https://www.myexamcollection.com/databricks-certified-professional-data-engineer-databricks-certified-professional-data-engineer-exam-question-answers.html

A junior developer complains that the code in their notebook isn't producing the correct results in the development environment. A shared screenshot reveals that while they're using a notebook versioned with Databricks Repos, they're using a personal branch that contains old logic. The desired branch named dev-2.3.9 is not available from the branch selection dropdown.
Which approach will allow this developer to review the current logic for this notebook?

• Use Repos to make a pull request use the Databricks REST API to update the current branch to dev-2.3.9

• Use Repos to pull changes from the remote Git repository and select the dev-2.3.9 branch.

• Use Repos to checkout the dev-2.3.9 branch and auto-resolve conflicts with the current branch

• Merge all changes back to the main branch in the remote Git repository and clone the repo again

• Use Repos to merge the current branch and the dev-2.3.9 branch, then make a pull request to sync with the remote repository

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).
(See screenshot on the left)
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.

Answer explanation

https://docs.databricks.com/en/external-data/jdbc.html

The data science team has created and logged a production model using MLflow. The following code correctly imports and applies the production model to output the predictions as a new DataFrame named preds with the schema "customer_id LONG, predictions DOUBLE, date DATE".

The data science team would like predictions saved to a Delta Lake table with the ability to compare all predictions across time. Churn predictions will be made at most once per day.
Which code block accomplishes this task while minimizing potential compute costs?

• preds.write.mode("append").saveAsTable("churn_preds")

• preds.write.format("delta").save("/preds/churn_preds")

• 
• 
• 

Answer explanation

You need:

- Batch operation since it is at most once a day - Append, since you need to keep track of past predictions

You don't need to specify "format" when you use saveAsTable.

An upstream source writes Parquet data as hourly batches to directories named with the current date. A nightly batch job runs the following code to ingest all data from the previous day as indicated by the date variable:

Assume that the fields customer_id and order_id serve as a composite key to uniquely identify each order.
If the upstream system is known to occasionally produce duplicate entries for a single order hours apart, which statement is correct?

• Each write to the orders table will only contain unique records, and only those records without duplicates in the target table will be written.

• Each write to the orders table will only contain unique records, but newly written records may have duplicates already present in the target table.

• Each write to the orders table will only contain unique records; if existing records with the same key are present in the target table, these records will be overwritten.

• Each write to the orders table will only contain unique records; if existing records with the same key are present in the target table, the operation will fail.

• Each write to the orders table will run deduplication over the union of new and existing records, ensuring no duplicate records are present.

Answer explanation

In the provided code, the .dropDuplicates(["customer_id","order_id"]) operation is performed on the data loaded from the Parquet files. This operation ensures that only unique records, based on the composite key of "customer_id" and "order_id," are retained in the DataFrame before writing to the "orders" table.

However, this operation does not consider duplicates that may already exist in the "orders" table. It only filters duplicates from the current batch of data. If there are duplicates in the "orders" table from previous batches, they will remain in the table.

So, newly written records will not have duplicates within the batch being written, but duplicates from previous batches may still exist in the target table.

A junior member of the data engineering team is exploring the language interoperability of Databricks notebooks. The intended outcome of the below code is to register a view of all sales that occurred in countries on the continent of Africa that appear in the geo_lookup table.
Before executing the code, running SHOW TABLES on the current database indicates the database contains only two tables: geo_lookup and sales.

Which statement correctly describes the outcome of executing these command cells in order in an interactive notebook?

• Both commands will succeed. Executing show tables will show that countries_af and sales_af have been registered as views.

• Cmd 1 will succeed. Cmd 2 will search all accessible databases for a table or view named countries_af: if this entity exists, Cmd 2 will succeed.

• Cmd 1 will succeed and Cmd 2 will fail. countries_af will be a Python variable representing a PySpark DataFrame.

• Both commands will fail. No new variables, tables, or views will be created.

• Cmd 1 will succeed and Cmd 2 will fail. countries_af will be a Python variable containing a list of strings.

Answer explanation

Cmd 1 is a PySpark command that collects the list of countries from the 'geo_lookup' table where the continent is Africa ('AF'). This command will execute successfully, resulting in countries_af being a list of country names (strings) in Python's local memory.

Cmd 2 is an SQL command intended to create a view named 'sales_af' from the 'sales' table, filtered by the cities in the countries_af list. However, this will fail because the countries_af variable exists in the Python environment and is not recognized in the SQL context. SQL does not have access to Python variables directly; they are two separate execution contexts within a Databricks notebook. There is no table or view named countries_af that SQL can reference; it is merely a Python list variable.

A Delta table of weather records is partitioned by date and has the below schema: date DATE, device_id INT, temp FLOAT, latitude FLOAT, longitude FLOAT
To find all the records from within the Arctic Circle, you execute a query with the below filter: latitude > 66.3
Which statement describes how the Delta engine identifies which files to load?

• All records are cached to an operational database and then the filter is applied

• The Parquet file footers are scanned for min and max statistics for the latitude column

• All records are cached to attached storage and then the filter is applied

• The Delta log is scanned for min and max statistics for the latitude column

• The Hive metastore is scanned for min and max statistics for the latitude column

Answer explanation

In the Transaction log, Delta Lake captures statistics for each data file of the table. These statistics indicate per file:

- Total number of records

- Minimum value in each column of the first 32 columns of the table

- Maximum value in each column of the first 32 columns of the table

- Null value counts for in each column of the first 32 columns of the table

When a query with a selective filter is executed against the table, the query optimizer uses these statistics to generate the query result. it leverages them to identify data files that may contain records matching the conditional filter.

For the SELECT query in the question, The transaction log is scanned for min and max statistics for the price colum