Databricks Certified Data Engineer Associate Exam Practice Test Instant Access

Question 1

Which of the following code blocks will remove the rows where the value in column age is greater than 25 from the existing Delta table my_table and save the updated table?

ASELECT * FROM my_table WHERE age > 25;

BUPDATE my_table WHERE age > 25;

CDELETE FROM my_table WHERE age > 25;

DUPDATE my_table WHERE age <= 25;

EDELETE FROM my_table WHERE age <= 25;

Answer : C

: The DELETE command in Delta Lake allows you to remove data that matches a predicate from a Delta table. This command will delete all the rows where the value in the column age is greater than 25 from the existing Delta table my_table and save the updated table. The other options are either incorrect or do not achieve the desired result. Option A will only select the rows that match the predicate, but not delete them. Option B will update the rows that match the predicate, but not delete them. Option D will update the rows that do not match the predicate, but not delete them. Option E will delete the rows that do not match the predicate, which is the opposite of what we want.Reference:Table deletes, updates, and merges --- Delta Lake Documentation

Question 2

A data engineer has configured a Structured Streaming job to read from a table, manipulate the data, and then perform a streaming write into a new table.

The code block used by the data engineer is below:

If the data engineer only wants the query to process all of the available data in as many batches as required, which of the following lines of code should the data engineer use to fill in the blank?

AprocessingTime(1)

Btrigger(availableNow=True)

Ctrigger(parallelBatch=True)

Dtrigger(processingTime='once')

Etrigger(continuous='once')

Answer : B

https://spark.apache.org/docs/latest/api/python/reference/pyspark.ss/api/pyspark.sql.streaming.DataStreamWriter.trigger.html

Question 3

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.

Which of the following describes why all of these files were deleted?

AThe table was managed

BThe table's data was smaller than 10 GB

CThe table's data was larger than 10 GB

DThe table was external

EThe table did not have a location

Answer : A

The reason why all of the data files and metadata files were deleted from the file system after dropping the table is that the table was managed. A managed table is a table that is created and managed by Spark SQL. It stores both the data and the metadata in the default location specified by thespark.sql.warehouse.dirconfiguration property. When a managed table is dropped, both the data and the metadata are deleted from the file system.

Option B is not correct, as the size of the table's data does not affect the behavior of dropping the table. Whether the table's data is smaller or larger than 10 GB, the data files and metadata files will be deleted if the table is managed, and will be preserved if the table is external.

Option C is not correct, for the same reason as option B.

Option D is not correct, as an external table is a table that is created and managed by the user. It stores the data in a user-specified location, and only stores the metadata in the Spark SQL catalog. When an external table is dropped, only the metadata is deleted from the catalog, but the data files are preserved in the file system.

Option E is not correct, as a table must have a location to store the data. If the location is not specified by the user, it will use the default location for managed tables. Therefore, a table without a location is a managed table, and dropping it will delete both the data and the metadata.

Managing Tables

[Databricks Data Engineer Professional Exam Guide]

Question 4

Identify a scenario to use an external table.

A Data Engineer needs to create a parquet bronze table and wants to ensure that it gets stored in a specific path in an external location.

Which table can be created in this scenario?

AAn external table where the location is pointing to specific path in external location.

BAn external table where the schema has managed location pointing to specific path in external location.

CA managed table where the catalog has managed location pointing to specific path in external location.

DA managed table where the location is pointing to specific path in external location.

Answer : A

Question 5

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?

AThey could submit a feature request with Databricks to add this functionality.

BThey could wrap the queries using PySpark and use Python's control flow system to determine when to run the final query.

CThey could only run the entire program on Sundays.

DThey could automatically restrict access to the source table in the final query so that it is only accessible on Sundays.

EThey could redesign the data model to separate the data used in the final query into a new table.

Answer : B

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 thedatetimemodule 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.Reference:PySpark SQL Module,Python datetime Module,Databricks Jobs

Question 6

The Delta transaction log for the 'students' tables is shown using the 'DESCRIBE HISTORY students' command. A Data Engineer needs to query the table as it existed before the UPDATE operation listed in the log.

Which command should the Data Engineer use to achieve this? (Choose two.)

ASELECT * FROM students@v4

BSELECT * FROM students TIMESTAMP AS OF '2024-04-22T 14:32:47.000+00:00'

CSELECT * FROM students FROM HISTORY VERSION AS OF 3

DSELECT * FROM students VERSION AS OF 5

ESELECT * FROM students TIMESTAMP AS OF '2024-04-22T 14:32:58.000+00:00'

Answer : A, B

Question 7

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?

ADatabase

BFunction

CView

DTemporary view

ETable

Answer : E

: A table is a data entity that is stored in a physical location and can be accessed by other data engineers in other sessions. A table can be created from one or more tables using theCREATE TABLEorCREATE TABLE AS SELECTcommands. A table can also be registered from an existing DataFrame using thespark.catalog.createTablemethod. A table can be queried using SQL or DataFrame APIs. A table can also be updated, deleted, or appended using theMERGE INTOcommand or the DeltaTable API.Reference:

Create a table

Create a table from a query result

[Query a table]

[Update, delete, or merge into a table]