Databricks Databricks Certifications DATABRICKS-CERTIFIED-PROFESSIONAL-DATA-ENGINEER Questions & Answers

• Question 81:

  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?

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

  B. 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.

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

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

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

  Correct Answer: E

  https://www.databricks.com/blog/2020/08/31/introducing-the-databricks-web-terminal.html The code is using %sh to execute shell code on the driver node. This means that the code is not taking advantage of the worker nodes or Databricks optimized Spark. This is why the code is taking longer to execute. A better approach would be to use Databricks libraries and APIs to read and write data from Git and DBFS, and to leverage the parallelism and performance of Spark. For example, you can use the Databricks Connect feature to run your Python code on a remote Databricks cluster, or you can use the Spark Git Connector to read data from Git repositories as Spark DataFrames.

• Question 82:

  The data architect has decided that once data has been ingested from external sources into the

  Databricks Lakehouse, table access controls will be leveraged to manage permissions for all production tables and views.

  The following logic was executed to grant privileges for interactive queries on a production database to the core engineering group.

  GRANT USAGE ON DATABASE prod TO eng;

  GRANT SELECT ON DATABASE prod TO eng;

  Assuming these are the only privileges that have been granted to the eng group and that these users are not workspace administrators, which statement describes their privileges?

  A. Group members have full permissions on the prod database and can also assign permissions to other users or groups.

  B. Group members are able to list all tables in the prod database but are not able to see the results of any queries on those tables.

  C. Group members are able to query and modify all tables and views in the prod database, but cannot create new tables or views.

  D. Group members are able to query all tables and views in the prod database, but cannot create or edit anything in the database.

  E. Group members are able to create, query, and modify all tables and views in the prod database, but cannot define custom functions.

  Correct Answer: D

  The GRANT USAGE ON DATABASE prod TO eng command grants the eng group the permission to use the prod database, which means they can list and access the tables and views in the database. The GRANT SELECT ON DATABASE prod TO eng command grants the eng group the permission to select data from the tables and views in the prod database, which means they can query the data using SQL or DataFrame API. However, these commands do not grant the eng group any other permissions, such as creating, modifying, or deleting tables and views, or defining custom functions. Therefore, the eng group members are able to query all tables and views in the prod database, but cannot create or edit anything in the database. References: Grant privileges on a database: https://docs.databricks.com/en/security/auth-authz/table-acls/grant-privileges-database.html Privileges you can grant on Hive metastore objects: https://docs.databricks.com/en/security/auth-authz/table-acls/privileges.html

• Question 83:

  A data pipeline uses Structured Streaming to ingest data from kafka to Delta Lake. Data is being stored in a bronze table, and includes the Kafka_generated timesamp, key, and value. Three months after the pipeline is deployed the data engineering team has noticed some latency issued during certain times of the day.

  A senior data engineer updates the Delta Table's schema and ingestion logic to include the current timestamp (as recoded by Apache Spark) as well the Kafka topic and partition. The team plans to use the additional metadata fields to diagnose the transient processing delays:

  Which limitation will the team face while diagnosing this problem?

  A. New fields not be computed for historic records.

  B. Updating the table schema will invalidate the Delta transaction log metadata.

  C. Updating the table schema requires a default value provided for each file added.

  D. Spark cannot capture the topic partition fields from the kafka source.

  Correct Answer: A

  When adding new fields to a Delta table's schema, these fields will not be retrospectively applied to historical records that were ingested before the schema change. Consequently, while the team can use the new metadata fields to investigate

  transient processing delays moving forward, they will be unable to apply this diagnostic approach to past data that lacks these fields.

  References:

  Databricks documentation on Delta Lake schema management:

  https://docs.databricks.com/delta/delta-batch.html#schema-management

