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

• Question 111:

  The data governance team is reviewing user for deleting records for compliance with GDPR. The following logic has been implemented to propagate deleted requests from the user_lookup table to the user aggregate table.

  

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

  A. No: files containing deleted records may still be accessible with time travel until a BACUM command is used to remove invalidated data files.

  B. Yes: Delta Lake ACID guarantees provide assurance that the DELETE command successed fully and permanently purged these records.

  C. No: the change data feed only tracks inserts and updates not deleted records.

  D. No: the Delta Lake DELETE command only provides ACID guarantees when combined with the MERGE INTO command

  Correct Answer: A

  The DELETE operation in Delta Lake is ACID compliant, which means that once the operation is successful, the records are logically removed from the table. However, the underlying files that contained these records may still exist and be accessible via time travel to older versions of the table. To ensure that these records are physically removed and compliance with GDPR is maintained, a VACUUM command should be used to clean up these data files after a certain retention period. The VACUUM command will remove the files from the storage layer, and after this, the records will no longer be accessible.

• Question 112:

  Which of the following is true of Delta Lake and the Lakehouse?

  A. Because Parquet compresses data row by row. strings will only be compressed when a character is repeated multiple times.

  B. Delta Lake automatically collects statistics on the first 32 columns of each table which are leveraged in data skipping based on query filters.

  C. Views in the Lakehouse maintain a valid cache of the most recent versions of source tables at all times.

  D. Primary and foreign key constraints can be leveraged to ensure duplicate values are never entered into a dimension table.

  E. Z-order can only be applied to numeric values stored in Delta Lake tables

  Correct Answer: B

  https://docs.delta.io/2.0.0/table-properties.html Delta Lake automatically collects statistics on the first 32 columns of each table, which are leveraged in data skipping based on query filters1. Data skipping is a performance optimization

  technique that aims to avoid reading irrelevant data from the storage layer1. By collecting statistics such as min/max values, null counts, and bloom filters, Delta Lake can efficiently prune unnecessary files or partitions from the query plan1.

  This can significantly improve the query performance and reduce the I/O cost.

  The other options are false because:

  Parquet compresses data column by column, not row by row2. This allows for better compression ratios, especially for repeated or similar values within a column2.

  Views in the Lakehouse do not maintain a valid cache of the most recent versions of source tables at all times3. Views are logical constructs that are defined by a SQL query on one or more base tables3. Views are not materialized by default,

  which means they do not store any data, but only the query definition3. Therefore, views always reflect the latest state of the source tables when queried3. However, views can be cached manually using the CACHE TABLE or CREATE

  TABLE AS SELECT commands.

  Primary and foreign key constraints can not be leveraged to ensure duplicate values are never entered into a dimension table. Delta Lake does not support enforcing primary and foreign key constraints on tables. Constraints are logical rules

  that define the integrity and validity of the data in a table. Delta Lake relies on the application logic or the user to ensure the data quality and consistency. Z-order can be applied to any values stored in Delta Lake tables, not only numeric

  values. Z-order is a technique to optimize the layout of the data files by sorting them on one or more columns. Z-order can improve the query performance by clustering related values together and enabling more efficient data skipping. Z-order

  can be applied to any column that has a defined ordering, such as numeric, string, date, or boolean values.

  References: Data Skipping, Parquet Format, Views, [Caching], [Constraints], [Z-Ordering]

• Question 113:

  Where in the Spark UI can one diagnose a performance problem induced by not leveraging predicate push-down?

  A. In the Executor's log file, by gripping for "predicate push-down"

  B. In the Stage's Detail screen, in the Completed Stages table, by noting the size of data read from the Input column

  C. In the Storage Detail screen, by noting which RDDs are not stored on disk

  D. In the Delta Lake transaction log. by noting the column statistics

  E. In the Query Detail screen, by interpreting the Physical Plan

  Correct Answer: E

  This is the correct answer because it is where in the Spark UI one can diagnose a performance problem induced by not leveraging predicate push-down. Predicate push-down is an optimization technique that allows filtering data at the source before loading it into memory or processing it further. This can improve performance and reduce I/O costs by avoiding reading unnecessary data. To leverage predicate push-down, one should use supported data sources and formats, such as Delta Lake, Parquet, or JDBC, and use filter expressions that can be pushed down to the source. To diagnose a performance problem induced by not leveraging predicate push-down, one can use the Spark UI to access the Query Detail screen, which shows information about a SQL query executed on a Spark cluster. The Query Detail screen includes the Physical Plan, which is the actual plan executed by Spark to perform the query. The Physical Plan shows the physical operators used by Spark, such as Scan, Filter, Project, or Aggregate, and their input and output statistics, such as rows and bytes. By interpreting the Physical Plan, one can see if the filter expressions are pushed down to the source or not, and how much data is read or processed by each operator. Verified References: [Databricks Certified Data Engineer Professional], under "Spark Core" section; Databricks Documentation, under "Predicate pushdown" section; Databricks Documentation, under "Query detail page" section.

