Certified Data Engineer Associate practice test

1. Question 1 · 1

   A data organization leader is upset about the data analysis team’s reports being different from the data engineering team’s reports. The leader believes the siloed nature of their organization’s data engineering and data analysis architectures is to blame. Which of the following describes how a data lakehouse could alleviate this issue?

   Choose one answer.

   • Both teams would autoscale their work as data size evolves
   • Both teams would use the same source of truth for their work
   • Both teams would reorganize to report to the same department
   • Both teams would be able to collaborate on projects in real-time
   • Both teams would respond more quickly to ad-hoc requests

2. Question 2 · 1

   A data engineer needs to determine whether to use the built-in Databricks Notebooks versioning or version their project using Databricks Repos. Which of the following is an advantage of using Databricks Repos over the Databricks Notebooks versioning?

   Choose one answer.

   • Databricks Repos automatically saves development progress
   • Databricks Repos supports the use of multiple branches
   • Databricks Repos allows users to revert to previous versions of a notebook
   • Databricks Repos provides the ability to comment on specific changes
   • Databricks Repos is wholly housed within the Databricks Lakehouse Platform

3. Question 3 · 1

   A data engineer has been given a new record of data: id STRING = 'a1' rank INTEGER = 6 rating FLOAT = 9.4 Which SQL commands can be used to append the new record to an existing Delta table my_table?

   Choose one answer.

   • INSERT INTO my_table VALUES ('a1', 6, 9.4)
   • INSERT VALUES ('a1', 6, 9.4) INTO my_table
   • UPDATE my_table VALUES ('a1', 6, 9.4)
   • UPDATE VALUES ('a1', 6, 9.4) my_table

4. Question 4 · 1

   A data engineer has realized that the data files associated with a Delta table are incredibly small. They want to compact the small files to form larger files to improve performance. Which keyword can be used to compact the small files?

   Choose one answer.

   • OPTIMIZE
   • VACUUM
   • COMPACTION
   • REPARTITION

5. Question 5 · 1

   A data engineer wants to create a data entity from a couple of tables. The data entity must be used by other data engineers in other sessions. It also must be saved to a physical location. Which of the following data entities should the data engineer create?

   Choose one answer.

   • Table
   • Function
   • View
   • Temporary view

6. Question 6 · 1

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

   Choose one answer.

   • DROP
   • INSERT
   • MERGE
   • APPEND

7. Question 7 · 1

   A data analyst has created a Delta table sales that is used by the entire data analysis team. They want help from the data engineering team to implement a series of tests to ensure the data is clean. However, the data engineering team uses Python for its tests rather than SQL. Which command could the data engineering team use to access sales in PySpark?

   Choose one answer.

   • SELECT * FROM sales
   • spark.table("sales")
   • spark.sql("sales")
   • spark.delta.table("sales")

8. Question 8 · 1

   A data engineer has created a new database using the following command: CREATE DATABASE IF NOT EXISTS customer360; In which location will the customer360 database be located?

   Choose one answer.

   • dbfs:/user/hive/database/customer360
   • dbfs:/user/hive/warehouse
   • dbfs:/user/hive/customer360
   • dbfs:/user/hive/database

9. Question 9 · 1

   A data engineer is attempting to drop a Spark SQL table my_table and runs the following command: DROP TABLE IF EXISTS my_table; After running this command, the engineer notices that the data files and metadata files have been deleted from the file system. What is the reason behind the deletion of all these files?

   Choose one answer.

   • The table was managed
   • The table's data was smaller than 10 GB
   • The table did not have a location
   • The table was external

10. Question 10 · 1

    What is a benefit of creating an external table from Parquet rather than CSV when using a CREATE TABLE AS SELECT statement?

    Choose one answer.

    • Parquet files can be partitioned
    • Parquet files will become Delta tables
    • Parquet files have a well-defined schema
    • Parquet files have the ability to be optimized

11. Question 11 · 1

    A data engineer has left the organization. The data team needs to transfer ownership of the data engineer’s Delta tables to a new data engineer. The new data engineer is the lead engineer on the data team. Assuming the original data engineer no longer has access, which of the following individuals must be the one to transfer ownership of the Delta tables in Data Explorer?

    Choose one answer.

    • Databricks account representative
    • This transfer is not possible
    • Workspace administrator
    • New lead data engineer
    • Original data engineer

12. Question 12 · 1

    Which SQL keyword can be used to convert a table from a long format to a wide format?

    Choose one answer.

    • TRANSFORM
    • PIVOT
    • SUM
    • CONVERT

13. Question 13 · 1

    A data engineer has a Python variable table_name that they would like to use in a SQL query. They want to construct a Python code block that will run the query using table_name. They have the following incomplete code block: ____(f"SELECT customer_id, spend FROM {table_name}") What can be used to fill in the blank to successfully complete the task?

    Choose one answer.

    • spark.delta.sql
    • spark.sql
    • spark.table
    • dbutils.sql

14. Question 14 · 1

    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?

    Choose one answer.

    • Auto Loader
    • Unity Catalog
    • Delta Lake
    • Delta Live Tables

15. Question 15 · 1

    A data engineer has three tables in a Delta Live Tables (DLT) pipeline. They have configured the pipeline to drop invalid records at each table. They notice that some data is being dropped due to quality concerns at some point in the DLT pipeline. They would like to determine at which table in their pipeline the data is being dropped. Which approach can the data engineer take to identify the table that is dropping the records?

    Choose one answer.

    • They can set up separate expectations for each table when developing their DLT pipeline.
    • They can navigate to the DLT pipeline page, click on the “Error” button, and review the present errors.
    • They can set up DLT to notify them via email when records are dropped.
    • They can navigate to the DLT pipeline page, click on each table, and view the data quality statistics.

Paid access to all 147 questions

Each purchase applies to this certification. Prices are one-time payments, not monthly subscriptions.