DEA-C01 Data Store Management Practice Question

A company uses Amazon Redshift for its data warehouse. The data engineering team loads data daily from Amazon S3 using COPY commands. Recently, the load performance has degraded because the S3 bucket contains many small files. The team needs to optimize the COPY operation to improve performance. Which approach should they take?

⚠ Common exam trap

Candidates often confuse scaling the cluster (Option B) with optimizing data ingestion, failing to recognize that the bottleneck is the number of S3 objects, not the cluster's compute capacity.

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 manifest file that lists only the necessary files, and consolidate small files into larger ones before loading.

The performance degradation is caused by the overhead of processing many small files during the COPY command. Consolidating small files into larger ones (e.g., 100 MB–1 GB each) reduces the number of S3 GET requests and the metadata overhead on Redshift, directly improving load throughput. Using a manifest file further optimizes by explicitly listing only the required files, avoiding unnecessary S3 list operations.

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 Redshift Spectrum to query the data directly from S3 without loading.
Why it's wrong here
Spectrum queries external S3 data directly and bypasses loading, so it does not optimise the COPY operation at all; the daily load still runs. Spectrum is the correct choice when data should remain in S3 and be queried occasionally rather than loaded.
✗
Increase the number of nodes in the Redshift cluster.
Why it's wrong here
Adding nodes increases cluster compute and storage capacity, yet COPY still issues one request per small file, so the per-file overhead remains. Node scaling suits workloads constrained by query concurrency or storage, not by S3 object count.
✓
Use a manifest file that lists only the necessary files, and consolidate small files into larger ones before loading.
Why this is correct
A manifest restricts COPY to the listed files, avoiding the per-file overhead that many small objects cause, while consolidating them into larger files reduces the number of COPY operations. Together these directly address the degraded load performance.
✗
Enable automatic compression on the Redshift table.
Why it's wrong here
Compression reduces storage and I/O volume per row but does not reduce the per-file overhead that COPY incurs when reading many small objects. Compression is the right optimisation when tables are large and uncompressed, not when file count drives load time.

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 Store Management Practice Question

Why each option matters

Option-by-option breakdown

AWS S3 Storage Class Comparison

Related to this question