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

• Question 11:

  The downstream consumers of a Delta Lake table have been complaining about data quality issues impacting performance in their applications. Specifically, they have complained that invalid latitude and longitude values in the activity_details table have been breaking their ability to use other geolocation processes.

  A junior engineer has written the following code to add CHECK constraints to the Delta Lake table:

  

  A senior engineer has confirmed the above logic is correct and the valid ranges for latitude and longitude are provided, but the code fails when executed. Which statement explains the cause of this failure?

  A. Because another team uses this table to support a frequently running application, two-phase locking is preventing the operation from committing.

  B. The activity details table already exists; CHECK constraints can only be added during initial table creation.

  C. The activity details table already contains records that violate the constraints; all existing data must pass CHECK constraints in order to add them to an existing table.

  D. The activity details table already contains records; CHECK constraints can only be added prior to inserting values into a table.

  E. The current table schema does not contain the field valid coordinates; schema evolution will need to be enabled before altering the table to add a constraint.

  Correct Answer: C

  The failure is that the code to add CHECK constraints to the Delta Lake table fails when executed. The code uses ALTER TABLE ADD CONSTRAINT commands to add two CHECK constraints to a table named activity_details. The first constraint checks if the latitude value is between-90 and 90, and the second constraint checks if the longitude value is between-180 and 180. The cause of this failure is that the activity_details table already contains records that violate these constraints, meaning that they have invalid latitude or longitude values outside of these ranges. When adding CHECK constraints to an existing table, Delta Lake verifies that all existing data satisfies the constraints before adding them to the table. If any record violates the constraints, Delta Lake throws an exception and aborts the operation. Verified References: [Databricks Certified Data Engineer Professional], under "Delta Lake" section; Databricks Documentation, under "Add a CHECK constraint to an existing table" section. https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-alter-table.html#add-constraint

• Question 12:

  A table named user_ltv is being used to create a view that will be used by data analysis on various teams. Users in the workspace are configured into groups, which are used for setting up data access using ACLs. The user_ltv table has the following schema:

  

  An analyze who is not a member of the auditing group executing the following query:

  

  Which result will be returned by this query?

  A. All columns will be displayed normally for those records that have an age greater than 18; records not meeting this condition will be omitted.

  B. All columns will be displayed normally for those records that have an age greater than 17; records not meeting this condition will be omitted.

  C. All age values less than 18 will be returned as null values all other columns will be returned with the values in user_ltv.

  D. All records from all columns will be displayed with the values in user_ltv.

  Correct Answer: A

  Given the CASE statement in the view definition, the result set for a user not in the auditing group would be constrained by the ELSE condition, which filters out records based on age. Therefore, the view will return all columns normally for records with an age greater than 18, as users who are not in the auditing group will not satisfy the is_member('auditing') condition. Records not meeting the age > 18 condition will not be displayed.

• Question 13:

  A distributed team of data analysts share computing resources on an interactive cluster with autoscaling configured. In order to better manage costs and query throughput, the workspace administrator is hoping to evaluate whether cluster upscaling is caused by many concurrent users or resource-intensive queries.

  In which location can one review the timeline for cluster resizing events?

  A. Workspace audit logs

  B. Driver's log file

  C. Ganglia

  D. Cluster Event Log

  E. Executor's log file

  Correct Answer: C

• Question 14:

  A CHECK constraint has been successfully added to the Delta table named activity_details using the following logic:

  

  A batch job is attempting to insert new records to the table, including a record where latitude = 45.50 and longitude = 212.67. Which statement describes the outcome of this batch insert?

  A. The write will fail when the violating record is reached; any records previously processed will be recorded to the target table.

  B. The write will fail completely because of the constraint violation and no records will be inserted into the target table.

  C. The write will insert all records except those that violate the table constraints; the violating records will be recorded to a quarantine table.

  D. The write will include all records in the target table; any violations will be indicated in the boolean column named valid_coordinates.

  E. The write will insert all records except those that violate the table constraints; the violating records will be reported in a warning log.

  Correct Answer: B

  The CHECK constraint is used to ensure that the data inserted into the table meets the specified conditions. In this case, the CHECK constraint is used to ensure that the latitude and longitude values are within the specified range. If the data does not meet the specified conditions, the write operation will fail completely and no records will be inserted into the target table. This is because Delta Lake supports ACID transactions, which means that either all the data is written or none of it is written. Therefore, the batch insert will fail when it encounters a record that violates the constraint, and the target table will not be updated. References: Constraints: https://docs.delta.io/latest/delta-constraints.html ACID Transactions: https://docs.delta.io/latest/delta-intro.html#acid-transactions

