DEA-C01 Data Operations and Support Practice Question
A data engineer is troubleshooting a slow-running Amazon Redshift query. The query involves a large fact table with a distribution style of EVEN and a sort key on date. The table has 10 slices. The engineer notices that the query is performing a broadcast join with a small dimension table. Which change would most improve performance?
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 fact table to DISTSTYLE KEY on the join column
Changing the fact table’s distribution style to KEY on the join column co-locates rows from both tables on the same nodes, eliminating the need for broadcasting and reducing network traffic. This fully optimizes the join for the large fact table. Option A is incorrect: removing the sort key can degrade range queries, and a compound sort key on the join column does not address data distribution. Option B: while setting the dimension table to DISTSTYLE ALL would avoid broadcasting by replicating the table to all nodes, it does not improve the fact table’s own data distribution, which remains EVEN and can cause skew or suboptimal joins in other queries. Option C: adding slices increases parallelism but still requires broadcasting, so it does not address the root cause of the performance issue.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Remove the sort key and use a compound sort key on the join column
Why it's wrong here
Removing the sort key degrades performance for date-range queries.
- ✗
Change the dimension table to DISTSTYLE ALL
Why it's wrong here
ALL distribution copies the table to all nodes, reducing broadcast, but the question asks for fact table change.
- ✗
Increase the number of slices by resizing the cluster
Why it's wrong here
More slices may not reduce broadcast join cost.
- ✓
Change the fact table to DISTSTYLE KEY on the join column
Why this is correct
KEY distribution colocates matching rows, reducing the need for broadcast.
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.