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 every row to every node, so joins need no redistribution, but it multiplies storage and load cost across the cluster for two large tables. It is tempting because ALL eliminates broadcast and distribution steps entirely, and would be correct for a small dimension table joined repeatedly against large facts.
- ✓
Change the distribution style of both tables to KEY on the join column
Why this is correct
Distributing both tables on the join column co-locates matching rows on the same slice, so Redshift performs local joins instead of redistributing or broadcasting data across nodes. This directly eliminates the distribution and broadcast operations the plan revealed, satisfying the stem's requirement to reduce network overhead during large-table joins.
- ✗
Change the distribution style of both tables to EVEN
Why it's wrong here
EVEN distributes rows round-robin, so join keys land on different slices and still require redistribution or broadcast during the join. It is tempting because EVEN is the default and balances storage evenly, and would be correct for tables with no join or grouping key, or where even scan parallelism matters most.
- ✗
Add a sort key on the join column
Why it's wrong here
A sort key orders rows within each slice and speeds range scans and merge joins, but it does not co-locate matching join keys, so distribution and broadcast operations persist. It is tempting because sort keys genuinely reduce I/O, and would be correct when filtering or range predicates dominate rather than join redistribution.
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.