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

• Question 91:

  The data governance team is reviewing code used for deleting records for compliance with GDPR. They note the following logic is used to delete records from the Delta Lake table named users.

  

  Assuming that user_id is a unique identifying key and that delete_requests contains all users that have requested deletion, which statement describes whether successfully executing the above logic guarantees that the records to be deleted are no longer accessible and why?

  A. Yes; Delta Lake ACID guarantees provide assurance that the delete command succeeded fully and permanently purged these records.

  B. No; the Delta cache may return records from previous versions of the table until the cluster is restarted.

  C. Yes; the Delta cache immediately updates to reflect the latest data files recorded to disk.

  D. No; the Delta Lake delete command only provides ACID guarantees when combined with the merge into command.

  E. No; files containing deleted records may still be accessible with time travel until a vacuum command is used to remove invalidated data files.

  Correct Answer: E

  The code uses the DELETE FROM command to delete records from the users table that match a condition based on a join with another table called delete_requests, which contains all users that have requested deletion. The DELETE FROM command deletes records from a Delta Lake table by creating a new version of the table that does not contain the deleted records. However, this does not guarantee that the records to be deleted are no longer accessible, because Delta Lake supports time travel, which allows querying previous versions of the table using a timestamp or version number. Therefore, files containing deleted records may still be accessible with time travel until a vacuum command is used to remove invalidated data files from physical storage. Verified References: [Databricks Certified Data Engineer Professional], under "Delta Lake" section; Databricks Documentation, under "Delete from a table" section; Databricks Documentation, under "Remove files no longer referenced by a Delta table" section.

• Question 92:

  Which statement describes integration testing?

  A. Validates interactions between subsystems of your application

  B. Requires an automated testing framework

  C. Requires manual intervention

  D. Validates an application use case

  E. Validates behavior of individual elements of your application

  Correct Answer: D

  This is the correct answer because it describes integration testing. Integration testing is a type of testing that validates interactions between subsystems of your application, such as modules, components, or services. Integration testing ensures that the subsystems work together as expected and produce the correct outputs or results. Integration testing can be done at different levels of granularity, such as component integration testing, system integration testing, or end-toend testing. Integration testing can help detect errors or bugs that may not be found by unit testing, which only validates behavior of individual elements of your application. Verified References: [Databricks Certified Data Engineer Professional], under "Testing" section; Databricks Documentation, under "Integration testing" section.

• Question 93:

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

  

  Which statement describes what will happen when the above code is executed?

  A. The connection to the external table will fail; the string "redacted" will be printed.

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

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

  D. The connection to the external table will succeed; the string value of password will be printed in plain text.

  E. The connection to the external table will succeed; the string "redacted" will be printed.

  Correct Answer: E

  This is the correct answer because the code is using the dbutils.secrets.get method to retrieve the password from the secrets module and store it in a variable. The secrets module allows users to securely store and access sensitive information such as passwords, tokens, or API keys. The connection to the external table will succeed because the password variable will contain the actual password value. However, when printing the password variable, the string "redacted" will be displayed instead of the plain text password, as a security measure to prevent exposing sensitive information in notebooks. Verified References: [Databricks Certified Data Engineer Professional], under "Security and Governance" section; Databricks Documentation, under "Secrets" section.

• Question 94:

  A user wants to use DLT expectations to validate that a derived table report contains all records from the source, included in the table validation_copy.

  The user attempts and fails to accomplish this by adding an expectation to the report table definition.

  Which approach would allow using DLT expectations to validate all expected records are present in this table?

  A. Define a SQL UDF that performs a left outer join on two tables, and check if this returns null values for report key values in a DLT expectation for the report table.

  B. Define a function that performs a left outer join on validation_copy and report and report, and check against the result in a DLT expectation for the report table

  C. Define a temporary table that perform a left outer join on validation_copy and report, and define an expectation that no report key values are null

  D. Define a view that performs a left outer join on validation_copy and report, and reference this view in DLT expectations for the report table

  Correct Answer: D

  To validate that all records from the source are included in the derived table, creating a view that performs a left outer join between the validation_copy table and the report table is effective. The view can highlight any discrepancies, such as null values in the report table's key columns, indicating missing records. This view can then be referenced in DLT (Delta Live Tables) expectations for the report table to ensure data integrity. This approach allows for a comprehensive comparison between the source and the derived table. References: Databricks Documentation on Delta Live Tables and Expectations: Delta Live Tables Expectations

