DEA-C01 Data Store Management Practice Question
A data engineer manages an Amazon Redshift cluster that stores sales data in a table with a sort key on the sale_date column. The table is growing rapidly, and queries that filter by sale_date are becoming slower. The engineer notices that the table has a high percentage of unsorted rows. What should the engineer do to improve query performance with the least effort?
⚠ Common exam trap
Watch out — candidates often confuse VACUUM SORT ONLY with VACUUM FULL; the former sorts without reclaiming space, while the latter does both and is overkill for just sorting.
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
✓
Run the VACUUM SORT ONLY command on the table.
VACUUM SORT ONLY sorts the rows in a table according to the defined sort key, which directly improves the performance of queries that filter on that key. It is the least intrusive vacuum operation that addresses unsorted rows. ANALYZE only updates statistics, while VACUUM FULL and VACUUM REINDEX are more resource-intensive and not required for the described 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.
- ✗
Run the VACUUM REINDEX command on the table.
Why it's wrong here
VACUUM REINDEX is used to reindex interleaved sort keys, which are not mentioned in the scenario. It performs a full vacuum and rebuilds the index, which is unnecessary for a standard sort key. This command would not be the least effort and does not target the specific problem of unsorted rows in a table with a compound sort key.
- ✗
Run the ANALYZE command on the table.
Why it's wrong here
ANALYZE updates table statistics used by the query planner to generate optimal execution plans. While it can improve query performance, it does not sort the rows or reduce the percentage of unsorted rows. The primary issue here is unsorted data, so ANALYZE alone will not resolve the slow range scans on sale_date.
- ✓
Run the VACUUM SORT ONLY command on the table.
Why this is correct
VACUUM SORT ONLY sorts the rows in the table according to the sort key without reclaiming space. This directly addresses the high percentage of unsorted rows and improves the efficiency of range-restricted scans on sale_date. It is a straightforward maintenance operation that requires minimal effort and targets the specific issue of unsorted data.
- ✗
Run the VACUUM FULL command on the table.
Why it's wrong here
VACUUM FULL sorts the rows and reclaims disk space by rewriting the entire table. While it would sort the data, it is more resource-intensive and time-consuming than necessary. The scenario asks for the least effort to improve performance, and VACUUM SORT ONLY is sufficient to address the unsorted rows without the overhead of reclaiming space.
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.