DEA-C01 Data Store Management Practice Question
A data engineer is using Amazon Redshift and needs to improve query performance for a large fact table that is frequently joined with a much smaller dimension table. The engineer wants to minimize data movement during joins. Which distribution style should be used for the dimension table?
⚠ Common exam trap
The trap here is assuming that automatic distribution or distributing on the join key is always best, when for small dimension tables, replicating the entire table with DISTSTYLE ALL is often more efficient.
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
✓
DISTSTYLE ALL
For a small dimension table that is frequently joined with a large fact table, DISTSTYLE ALL replicates the dimension table to all nodes. This ensures that each node has a local copy, eliminating the need to broadcast or redistribute the dimension data during joins. As a result, data movement is minimized, and join performance is significantly improved.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
DISTSTYLE AUTO
Why it's wrong here
DISTSTYLE AUTO allows Amazon Redshift to automatically choose the distribution style based on table size and workload. While it may eventually select ALL for a small dimension table, it is not a guaranteed immediate solution and may initially choose a different style. For a known small dimension table, explicitly setting DISTSTYLE ALL is more reliable and immediate.
- ✗
DISTSTYLE KEY
Why it's wrong here
DISTSTYLE KEY distributes rows based on the values of a specified column. If the dimension table is distributed on the join key, it can co-locate matching rows with the fact table if the fact table is also distributed on the same key. However, this requires careful selection of the distribution key and may not be optimal for a small dimension table, as it could still cause data movement if the fact table is not distributed on the same key.
- ✓
DISTSTYLE ALL
Why this is correct
DISTSTYLE ALL replicates the entire dimension table to every compute node. When joining with a large fact table, each node already has a copy of the dimension table, eliminating the need to redistribute the dimension data during the join. This minimizes data movement and improves join performance, which is ideal for small dimension tables.
- ✗
DISTSTYLE EVEN
Why it's wrong here
DISTSTYLE EVEN distributes rows evenly across all slices, which is useful for tables that are not joined or when the join key is not known. However, for a small dimension table joined with a large fact table, distributing evenly would cause data movement during the join because matching rows are not co-located. This increases network traffic and slows query performance.
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.