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

• Question 1:

  A data engineer wants to join a stream of advertisement impressions (when an ad was shown) with another stream of user clicks on advertisements to correlate when impression led to monitizable clicks.

  

  Which solution would improve the performance?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  Correct Answer: A

  When joining a stream of advertisement impressions with a stream of user clicks, you want to minimize the state that you need to maintain for the join. Option A suggests using a left outer join with the condition that clickTime == impressionTime, which is suitable for correlating events that occur at the exact same time. However, in a real-world scenario, you would likely need some leeway to account for the delay between an impression and a possible click. It's important to design the join condition and the window of time considered to optimize performance while still capturing the relevant user interactions. In this case, having the watermark can help with state management and avoid state growing unbounded by discarding old state data that's unlikely to match with new data.

• Question 2:

  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?

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

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

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

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

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

  Correct Answer: B

  This is the correct answer because it will allow the developer to update their local repository with the latest changes from the remote repository and switch to the desired branch. Pulling changes will not affect the current branch or create any conflicts, as it will only fetch the changes and not merge them. Selecting the dev-2.3.9 branch from the dropdown will checkout that branch and display its contents in the notebook. Verified References: [Databricks Certified Data Engineer Professional], under "Databricks Tooling" section; Databricks Documentation, under "Pull changes from a remote repository" section.

• Question 3:

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

  

  A. The silver_customer_sales table will be overwritten by aggregated values calculated from all records in the gold_customer_lifetime_sales_summary table as a batch job.

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

  C. The gold_customer_lifetime_sales_summary table will be overwritten by aggregated values calculated from all records in the silver_customer_sales table as a batch job.

  D. An incremental job will leverage running information in the state store to update aggregate values in the gold_customer_lifetime_sales_summary table.

  E. An incremental job will detect if new rows have been written to the silver_customer_sales table; if new rows are detected, all aggregates will be recalculated and used to overwrite the gold_customer_lifetime_sales_summary table.

  Correct Answer: C

  This code is using the pyspark.sql.functions library to group the silver_customer_sales table by customer_id and then aggregate the data using the minimum sale date, maximum sale total, and sum of distinct order ids. The resulting aggregated data is then written to the gold_customer_lifetime_sales_summary table, overwriting any existing data in that table. This is a batch job that does not use any incremental or streaming logic, and does not perform any merge or update operations. Therefore, the code will overwrite the gold table with the aggregated values from the silver table every time it is executed. References: https://docs.databricks.com/spark/latest/dataframes-datasets/introduction-to-dataframes-python.html https://docs.databricks.com/spark/latest/dataframes-datasets/transforming-data-with-dataframes.html https://docs.databricks.com/spark/latest/dataframes-datasets/aggregating-data-with-dataframes.html

• Question 4:

  A Data engineer wants to run unit's tests using common Python testing frameworks on python functions defined across several Databricks notebooks currently used in production.

  How can the data engineer run unit tests against function that work with data in production?

  A. Run unit tests against non-production data that closely mirrors production

  B. Define and unit test functions using Files in Repos

  C. Define units test and functions within the same notebook

  D. Define and import unit test functions from a separate Databricks notebook

  Correct Answer: A

  The best practice for running unit tests on functions that interact with data is to use a dataset that closely mirrors the production data. This approach allows data engineers to validate the logic of their functions without the risk of affecting the

  actual production data. It's important to have a representative sample of production data to catch edge cases and ensure the functions will work correctly when used in a production environment.

  References:

  Databricks Documentation on Testing: Testing and Validation of Data and Notebooks

