DEA-C01 Data Store Management Practice Question
A company is running a data warehouse on Amazon Redshift. The data engineering team notices that query performance has degraded over time. They suspect that data distribution is causing excessive data movement between nodes. The table is joined frequently on the customer_id column. Which column should be chosen as the distribution key to optimize join performance?
⚠ Common exam trap
A common mix-up: candidates choose EVEN distribution (D) thinking it balances data evenly, but they overlook that it causes maximum data movement for joins, while AUTO distribution (A) seems safe but does not guarantee co-location for the specific join column.
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
✓
customer_id
(customer_id) because Redshift distributes data across nodes based on the distribution key. When two tables are joined on customer_id, using it as the distribution key ensures that matching rows from both tables are co-located on the same node, eliminating the need for data redistribution (broadcast or shuffle) during the join. This minimizes network traffic and reduces query latency, directly addressing the performance degradation caused by excessive data movement.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
AUTO distribution
Why it's wrong here
AUTO distribution lets Redshift choose keys per table and cannot guarantee co-location for the customer_id join, so redistribution persists. It is tempting as a hands-off default, and would be correct for tables with no dominant join or filter column where manual key selection offers no benefit.
- ✓
customer_id
Why this is correct
Choosing customer_id as the distribution key colocates rows with identical customer_id values on the same node, so frequent joins on that column execute locally without network redistribution. This directly addresses the excessive data movement degrading query performance.
- ✗
order_date
Why it's wrong here
order_date is not the frequent join column, so distributing on it forces customer_id joins to redistribute rows across nodes, the very movement being diagnosed. It is tempting because date keys suit range-filtered fact tables, and would be correct if queries filtered or joined predominantly on order_date.
- ✗
EVEN distribution
Why it's wrong here
EVEN distribution spreads rows round-robin, so customer_id values land on arbitrary nodes and every join must redistribute both tables. It is tempting for evenly balanced storage, and would be correct for a table never joined, where uniform data placement matters more than co-location.
Go deeper
Related to this question
About these practice questions
This DEA-C01 question is part of Courseiva's 1,321-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.