DEA-C01 Data Ingestion and Transformation Practice Question
A data engineer is using AWS Glue Studio to build a job that reads from Amazon S3, applies a filter, and writes to Amazon Redshift. The engineer needs to ensure the job can be rerun safely without creating duplicate rows in Redshift if a previous run partially succeeded. Which design choice best meets this requirement?
⚠ Common exam trap
The trap here is assuming that Glue job bookmarks or Redshift unique constraints guarantee idempotent writes, when only an explicit merge on a business key does.
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
✓
Write the output to a staging table in Redshift and use a MERGE or upsert operation keyed on a business key to apply changes idempotently.
Loading into a staging table and then merging into the target on a business key is the canonical idempotent pattern for Redshift. Reruns re-execute the same merge deterministically, so partial failures do not leave duplicates. This decouples the risky load from the final apply step and supports safe retries.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Enable AWS Glue job bookmarks and rely on them to prevent duplicate writes to Redshift on rerun.
Why it's wrong here
Bookmarks track processed source data but do not deduplicate rows already written to Redshift. If a previous run wrote some rows before failing, bookmarks may skip those source files on rerun, leaving the target in an inconsistent state rather than guaranteeing idempotency.
- ✗
Configure the Redshift write to use a pre-action and post-action that truncate the target table before each load.
Why it's wrong here
Truncating before each load removes all existing rows, which may be acceptable for full refreshes but is destructive for incremental pipelines and does not by itself guarantee idempotency if the job fails mid-load. It also risks data loss if the load fails after the truncate.
- ✗
Set the Glue job's write mode to append and add a unique constraint on the Redshift target table to reject duplicates.
Why it's wrong here
Redshift does not enforce unique constraints as a load-time rejection mechanism; it accepts duplicates and only enforces uniqueness in certain query contexts. Appending would therefore still create duplicate rows, and the constraint would not prevent them during COPY.
- ✓
Write the output to a staging table in Redshift and use a MERGE or upsert operation keyed on a business key to apply changes idempotently.
Why this is correct
Staging plus MERGE keyed on a business key makes the load idempotent: rerunning the job re-applies the same changes without creating duplicates. This is a standard pattern for safe reruns in Redshift, and it isolates failures to the staging step before the final merge.
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
Courseiva writes every DEA-C01 question from scratch — 1,321 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Amazon Web Services exam blueprint
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.