DEA-C01 Data Store Management Practice Question
A data engineer notices that an Amazon Redshift cluster’s storage usage is increasing rapidly due to many UPDATE and DELETE operations. The engineer needs to reclaim storage space and improve query performance. Which action should be taken?
⚠ Common exam trap
Test-takers frequently confuse ANALYZE with VACUUM, thinking updating statistics will also reclaim storage, when in fact ANALYZE only refreshes metadata for the query optimizer and has no effect on physical storage.
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 VACUUM command
The VACUUM command in Amazon Redshift reclaims disk space occupied by deleted or updated rows and re-sorts the data according to the table's sort keys. This directly addresses the storage increase from UPDATE/DELETE operations and improves query performance by restoring the physical order of rows, which reduces the number of blocks scanned.
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 VACUUM command
Why this is correct
VACUUM reclaims space from deleted rows and re-sorts unsorted regions, restoring sequential scan performance degraded by frequent UPDATE and DELETE operations. This directly addresses the stem's storage growth and query-performance constraints on the Redshift cluster.
- ✗
UNLOAD the table to S3 and reload
Why it's wrong here
Unloading and reloading rewrites the table externally, requiring S3 staging and downtime, whereas VACUUM performs the same reclamation in place. It is tempting as a full rebuild, and would be correct for migrating data or changing distribution and sort keys.
- ✗
Increase cluster node count
Why it's wrong here
Adding nodes increases compute capacity and cost but leaves deleted rows occupying existing slice storage; VACUUM reclaims that space. It is tempting when queries are slow due to insufficient compute, and would be correct if the cluster were CPU- or memory-bound rather than bloated by updates and deletes.
- ✗
Run ANALYZE command
Why it's wrong here
ANALYZE refreshes table statistics for the query planner; it does not reclaim space occupied by deleted rows, which VACUUM does. It is tempting because both are maintenance commands, and would be correct after bulk loads to improve plan quality rather than to recover storage.
Go deeper
Related to this question
About these practice questions
One of 1,321 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 →
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 notices that an Amazon Redshift cluster's storage utilization has grown unexpectedly. The cluster uses automatic compression and has a mix of fact and dimension tables. The engineer runs VACUUM and ANALYZE, but storage does not decrease. Which action is most likely to reduce storage consumption?
hard- ✓ A.Perform a DEEP COPY on the largest tables.
- B.Run VACUUM with the BOOST option.
- C.Run ANALYZE with the FULL keyword on all tables.
- D.Modify the sort key on the largest tables to a more selective column.
Why A: DEEP COPY recreates the table with a fresh, optimally sorted and compressed storage layout, reclaiming space that VACUUM alone cannot recover. In Redshift, VACUUM reorganizes and reclaims space from deleted rows but does not re-apply compression or rebuild the underlying storage blocks; DEEP COPY (e.g., using CREATE TABLE AS or the DEEP COPY command) physically rewrites the data, eliminating fragmentation and applying the current compression encoding, which can significantly reduce storage consumption when automatic compression has left suboptimal encodings or when historical updates have bloated the table.
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.