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

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

• Question 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 of the following commands could the data engineering team use to access sales in PySpark?

  A. SELECT * FROM sales
  B. There is no way to share data between PySpark and SQL.
  C. spark.sql("sales")
  D. spark.delta.table("sales")
  E. spark.table("sales")
  E. spark.table("sales")

  ---

  Explanation

  The data engineering team can use the spark.table method to access the Delta table sales in PySpark. This method returns a DataFrame representation of the Delta table, which can be used for further processing or testing. The spark.table method works for any table that is registered in the Hive metastore or the Spark catalog, regardless of the file format1. Alternatively, the data engineering team can also use the DeltaTable.forPath method to load the Delta table from its path2.

  References: 1: SparkSession | PySpark 3.2.0 documentation 2: Welcome to Delta Lake's Python documentation page -- delta-spark 2.4.0 documentation

• Question 2:

  Which Delta Lake feature ensures multiple writes do not corrupt data?

  A. Auto Loader
  B. Data Explorer
  C. ACID transactions
  D. Unity Catalog
  E. Data Skipping
  C. ACID transactions

  ---

  Explanation

  Delta Lake uses ACID transactions to ensure reliable concurrent writes.

• Question 3:

  In which of the following scenarios should a data engineer use the MERGE INTO command instead of the INSERT INTO command?

  A. When the location of the data needs to be changed
  B. When the target table is an external table
  C. When the source table can be deleted
  D. When the target table cannot contain duplicate records
  E. When the source is not a Delta table
  D. When the target table cannot contain duplicate records

  ---

  Explanation

  The MERGE INTO command is used to perform upserts, which are a combination of insertions and updates, based on a source table into a target Delta table1. The MERGE INTO command can handle scenarios where the target table cannot contain duplicate records, such as when there is a primary key or a unique constraint on the target table. The MERGE INTO command can match the source and target rows based on a merge condition and perform different actions depending on whether the rows are matched or not. For example, the MERGE INTO command can update the existing target rows with the new source values, insert the new source rows that do not exist in the target table, or delete the target rows that do not exist in the source table1. The INSERT INTO command is used to append new rows to an existing table or create a new table from a query result2. The INSERT INTO command does not perform any updates or deletions on the existing target table rows. The INSERT INTO command can handle scenarios where the location of the data needs to be changed, such as when the data needs to be moved from one table to another, or when the data needs to be partitioned by a certain column2. The INSERT INTO command can also handle scenarios where the target table is an external table, such as when the data is stored in an external storage system like Amazon S3 or Azure Blob Storage3. The INSERT INTO command can also handle scenarios where the source table can be deleted, such as when the source table is a temporary table or a view4. The INSERT INTO command can also handle scenarios where the source is not a Delta table, such as when the source is a Parquet, CSV, JSON, or Avro file5.

  References:

  1: MERGE INTO | Databricks on AWS

  2: [INSERT INTO | Databricks on AWS]

  3: [External tables | Databricks on AWS]

  4: [Temporary views | Databricks on AWS]

  5: [Data sources | Databricks on AWS]

• Question 4:

  A data engineer is working with two tables. Each of these tables is displayed below in its entirety.

  

  The data engineer runs the following query to join these tables together:

  

  Which of the following will be returned by the above query?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  E. Option E
  C. Option C

  ---

  Explanation

• Question 5:

  A data engineer wants to create an external table in Databricks that references data stored in an Azure Data Lake Storage (ADLS) location. The goal is to enable Databricks to access and query this external data without moving it into the Databricks-managed storage.

  Which step should the data engineer take to successfully create the external table?

  A. Use the CREATE MANAGED TABLE statement and specify the LOCATION clause with the path to the external data.
  B. CREATE UNMANAGED TABLE statement without specifying a LOCATION clause.
  C. Use the CREATE TABLE statement and specify the LOCATION clause with the path to the external data.
  D. CREATE EXTERNAL TABLE statement without specifying a LOCATION clause.
  C. Use the CREATE TABLE statement and specify the LOCATION clause with the path to the external data.

  ---

  Explanation

  To reference data stored outside of Databricks-managed storage, the engineer should use CREATE TABLE ... LOCATION 'path', which creates an unmanaged (external) table pointing to the ADLS data without moving it into Databricks storage.

