DEA-C01 Data Store Management Practice Question
A company uses Amazon Redshift for analytics. They notice that some queries are slow due to data redistribution. The data engineer wants to minimize data movement across nodes. Which table design strategy should be used? (Choose TWO.)
⚠ Common exam trap
Watch out — candidates often confuse distribution keys with sort keys, thinking that sorting alone can reduce data movement, or they assume AUTO distribution always optimizes for joins, when in fact it may default to EVEN or ALL without guaranteeing collocation for specific join patterns.
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 a distribution key that matches the join key for large tables.
Option C is correct because choosing a distribution key that matches the join key on large tables colocates matching rows on the same compute node slice, so joins between those tables can be performed locally without broadcasting or redistributing data across nodes. Option E is correct because using distribution style ALL replicates small dimension tables to every node, eliminating the need to redistribute the dimension during joins with large fact tables and thereby minimizing cross-node data movement. Option A is not ideal here because AUTO lets Redshift decide and may still choose EVEN or KEY distribution that results in redistribution for some workloads, rather than guaranteeing join-key alignment. Option B addresses sort keys, which optimize range-restricted scans and merge joins but do not control how rows are distributed across nodes, so they do not directly reduce redistribution. Option D is incorrect because EVEN distribution spreads rows round-robin regardless of join keys, which typically forces data redistribution during joins and can worsen the problem.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Set the distribution style to AUTO for all tables.
Why it's wrong here
AUTO leaves distribution to Redshift, which may pick EVEN or ALL and still trigger redistribution at query time; it does not guarantee co-location. It is tempting because AUTO suits variable or unknown workloads, but here the requirement is eliminating inter-node movement, which only explicit KEY distribution on join columns achieves.
- ✗
Define compound sort keys on frequently filtered columns.
Why it's wrong here
Compound sort keys order rows within each slice to speed range-filtered scans; they do not control which node stores which row, so redistribution persists. Distribution style or key is the mechanism that colocates joined rows across nodes.
- ✓
Choose a distribution key that matches the join key for large tables.
Why this is correct
When two large tables share the same distribution key as their join column, matching rows are already co-located on the same slice. Redshift avoids the broadcast or shuffle step during joins, eliminating the cross-node data redistribution that was slowing queries.
- ✗
Use EVEN distribution for all tables.
Why it's wrong here
EVEN spreads rows round-robin, so matching join keys land on different nodes and Redshift must redistribute at query time — the opposite of the goal. It is tempting because EVEN balances storage and suits tables never joined on a common column, but that is not this scenario.
- ✓
Use distribution style ALL for small dimension tables.
Why this is correct
Distribution style ALL replicates a small dimension table to every compute node. Each node then joins locally against its fact rows without broadcasting or redistributing the dimension, removing the network movement that caused slow query performance.
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 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.