• Question 114:

  A small company based in the United States has recently contracted a consulting firm in India to implement several new data engineering pipelines to power artificial intelligence applications. All the company's data is stored in regional cloud storage in the United States.

  The workspace administrator at the company is uncertain about where the Databricks workspace used by the contractors should be deployed. Assuming that all data governance considerations are accounted for, which statement accurately informs this decision?

  A. Databricks runs HDFS on cloud volume storage; as such, cloud virtual machines must be deployed in the region where the data is stored.

  B. Databricks workspaces do not rely on any regional infrastructure; as such, the decision should be made based upon what is most convenient for the workspace administrator.

  C. Cross-region reads and writes can incur significant costs and latency; whenever possible, compute should be deployed in the same region the data is stored.

  D. Databricks leverages user workstations as the driver during interactive development; as such, users should always use a workspace deployed in a region they are physically near.

  E. Databricks notebooks send all executable code from the user's browser to virtual machines over the open internet; whenever possible, choosing a workspace region near the end users is the most secure.

  Correct Answer: C

  This is the correct answer because it accurately informs this decision. The decision is about where the Databricks workspace used by the contractors should be deployed. The contractors are based in India, while all the company's data is stored in regional cloud storage in the United States. When choosing a region for deploying a Databricks workspace, one of the important factors to consider is the proximity to the data sources and sinks. Cross-region reads and writes can incur significant costs and latency due to network bandwidth and data transfer fees. Therefore, whenever possible, compute should be deployed in the same region the data is stored to optimize performance and reduce costs. Verified References: [Databricks Certified Data Engineer Professional], under "Databricks Workspace" section; Databricks Documentation, under "Choose a region" section.

• Question 115:

  An external object storage container has been mounted to the location /mnt/finance_eda_bucket.

  The following logic was executed to create a database for the finance team:

  

  After the database was successfully created and permissions configured, a member of the finance team runs the following code:

  

  If all users on the finance team are members of the finance group, which statement describes how the tx_sales table will be created?

  A. A logical table will persist the query plan to the Hive Metastore in the Databricks control plane.

  B. An external table will be created in the storage container mounted to /mnt/finance eda bucket.

  C. A logical table will persist the physical plan to the Hive Metastore in the Databricks control plane.

  D. An managed table will be created in the storage container mounted to /mnt/finance eda bucket.

  E. A managed table will be created in the DBFS root storage container.

  Correct Answer: A

  https://docs.databricks.com/en/lakehouse/data-objects.html

• Question 116:

  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.

  MERGE INTO customers

  USING (

  SELECT updates.customer_id as merge_ey, updates .*

  FROM updates

  UNION ALL

  SELECT NULL as merge_key, updates .*

  FROM updates JOIN customers

  ON updates.customer_id = customers.customer_id

  WHERE customers.current = true AND updates.address <> customers.address

  ) staged_updates

  ON customers.customer_id = mergekey

  WHEN MATCHED AND customers. current = true AND customers.address <> staged_updates.address THEN

  UPDATE SET current = false, end_date = staged_updates.effective_date

  WHEN NOT MATCHED THEN

  INSERT (customer_id, address, current, effective_date, end_date)

  VALUES (staged_updates.customer_id, staged_updates.address, true, staged_updates.effective_date, null)

  Which statement describes this implementation?

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

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

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

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

  Correct Answer: C

  The provided MERGE statement is a classic implementation of a Type 2 SCD in a data warehousing context. In this approach, historical data is preserved by keeping old records (marking them as not current) and adding new records for changes. Specifically, when a match is found and there's a change in the address, the existing record in the customers table is updated to mark it as no longer current (current = false), and an end date is assigned (end_date = staged_updates.effective_date). A new record for the customer is then inserted with the updated information, marked as current. This method ensures that the full history of changes to customer information is maintained in the table, allowing for time-based analysis of customer data.References: Databricks documentation on implementing SCDs using Delta Lake and the MERGE statement (https://docs.databricks.com/delta/delta-update.html#upsert-into-a-table-using-merge).

• Question 117:

  Which statement describes Delta Lake Auto Compaction?

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

  B. Before a Jobs cluster terminates, optimize is executed on all tables modified during the most recent job.

  C. Optimized writes use logical partitions instead of directory partitions; because partition boundaries are only represented in metadata, fewer small files are written.

  D. Data is queued in a messaging bus instead of committing data directly to memory; all data is committed from the messaging bus in one batch once the job is complete.

  E. An asynchronous job runs after the write completes to detect if files could be further compacted; if yes, an optimize job is executed toward a default of 128 MB.

  Correct Answer: E

  This is the correct answer because it describes the behavior of Delta Lake Auto Compaction, which is a feature that automatically optimizes the layout of Delta Lake tables by coalescing small files into larger ones. Auto Compaction runs as an asynchronous job after a write to a table has succeeded and checks if files within a partition can be further compacted. If yes, it runs an optimize job with a default target file size of 128 MB. Auto Compaction only compacts files that have not been compacted previously. Verified References: [Databricks Certified Data Engineer Professional], under "Delta Lake" section; Databricks Documentation, under "Auto Compaction for Delta Lake on Databricks" section. "Auto compaction occurs after a write to a table has succeeded and runs synchronously on the cluster that has performed the write. Auto compaction only compacts files that haven't been compacted previously." https://learn.microsoft.com/en-us/azure/databricks/delta/tune-file-size

