DBS-C01 Workload-Specific Database Design Practice Question
A company uses Amazon Redshift for data warehousing. They run a query that joins a large fact table (10 billion rows) with a small dimension table (1 million rows). The query is slow. The distribution style of the fact table is AUTO, and the dimension table has DISTSTYLE ALL. The join key is user_id. What is the MOST likely reason for the poor performance?
⚠ Common exam trap
A common mix-up: candidates assume DISTSTYLE ALL is always inefficient for joins, but in this scenario it is actually beneficial, while the real culprit is the fact table's distribution key not matching the join key due to AUTO assignment.
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
✓
The fact table's distribution key is not user_id, causing redistribution
When the fact table uses DISTSTYLE AUTO, Redshift may choose a distribution key that is not user_id. When the fact table is distributed on a different key, joining on user_id requires Redshift to redistribute the fact table rows across nodes to match the dimension table's distribution, causing significant network traffic and slower performance. The dimension table with DISTSTYLE ALL is already replicated to all nodes, so the bottleneck is the fact table's distribution mismatch.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The dimension table does not have a sort key on user_id
Why it's wrong here
Sort key helps with range queries but not necessarily with hash joins.
- ✓
The fact table's distribution key is not user_id, causing redistribution
Why this is correct
AUTO may distribute by another key, leading to large data movement during join.
- ✗
The dimension table uses DISTSTYLE ALL, which is inefficient for joins
Why it's wrong here
DISTSTYLE ALL is efficient for small dimension tables.
- ✗
The fact table should have column compression disabled for the join key
Why it's wrong here
Compression is always beneficial and does not affect join performance negatively.
Go deeper
Related to this question
About these practice questions
This DBS-C01 question is part of Courseiva's 1,663-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 by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DBS-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 DBS-C01 exam.