DEA-C01 Data Ingestion and Transformation Practice Question
A data engineer must load a 500 MB CSV file from Amazon S3 into an existing Amazon Redshift table once per day. The file has a header row and uses a pipe delimiter. The engineer wants the fastest load and the least operational overhead. Which approach should the engineer use?
⚠ Common exam trap
The trap here is reaching for a managed ETL service by default, when Redshift's own COPY command is both faster and simpler for a straightforward file load.
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 Redshift COPY command with the IAM role, CSV, HEADER, and DELIMITER options specified.
The Redshift COPY command is purpose-built for bulk loading from S3 and parallelizes the read, making it the fastest and simplest choice for a single delimited file. The CSV, HEADER, and DELIMITER parameters align the load with the file format, so no staging or transformation layer is needed.
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 Redshift Data API to run individual INSERT statements for each row in the CSV file.
Why it's wrong here
Row-by-row INSERT statements are dramatically slower than a bulk load and would generate enormous statement volume for a 500 MB file. The Data API is useful for running SQL without a persistent connection, but it is not a bulk ingestion mechanism, so this approach would take far longer and consume more resources than the native loader.
- ✗
Use an AWS Glue ETL job to read the CSV from S3 and write to Redshift through a JDBC connection.
Why it's wrong here
A Glue job can perform the load but adds a job definition, a schedule, and DPU cost for what is a single simple file transfer. JDBC writes are also slower than COPY for bulk data because they insert through the connection rather than using Redshift's parallel load path, so it carries more overhead for no benefit here.
- ✗
Use AWS Database Migration Service with a change data capture task to replicate the S3 file into Redshift.
Why it's wrong here
DMS is designed for ongoing database replication and CDC, not for loading a single static file once per day. Configuring a replication instance, endpoints, and a task for a 500 MB daily file adds significant cost and complexity, and S3 as a source with CDC semantics does not match the simple batch load described.
- ✓
Use the Redshift COPY command with the IAM role, CSV, HEADER, and DELIMITER options specified.
Why this is correct
COPY is Redshift's native bulk loader and reads directly from S3 in parallel, which is the fastest option for a 500 MB file and needs no extra infrastructure. Specifying CSV, HEADER, and the pipe DELIMITER matches the file layout, so the load succeeds without preprocessing and with minimal ongoing operational effort.
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
This DEA-C01 question is part of Courseiva's 1,321-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.