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

• Question 41:

  A dataset has been defined using Delta Live Tables and includes an expectations clause:

  CONSTRAINT valid_timestamp EXPECT (timestamp > '2020-01-01') ON VIOLATION DROP ROW

  What is the expected behavior when a batch of data containing data that violates these constraints is processed?

  A. Records that violate the expectation are dropped from the target dataset and loaded into a quarantine table.

  B. Records that violate the expectation are added to the target dataset and flagged as invalid in a field added to the target dataset.

  C. Records that violate the expectation are dropped from the target dataset and recorded as invalid in the event log.

  D. Records that violate the expectation are added to the target dataset and recorded as invalid in the event log.

  E. Records that violate the expectation cause the job to fail.

  Correct Answer: C

  Explanation: With the defined constraint and expectation clause, when a batch of data is processed, any records that violate the expectation (in this case, where the timestamp is not greater than '2020-01-01') will be dropped from the target dataset. These dropped records will also be recorded as invalid in the event log, allowing for auditing and tracking of the data quality issues without causing the entire job to fail. https://docs.databricks.com/en/delta-live-tables/expectations.html

• Question 42:

  A data engineer needs to apply custom logic to identify employees with more than 5 years of experience in array column employees in table stores. The custom logic should create a new column exp_employees that is an array of all of the employees with more than 5 years of experience for each row. In order to apply this custom logic at scale, the data engineer wants to use the FILTER higher-order function.

  Which of the following code blocks successfully completes this task?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: A

• Question 43:

  Which of the following commands will return the number of null values in the member_id column?

  A. SELECT count(member_id) FROM my_table;

  B. SELECT count(member_id) - count_null(member_id) FROM my_table;

  C. SELECT count_if(member_id IS NULL) FROM my_table;

  D. SELECT null(member_id) FROM my_table;

  E. SELECT count_null(member_id) FROM my_table;

  Correct Answer: C

  Explanation: https://docs.databricks.com/en/sql/language-manual/functions/count.html

  Returns

  A BIGINT.

  If * is specified also counts row containing NULL values.

  If expr are specified counts only rows for which all expr are not NULL.

  If DISTINCT duplicate rows are not counted.

• Question 44:

  A data engineer runs a statement every day to copy the previous day's sales into the table transactions. Each day's sales are in their own file in the location "/transactions/raw". Today, the data engineer runs the following command to complete this task:

  

  After running the command today, the data engineer notices that the number of records in table transactions has not changed. Which of the following describes why the statement might not have copied any new records into the table?

  A. The format of the files to be copied were not included with the FORMAT_OPTIONS keyword.

  B. The names of the files to be copied were not included with the FILES keyword.

  C. The previous day's file has already been copied into the table.

  D. The PARQUET file format does not support COPY INTO.

  E. The COPY INTO statement requires the table to be refreshed to view the copied rows.

  Correct Answer: C

  Explanation: https://docs.databricks.com/en/ingestion/copy-into/index.html The COPY INTO SQL command lets you load data from a file location into a Delta table. This is a re- triable and idempotent operation; files in the source location that have already been loaded are skipped. if there are no new records, the only consistent choice is C no new files were loaded because already loaded files were skipped.

• Question 45:

  A data engineer wants to schedule their Databricks SQL dashboard to refresh once per day, but they only want the associated SQL endpoint to be running when it is necessary.

  Which of the following approaches can the data engineer use to minimize the total running time of the SQL endpoint used in the refresh schedule of their dashboard?

  A. They can ensure the dashboard's SQL endpoint matches each of the queries' SQL endpoints.

  B. They can set up the dashboard's SQL endpoint to be serverless.

  C. They can turn on the Auto Stop feature for the SQL endpoint.

  D. They can reduce the cluster size of the SQL endpoint.

  E. They can ensure the dashboard's SQL endpoint is not one of the included query's SQL endpoint.

  Correct Answer: C

• Question 46:

  Which of the following describes the relationship between Gold tables and Silver tables?

  A. Gold tables are more likely to contain aggregations than Silver tables.

  B. Gold tables are more likely to contain valuable data than Silver tables.

  C. Gold tables are more likely to contain a less refined view of data than Silver tables.

  D. Gold tables are more likely to contain more data than Silver tables.

  E. Gold tables are more likely to contain truthful data than Silver tables.

  Correct Answer: A

  Explanation: In some data processing pipelines, especially those following a typical "Bronze-Silver-Gold" data lakehouse architecture, Silver tables are often considered a more refined version of the raw or Bronze data. Silver tables may include data cleansing, schema enforcement, and some initial transformations. Gold tables, on the other hand, typically represent a stage where data is further enriched, aggregated, and processed to provide valuable insights for analytical purposes. This could indeed involve more aggregations compared to Silver tables.

• Question 47:

  Which of the following commands can be used to write data into a Delta table while avoiding the writing of duplicate records?

  A. DROP

  B. IGNORE

  C. MERGE

  D. APPEND

  E. INSERT

  Correct Answer: C

  Explanation: To write data into a Delta table while avoiding the writing of duplicate records, you can use the MERGE command. The MERGE command in Delta Lake allows you to combine the ability to insert new records and update existing records in a single atomic operation. The MERGE command compares the data being written with the existing data in the Delta table based on specified matching criteria, typically using a primary key or unique identifier. It then performs conditional actions, such as inserting new records or updating existing records, depending on the comparison results. By using the MERGE command, you can handle the prevention of duplicate records in a more controlled and efficient manner. It allows you to synchronize and reconcile data from different sources while avoiding duplication and ensuring data integrity.

• Question 48:

  Which of the following Git operations must be performed outside of Databricks Repos?

  A. Commit

  B. Pull

  C. Push

  D. Clone

  E. Merge

  Correct Answer: E

  Explanation: For following tasks, work in your Git provider:

  Create a pull request.

  Resolve merge conflicts.

  Merge or delete branches.

  Rebase a branch.

  https://docs.databricks.com/repos/index.html

• Question 49:

  A data engineer has a Python notebook in Databricks, but they need to use SQL to accomplish a specific task within a cell. They still want all of the other cells to use Python without making any changes to those cells.

  Which of the following describes how the data engineer can use SQL within a cell of their Python notebook?

  A. It is not possible to use SQL in a Python notebook

  B. They can attach the cell to a SQL endpoint rather than a Databricks cluster

  C. They can simply write SQL syntax in the cell

  D. They can add %sql to the first line of the cell

  E. They can change the default language of the notebook to SQL

  Correct Answer: D

• Question 50:

  A data engineer has been using a Databricks SQL dashboard to monitor the cleanliness of the input data to an ELT job. The ELT job has its Databricks SQL query that returns the number of input records containing unexpected NULL values. The data engineer wants their entire team to be notified via a messaging webhook whenever this value reaches 100.

  Which of the following approaches can the data engineer use to notify their entire team via a messaging webhook whenever the number of NULL values reaches 100?

  A. They can set up an Alert with a custom template.

  B. They can set up an Alert with a new email alert destination.

  C. They can set up an Alert with a new webhook alert destination.

  D. They can set up an Alert with one-time notifications.

  E. They can set up an Alert without notifications.

  Correct Answer: C

  Explanation: To achieve this, the data engineer can set up an Alert in the Databricks workspace that triggers when the query results exceed the threshold of 100 NULL values. They can create a new webhook alert destination in the Alert's configuration settings and provide the necessary messaging webhook URL to receive notifications. When the Alert is triggered, it will send a message to the configured webhook URL, which will then notify the entire team of the issue.