• Question 84:

  A table in the Lakehouse named customer_churn_params is used in churn prediction by the machine learning team. The table contains information about customers derived from a number of upstream sources. Currently, the data engineering team populates this table nightly by overwriting the table with the current valid values derived from upstream data sources.

  The churn prediction model used by the ML team is fairly stable in production. The team is only interested in making predictions on records that have changed in the past 24 hours.

  Which approach would simplify the identification of these changed records?

  A. Apply the churn model to all rows in the customer_churn_params table, but implement logic to perform an upsert into the predictions table that ignores rows where predictions have not changed.

  B. Convert the batch job to a Structured Streaming job using the complete output mode; configure a Structured Streaming job to read from the customer_churn_params table and incrementally predict against the churn model.

  C. Calculate the difference between the previous model predictions and the current customer_churn_params on a key identifying unique customers before making new predictions; only make predictions on those customers not in the previous predictions.

  D. Modify the overwrite logic to include a field populated by calling spark.sql.functions.current_timestamp() as data are being written; use this field to identify records written on a particular date.

  E. Replace the current overwrite logic with a merge statement to modify only those records that have changed; write logic to make predictions on the changed records identified by the change data feed.

  Correct Answer: E

  The approach that would simplify the identification of the changed records is to replace the current overwrite logic with a merge statement to modify only those records that have changed, and write logic to make predictions on the changed records identified by the change data feed. This approach leverages the Delta Lake features of merge and change data feed, which are designed to handle upserts and track row-level changes in a Delta table12. By using merge, the data engineering team can avoid overwriting the entire table every night, and only update or insert the records that have changed in the source data. By using change data feed, the ML team can easily access the change events that have occurred in the customer_churn_params table, and filter them by operation type (update or insert) and timestamp. This way, they can only make predictions on the records that have changed in the past 24 hours, and avoid re-processing the unchanged records. The other options are not as simple or efficient as the proposed approach, because: Option A would require applying the churn model to all rows in the customer_churn_params table, which would be wasteful and redundant. It would also require implementing logic to perform an upsert into the predictions table, which would be more complex than using the merge statement. Option B would require converting the batch job to a Structured Streaming job, which would involve changing the data ingestion and processing logic. It would also require using the complete output mode, which would output the entire result table every time there is a change in the source data, which would be inefficient and costly. Option C would require calculating the difference between the previous model predictions and the current customer_churn_params on a key identifying unique customers, which would be computationally expensive and prone to errors. It would also require storing and accessing the previous predictions, which would add extra storage and I/O costs. Option D would require modifying the overwrite logic to include a field populated by calling spark.sql.functions.current_timestamp() as data are being written, which would add extra complexity and overhead to the data engineering job. It would also require using this field to identify records written on a particular date, which would be less accurate and reliable than using the change data feed. References: Merge, Change data feed

• Question 85:

  The data science team has requested assistance in accelerating queries on free form text from user reviews. The data is currently stored in Parquet with the below schema:

  item_id INT, user_id INT, review_id INT, rating FLOAT, review STRING

  The review column contains the full text of the review left by the user. Specifically, the data science team is looking to identify if any of 30 key words exist in this field. A junior data engineer suggests converting this data to Delta Lake will improve query performance.

  Which response to the junior data engineer s suggestion is correct?

  A. Delta Lake statistics are not optimized for free text fields with high cardinality.

  B. Text data cannot be stored with Delta Lake.

  C. ZORDER ON review will need to be run to see performance gains.

  D. The Delta log creates a term matrix for free text fields to support selective filtering.

  E. Delta Lake statistics are only collected on the first 4 columns in a table.

  Correct Answer: A

  Converting the data to Delta Lake may not improve query performance on free text fields with high cardinality, such as the review column. This is because Delta Lake collects statistics on the minimum and maximum values of each column, which are not very useful for filtering or skipping data on free text fields. Moreover, Delta Lake collects statistics on the first 32 columns by default, which may not include the review column if the table has more columns. Therefore, the junior data engineer's suggestion is not correct. A better approach would be to use a full-text search engine, such as Elasticsearch, to index and query the review column. Alternatively, you can use natural language processing techniques, such as tokenization, stemming, and lemmatization, to preprocess the review column and create a new column with normalized terms that can be used for filtering or skipping data. References: Optimizations: https://docs.delta.io/latest/optimizations-oss.html Full-text search with Elasticsearch: https://docs.databricks.com/data/data-sources/elasticsearch.html Natural language processing: https://docs.databricks.com/applications/nlp/index.html

• Question 86:

  A Delta Lake table representing metadata about content from user has the following schema:

  Based on the above schema, which column is a good candidate for partitioning the Delta Table?

  A. Date

  B. Post_id

  C. User_id

  D. Post_time

  Correct Answer: A

  Partitioning a Delta Lake table improves query performance by organizing data into partitions based on the values of a column. In the given schema, the date column is a good candidate for partitioning for several reasons:

  Time-Based Queries: If queries frequently filter or group by date, partitioning by the date column can significantly improve performance by limiting the amount of data scanned.

  Granularity: The date column likely has a granularity that leads to a reasonable number of partitions (not too many and not too few). This balance is important for optimizing both read and write performance.

  Data Skew: Other columns like post_id or user_id might lead to uneven partition sizes (data skew), which can negatively impact performance. Partitioning by post_time could also be considered, but typically date is preferred due to its more

  manageable granularity.

  References:

  Delta Lake Documentation on Table Partitioning: Optimizing Layout with Partitioning

