DEA-C01 Data Store Management Practice Question
A data engineer is using Amazon Redshift and needs to improve query performance for a workload that involves frequent joins between a large fact table and a small dimension table. The dimension table is updated daily with new records. The engineer wants to minimize data movement during joins. Which Redshift distribution style should the engineer use for the dimension table?
⚠ Common exam trap
The trap here is assuming that KEY distribution on the join column always eliminates data movement, but it does not when the fact table is distributed differently.
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
✓
ALL
ALL distribution replicates the entire dimension table to every node, eliminating the need to redistribute data during joins with the fact table. This minimizes network traffic and speeds up join operations. It is best suited for small dimension tables that are frequently joined with larger tables.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
AUTO
Why it's wrong here
AUTO distribution lets Redshift automatically choose the distribution style based on table size and usage. While it may eventually select ALL for a small dimension table, it is not a deterministic choice and may not guarantee minimal data movement. The engineer needs explicit control for this scenario.
- ✗
EVEN
Why it's wrong here
EVEN distribution spreads rows evenly across all slices. For a small dimension table, this causes data to be redistributed during joins with the fact table, leading to increased network traffic and slower query performance. It does not minimize data movement for joins with a large fact table.
- ✗
KEY
Why it's wrong here
KEY distribution distributes rows based on the values of a specified column. If the join key is used, it can colocate matching rows, but for a small dimension table, this may still require redistribution if the fact table is distributed on a different key. It is not optimal for minimizing movement when joining with a large fact table.
- ✓
ALL
Why this is correct
ALL distribution replicates the entire dimension table to every compute node. This ensures that joins with the fact table can be performed locally on each node without data movement, significantly improving join performance. It is ideal for small, slowly changing dimension tables.
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.