DEA-C01 Data Store Management Practice Question
A data engineer manages an Amazon Redshift cluster that experiences performance degradation during complex analytical queries. The engineer notices that some queries spill to disk. The engineer wants to improve query performance by optimizing the distribution style and sort keys. Which action should the engineer take first?
⚠ Common exam trap
The trap here is jumping to schema changes or scaling without first diagnosing the actual query execution bottlenecks.
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
✓
Analyze the query execution plans to identify tables with high redistribution or broadcast steps.
Analyzing query execution plans is the essential first step to identify performance bottlenecks such as data redistribution or broadcast operations. These indicate suboptimal distribution styles. Once identified, the engineer can make informed changes to distribution keys and sort keys. The other options are either premature or not targeted at the root cause.
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 all tables to use EVEN distribution style to balance data across nodes.
Why it's wrong here
EVEN distribution distributes data evenly but does not consider join keys, which can lead to increased data movement during joins. It is not a one-size-fits-all solution and may worsen performance for queries that join on specific columns. Without analyzing query patterns, this change could be counterproductive. The engineer should first understand the workload before altering distribution styles.
- ✓
Analyze the query execution plans to identify tables with high redistribution or broadcast steps.
Why this is correct
Query execution plans reveal how data is distributed and joined. High redistribution or broadcast steps indicate suboptimal distribution styles, causing data movement across nodes. By analyzing these plans, the engineer can identify which tables need distribution key changes. This is the first step before making changes, as it provides data-driven insights. It ensures that modifications target the actual bottlenecks.
- ✗
Set the sort key to the column most frequently used in WHERE clauses for all tables.
Why it's wrong here
Sort keys improve performance for range-restricted scans, but they are not the primary factor for join performance. Setting a sort key on every table without analysis may not address the disk spill issue, which is often related to distribution and memory. Sort keys should be chosen based on query patterns and data access. This action alone may not resolve the performance degradation.
- ✗
Increase the number of nodes in the cluster to add more memory and storage.
Why it's wrong here
Adding nodes increases capacity but does not address inefficient data distribution or sort keys. It may temporarily alleviate spilling, but it is costly and does not optimize the underlying query performance. The engineer should first optimize the schema design. Scaling should be considered after tuning, not as the first step.
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 and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Amazon Web Services exam blueprint
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.