DEA-C01 Data Operations and Support Practice Question
A data engineer is troubleshooting a failed AWS Glue ETL job that reads from Amazon S3 and writes to Amazon Redshift. The job fails with the error: 'ERROR: Cannot insert a duplicate key into unique index'. The Redshift table has a primary key on the 'id' column. The data in S3 contains multiple records with the same 'id'. The engineer needs to ensure that only the latest record for each 'id' is loaded into Redshift. The data has a 'timestamp' column. Which approach should the engineer take?
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use the 'dropDuplicates' transformation in the Glue ETL script, ordering by 'timestamp' descending to keep the latest record for each 'id'.
Using the AWS Glue 'dropDuplicates' transformation on the 'id' column, ordering by 'timestamp' descending, will remove duplicate 'id' values, keeping the latest record. This is the direct approach within the Glue ETL script. Option B is incorrect because using 'overwrite' mode would replace the entire Redshift table, which would discard existing data and is not a targeted solution for handling duplicates. Option C is incorrect because loading into a staging table and using MERGE requires additional setup and is not directly part of the Glue ETL script, making it less efficient for the stated requirement. Option D is incorrect because disabling primary key constraints only defers the duplicate key error, and it would compromise data integrity rather than properly deduplicating records.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use the 'dropDuplicates' transformation in the Glue ETL script, ordering by 'timestamp' descending to keep the latest record for each 'id'.
Why this is correct
This removes duplicate IDs while preserving the most recent record.
- ✗
Set the write mode to 'overwrite' in the Glue job to replace the entire Redshift table.
Why it's wrong here
Overwrite replaces the entire table, which is inefficient and does not handle duplicates within the new data.
- ✗
Load data into a staging table in Redshift and then use a MERGE operation to insert only new records.
Why it's wrong here
Loading data into a staging table and using a `MERGE` operation is a standard approach for upserting data, efficiently inserting new records and updating existing ones in a target table based on a join condition. However, this option fails here because the problem explicitly states the *source data* (S3) contains multiple records with the same 'id'. A simple `MERGE` focused on "insert only new records" does not inherently de-duplicate the source data to select only the *latest* record for each 'id' based on the 'timestamp' column before the merge, which is the core requirement.
- ✗
Disable primary key constraints on the Redshift table before loading.
Why it's wrong here
Disabling constraints will skip the check but will still result in duplicate data in the table, which violates data integrity.
Quick reference
AWS S3 Storage Class Comparison
| Storage Class | Min Duration | Retrieval | Use Case |
|---|---|---|---|
| S3 Standard | None | Immediate | Frequently accessed data |
| S3 Standard-IA | 30 days | Immediate | Infrequent access, rapid retrieval |
| S3 One Zone-IA | 30 days | Immediate | Non-critical infrequent data |
| S3 Intelligent-Tiering | None | Immediate–hours | Unknown or changing access patterns |
| S3 Glacier Instant | 90 days | Milliseconds | Archive with instant retrieval |
| S3 Glacier Flexible | 90 days | Minutes–hours | Archive, flexible retrieval |
| S3 Glacier Deep Archive | 180 days | Hours | Long-term compliance archive |
Go deeper
Related to this question
About these practice questions
One of 1,711 original DEA-C01 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DEA-C01 practice question is part of Courseiva's free Amazon Web Services certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DEA-C01 exam.