• Question 15:

  When scheduling Structured Streaming jobs for production, which configuration automatically recovers from query failures and keeps costs low?

  A. Cluster: New Job Cluster; Retries: Unlimited; Maximum Concurrent Runs: Unlimited

  B. Cluster: New Job Cluster; Retries: None; Maximum Concurrent Runs: 1

  C. Cluster: Existing All-Purpose Cluster; Retries: Unlimited; Maximum Concurrent Runs: 1

  D. Cluster: Existing All-Purpose Cluster; Retries: Unlimited; Maximum Concurrent Runs: 1

  E. Cluster: Existing All-Purpose Cluster; Retries: None; Maximum Concurrent Runs: 1

  Correct Answer: D

  The configuration that automatically recovers from query failures and keeps costs low is to use a new job cluster, set retries to unlimited, and set maximum concurrent runs to 1. This configuration has the following advantages: A new job cluster is a cluster that is created and terminated for each job run. This means that the cluster resources are only used when the job is running, and no idle costs are incurred. This also ensures that the cluster is always in a clean state and has the latest configuration and libraries for the job1. Setting retries to unlimited means that the job will automatically restart the query in case of any failure, such as network issues, node failures, or transient errors. This improves the reliability and availability of the streaming job, and avoids data loss or inconsistency2. Setting maximum concurrent runs to 1 means that only one instance of the job can run at a time. This prevents multiple queries from competing for the same resources or writing to the same output location, which can cause performance degradation or data corruption3. Therefore, this configuration is the best practice for scheduling Structured Streaming jobs for production, as it ensures that the job is resilient, efficient, and consistent. References: Job clusters, Job retries, Maximum concurrent runs

