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

• Question 101:

  Which configuration parameter directly affects the size of a spark-partition upon ingestion of data into Spark?

  A. spark.sql.files.maxPartitionBytes

  B. spark.sql.autoBroadcastJoinThreshold

  C. spark.sql.files.openCostInBytes

  D. spark.sql.adaptive.coalescePartitions.minPartitionNum

  E. spark.sql.adaptive.advisoryPartitionSizeInBytes

  Correct Answer: A

  This is the correct answer because spark.sql.files.maxPartitionBytes is a configuration parameter that directly affects the size of a spark-partition upon ingestion of data into Spark. This parameter configures the maximum number of bytes to

  pack into a single partition when reading files from file-based sources such as Parquet, JSON and ORC. The default value is 128 MB, which means each partition will be roughly 128 MB in size, unless there are too many small files or only

  one large file. Verified References:

  [Databricks Certified Data Engineer Professional], under "Spark Configuration" section; Databricks Documentation, under "Available Properties-spark.sql.files.maxPartitionBytes" section.

• Question 102:

  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 below query 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?

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

  B. The recent_sensor_recordingstable was unresponsive for three consecutive runs of the query

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

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

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

  Correct Answer: E

  This is the correct answer because the query is using a GROUP BY clause on the sensor_id column, which means it will calculate the mean temperature for each sensor separately. The alert will trigger when the mean temperature for any sensor is greater than 120, which means at least one sensor had an average temperature above 120 for three consecutive minutes. The alert will stop when the mean temperature for all sensors drops below 120. Verified References: [Databricks Certified Data Engineer Professional], under "SQL Analytics" section; Databricks Documentation, under "Alerts" section.

• Question 103:

  Which statement describes Delta Lake optimized writes?

  A. A shuffle occurs prior to writing to try to group data together resulting in fewer files instead of each executor writing multiple files based on directory partitions.

  B. Optimized writes logical partitions instead of directory partitions partition boundaries are only represented in metadata fewer small files are written.

  C. An asynchronous job runs after the write completes to detect if files could be further compacted; yes, an OPTIMIZE job is executed toward a default of 1 GB.

  D. Before a job cluster terminates, OPTIMIZE is executed on all tables modified during the most recent job.

  Correct Answer: A

  Delta Lake optimized writes involve a shuffle operation before writing out data to the Delta table. The shuffle operation groups data by partition keys, which can lead to a reduction in the number of output files and potentially larger files, instead

  of multiple smaller files. This approach can significantly reduce the total number of files in the table, improve read performance by reducing the metadata overhead, and optimize the table storage layout, especially for workloads with many

  small files.

  References:

  Databricks documentation on Delta Lake performance tuning:

  https://docs.databricks.com/delta/optimizations/auto-optimize.html

• Question 104:

  A data architect has heard about lake's built-in versioning and time travel capabilities. For auditing purposes they have a requirement to maintain a full of all valid street addresses as they appear in the customers table.

  The architect is interested in implementing a Type 1 table, overwriting existing records with new values and relying on Delta Lake time travel to support long-term auditing. A data engineer on the project feels that a Type 2 table will provide

  better performance and scalability.

  Which piece of information is critical to this decision?

  A. Delta Lake time travel does not scale well in cost or latency to provide a long-term versioning solution.

  B. Delta Lake time travel cannot be used to query previous versions of these tables because Type 1 changes modify data files in place.

  C. Shallow clones can be combined with Type 1 tables to accelerate historic queries for long-term versioning.

  D. Data corruption can occur if a query fails in a partially completed state because Type 2 tables requires Setting multiple fields in a single update.

  Correct Answer: A

  Delta Lake's time travel feature allows users to access previous versions of a table, providing a powerful tool for auditing and versioning. However, using time travel as a long-term versioning solution for auditing purposes can be less optimal in terms of cost and performance, especially as the volume of data and the number of versions grow. For maintaining a full history of valid street addresses as they appear in a customers table, using a Type 2 table (where each update creates a new record with versioning) might provide better scalability and performance by avoiding the overhead associated with accessing older versions of a large table. While Type 1 tables, where existing records are overwritten with new values, seem simpler and can leverage time travel for auditing, the critical piece of information is that time travel might not scale well in cost or latency for long-term versioning needs, making a Type 2 approach more viable for performance and scalability.References: Databricks Documentation on Delta Lake's Time Travel: Delta Lake Time Travel Databricks Blog on Managing Slowly Changing Dimensions in Delta Lake: Managing SCDs in Delta Lake

• Question 105:

  Which distribution does Databricks support for installing custom Python code packages?

  A. sbt

  B. CRAN

  C. CRAM

  D. nom

  E. Wheels

  F. jars

  Correct Answer: D

