DEA-C01 practice question: Querying with Athena (3 of 3)
Data Operations and Support A free question from our original DEA-C01 practice exam. Try it, then open the answer to see why each option is right or wrong.
A bank receives change files every hour that contain new, updated and deleted customer records. The team stores the customer table as an Apache Iceberg table and queries it with Amazon Athena. Each hourly change must be applied to the table without duplicates, and the team wants to use Athena for the upserts rather than a Spark cluster.
The table already holds 5 years of data, and the team wants to keep the cost of the hourly loads low.
Which action meets these requirements?
Choose one answer.
AA. Run an INSERT INTO statement that appends the hourly change file to the table, and let the queries de-duplicate the rows at read time.
BB. Load the hourly change file into a staging table, and run a MERGE INTO that matches on customer ID and updates, inserts or deletes rows.
CC. Rebuild the whole customer table with a CTAS query every hour, and drop the previous version after each rebuild.
DD. Run UPDATE and DELETE statements against the customer table as it is stored in S3 as Hive tables, one statement for each changed customer.
Show the answer and explanation
Answer: B
A. Wrong: Appending the changes stores duplicate and deleted rows, and every query must filter them out again. The option pushes the cleanup onto every reader and does not meet the no-duplicates requirement.
B. Right: Correct. MERGE INTO applies inserts, updates and deletes from a staging table to the Iceberg table in one statement. Matching on the customer ID keeps the result free of duplicates, and Athena runs it without a Spark cluster. Learn more: MERGE INTO (opens in a new tab)
C. Wrong: A full rebuild every hour scans and writes all 5 years of data each time, which is far more expensive than applying the changes. The option meets the result requirement but fails the cost goal.
D. Wrong: Athena does not run row-level updates and deletes on plain Hive tables. The option uses a capability the table format does not have, and it would be slow for thousands of changes.
Explanation
Iceberg tables in Athena support MERGE INTO, which applies a batch of inserts, updates and deletes from a staging table in one statement without duplicates, and it touches only the affected data. Appending changes leaves duplicates, a full CTAS rebuild every hour is expensive, and Athena cannot update or delete rows in plain Hive tables.
Want more on Querying with Athena? The 5 full-length practice sets have 14 more Querying with Athena questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →