DEA-C01 Data Store Management Practice Question
A data engineer is building an Amazon Redshift data warehouse. The cluster will ingest data from Amazon S3 using the COPY command. The engineer needs to ensure that the data is loaded in a way that maximizes query performance for future complex analytical queries. The data is currently stored as uncompressed CSV files in S3. Which action should the engineer take to optimize the load and subsequent query performance?
⚠ Common exam trap
The trap here is assuming that loading compressed files or running VACUUM will automatically optimize the table's physical design for query performance.
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 the COMPUPDATE ON option to automatically apply compression, and define an appropriate distribution key and sort key on the target table.
The correct approach is to use COPY with COMPUPDATE ON to automatically apply compression, and to define an appropriate distribution key and sort key. This optimizes storage, reduces I/O, and improves query performance by ensuring even data distribution and efficient data access patterns. Other options either do not address compression, distribution, or sort keys adequately, or rely on commands that do not achieve the desired optimization.
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 the GZIP option to load compressed files, which automatically distributes the data evenly across all nodes.
Why it's wrong here
The GZIP option in COPY is used when the source files in S3 are already compressed with gzip. It does not distribute data across nodes; distribution is determined by the table's distribution style. Simply loading compressed files does not optimize distribution or sort keys, which are critical for query performance. This option misinterprets the purpose of the GZIP parameter.
- ✗
Load the data into a table with no compression and then run a VACUUM command to reorganize and compress the data.
Why it's wrong here
VACUUM can reorganize data and reclaim space, but it does not apply compression encodings. Redshift's VACUUM is primarily for sorting and reclaiming space after deletions or updates. It does not automatically choose or apply compression. Relying on VACUUM to compress data would be ineffective and would not improve query performance as needed for analytical workloads.
- ✗
Load the data into a single large staging table and then use CREATE TABLE AS (CTAS) to redistribute it into multiple tables.
Why it's wrong here
Loading into a single staging table and then using CTAS can help with some transformations, but it does not address the fundamental need for compression and distribution key selection during the initial load. This approach adds unnecessary complexity and does not directly optimize for the analytical queries described. It also may not leverage the full benefits of Redshift's columnar storage and zone maps.
- ✓
Use the COPY command with the COMPUPDATE ON option to automatically apply compression, and define an appropriate distribution key and sort key on the target table.
Why this is correct
Using COPY with COMPUPDATE ON allows Redshift to analyze the data and apply optimal compression encodings automatically, which reduces storage and improves I/O performance. Defining a distribution key ensures even data distribution across nodes, and a sort key improves query performance by enabling efficient range scans and minimizing data movement during joins. This combination directly addresses the need for optimized load and query 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
One of 1,321 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 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.