DEA-C01 Data Operations and Support Practice Question
A data engineer is designing an Amazon Redshift data warehouse for a high-traffic analytics workload. The engineer needs to ensure fast query performance and minimize data movement. Which THREE design decisions should be made? (Choose THREE.)
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 DISTSTYLE KEY for tables that are frequently joined.
Option A is correct because choosing DISTSTYLE KEY on the columns used in frequent joins colocates matching rows on the same slice, so join processing avoids broadcasting or redistributing data across nodes. Option D is correct because applying appropriate compression encodings (for example AZ64, ZSTD, or LZO) reduces the amount of data read from disk and moved across the network, directly improving query performance. Option E is correct because defining SORT KEYs on columns used in WHERE clauses enables zone-map block skipping, so the query reads only the relevant blocks instead of scanning the whole table. Option B is not appropriate because the default distribution style (AUTO) may not optimally colocate join keys, and relying on it for all tables can cause unnecessary data movement. Option C is wrong because DISTSTYLE ALL replicates the entire table to every node, which is suitable only for small dimension tables, not large fact tables, since it would multiply storage and load/redistribution overhead.
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 DISTSTYLE KEY for tables that are frequently joined.
Why this is correct
DISTSTYLE KEY distributes rows according to a chosen column's values, so matching join keys co-locate on the same slice. For frequently joined tables, this eliminates cross-node data movement during joins, satisfying the requirement to minimise data movement and speed queries.
- ✗
Use the default distribution style for all tables.
Why it's wrong here
The default distribution style is AUTO, which assigns ALL to small tables and EVEN to larger ones without regard to join columns, so joins between large tables still broadcast or redistribute rows. Explicit KEY distribution on join columns is needed to minimise movement.
- ✗
Use DISTSTYLE ALL for all large fact tables.
Why it's wrong here
DISTSTYLE ALL replicates the entire table to every node, consuming substantial storage and slowing loads on large fact tables. It suits small, frequently joined dimension tables; fact tables need KEY or EVEN distribution to co-locate joins and balance slices.
- ✓
Apply appropriate compression encodings to columns.
Why this is correct
Columnar compression reduces the bytes read from disk, so Redshift scans less data per query and I/O bandwidth is preserved. Choosing encodings that match each column's data type and cardinality also shrinks stored blocks, directly supporting the fast-query, low-data-movement requirement.
- ✓
Define SORT KEYs on columns used in WHERE clauses.
Why this is correct
SORT KEYs physically order rows on disk, so zone maps skip whole blocks that cannot match a WHERE predicate. This prunes scanned data and reduces the rows shuffled between compute nodes, satisfying the requirement to minimise data movement and speed up filtered queries.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DEA-C01 question from scratch — 1,321 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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.