• Question 118:

  A new data engineer notices that a critical field was omitted from an application that writes its Kafka source to Delta Lake. This happened even though the critical field was in the Kafka source. That field was further missing from data written to dependent, long-term storage. The retention threshold on the Kafka service is seven days. The pipeline has been in production for three months.

  Which describes how Delta Lake can help to avoid data loss of this nature in the future?

  A. The Delta log and Structured Streaming checkpoints record the full history of the Kafka producer.

  B. Delta Lake schema evolution can retroactively calculate the correct value for newly added fields, as long as the data was in the original source.

  C. Delta Lake automatically checks that all fields present in the source data are included in the ingestion layer.

  D. Data can never be permanently dropped or deleted from Delta Lake, so data loss is not possible under any circumstance.

  E. Ingestine all raw data and metadata from Kafka to a bronze Delta table creates a permanent, replayable history of the data state.

  Correct Answer: E

  This is the correct answer because it describes how Delta Lake can help to avoid data loss of this nature in the future. By ingesting all raw data and metadata from Kafka to a bronze Delta table, Delta Lake creates a permanent, replayable history of the data state that can be used for recovery or reprocessing in case of errors or omissions in downstream applications or pipelines. Delta Lake also supports schema evolution, which allows adding new columns to existing tables without affecting existing queries or pipelines. Therefore, if a critical field was omitted from an application that writes its Kafka source to Delta Lake, it can be easily added later and the data can be reprocessed from the bronze table without losing any information. Verified References: [Databricks Certified Data Engineer Professional], under "Delta Lake" section; Databricks Documentation, under "Delta Lake core features" section.

• Question 119:

  Each configuration below is identical to the extent that each cluster has 400 GB total of RAM, 160 total cores and only one Executor per VM.

  Given a job with at least one wide transformation, which of the following cluster configurations will result in maximum performance?

  A. Total VMs; 1 400 GB per Executor 160 Cores / Executor

  B. Total VMs: 8 50 GB per Executor 20 Cores / Executor

  C. Total VMs: 4 100 GB per Executor 40 Cores/Executor

  D. Total VMs:2 200 GB per Executor 80 Cores / Executor

  Correct Answer: B

  This is the correct answer because it is the cluster configuration that will result in maximum performance for a job with at least one wide transformation. A wide transformation is a type of transformation that requires shuffling data across partitions, such as join, groupBy, or orderBy. Shuffling can be expensive and time-consuming, especially if there are too many or too few partitions. Therefore, it is important to choose a cluster configuration that can balance the trade-off between parallelism and network overhead. In this case, having 8 VMs with 50 GB per executor and 20 cores per executor will create 8 partitions, each with enough memory and CPU resources to handle the shuffling efficiently. Having fewer VMs with more memory and cores per executor will create fewer partitions, which will reduce parallelism and increase the size of each shuffle block. Having more VMs with less memory and cores per executor will create more partitions, which will increase parallelism but also increase the network overhead and the number of shuffle files. Verified References: [Databricks Certified Data Engineer Professional], under "Performance Tuning" section; Databricks Documentation, under "Cluster configurations" section.

• Question 120:

  All records from an Apache Kafka producer are being ingested into a single Delta Lake table with the following schema:

  key BINARY, value BINARY, topic STRING, partition LONG, offset LONG, timestamp LONG

  There are 5 unique topics being ingested. Only the "registration" topic contains Personal Identifiable Information (PII). The company wishes to restrict access to PII. The company also wishes to only retain records containing PII in this table for 14 days after initial ingestion. However, for non-PII information, it would like to retain these records indefinitely.

  Which of the following solutions meets the requirements?

  A. All data should be deleted biweekly; Delta Lake's time travel functionality should be leveraged to maintain a history of non-PII information.

  B. Data should be partitioned by the registration field, allowing ACLs and delete statements to be set for the PII directory.

  C. Because the value field is stored as binary data, this information is not considered PII and no special precautions should be taken.

  D. Separate object storage containers should be specified based on the partition field, allowing isolation at the storage level.

  E. Data should be partitioned by the topic field, allowing ACLs and delete statements to leverage partition boundaries.

  Correct Answer: B

  Partitioning the data by the topic field allows the company to apply different access control policies and retention policies for different topics. For example, the company can use the Table Access Control feature to grant or revoke permissions to the registration topic based on user roles or groups. The company can also use the DELETE command to remove records from the registration topic that are older than 14 days, while keeping the records from other topics indefinitely. Partitioning by the topic field also improves the performance of queries that filter by the topic field, as they can skip reading irrelevant partitions. References: Table Access Control: https://docs.databricks.com/security/access-control/table-acls/index.html DELETE: https://docs.databricks.com/delta/delta-update.html#delete-from-a-table