• Question 5:

  The following table consists of items found in user carts within an e-commerce website.

  

  The following MERGE statement is used to update this table using an updates view, with schema evaluation enabled on this table.

  

  How would the following update be handled?

  A. The update is moved to separate ''restored'' column because it is missing a column expected in the target schema.

  B. The new restored field is added to the target schema, and dynamically read as NULL for existing unmatched records.

  C. The update throws an error because changes to existing columns in the target schema are not supported.

  D. The new nested field is added to the target schema, and files underlying existing records are updated to include NULL values for the new field.

  Correct Answer: D

  With schema evolution enabled in Databricks Delta tables, when a new field is added to a record through a MERGE operation, Databricks automatically modifies the table schema to include the new field. In existing records where this new

  field is not present, Databricks will insert NULL values for that field. This ensures that the schema remains consistent across all records in the table, with the new field being present in every record, even if it is NULL for records that did not

  originally include it.

  References:

  Databricks documentation on schema evolution in Delta Lake:

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

• Question 6:

  A data team's Structured Streaming job is configured to calculate running aggregates for item sales to update a downstream marketing dashboard. The marketing team has introduced a new field to track the number of times this promotion code is used for each item. A junior data engineer suggests updating the existing query as follows: Note that proposed changes are in bold.

  

  Which step must also be completed to put the proposed query into production?

  A. Increase the shuffle partitions to account for additional aggregates

  B. Specify a new checkpointlocation

  C. Run REFRESH TABLE delta, /item_agg'

  D. Remove .option (mergeSchema', true') from the streaming write

  Correct Answer: B

  When introducing a new aggregation or a change in the logic of a Structured Streaming query, it is generally necessary to specify a new checkpoint location. This is because the checkpoint directory contains metadata about the offsets and

  the state of the aggregations of a streaming query. If the logic of the query changes, such as including a new aggregation field, the state information saved in the current checkpoint would not be compatible with the new logic, potentially

  leading to incorrect results or failures. Therefore, to accommodate the new field and ensure the streaming job has the correct starting point and state information for aggregations, a new checkpoint location should be specified.

  References:

  Databricks documentation on Structured Streaming:

  https://docs.databricks.com/spark/latest/structured-streaming/index.html Databricks documentation on streaming checkpoints:

  https://docs.databricks.com/spark/latest/structured-streaming/production.html#checkpointing

• Question 7:

  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.

  A. "Can Manage" privileges on the required cluster

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

  C. Cluster creation allowed. "Can Attach To" privileges on the required cluster

  D. "Can Restart" privileges on the required cluster

  E. Cluster creation allowed. "Can Restart" privileges on the required cluster

  Correct Answer: D

  https://learn.microsoft.com/en-us/azure/databricks/security/auth-authz/access-control/cluster-acl https://docs.databricks.com/en/security/auth-authz/access-control/cluster-acl.html

• Question 8:

  The data engineering team maintains a table of aggregate statistics through batch nightly updates. This includes total sales for the previous day alongside totals and averages for a variety of time periods including the 7 previous days, year-todate, and quarter-to-date. This table is named store_saies_summary and the schema is as follows:

  

  The table daily_store_sales contains all the information needed to update store_sales_summary. The schema for this table is:

  store_id INT, sales_date DATE, total_sales FLOAT

  If daily_store_sales is implemented as a Type 1 table and the total_sales column might be adjusted after manual data auditing, which approach is the safest to generate accurate reports in the store_sales_summary table?

  A. Implement the appropriate aggregate logic as a batch read against the daily_store_sales table and overwrite the store_sales_summary table with each Update.

  B. Implement the appropriate aggregate logic as a batch read against the daily_store_sales table and append new rows nightly to the store_sales_summary table.

  C. Implement the appropriate aggregate logic as a batch read against the daily_store_sales table and use upsert logic to update results in the store_sales_summary table.

  D. Implement the appropriate aggregate logic as a Structured Streaming read against the daily_store_sales table and use upsert logic to update results in the store_sales_summary table.

  E. Use Structured Streaming to subscribe to the change data feed for daily_store_sales and apply changes to the aggregates in the store_sales_summary table with each update.

  Correct Answer: E

  The daily_store_sales table contains all the information needed to update store_sales_summary. The schema of the table is: store_id INT, sales_date DATE, total_sales FLOAT The daily_store_sales table is implemented as a Type 1 table, which means that old values are overwritten by new values and no history is maintained. The total_sales column might be adjusted after manual data auditing, which means that the data in the table may change over time. The safest approach to generate accurate reports in the store_sales_summary table is to use Structured Streaming to subscribe to the change data feed for daily_store_sales and apply changes to the aggregates in the store_sales_summary table with each update. Structured Streaming is a scalable and fault-tolerant stream processing engine built on Spark SQL. Structured Streaming allows processing data streams as if they were tables or DataFrames, using familiar operations such as select, filter, groupBy, or join. Structured Streaming also supports output modes that specify how to write the results of a streaming query to a sink, such as append, update, or complete. Structured Streaming can handle both streaming and batch data sources in a unified manner. The change data feed is a feature of Delta Lake that provides structured streaming sources that can subscribe to changes made to a Delta Lake table. The change data feed captures both data changes and schema changes as ordered events that can be processed by downstream applications or services. The change data feed can be configured with different options, such as starting from a specific version or timestamp, filtering by operation type or partition values, or excluding no-op changes. By using Structured Streaming to subscribe to the change data feed for daily_store_sales, one can capture and process any changes made to the total_sales column due to manual data auditing. By applying these changes to the aggregates in the store_sales_summary table with each update, one can ensure that the reports are always consistent and accurate with the latest data. Verified References: [Databricks Certified Data Engineer Professional], under "Spark Core" section; Databricks Documentation, under "Structured Streaming" section; Databricks Documentation, under "Delta Change Data Feed" section.

