DBS-C01 COPY command performance Practice Question
A company runs an Amazon Redshift cluster with 8 dc2.large nodes for its data warehouse. The data engineering team loads data daily using COPY commands from S3. Recently, the load times have increased significantly. The cluster's CloudWatch metric 'CPUUtilization' is high during the load. The administrator runs the STL_LOAD_ERRORS table and finds no errors. The SVL_S3LOG shows that the COPY command is scanning many small files. The data in S3 is stored as 10,000 small CSV files (each ~100 KB). Which action will MOST improve the COPY performance?
⚠ Common exam trap
Candidates may confuse the benefit of file format (JSON vs. CSV) with the performance impact of file size. The real issue is the large number of small files, not the format.
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
✓
Consolidate the small files into fewer, larger files (e.g., 100 files of 10 MB each)
Consolidating many small files into fewer, larger files reduces the overhead of opening and processing numerous small files during the COPY command. Redshift performs better with larger files (e.g., 64 MB to 1 GB) because it can parallelize the load across slices more efficiently. Option A is incorrect because the MANIFEST option helps with specifying files but does not address the root cause of many small files. Option B is incorrect because JSON format typically increases parsing overhead compared to CSV. Option D is incorrect because changing the distribution style to ALL does not improve COPY performance; it affects query performance after data is loaded.
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 MANIFEST option to specify the files explicitly
Why it's wrong here
Manifest helps specify files but does not reduce the overhead of many small files.
- ✗
Use the JSON format instead of CSV to reduce parsing overhead
Why it's wrong here
JSON parsing is generally more overhead than CSV.
- ✓
Consolidate the small files into fewer, larger files (e.g., 100 files of 10 MB each)
Why this is correct
Larger files reduce the overhead of file opening and improve parallelism.
- ✗
Change the table's distribution style to ALL to avoid data redistribution
Why it's wrong here
Distribution style does not affect COPY performance.
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 DBS-C01 question is part of Courseiva's 1,663-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 by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DBS-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 DBS-C01 exam.