• Question 16:

  The data science team has created and logged a production using MLFlow. The model accepts a list of column names and returns a new column of type DOUBLE.

  The following code correctly imports the production model, load the customer table containing the customer_id key column into a Dataframe, and defines the feature columns needed for the model.

  

  Which code block will output DataFrame with the schema'' customer_id LONG, predictions DOUBLE''?

  A. Model, predict (df, columns)

  B. Df, map (lambda k:midel (x [columns]) ,select (''customer_id predictions'')

  C. Df. Select (''customer_id''. Model (''columns) alias (''predictions'')

  D. Df.apply(model, columns). Select (''customer_id, prediction''

  Correct Answer: A

  Given the information that the model is registered with MLflow and assuming predict is the method used to apply the model to a set of columns, we use the model.predict() function to apply the model to the DataFrame df using the specified

  columns. The model.predict() function is designed to take in a DataFrame and a list of column names as arguments, applying the trained model to these features to produce a predictions column. When working with PySpark, this predictions

  column needs to be selected alongside the customer_id to create a new DataFrame with the schema customer_id LONG, predictions DOUBLE.

  References:

  MLflow documentation on using Python function models:

  https://www.mlflow.org/docs/latest/models.html#python-function-python PySpark MLlib documentation on model prediction:

  https://spark.apache.org/docs/latest/ml-pipeline.html#pipeline

• Question 17:

  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

  Correct Answer: A

  This is the correct answer because it allows manual confirmation that these three requirements have been met. The requirements are that all tables containing Personal Identifiable Information (PII) must be clearly annotated, which includes adding column comments, table comments, and setting the custom table property "contains_pii" = true. The DESCRIBE EXTENDED command is used to display detailed information about a table, such as its schema, location, properties, and comments. By using this command on the dev.pii_test table, one can verify that the table has been created with the correct column comments, table comment, and custom table property as specified in the SQL DDL statement. Verified References: [Databricks Certified Data Engineer Professional], under "Lakehouse" section; Databricks Documentation, under "DESCRIBE EXTENDED" section.

• Question 18:

  The data science team has created and logged a production model using MLflow. The following code correctly imports and applies the production model to output the predictions as a new DataFrame named preds with the schema "customer_id LONG, predictions DOUBLE, date DATE".

  

  The data science team would like predictions saved to a Delta Lake table with the ability to compare all predictions across time. Churn predictions will be made at most once per day. Which code block accomplishes this task while minimizing potential compute costs?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: A

• Question 19:

  A data architect has designed a system in which two Structured Streaming jobs will concurrently write to a single bronze Delta table. Each job is subscribing to a different topic from an Apache Kafka source, but they will write data with the same schema. To keep the directory structure simple, a data engineer has decided to nest a checkpoint directory to be shared by both streams.

  The proposed directory structure is displayed below: Which statement describes whether this checkpoint directory structure is valid for the given scenario and why?

  

  A. No; Delta Lake manages streaming checkpoints in the transaction log.

  B. Yes; both of the streams can share a single checkpoint directory.

  C. No; only one stream can write to a Delta Lake table.

  D. Yes; Delta Lake supports infinite concurrent writers.

  E. No; each of the streams needs to have its own checkpoint directory.

  Correct Answer: E

  This is the correct answer because checkpointing is a critical feature of Structured Streaming that provides fault tolerance and recovery in case of failures. Checkpointing stores the current state and progress of a streaming query in a reliable storage system, such as DBFS or S3. Each streaming query must have its own checkpoint directory that is unique and exclusive to that query. If two streaming queries share the same checkpoint directory, they will interfere with each other and cause unexpected errors or data loss. Verified References: [Databricks Certified Data Engineer Professional], under "Structured Streaming" section; Databricks Documentation, under "Checkpointing" section.

• Question 20:

  An upstream system has been configured to pass the date for a given batch of data to the Databricks Jobs API as a parameter. The notebook to be scheduled will use this parameter to load data with the following code:

  df = spark.read.format("parquet").load(f"/mnt/source/(date)")

  Which code block should be used to create the date Python variable used in the above code block?

  A. date = spark.conf.get("date")

  B. input_dict = input() date= input_dict["date"]

  C. import sys date = sys.argv[1]

  D. date = dbutils.notebooks.getParam("date")

  E. dbutils.widgets.text("date", "null") date = dbutils.widgets.get("date")

  Correct Answer: E

  The code block that should be used to create the date Python variable used in the above code block is: dbutils.widgets.text("date", "null") date = dbutils.widgets.get("date") This code block uses the dbutils.widgets API to create and get a text widget named "date" that can accept a string value as a parameter1. The default value of the widget is "null", which means that if no parameter is passed, the date variable will be "null". However, if a parameter is passed through the Databricks Jobs API, the date variable will be assigned the value of the parameter. For example, if the parameter is "2021-11-01", the date variable will be "2021-11-01". This way, the notebook can use the date variable to load data from the specified path. The other options are not correct, because: Option A is incorrect because spark.conf.get("date") is not a valid way to get a parameter passed through the Databricks Jobs API. The spark.conf API is used to get or set Spark configuration properties, not notebook parameters2. Option B is incorrect because input() is not a valid way to get a parameter passed through the Databricks Jobs API. The input() function is used to get user input from the standard input stream, not from the API request3. Option C is incorrect because sys.argv1 is not a valid way to get a parameter passed through the Databricks Jobs API. The sys.argv list is used to get the command-line arguments passed to a Python script, not to a notebook4. Option D is incorrect because dbutils.notebooks.getParam("date") is not a valid way to get a parameter passed through the Databricks Jobs API. The dbutils.notebooks API is used to get or set notebook parameters when running a notebook as a job or as a subnotebook, not when passing parameters through the API5. References: Widgets, Spark Configuration, input(), sys.argv, Notebooks