Databricks DATABRICKS-CERTIFIED-PROFESSIONAL-DATA-ENGINEER Online Practice Questions and Exam Preparation

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

• Question 101:

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

• Question 102:

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

• Question 103:

  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
  D. "Can Restart" privileges on the required cluster

• Question 104:

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

• Question 105:

  The data governance team has instituted a requirement that all tables containing Personal Identifiable Information (PH) must be clearly annotated. This includes adding column comments, table comments, and setting the custom table property"contains_pii" = true.

  The following SQL DDL statement is executed to create a new table: Which command allows manual confirmation that these three requirements have been met?

  

  A. DESCRIBE EXTENDED dev.pii test
  B. DESCRIBE DETAIL dev.pii test
  C. SHOW TBLPROPERTIES dev.pii test
  D. DESCRIBE HISTORY dev.pii test
  E. SHOW TABLES dev
  A. DESCRIBE EXTENDED dev.pii test

• Question 106:

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

• Question 107:

  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
  A. spark.sql.files.maxPartitionBytes

• Question 108:

  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
  B. Delta Lake automatically collects statistics on the first 32 columns of each table which are leveraged in data skipping based on query filters.

• Question 109:

  A junior data engineer has been asked to develop a streaming data pipeline with a grouped aggregation using DataFrame df. The pipeline needs to calculate the average humidity and average temperature for each non-overlapping five-

  minute interval. Incremental state information should be maintained for 10 minutes for late-arriving data.

  Streaming DataFrame df has the following schema:

  "device_id INT, event_time TIMESTAMP, temp FLOAT, humidity FLOAT"

  Code block:

  

  Choose the response that correctly fills in the blank within the code block to complete this task.

  A. withWatermark("event_time", "10 minutes")
  B. awaitArrival("event_time", "10 minutes")
  C. await("event_time + `10 minutes'")
  D. slidingWindow("event_time", "10 minutes")
  E. delayWrite("event_time", "10 minutes")
  A. withWatermark("event_time", "10 minutes")

• Question 110:

  A junior data engineer is migrating a workload from a relational database system to the Databricks Lakehouse. The source system uses a star schema, leveraging foreign key constrains and multi-table inserts to validate records on write. Which consideration will impact the decisions made by the engineer while migrating this workload?

  A. All Delta Lake transactions are ACID compliance against a single table, and Databricks does not enforce foreign key constraints.
  B. Databricks only allows foreign key constraints on hashed identifiers, which avoid collisions in highly-parallel writes.
  C. Foreign keys must reference a primary key field; multi-table inserts must leverage Delta Lake's upsert functionality.
  D. Committing to multiple tables simultaneously requires taking out multiple table locks and can lead to a state of deadlock.
  A. All Delta Lake transactions are ACID compliance against a single table, and Databricks does not enforce foreign key constraints.