• Question 95:

  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?

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

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

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

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

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

  Correct Answer: D

  This is the correct answer because Delta Lake uses a transaction log to store metadata about each table, including min and max statistics for each column in each data file. The Delta engine can use this information to quickly identify which

  files to load based on a filter condition, without scanning the entire table or the file footers. This is called data skipping and it can improve query performance significantly. Verified References:

  [Databricks Certified Data Engineer Professional], under "Delta Lake" section; [Databricks Documentation], under "Optimizations-Data Skipping" section.

  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 column

• Question 96:

  The data engineering team maintains the following code: Assuming that this code produces logically correct results and the data in the source tables has been de-duplicated and validated, which statement describes what will occur when this code is executed?

  

  A. A batch job will update the enriched_itemized_orders_by_account table, replacing only those rows that have different values than the current version of the table, using accountID as the primary key.

  B. The enriched_itemized_orders_by_account table will be overwritten using the current valid version of data in each of the three tables referenced in the join logic.

  C. An incremental job will leverage information in the state store to identify unjoined rows in the source tables and write these rows to the enriched_iteinized_orders_by_account table.

  D. An incremental job will detect if new rows have been written to any of the source tables; if new rows are detected, all results will be recalculated and used to overwrite the enriched_itemized_orders_by_account table.

  E. No computation will occur until enriched_itemized_orders_by_account is queried; upon query materialization, results will be calculated using the current valid version of data in each of the three tables referenced in the join logic.

  Correct Answer: B

  This is the correct answer because it describes what will occur when this code is executed. The code uses three Delta Lake tables as input sources: accounts, orders, and order_items. These tables are joined together using SQL queries to create a view called new_enriched_itemized_orders_by_account, which contains information about each order item and its associated account details. Then, the code uses write.format("delta").mode("overwrite") to overwrite a target table called enriched_itemized_orders_by_account using the data from the view. This means that every time this code is executed, it will replace all existing data in the target table with new data based on the current valid version of data in each of the three input tables. Verified References: [Databricks Certified Data Engineer Professional], under "Delta Lake" section; Databricks Documentation, under "Write to Delta tables" section.

• Question 97:

  A data engineer is performing a join operating to combine values from a static userlookup table with a streaming DataFrame streamingDF. Which code block attempts to perform an invalid stream-static join?

  A. userLookup.join(streamingDF, ["userid"], how="inner")

  B. streamingDF.join(userLookup, ["user_id"], how="outer")

  C. streamingDF.join(userLookup, ["user_id"], how="left")

  D. streamingDF.join(userLookup, ["userid"], how="inner")

  E. userLookup.join(streamingDF, ["user_id"], how="right")

  Correct Answer: E

  In Spark Structured Streaming, certain types of joins between a static DataFrame and a streaming DataFrame are not supported. Specifically, a right outer join where the static DataFrame is on the left side and the streaming DataFrame is on

  the right side is not valid. This is because Spark Structured Streaming cannot handle scenarios where it has to wait for new rows to arrive in the streaming DataFrame to match rows in the static DataFrame. The other join types listed (inner,

  left, and full outer joins) are supported in streaming-static DataFrame joins.

  References:

  Structured Streaming Programming Guide: Join Operations Databricks Documentation on Stream-Static Joins: Databricks Stream-Static Joins

