DEA-C01 Data Store Management Practice Question
A data engineer manages an Amazon Redshift provisioned cluster that serves a nightly ELT workload. The cluster's largest fact table is loaded with new rows each night, and queries frequently filter on a date column and join to a customer dimension. The engineer wants to improve query performance and reduce the time spent vacuuming. Which TWO actions should the engineer take? (Choose two.)
⚠ Common exam trap
The trap here is believing that adding cluster capacity or compression alone fixes slow filtered joins, when the decisive levers are the sort key and distribution key choices.
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
✓
Set the customer join column as the distribution key so matching rows of the fact and dimension tables co-locate on the same slice.
Performance and vacuum efficiency in Redshift are driven primarily by table design. Sorting on the frequently filtered date column enables zone-map pruning and makes VACUUM's work easier, while distributing the fact table and customer dimension on the join column enables collocated joins that avoid network-heavy shuffles. Together they reduce scanned blocks and join cost without adding hardware.
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 automatic compression on all columns and disable the sort key to reduce storage footprint.
Why it's wrong here
Column compression reduces storage and I/O, but removing the sort key eliminates zone-map pruning on the date filter and makes VACUUM less effective because rows are no longer ordered. This harms the query and maintenance goals rather than helping them.
- ✗
Convert the fact table to a view over the raw staging table to avoid storing duplicate data.
Why it's wrong here
A view stores no data and executes its definition at query time, so filtering and joining still scan the underlying staging table with no sort or distribution benefits. This removes the ability to apply sort keys and dist keys to the fact table, degrading performance instead of improving it.
- ✓
Set the customer join column as the distribution key so matching rows of the fact and dimension tables co-locate on the same slice.
Why this is correct
Distributing both the fact table and the customer dimension on the join column places matching rows on the same slice, enabling collocated joins that avoid broadcasting or shuffling data across the network. This lowers join cost and improves overall query performance for the frequent fact-to-dimension joins in this workload.
- ✗
Increase the cluster's number of nodes without changing the table design or sort keys.
Why it's wrong here
Adding nodes increases compute and storage capacity, but queries that scan unsorted data and shuffle joins still do unnecessary work. Scaling out does not address the missing sort key or distribution strategy, and it raises cost without fixing the underlying table design problems.
- ✓
Define the date column as the sort key so range-filtered queries skip irrelevant blocks during scans.
Why this is correct
A sort key physically orders rows on disk by the chosen column, so zone maps can eliminate blocks outside the queried date range. This reduces I/O for the nightly queries that filter on date and also lets VACUUM operate more efficiently on already-ordered data, directly addressing both stated goals.
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.