DEA-C01 Data Ingestion and Transformation Practice Question

A data engineer must load a 250 GB uncompressed CSV dataset from Amazon S3 into Amazon Redshift. The data must be loaded daily, and the engineer wants to minimize load time and avoid saturating the cluster's leader node. Which approach meets these requirements?

⚠ Common exam trap

The trap here is assuming that a single large file or an ETL service like AWS DMS is required for bulk loading, when the COPY command with a manifest is designed specifically for parallel, high-performance loads from Amazon S3.

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 COPY command with a manifest file that references multiple files in Amazon S3.

The COPY command is the most efficient way to load large datasets from Amazon S3 into Amazon Redshift. By using a manifest file that lists multiple S3 objects, the load is parallelized across all slices, reducing load time and preventing the leader node from becoming a bottleneck. This is the standard best practice for bulk data ingestion into Redshift.

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 COPY command with a single large file and the COMPUPDATE OFF option.
Why it's wrong here
A single large file forces the COPY operation through fewer slices, and the leader node must read and distribute the entire file, creating a bottleneck. COMPUPDATE OFF only disables compression analysis; it does not parallelize the load. This approach will not minimize load time for a 250 GB dataset and can saturate the leader node.
✗
Use Amazon Redshift Spectrum to query the CSV files directly from Amazon S3 without loading them.
Why it's wrong here
Redshift Spectrum allows querying external tables in Amazon S3, but it does not load data into Redshift tables. The requirement is to load the data, not query it in place. Spectrum also incurs additional costs and may not match the performance of a native load for repeated daily queries.
✗
Use AWS Database Migration Service (AWS DMS) with a full load task to copy the CSV data into Amazon Redshift.
Why it's wrong here
AWS DMS is designed for migrating databases, not for bulk loading flat files from Amazon S3. While DMS can read from S3, it adds unnecessary complexity and does not provide the same parallel load performance as COPY. For a 250 GB CSV load, COPY with a manifest is simpler and faster.
✓
Use the COPY command with a manifest file that references multiple files in Amazon S3.
Why this is correct
The COPY command can load multiple files in parallel when a manifest is provided. Each slice on the compute nodes reads a portion of the data, distributing the workload and avoiding leader node saturation. This is the recommended way to load large datasets from Amazon S3 into Amazon Redshift efficiently.

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

DEA-C01 Data Ingestion and Transformation Practice Question

Why each option matters

Option-by-option breakdown

AWS S3 Storage Class Comparison

Related to this question