DEA-C01 Data Operations and Support Practice Question
A data engineer maintains an Amazon Redshift cluster where a nightly COPY job loads data into a large fact table. After the load, analysts run queries that filter on a `sale_date` column and join to a small dimension table. Query performance degrades over time as the fact table grows. The engineer wants to improve performance for these recurring queries without changing the query text. Which combination of actions should the engineer take?
⚠ Common exam trap
The trap here is assuming that adding concurrency scaling or more slices will fix a single query's cost, when scan and join efficiency depend on sort and distribution design.
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
✓
Apply a compound sort key on `sale_date` and enable automatic vacuum and analyze, then distribute the dimension table as ALL.
The recurring workload filters on `sale_date` and joins to a small dimension. A compound sort key on the filter column enables zone-map block skipping, while an ALL distribution on the small dimension removes network redistribution during joins. Automatic vacuum and analyze keep the physical layout and statistics healthy as nightly loads accumulate, so the planner keeps producing efficient plans without query changes.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Enable concurrency scaling on the cluster and increase the number of slices per node.
Why it's wrong here
Concurrency scaling adds transient clusters to handle bursts of concurrent queries, which helps queueing but not the per-query cost of scanning a growing fact table or moving join data. The number of slices per node is determined by node type and cannot be tuned arbitrarily. Neither action addresses the sort key or distribution choices that govern scan efficiency and join data movement for these queries.
- ✗
Add an interleaved sort key on every column of the fact table and distribute the dimension table as EVEN.
Why it's wrong here
Interleaved sort keys on every column impose heavy vacuum and insert costs and can degrade performance because each column's sort benefit is diluted. Distributing the small dimension table as EVEN spreads it across nodes and forces data movement on every join. Together these choices increase maintenance overhead and join cost instead of optimizing the recurring date-filtered, dimension-joined workload.
- ✓
Apply a compound sort key on `sale_date` and enable automatic vacuum and analyze, then distribute the dimension table as ALL.
Why this is correct
A compound sort key on `sale_date` lets Redshift skip blocks outside the filtered range, reducing scanned data for date-filtered queries. Distributing the small dimension table as ALL replicates it to every node, eliminating data movement during joins. Enabling automatic vacuum and analyze keeps statistics current and reclaims space after the nightly loads, so the query planner continues choosing efficient plans as the table grows.
- ✗
Convert the fact table to a view over the raw staging table and rely on Redshift Spectrum for all reads.
Why it's wrong here
Redshift Spectrum reads from Amazon S3 rather than local cluster storage, which adds latency and cost for frequently accessed fact data. Converting the fact table to a view does not create the sort or distribution benefits that speed up joins and range filters on managed storage. This approach shifts work to an external layer and typically degrades performance for recurring analytical queries against the growing fact table.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DEA-C01 question from scratch — 1,321 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 →
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.