• Question 6:

  A data engineering team has two tables. The first table march_transactions is a collection of all retail transactions in the month of March. The second table april_transactions is a collection of all retail transactions in the month of April. There are no duplicate records between the tables.

  Which of the following commands should be run to create a new table all_transactions that contains all records from march_transactions and april_transactions without duplicate records?

  A. CREATE TABLE all_transactions AS SELECT * FROM march_transactions INNER JOIN SELECT * FROM april_transactions;
  B. CREATE TABLE all_transactions AS SELECT * FROM march_transactions UNION SELECT * FROM april_transactions;
  C. CREATE TABLE all_transactions AS SELECT * FROM march_transactions OUTER JOIN SELECT * FROM april_transactions;
  D. CREATE TABLE all_transactions AS SELECT * FROM march_transactions INTERSECT SELECT * from april_transactions;
  E. CREATE TABLE all_transactions AS SELECT * FROM march_transactions MERGE SELECT * FROM april_transactions;
  B. CREATE TABLE all_transactions AS SELECT * FROM march_transactions UNION SELECT * FROM april_transactions;

  ---

  Explanation

  The correct command to create a new table that contains all records from two tables without duplicate records is to use the UNION operator. The UNION operator combines the results of two queries and removes any duplicate rows. The INNER JOIN, OUTER JOIN, and MERGE operators do not remove duplicate rows, and the INTERSECT operator only returns the rows that are common to both tables. Therefore, option B is the only correct answer.

  References: Databricks SQL Reference - UNION, Databricks SQL Reference - JOIN, Databricks SQL Reference - MERGE, [Databricks SQL Reference - INTERSECT]

• Question 7:

  A data engineer has configured a Structured Streaming job to process streaming data and wants it to stop automatically after consuming all available data. Which trigger should be used?

  A. trigger(once=True)
  B. trigger(availableNow=True)
  C. trigger(continuous=True)
  D. trigger(processingTime="5 seconds")
  E. trigger(manual=True)
  B. trigger(availableNow=True)

  ---

  Explanation

  availableNow processes all available data and stops, ideal for micro-batch "catch-up" processing.

• Question 8:

  A data analyst has a series of queries in a SQL program. The data analyst wants this program to run every day. They only want the final query in the program to run on Sundays. They ask for help from the data engineering team to complete this task.

  Which of the following approaches could be used by the data engineering team to complete this task?

  A. They could submit a feature request with Databricks to add this functionality.
  B. They could wrap the queries using PySpark and use Python's control flow system to determine when to run the final query.
  C. They could only run the entire program on Sundays.
  D. They could automatically restrict access to the source table in the final query so that it is only accessible on Sundays.
  E. They could redesign the data model to separate the data used in the final query into a new table.
  B. They could wrap the queries using PySpark and use Python's control flow system to determine when to run the final query.

  ---

  Explanation

  This approach would allow the data engineering team to use the existing SQL program and add some logic to control the execution of the final query based on the day of the week. They could use the datetime module in Python to get the current date and check if it is a Sunday. If so, they could run the final query, otherwise they could skip it. This way, they could schedule the program to run every day without changing the data model or the source table.

  References: PySpark SQL Module, Python datetime Module, Databricks Jobs

• Question 9:

  Which Databricks asset bundle format is valid?

  A. resources: jobs: hello-job: name: hello-job tasks: - task_key: hello-task existing_cluster_id: 1234-567890-abcde123 notebook_task: notebook_path: ./ hello.py
  B. "resources":{ "jobs":{ "name":"hello-job", "tasks":{ "task_key:"hello-task", "existing_cluster_id":"1234-567890-abcde123", "notebook_task":{ "notebook_path": ".hello.py" } } }
  C. configuration = { "resources":{ "jobs":{ "name":"hello-job", "tasks":{ "task_key:"hello-task", "existing_cluster_id":"1234-567890-abcde123", "notebook_task":{ "notebook_path": ".hello.py" } } } }
  D. resources { jobs { name = "hello-job" tasks{ task_key = "hello-task" existing_cluster_id = "1234-567890-abcde123" notebook_task{ notebook_path = ".hello.py" } } } }
  A. resources: jobs: hello-job: name: hello-job tasks: - task_key: hello-task existing_cluster_id: 1234-567890-abcde123 notebook_task: notebook_path: ./ hello.py

  ---

  Explanation

  Databricks asset bundles use YAML format, where resources are defined hierarchically with indentation. Option A correctly follows the YAML structure for defining a job, task key, cluster, and notebook path.

• Question 10:

  Which layer in the medallion architecture typically contains raw, unprocessed data?

  A. Gold
  B. Bronze
  C. Silver
  D. Platinum
  E. Archive
  B. Bronze

  ---

  Explanation

  The Bronze layer holds raw data directly ingested from the source.