DEA-C01 Data Store Management Practice Question
A data engineer is troubleshooting a slow-running query on an Amazon Redshift cluster. The query involves joining two large tables. The engineer notices that the query plan shows a large number of distribution and broadcast operations. Which design change would most likely improve query performance?
⚠ Common exam trap
Test-takers frequently confuse distribution and sort keys, thinking a sort key on the join column will reduce data movement, when in fact only distribution key alignment eliminates broadcast/redistribution operations in the query plan.
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
✓
Change the distribution style of both tables to KEY on the join column
Changing the distribution style of both tables to KEY on the join column ensures that rows with the same join key value are co-located on the same node. This eliminates the need for expensive broadcast or redistribution operations during the join, as Redshift can perform the join locally on each slice without moving data across the network.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Change the distribution style of both tables to ALL
Why it's wrong here
ALL replicates the entire table to every node, which is inefficient for large tables.
- ✓
Change the distribution style of both tables to KEY on the join column
Why this is correct
KEY distribution on the join column ensures matching rows are on the same node, reducing redistribution.
- ✗
Change the distribution style of both tables to EVEN
Why it's wrong here
EVEN distribution spreads data round-robin, which does not collocate join keys.
- ✗
Add a sort key on the join column
Why it's wrong here
Sort keys improve data pruning but do not reduce redistribution during joins.
Go deeper
Related to this question
About these practice questions
One of 1,711 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.