• Question 87:

  A production cluster has 3 executor nodes and uses the same virtual machine type for the driver and executor.

  When evaluating the Ganglia Metrics for this cluster, which indicator would signal a bottleneck caused by code executing on the driver?

  A. The five Minute Load Average remains consistent/flat

  B. Bytes Received never exceeds 80 million bytes per second

  C. Total Disk Space remains constant

  D. Network I/O never spikes

  E. Overall cluster CPU utilization is around 25%

  Correct Answer: E

  This is the correct answer because it indicates a bottleneck caused by code executing on the driver. A bottleneck is a situation where the performance or capacity of a system is limited by a single component or resource. A bottleneck can cause slow execution, high latency, or low throughput. A production cluster has 3 executor nodes and uses the same virtual machine type for the driver and executor. When evaluating the Ganglia Metrics for this cluster, one can look for indicators that show how the cluster resources are being utilized, such as CPU, memory, disk, or network. If the overall cluster CPU utilization is around 25%, it means that only one out of the four nodes (driver + 3 executors) is using its full CPU capacity, while the other three nodes are idle or underutilized. This suggests that the code executing on the driver is taking too long or consuming too much CPU resources, preventing the executors from receiving tasks or data to process. This can happen when the code has driver-side operations that are not parallelized or distributed, such as collecting large amounts of data to the driver, performing complex calculations on the driver, or using non-Spark libraries on the driver. Verified References: [Databricks Certified Data Engineer Professional], under "Spark Core" section; Databricks Documentation, under "View cluster status and event logs-Ganglia metrics" section; Databricks Documentation, under "Avoid collecting large RDDs" section.

  In a Spark cluster, the driver node is responsible for managing the execution of the Spark application, including scheduling tasks, managing the execution plan, and interacting with the cluster manager. If the overall cluster CPU utilization is low (e.g., around 25%), it may indicate that the driver node is not utilizing the available resources effectively and might be a bottleneck.

• Question 88:

  A table is registered with the following code: Both users and orders are Delta Lake tables. Which statement describes the results of querying recent_orders?

  

  A. All logic will execute at query time and return the result of joining the valid versions of the source tables at the time the query finishes.

  B. All logic will execute when the table is defined and store the result of joining tables to the DBFS; this stored data will be returned when the table is queried.

  C. Results will be computed and cached when the table is defined; these cached results will incrementally update as new records are inserted into source tables.

  D. All logic will execute at query time and return the result of joining the valid versions of the source tables at the time the query began.

  E. The versions of each source table will be stored in the table transaction log; query results will be saved to DBFS with each query.

  Correct Answer: B

• Question 89:

  Which Python variable contains a list of directories to be searched when trying to locate required modules?

  A. importlib.resource path

  B. ,sys.path

  C. os-path

  D. pypi.path

  E. pylib.source

  Correct Answer: B

• Question 90:

  The DevOps team has configured a production workload as a collection of notebooks scheduled to run daily using the Jobs UI. A new data engineering hire is onboarding to the team and has requested access to one of these notebooks to review the production logic.

  What are the maximum notebook permissions that can be granted to the user without allowing accidental changes to production code or data?

  A. Can Manage

  B. Can Edit

  C. No permissions

  D. Can Read

  E. Can Run

  Correct Answer: C

  This is the correct answer because it is the maximum notebook permissions that can be granted to the user without allowing accidental changes to production code or data. Notebook permissions are used to control access to notebooks in Databricks workspaces. There are four types of notebook permissions: Can Manage, Can Edit, Can Run, and Can Read. Can Manage allows full control over the notebook, including editing, running, deleting, exporting, and changing permissions. Can Edit allows modifying and running the notebook, but not changing permissions or deleting it. Can Run allows executing commands in an existing cluster attached to the notebook, but not modifying or exporting it. Can Read allows viewing the notebook content, but not running or modifying it. In this case, granting Can Read permission to the user will allow them to review the production logic in the notebook without allowing them to make any changes to it or run any commands that may affect production data. Verified References: [Databricks Certified Data Engineer Professional], under "Databricks Workspace" section; Databricks Documentation, under "Notebook permissions" section.