Certified Data Engineer Associate 02
101. 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?
Answer: OPTIMIZE
OPTIMIZE table_name: Compacts small files into larger files to improve performance.
VACUUM table_name: Removes old files that are no longer needed after compaction.
102. 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?
Answer: Table.
103. 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:
1 | COPY INTO transactions |
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?
Answer：The previous day’s le has already been copied into the table.
The COPY INTO command does not copy files that have already been copied into the table. To copy new files, the data engineer must ensure that the new files have a different name than the previous day’s file or that the previous day’s file has been removed from the location.
104. Which command can be used to write data into a Delta table while avoiding the writing of duplicate records?
Answer: MERGE INTO
105. 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?
Answer: spark.table(“sales”)
106. A data engineer has created a new database using the following command:
1 | CREATE DATABASE IF NOT EXISTS customer360; |
In which location will the customer360 database be located?
Answer: dbfs:/user/hive/warehouse
107. A data engineer is attempting to drop a Spark SQL table my_table and runs the following command:
1 | 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?
Answer: The table was managed
The DROP TABLE command in Spark SQL behaves differently based on whether the table is internal or external. For an internal table, both the metadata and the data files are deleted when the table is dropped. However, for an external table, only the metadata is deleted, and the data files remain intact. Since in this case both the data files and metadata files were deleted, it indicates that the table was a managed table.
108. A data engineer needs to create a table in Databricks using data from a CSV le at location /path/to/csv. They run the following command:
1 | CREATE TABLE new_table |
Which of the following lines of code fills in the above blank to successfully complete the task?
Answer: USING CSV
109. What is a benet of creating an external table from Parquet rather than CSV when using a CREATE TABLE AS SELECT statement?
Answer: Parquet files have a well-defined schema, which can be used to optimize query performance.
110. Which SQL keyword can be used to convert a table from a long format to a wide format?
Answer: PIVOT
111. 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:
1 | ___(f"SELECT customer_id, spend FROM {table_name}") |
Which of the following can be used to fill in the blank to successfully complete the task?
Answer: spark.sql
112. 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?
Answer: Delta Live Tables.
Delta Live Tables is a tool provided by Databricks that can help data engineers automate the monitoring of data quality. It is designed for managing data pipelines, monitoring data quality, and automating workflows. With Delta Live Tables, you can set up data quality checks and alerts to detect issues and anomalies in your data as it is ingested and processed in real-time. It provides a way to ensure that the data quality meets your desired standards and can trigger actions or notifications when issues are detected.
113. A data engineer has three tables in a Delta Live Tables (DLT) pipeline. They have congured 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?
Answer: They can navigate to the DLT pipeline page, click on each table, and view the data quality statistics.
EXCEPT:
- EXPECT (…): If the condition is not met, the record is marked as invalid but still included in the target dataset.
- EXPECT (…) ON VIOLATION DROP ROW: If the condition is not met, the record is dropped from the target dataset.
- EXPECT (…) ON VIOLATION FAIL UPDATE: If the condition is not met, the record is marked as invalid and the job fails.
114. What is used by Spark to record the offset range of the data being processed in each trigger in order for Structured Streaming to reliably track the exact progress of the processing so that it can handle any kind of failure by restarting and/or reprocessing?
Answer: Checkpointing and Write-ahead Logs
Checkpointing and Write-ahead Logs: The engine uses checkpointing and write-ahead logs to record the offset range of the data being processed in each trigger. This allows Spark to reliably track the exact progress of the processing and handle any kind of failure by restarting and/or reprocessing from the last checkpoint or log entry.
Progress Tracking / Offset Range => Checkpointing and Write-ahead Logs
Repeatedly process data => replayable sources
Avoid Repeatedly process data => idempotent sinks
115. What describes the relationship between Gold tables and Silver tables?
Answer: Gold tables are more likely to contain aggregations than Silver tables.
116. What describes when to use the CREATE STREAMING LIVE TABLE (formerly CREATE INCREMENTAL LIVE TABLE) syntax over the CREATE LIVE TABLE syntax when creating Delta Live Tables (DLT) tables using SQL?
Answer: CREATE STREAMING LIVE TABLE should be used when data needs to be processed incrementally
117. Which type of workloads are compatible with Auto Loader?
Answer: Streaming workloads
Auto Loader = cloudFiles + readStream = Streaming workloads
118. A dataset has been dened 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?
Answer: Records that violate the expectation are dropped from the target dataset and recorded as invalid in the event log.
119. A data engineer has a Job with multiple tasks that runs nightly. Each of the tasks runs slowly because the clusters take a long time to start. Which action can the data engineer perform to improve the start up time for the clusters used for the Job?
Answer: They can use clusters that are from a cluster pool
120. A data engineer has a single-task Job that runs each morning before they begin working. After identifying an upstream data issue, they need to set up another task to run a new notebook prior to the original task. Which approach can the data engineer use to set up the new task?
Answer: They can create a new task in the existing Job and then add it as a dependency of the original task.
121. A single Job runs two notebooks as two separate tasks. A data engineer has noticed that one of the notebooks is running slowly in the Job’s current run. The data engineer asks a tech lead for help in identifying why this might be the case. Which approach can the tech lead use to identify why the notebook is running slowly as part of the Job?
Answer: They can navigate to the Runs tab in the Jobs UI and click on the active run to review the processing notebook.
122. A data analysis team has noticed that their Databricks SQL queries are running too slowly when connected to their always-on SQL endpoint. They claim that this issue is present when many members of the team are running small queries simultaneously. They ask the data engineering team for help. The data engineering team notices that each of the team’s queries uses the same SQL endpoint. Which approach can the data engineering team use to improve the latency of the team’s queries?
Answer: hey can increase the maximum bound of the SQL endpoint’s scaling range.
increase the cluster size: Single big query is run slowly / Complex Join or Aggregation / Scanning a large amount of data
maximum bound of scaling range = Multiple small queries are run slowly / Many users are running queries simultaneously
123. Differentiate between all-purpose clusters and jobs clusters. A data engineering team has created a python notebook to load data from cloud storage, this job has been tested and now needs to be scheduled in production. Which would be the best cluster to be used in this case?
Answer: A jobs cluster
124. Identify how the count_if function and the count where x is null can be used Consider a table random_values with below data. What would be the output of below query?
1 | select count_if(col > 1) as count_a, count(*) as count_b, count(col1) as count_c from random_values col1 |
1 | 0 |
Answer: count_a = 3, count_b = 6, count_c = 5
125. Which two components function in the DB platform architecture’s control plane? (Choose two.)
Answer: Compute Orchestration and Unity Catalog
126. In a healthcare provider organization using Delta Lake to store electronic health records (EHRs), a data analyst needs to analyze a snapshot of the patient_records table from two weeks ago before some recent data corrections were applied. What approach should the Data Engineer take to allow the analyst to query that specific prior version?
Answer: Identify the version number corresponding to two weeks ago from the Delta transaction log, share that version number with the analyst to query using VERSION AS OF syntax, or export that version to a new Delta table for the analyst to query.
Time Travel = VERSION AS OF / TIMESTAMP AS OF
127. What can be used to simplify and unify siloed data architectures that are specialized for specic use cases?
Answer: Data Lakehouse
Data Lakehouse: A data lakehouse is a modern data architecture that combines the best features of a data lake and a data warehouse. It is designed to provide a unified view of data from multiple sources, making it easier to analyze and make decisions. Data lakehouses are often used for data integration and data analytics.
Data Warehouse: A data warehouse is a specialized database designed for reporting and analysis. It typically stores structured data and is optimized for query performance. Data warehouses are often used for business intelligence and reporting.
Data Lake: A data lake is a centralized repository for storing and managing data. It can store structured, semi-structured, and unstructured data. Data lakes are often used for data integration and data analytics.
128. 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 specic path in an external location. Which table can be created in this scenario?
Answer: An external table where the location is pointing to specic path in external location.
129. Identify the impact of ON VIOLATION DROP ROW and ON VIOLATION FAIL UPDATE for a constraint violation. A data engineer has created an ETL pipeline using Delta Live table to manage their company travel reimbursement detail, they want to ensure that the if the location details has not been provided by the employee, the pipeline needs to be terminated. How can the scenario be implemented?
Answer: CONSTRAINT valid_location EXPECT (location != NULL) ON VIOLATION FAIL UPDATE
130. Which two conditions are applicable for governance in Databricks Unity Catalog? (Choose two.)
Answer:
- You can have more than 1 metastore within a databricks account console but only 1 per region.
- If metastore is not associated with location, it’s mandatory to associate catalog with managed locations
Schema location > Catalog location > Metastore location
131. A data engineer needs to access the view created by the sales team, using a shared cluster. The data engineer has been provided usage permissions on the catalog and schema. In order to access the view created by sales team. What are the minimum permissions the data engineer would require in addition?
Answer: Needs SELECT permission on the VIEW and the underlying TABLE.
132. Which method should a Data Engineer apply to ensure Workows are being triggered on schedule?
Answer: Scheduled Workows can reduce resource consumption and expense since the cluster runs only long enough to execute the pipeline.
133. 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.)
| — | version | timestamp | operation |
|---|---|---|---|
| 0 | 0 | 2025-09-01 10:00:00 | CREATE TABLE |
| 1 | 0 | 2025-09-01 10:00:00 | INSERT INTO |
| 2 | 0 | 2025-09-01 10:00:00 | UPDATE |
Answer:
- SELECT FROM students@v4 // SELECT FROM students VERSION AS OF 4;
- SELECT * FROM students TIMESTAMP AS OF ‘2025-09-01 10:00:00’
VERSION AS OF: The VERSION AS OF command can be used to query a table as it existed before a specific version. The command takes the version number as an argument and returns the results of the query as they existed at that version.
SELECT FROM table VERSION AS OF version_number;
SELECT FROM table TIMESTAMP AS OF ‘timestamp’;
SELECT * FROM table@VERSION;
134. An engineering manager uses a Databricks SQL query to monitor ingestion latency for each data source. The manager checks the results of the query every day, but they are manually rerunning the query each day and waiting for the results.
Answer: They can schedule the query to refresh every 1 day from the query’s page in Databricks SQL.
135. A data engineer only wants to execute the final block of a Python program if the Python variable day_of_week is equal to 1 and the Python variable review_period is True. Which of the following control ow statements should the data engineer use to begin this conditionally executed code block?
Answer: if day_of_week == 1 and review_period:
136. Which of the following must be specied when creating a new Delta Live Tables pipeline?
Answer: At least one notebook library to be executed.
137. Which of the following benets is provided by the array functions from Spark SQL?
Answer: An ability to work with complex, nested data ingested from JSON les
