DEA-C01 Practice Question 74: Iceberg Merge Idempotent CDC
AWS Certified Data Engineer – Associate (DEA-C01) · Test 8 · question 4 of 10
Hourly change files from a source database land in Amazon S3. Each change record has a primary key and an operation type of insert, update or delete, and each hourly file contains at most one change record per key. A data engineer loads the changes into an Apache Iceberg table that is queried with Amazon Athena. The orchestration layer sometimes re-runs a failed load for the same hour, and the final table must be correct with no duplicate rows or half-applied changes no matter how many times a batch is run. Which approach should the engineer use?
Show answer and explanation
Answer:
MERGE INTO *"conditionally updates, deletes, or inserts rows into an Iceberg table. A single statement can combine update, delete, and insert actions,"* and it *"is transactional"* on Iceberg tables in Athena engine version 3. Matching on the primary key and choosing the action from the operation column (delete, update, or insert when not matched) means a re-run of the same batch produces the same final state. Because the whole batch is one transaction, a failure leaves no half-applied changes (option B).
- A is wrong because the table itself would contain duplicates and deleted rows, and every reader would depend on the view to hide them. It also does not apply deletes to the table. - C is wrong because each hourly file contains only the changes for that hour, so overwriting the table with it would discard all rows that did not change. - D is wrong because three separate statements are separate transactions. A failure between them leaves the table half-updated, and a re-run may insert rows that were already inserted.
Reference: MERGE INTO