Amazon Redshift Performance Optimization: Distribution Styles, Sort Keys, and Compression
A company is migrating a large Oracle data warehouse to Amazon Redshift. Which THREE design considerations are important for performance optimization?
Quick Answer
The answer is that defining sort keys for commonly filtered columns is one of the three critical design considerations for performance optimization when migrating a large Oracle data warehouse to Amazon Redshift. This is correct because sort keys physically order data on disk within each node, allowing Redshift’s zone maps to skip entire blocks of data that don’t match query filters, dramatically reducing the amount of data scanned. Alongside sort keys, you must also choose an appropriate distribution style—such as KEY, EVEN, or ALL—to minimize data shuffling during joins, and apply column compression encodings to reduce storage and I/O. On the AWS Certified Database Specialty DBS-C01 exam, this question tests your understanding that Redshift is a columnar, massively parallel processing (MPP) database, so performance hinges on data locality and scan efficiency. A common trap is focusing only on distribution styles while neglecting sort keys and compression, which together form the three pillars of Redshift optimization. Memory tip: think “Sort, Distribute, Compress” as the three levers you pull to make Redshift fast.
⚠ Common exam trap
Many candidates confuse disaster recovery features (like cross-Region replication) with performance design choices, or mistakenly think row-based storage applies to Redshift because of their Oracle background, where row storage is the norm.
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
✓
Choose appropriate distribution styles (KEY, ALL, EVEN).
Distribution styles (KEY, ALL, EVEN) control how data is distributed across compute nodes in Amazon Redshift. Choosing the right distribution style minimizes data movement during joins and aggregations, which is critical for performance in a large data warehouse migration from Oracle. For example, using KEY distribution on a frequently joined column keeps related rows on the same node, reducing network traffic.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Choose appropriate distribution styles (KEY, ALL, EVEN).
Why this is correct
Affects data distribution across nodes.
- ✓
Use compression encodings for columns.
Why this is correct
Reduces storage and improves I/O.
- ✓
Define sort keys for commonly filtered columns.
Why this is correct
Improves query performance.
- ✗
Enable cross-Region replication for data locality.
Why it's wrong here
Not a performance optimization.
- ✗
Use row-based storage for fact tables.
Why it's wrong here
Redshift uses columnar storage.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DBS-C01 question from scratch — 1,663 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 →
Same concept, more angles
1 more way this is tested on DBS-C01
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. 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?
medium- A.The dimension table does not have a sort key on user_id
- ✓ B.The fact table's distribution key is not user_id, causing redistribution
- C.The dimension table uses DISTSTYLE ALL, which is inefficient for joins
- D.The fact table should have column compression disabled for the join key
Why B: 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.
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.