• Question 9:

  A production workload incrementally applies updates from an external Change Data Capture feed to a Delta Lake table as an always-on Structured Stream job. When data was initially migrated for this table, OPTIMIZE was executed and most data files were resized to 1 GB. Auto Optimize and Auto Compaction were both turned on for the streaming production job. Recent review of data files shows that most data files are under 64 MB, although each partition in the table contains at least 1 GB of data and the total table size is over 10 TB.

  Which of the following likely explains these smaller file sizes?

  A. Databricks has autotuned to a smaller target file size to reduce duration of MERGE operations

  B. Z-order indices calculated on the table are preventing file compaction C Bloom filler indices calculated on the table are preventing file compaction

  C. Databricks has autotuned to a smaller target file size based on the overall size of data in the table

  D. Databricks has autotuned to a smaller target file size based on the amount of data in each partition

  Correct Answer: A

  This is the correct answer because Databricks has a feature called Auto Optimize, which automatically optimizes the layout of Delta Lake tables by coalescing small files into larger ones and sorting data within each file by a specified column. However, Auto Optimize also considers the trade-off between file size and merge performance, and may choose a smaller target file size to reduce the duration of merge operations, especially for streaming workloads that frequently update existing records. Therefore, it is possible that Auto Optimize has autotuned to a smaller target file size based on the characteristics of the streaming production job. Verified References: [Databricks Certified Data Engineer Professional], under "Delta Lake" section; Databricks Documentation, under "Auto Optimize" section. https://docs.databricks.com/en/delta/tune-file-size.html#autotune-table 'Autotune file size based on workload'

• Question 10:

  Which statement regarding stream-static joins and static Delta tables is correct?

  A. Each microbatch of a stream-static join will use the most recent version of the static Delta table as of each microbatch.

  B. Each microbatch of a stream-static join will use the most recent version of the static Delta table as of the job's initialization.

  C. The checkpoint directory will be used to track state information for the unique keys present in the join.

  D. Stream-static joins cannot use static Delta tables because of consistency issues.

  E. The checkpoint directory will be used to track updates to the static Delta table.

  Correct Answer: A

  This is the correct answer because stream-static joins are supported by Structured Streaming when one of the tables is a static Delta table. A static Delta table is a Delta table that is not updated by any concurrent writes, such as appends or merges, during the execution of a streaming query. In this case, each microbatch of a stream-static join will use the most recent version of the static Delta table as of each microbatch, which means it will reflect any changes made to the static Delta table before the start of each microbatch. Verified References: [Databricks Certified Data Engineer Professional], under "Structured Streaming" section; Databricks Documentation, under "Stream and static joins" section.