DEA-C01 Data Store Management Practice Question
Which TWO of the following are best practices for Amazon Redshift table design? (Choose TWO.)
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
✓
Choose sort keys based on query patterns
Options A and D are correct. Choosing sort keys based on query patterns optimizes data organization for faster retrieval, and specifying distribution keys minimizes data movement across nodes. Option B is wrong because large data loads should use the COPY command, not INSERT statements. Option C is wrong because compression encoding reduces storage and I/O, improving performance despite slightly higher CPU overhead. Option E is wrong because setting distribution style to ALL for all tables can cause excessive data duplication and storage costs; distribution keys should be selected based on table size and join patterns.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Choose sort keys based on query patterns
Why this is correct
Sort keys improve query performance for range-filtered queries.
- ✗
Use INSERT statements for large data loads
Why it's wrong here
Using individual `INSERT` statements for large data loads bypasses Amazon Redshift’s massively parallel processing (MPP) architecture, which is designed to bulk-load data via `COPY` from Amazon S3. This approach forces row-by-row ingestion, causing excessive commit overhead and locking that degrades cluster performance. It is tempting because `INSERT` works well for transactional, low-volume row inserts in OLTP databases, where immediate single-row visibility is required.
- ✗
Avoid compression encoding to reduce CPU overhead
Why it's wrong here
Compression reduces storage and I/O, and should be used.
- ✓
Specify distribution keys to minimize data movement
Why this is correct
Distribution keys reduce data shuffling during joins.
- ✗
Set distribution style to ALL for all tables
Why it's wrong here
ALL distributes full copies to all nodes, which can waste space.
Go deeper
Related to this question
About these practice questions
This DEA-C01 question is part of Courseiva's 1,711-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 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.