SAP-C02 Practice Question: Accelerate Workload Migration and Modernization
A company is migrating a data warehouse from on-premises to Amazon Redshift. The current workload runs complex queries that join large tables. The company wants to optimize query performance after migration. Which design should the company implement?
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 distribution style KEY on the join columns of large tables
Distribution style KEY on join columns ensures data is co-located, minimizing data movement. Option A (AUTO) may not use the optimal distribution strategy. Option B (EVEN) distributes rows evenly, which can cause significant data movement across nodes. Option C (ALL) is not suitable for large tables because it duplicates the entire table on every node, leading to high storage and performance issues.
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 distribution style AUTO and let Redshift decide
Why it's wrong here
AUTO leaves distribution to Redshift's heuristics and may not co-locate the large joined tables, causing redistribution over the network. It is tempting because AUTO adapts as workload patterns change, which is the right choice when query patterns are unknown or genuinely mixed.
- ✗
Use distribution style EVEN on all tables
Why it's wrong here
EVEN spreads rows round-robin, so matching join keys land on different slices and every large join triggers data redistribution across nodes. It is tempting because EVEN balances storage and parallel scans, which suits tables never joined on a common column.
- ✗
Use distribution style ALL on all large tables
Why it's wrong here
Distribution style ALL copies every row of a table to every compute node. For large tables this multiplies storage and slows loads and vacuum operations, while joins still require redistribution. ALL suits small dimension tables; large fact tables need KEY or EVEN distribution to avoid excessive data movement.
- ✓
Use distribution style KEY on the join columns of large tables
Why this is correct
KEY distribution co-locates matching join-column values on the same slice, so large-table joins execute locally without network redistribution. This directly targets the stem's complex joins across large tables, the dominant cost in Redshift query performance.
Go deeper
Related to this question
About these practice questions
One of 984 original SAP-C02 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 by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This SAP-C02 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 SAP-C02 exam.