• Question 106:

  A nightly job ingests data into a Delta Lake table using the following code:

  

  The next step in the pipeline requires a function that returns an object that can be used to manipulate new records that have not yet been processed to the next table in the pipeline. Which code snippet completes this function definition?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: E

  https://docs.databricks.com/en/delta/delta-change-data-feed.html

• Question 107:

  A Databricks SQL dashboard has been configured to monitor the total number of records present in a collection of Delta Lake tables using the following query pattern:

  SELECT COUNT (*) FROM table-

  Which of the following describes how results are generated each time the dashboard is updated?

  A. The total count of rows is calculated by scanning all data files

  B. The total count of rows will be returned from cached results unless REFRESH is run

  C. The total count of records is calculated from the Delta transaction logs

  D. The total count of records is calculated from the parquet file metadata

  E. The total count of records is calculated from the Hive metastore

  Correct Answer: C

  https://delta.io/blog/2023-04-19-faster-aggregations-metadata/#:~:text=You%20can%20get%20the%20number,a%20given%20Delta%20table %20version.

• Question 108:

  Although the Databricks Utilities Secrets module provides tools to store sensitive credentials and avoid accidentally displaying them in plain text users should still be careful with which credentials are stored here and which users have access to using these secrets.

  Which statement describes a limitation of Databricks Secrets?

  A. Because the SHA256 hash is used to obfuscate stored secrets, reversing this hash will display the value in plain text.

  B. Account administrators can see all secrets in plain text by logging on to the Databricks Accounts console.

  C. Secrets are stored in an administrators-only table within the Hive Metastore; database administrators have permission to query this table by default.

  D. Iterating through a stored secret and printing each character will display secret contents in plain text.

  E. The Databricks REST API can be used to list secrets in plain text if the personal access token has proper credentials.

  Correct Answer: E

  This is the correct answer because it describes a limitation of Databricks Secrets. Databricks Secrets is a module that provides tools to store sensitive credentials and avoid accidentally displaying them in plain text. Databricks Secrets allows creating secret scopes, which are collections of secrets that can be accessed by users or groups. Databricks Secrets also allows creating and managing secrets using the Databricks CLI or the Databricks REST API. However, a limitation of Databricks Secrets is that the Databricks REST API can be used to list secrets in plain text if the personal access token has proper credentials. Therefore, users should still be careful with which credentials are stored in Databricks Secrets and which users have access to using these secrets. Verified References: [Databricks Certified Data Engineer Professional], under "Databricks Workspace" section; Databricks Documentation, under "List secrets" section.

• Question 109:

  A junior data engineer on your team has implemented the following code block.

  

  The view new_events contains a batch of records with the same schema as the events Delta table. The event_id field serves as a unique key for this table.

  When this query is executed, what will happen with new records that have the same event_id as an existing record?

  A. They are merged.

  B. They are ignored.

  C. They are updated.

  D. They are inserted.

  E. They are deleted.

  Correct Answer: B

  This is the correct answer because it describes what will happen with new records that have the same event_id as an existing record when the query is executed. The query uses the INSERT INTO command to append new records from the view new_events to the table events. However, the INSERT INTO command does not check for duplicate values in the primary key column (event_id) and does not perform any update or delete operations on existing records. Therefore, if there are new records that have the same event_id as an existing record, they will be ignored and not inserted into the table events. Verified References: [Databricks Certified Data Engineer Professional], under "Delta Lake" section; Databricks Documentation, under "Append data using INSERT INTO" section.

  "If none of the WHEN MATCHED conditions evaluate to true for a source and target row pair that matches the merge_condition, then the target row is left unchanged." https://docs.databricks.com/en/sql/language-manual/delta-mergeinto.html#:~:text=If%20none%20of%20the%20WHEN%20MATCHED%20conditions%20ev aluate%20to%20true%20for%20a%20source%20and%20target%20row%20pair%20that% 20matches%20the%20merge_condition%2C%20then% 20the%20target%20row%20is%20l eft%20unchanged.

• Question 110:

  Which is a key benefit of an end-to-end test?

  A. It closely simulates real world usage of your application.

  B. It pinpoint errors in the building blocks of your application.

  C. It provides testing coverage for all code paths and branches.

  D. It makes it easier to automate your test suite

  Correct Answer: A

  End-to-end testing is a methodology used to test whether the flow of an application, from start to finish, behaves as expected. The key benefit of an end-to-end test is that it closely simulates real-world, user behavior, ensuring that the system

  as a whole operates correctly.

  References:

  Software Testing: End-to-End Testing