DEA-C01 Data Store Management Practice Question
A financial services company uses Amazon Redshift for its data warehouse. The cluster has two nodes and is used for complex analytical queries. The company recently migrated from a single-node cluster to a two-node cluster to improve performance. After the migration, the data engineer notices that query performance has not improved as expected. Some queries are even slower than before. The engineer checks the workload management (WLM) queue configuration and sees that there is only one queue with a concurrency level of 5. The queries are mostly large scans and aggregations. The cluster's CPU utilization is low, but disk I/O is high. What should the data engineer do to improve query 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
✓
Add more nodes or upgrade to a larger node type to increase memory and reduce disk spills.
The high disk I/O and low CPU utilization indicate that queries are spilling to disk because the cluster lacks sufficient memory for large scans and aggregations. Adding more nodes or upgrading to larger node types (e.g., from dc2.large to dc2.8xlarge) increases the total memory, reducing disk spills and improving performance. Option A is incorrect because applying compression reduces the amount of data scanned, but the bottleneck here is memory, not scan volume. Option B is incorrect because increasing the concurrency level would allow more queries to run simultaneously, increasing contention and likely worsening performance. Option D is incorrect because changing distribution style to DISTSTYLE ALL can help with data redistribution but does not directly address memory spilling; the primary issue is insufficient memory.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Apply compression to the tables to reduce the amount of data scanned.
Why it's wrong here
Redshift already uses columnar compression; manual compression is not needed.
- ✗
Increase the concurrency level in the WLM queue to allow more queries to run simultaneously.
Why it's wrong here
Higher concurrency can increase disk I/O and memory pressure, worsening performance.
- ✓
Add more nodes or upgrade to a larger node type to increase memory and reduce disk spills.
Why this is correct
More memory reduces disk I/O by allowing intermediate results to stay in memory.
- ✗
Change the distribution style of large tables to DISTSTYLE ALL to avoid data redistribution.
Why it's wrong here
While this can help, the primary issue is memory spilling.
Go deeper
Related to this question
About these practice questions
This DEA-C01 question is part of Courseiva's 1,711-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 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.