DEA-C01 Data Store Management Practice Question
A data engineer is troubleshooting a slow Amazon Redshift query that joins several large tables. The query plan shows a large number of broadcasts. Which design change would most likely reduce the broadcast operations?
⚠ Common exam trap
Many candidates confuse SORT KEY (which optimizes data skipping and range scans) with DISTKEY (which controls data distribution for joins), leading them to pick Option A, even though broadcast reduction is purely a distribution concern.
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 DISTKEY on all tables to match the join column.
Setting the DISTKEY on all tables to the join column ensures that rows with the same join key value are co-located on the same compute node. This allows Redshift to perform a collocated join, eliminating the need to broadcast entire tables across the network, which is the primary cause of the slow query.
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 SORT KEY on all tables to match the join column.
Why it's wrong here
SORT KEY only affects ordering, not distribution, so it does not reduce broadcasts.
- ✗
Change the DISTSTYLE to EVEN on all tables.
Why it's wrong here
EVEN distributes rows randomly across slices, which can increase broadcasts for joins.
- ✓
Change the DISTKEY on all tables to match the join column.
Why this is correct
Matching DISTKEY on join columns ensures data is co-located, avoiding broadcasts.
- ✗
Change the DISTSTYLE to ALL on all large tables.
Why it's wrong here
DISTSTYLE ALL replicates the entire table to every node, which is inefficient for large tables and does not reduce broadcasts.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DEA-C01 question from scratch — 1,711 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on DEA-C01
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. 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?
medium- A.Change the distribution style of both tables to ALL
- ✓ B.Change the distribution style of both tables to KEY on the join column
- C.Change the distribution style of both tables to EVEN
- D.Add a sort key on the join column
Why B: 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.
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.