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

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

• Question 31:

  Which SQL operation transforms rows into columns?

  A. UNPIVOT
  B. EXPAND
  C. PIVOT
  D. TRANSFORM
  E. RENAME
  C. PIVOT

  ---

  Explanation

  PIVOT rotates row data into columns based on a grouping column.

• Question 32:

  A data engineer is attempting to drop a Spark SQL table my_table. The data engineer wants to delete all table metadata and data.

  They run the following command:

  DROP TABLE IF EXISTS my_table

  While the object no longer appears when they run SHOW TABLES, the data files still exist.

  Which of the following describes why the data files still exist and the metadata files were deleted?

  A. The table's data was larger than 10 GB
  B. The table's data was smaller than 10 GB
  C. The table was external
  D. The table did not have a location
  E. The table was managed
  C. The table was external

  ---

  Explanation

  An external table is a table that is defined in the metastore and points to an existing location in the storage system. When you drop an external table, only the metadata is deleted from the metastore, but the data files are not deleted from the storage system. This is because external tables are meant to be shared by multiple applications and users, and dropping them should not affect the data availability. On the other hand, a managed table is a table that is defined in the metastore and also managed by the metastore. When you drop a managed table, both the metadata and the data files are deleted from the metastore and the storage system, respectively. This is because managed tables are meant to be exclusive to the application or user that created them, and dropping them should free up the storage space. Therefore, the correct answer is C, because the table was external and only the metadata was deleted when the table was dropped.

  References: Databricks Documentation - Managed and External Tables, Databricks Documentation - Drop Table

• Question 33:

  A data engineer is maintaining a data pipeline. Upon data ingestion, the data engineer notices that the source data is starting to have a lower level of quality. The data engineer would like to automate the process of monitoring the quality level. Which of the following tools can the data engineer use to solve this problem?

  A. Unity Catalog
  B. Data Explorer
  C. Delta Lake
  D. Delta Live Tables
  E. Auto Loader
  D. Delta Live Tables

  ---

  Explanation

  Delta Live Tables is a tool that enables data engineers to build and manage reliable data pipelines with minimal code. One of the features of Delta Live Tables is data quality monitoring, which allows data engineers to define quality expectations for their data and automatically check them at every step of the pipeline. Data quality monitoring can help detect and resolve data quality issues, such as missing values, duplicates, outliers, or schema changes. Data quality monitoring can also generate alerts and reports on the quality level of the data, and enable data engineers to troubleshoot and fix problems quickly.

  References: Delta Live Tables Overview, Data Quality Monitoring

• Question 34:

  Which of the following statements about Auto Loader is true?

  A. It supports both batch and streaming workloads.
  B. It only supports batch workloads.
  C. It requires manual schema definitions.
  D. It does not support checkpointing.
  E. It can only read JSON files.
  A. It supports both batch and streaming workloads.

  ---

  Explanation

  Auto Loader supports both incremental batch and streaming ingestion while automatically managing schema inference and checkpointing.

• Question 35:

  A data engineer has inherited a Databricks pipeline from a previous team. The pipeline is missing SLAs and costs more than the allotted budget. On analysis, it is noted that the cluster is not being fully utilized, and the dataset is getting skewed.

  How should the data engineer resolve this issue?

  A. Use coalesce() on the dataset to merge partitions and reduce skew.
  B. Increase the number of executors for the job.
  C. Repartition the dataset to have it be more optimally spread across all nodes.
  D. Increase the executor memory for the job.
  C. Repartition the dataset to have it be more optimally spread across all nodes.

  ---

  Explanation

  Repartitioning the dataset ensures data is evenly distributed across all nodes, reducing skew, improving cluster utilization, and helping control costs while meeting SLAs.

• Question 36:

  A data engineer has realized that they made a mistake when making a daily update to a table. They need to use Delta time travel to restore the table to a version that is 3 days old. However, when the data engineer attempts to time travel to the older version, they are unable to restore the data because the data files have been deleted.

  Which of the following explains why the data files are no longer present?

  A. The VACUUM command was run on the table
  B. The TIME TRAVEL command was run on the table
  C. The DELETE HISTORY command was run on the table
  D. The OPTIMIZE command was nun on the table
  E. The HISTORY command was run on the table
  A. The VACUUM command was run on the table

  ---

  Explanation

  The VACUUM command is used to remove files that are no longer referenced by a Delta table and are older than the retention threshold1. The default retention period is 7 days2, but it can be changed by setting the delta.logRetentionDuration and delta.deletedFileRetentionDuration configurations3. If the VACUUM command was run on the table with a retention period shorter than 3 days, then the data files that were needed to restore the table to a 3day-old version would have been deleted. The other commands do not delete data files from the table. The TIME TRAVEL command is used to query a historical version of the table4. The DELETE HISTORY command is not a valid command in Delta Lake. The OPTIMIZE command is used to improve the performance of the table by compacting small files into larger ones5. The HISTORY command is used to retrieve information about the operations performed on the table.

  References: 1: VACUUM | Databricks on AWS 2: Work with Delta Lake table history | Databricks on AWS 3: [Delta Lake configuration | Databricks on AWS] 4: Work with Delta Lake table history - Azure Databricks 5: [OPTIMIZE | Databricks on AWS] : [HISTORY | Databricks on AWS]

• Question 37:

  What must be included to create a Delta Live Table pipeline?

  A. Cloud region
  B. Cluster ID
  C. At least one notebook or script
  D. A registered catalog
  E. A webhook endpoint
  C. At least one notebook or script

  ---

  Explanation

  At least one SQL or Python notebook/script is required to define the pipeline logic.

• Question 38:

  What is the correct command to view the history of changes made to a Delta table in Databricks?

  A. DESCRIBE HISTORY my_table
  B. SHOW HISTORY my_table
  C. SELECT HISTORY FROM my_table
  D. HISTORY TABLE my_table
  E. DELTA HISTORY my_table
  B. SHOW HISTORY my_table

  ---

  Explanation

  SHOW HISTORY is the correct command to view the transaction log of a Delta table.

• Question 39:

  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;
  C. SELECT count_if(member_id IS NULL) FROM my_table;

  ---

  Explanation

  To return the number of null values in the member_id column, the best option is to use the count_if function, which counts the number of rows that satisfy a given condition. In this case, the condition is that the member_id column is null. The other options are either incorrect or not supported by Spark SQL. Option A will return the number of non- null values in the member_id column. Option B will not work because there is no count_null function in Spark SQL. Option D will not work because there is no null function in Spark SQL. Option E will not work because there is no count_null function in Spark SQL.

  References: Built-in Functions - Spark SQL, Built-in Functions count_if - Spark SQL, Built-in Functions

• Question 40:

  When a managed Delta table is dropped, what happens to its data and metadata?

  A. Metadata is deleted, but data remains.
  B. Data is deleted, metadata remains.
  C. Both data and metadata are deleted.
  D. Only permissions are removed.
  E. Only the transaction log is deleted.
  C. Both data and metadata are deleted.

  ---

  Explanation

  Managed tables are fully controlled by Databricks; dropping them removes both data and metadata from storage.