Databricks Data Engineer Associate Question 406
Select 3You are using Databricks to implement a Change Data Capture (CDC) pipeline for a retail dataset. The pipeline uses the APPLY CHANGES INTO command to process incoming changes from a source table (sales_stream) into a target Delta table (sales_target). You observe that some historical records in the sales_target table are being updated or deleted incorrectly. Which of the following could be potential causes for this issue?
- A
The source table contains duplicate rows for the same primary key and timestamp.
- B
The target table schema does not include a primary key column.
- C
The watermark condition in
APPLY CHANGES INTOis set too aggressively, filtering out valid late-arriving data. - D
The
APPLY CHANGES INTOcommand is using append-only mode, which does not allow updates or deletes. - E
The source table contains null values in the primary key column.
Show answer and explanation
Correct answers: A, C, E
Explanation
The APPLY CHANGES INTO command enables Change Data Capture (CDC) by merging incremental changes from a source table into a target Delta table. Issues such as duplicate primary key values in the source, null values in primary key columns, or overly aggressive watermarking can cause incorrect updates or deletes in the target table. Understanding these scenarios is critical for properly implementing a CDC pipeline.
- A. Correct.
Duplicate rows with the same primary key and timestamp in the source table can lead to unexpected behavior during updates, as Databricks may not know which row to prioritize.
- B. Incorrect.
The target table schema does not need to explicitly define a primary key column for
APPLY CHANGES INTOto function correctly. Instead, the command relies on the primary key specified in the command itself. - C. Correct.
An overly aggressive watermark condition can discard late-arriving data, causing missing or incorrect updates in the target table.
- D. Incorrect.
Append-only mode does not cause incorrect updates or deletes since it does not perform any updates or deletes in the first place.
- E. Correct.
Null values in the primary key column can lead to undefined behavior, as the primary key is required to uniquely identify records for updates and deletes.