• Question 98:

  The data engineering team has configured a job to process customer requests to be forgotten (have their data deleted). All user data that needs to be deleted is stored in Delta Lake tables using default table settings.

  The team has decided to process all deletions from the previous week as a batch job at 1am each Sunday. The total duration of this job is less than one hour. Every Monday at 3am, a batch job executes a series of VACUUM commands on all

  Delta Lake tables throughout the organization.

  The compliance officer has recently learned about Delta Lake's time travel functionality. They are concerned that this might allow continued access to deleted data.

  Assuming all delete logic is correctly implemented, which statement correctly addresses this concern?

  A. Because the vacuum command permanently deletes all files containing deleted records, deleted records may be accessible with time travel for around 24 hours.

  B. Because the default data retention threshold is 24 hours, data files containing deleted records will be retained until the vacuum job is run the following day.

  C. Because Delta Lake time travel provides full access to the entire history of a table, deleted records can always be recreated by users with full admin privileges.

  D. Because Delta Lake's delete statements have ACID guarantees, deleted records will be permanently purged from all storage systems as soon as a delete job completes.

  E. Because the default data retention threshold is 7 days, data files containing deleted records will be retained until the vacuum job is run 8 days later.

  Correct Answer: E

  https://learn.microsoft.com/en-us/azure/databricks/delta/vacuum

• Question 99:

  The view updates represents an incremental batch of all newly ingested data to be inserted or updated in the customers table. The following logic is used to process these records.

  

  Which statement describes this implementation?

  A. The customers table is implemented as a Type 3 table; old values are maintained as a new column alongside the current value.

  B. The customers table is implemented as a Type 2 table; old values are maintained but marked as no longer current and new values are inserted.

  C. The customers table is implemented as a Type 0 table; all writes are append only with no changes to existing values.

  D. The customers table is implemented as a Type 1 table; old values are overwritten by new values and no history is maintained.

  E. The customers table is implemented as a Type 2 table; old values are overwritten and new customers are appended.

  Correct Answer: A

  The logic uses the MERGE INTO command to merge new records from the view updates into the table customers. The MERGE INTO command takes two arguments:

  a target table and a source table or view. The command also specifies a condition to match records between the target and the source, and a set of actions to perform when there is a match or not. In this case, the condition is to match

  records by customer_id, which is the primary key of the customers table. The actions are to update the existing record in the target with the new values from the source, and set the current_flag to false to indicate that the record is no longer

  current; and to insert a new record in the target with the new values from the source, and set the current_flag to true to indicate that the record is current. This means that old values are maintained but marked as no longer current and new

  values are inserted, which is the definition of a Type 2 table. Verified References: [Databricks Certified Data Engineer Professional], under "Delta Lake" section; Databricks Documentation, under "Merge Into (Delta Lake on Databricks)"

  section.

• Question 100:

  Two of the most common data locations on Databricks are the DBFS root storage and external object storage mounted with dbutils.fs.mount().

  Which of the following statements is correct?

  A. DBFS is a file system protocol that allows users to interact with files stored in object storage using syntax and guarantees similar to Unix file systems.

  B. By default, both the DBFS root and mounted data sources are only accessible to workspace administrators.

  C. The DBFS root is the most secure location to store data, because mounted storage volumes must have full public read and write permissions.

  D. Neither the DBFS root nor mounted storage can be accessed when using %sh in a Databricks notebook.

  E. The DBFS root stores files in ephemeral block volumes attached to the driver, while mounted directories will always persist saved data to external storage between sessions.

  Correct Answer: A

  DBFS is a file system protocol that allows users to interact with files stored in object storage using syntax and guarantees similar to Unix file systems1. DBFS is not a physical file system, but a layer over the object storage that provides a unified view of data across different data sources1. By default, the DBFS root is accessible to all users in the workspace, and the access to mounted data sources depends on the permissions of the storage account or container2. Mounted storage volumes do not need to have full public read and write permissions, but they do require a valid connection string or access key to be provided when mounting3. Both the DBFS root and mounted storage can be accessed when using %sh in a Databricks notebook, as long as the cluster has FUSE enabled4. The DBFS root does not store files in ephemeral block volumes attached to the driver, but in the object storage associated with the workspace1. Mounted directories will persist saved data to external storage between sessions, unless they are unmounted or deleted3. References: DBFS, Work with files on Azure Databricks, Mounting cloud object storage on Azure Databricks, Access DBFS with FUSE