DEA-C01 Data Ingestion and Transformation Practice Question
A company needs to ingest data from Amazon S3 into Amazon Redshift for analytics. The data arrives in CSV format with headers and may contain duplicate rows. Which Redshift command should be used to load the data while handling duplicates?
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 a staging table with COPY and then MERGE into the target table
The correct approach to handle duplicates when loading data from S3 into Redshift is to use a staging table with the COPY command, followed by a MERGE (or UPSERT) operation to insert distinct rows into the target table. Option A is incorrect because `REMOVEDUPLICATES` is not a valid COPY option. Option B is incorrect because INSERT INTO ... SELECT DISTINCT via Spectrum does not use Redshift's native ingestion and may be slower. Option D is incorrect because CREATE TABLE AS SELECT DISTINCT does not integrate with existing tables and is not a standard loading pattern.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
COPY command with the `REMOVEDUPLICATES` option
Why it's wrong here
No such option in COPY.
- ✗
INSERT INTO ... SELECT DISTINCT from S3 via Spectrum
Why it's wrong here
Spectrum queries external data but not optimal for loading.
- ✓
Use a staging table with COPY and then MERGE into the target table
Why this is correct
MERGE allows handling duplicates.
- ✗
CREATE TABLE AS SELECT DISTINCT from the S3 bucket
Why it's wrong here
Not a direct load command.
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.