DEA-C01 Data Store Management Practice Question
A data engineer is designing a data warehouse on Amazon Redshift. The workload includes many ad-hoc queries that filter on a high-cardinality column, such as customer_id, and join large dimension tables. The engineer wants to improve query performance by choosing an appropriate distribution style and sort key. Which combination should the engineer use?
⚠ Common exam trap
The trap here is assuming that sorting by date or using AUTO distribution will optimize all queries, when the key is to align distribution and sort keys with the most common join and filter columns.
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
✓
Use KEY distribution on customer_id and set the sort key to customer_id.
For a workload that frequently filters and joins on a high-cardinality column like customer_id, distributing the fact table by that key colocates matching rows and minimizes network traffic during joins. Using the same column as the sort key further speeds up range scans and merge joins. Together, they reduce data movement and I/O, improving ad-hoc query performance.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use KEY distribution on customer_id and set the sort key to customer_id.
Why this is correct
KEY distribution on customer_id colocates matching rows on the same slice, minimizing data movement during joins on that column. Setting the sort key to customer_id also enables efficient range-restricted scans and merge joins on that column. This directly addresses the high-cardinality filter and join performance, making it the best choice for this workload.
- ✗
Use AUTO distribution and set the sort key to the date column with a compound sort key.
Why it's wrong here
AUTO distribution lets Redshift choose, but it may not select customer_id, potentially causing data movement during joins. A compound sort key on date does not prioritize customer_id, so filters on customer_id cannot take full advantage of sort order. This may improve date-range queries but not the primary ad-hoc filters and joins on customer_id.
- ✗
Use EVEN distribution and set the sort key to the date column.
Why it's wrong here
EVEN distribution spreads rows evenly across slices, which can cause data movement during joins on customer_id because matching rows may reside on different slices. Sorting by date helps range scans on date, but the primary filter is on customer_id. This combination does not optimize the join or the high-cardinality filter, leading to slower ad-hoc queries.
- ✗
Use ALL distribution on the fact table and set the sort key to the join key of the largest dimension.
Why it's wrong here
ALL distribution copies the entire table to every slice, which is suitable for small dimension tables, not large fact tables. Applying it to a fact table increases storage and load time significantly. Sorting by a dimension join key may help some joins, but the distribution style is inappropriate for a large fact table and does not address the high-cardinality filter on customer_id.
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 →
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.