DEA-C01 Data Store Management Practice Question
A data engineer is migrating a large Oracle data warehouse to Amazon Redshift. The engineer needs to ensure optimal performance. Which TWO practices should the engineer follow?
⚠ Common exam trap
Test-takers frequently confuse Redshift's columnar storage with row-based storage and assume a normalized star schema is optimal, when in fact Redshift is designed for denormalized, columnar tables with explicit sort and distribution keys.
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 sort keys based on common query patterns.
Amazon Redshift uses sort keys to physically order data on disk, which allows the query optimizer to skip large blocks of data during scans via zone maps. Choosing sort keys based on common query patterns (e.g., range filters or frequent GROUP BY columns) dramatically reduces I/O and improves query performance, especially for large 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.
- ✓
Choose appropriate sort keys based on common query patterns.
Why this is correct
Sort keys reduce the amount of data scanned.
- ✗
Design the schema as a normalized star schema with row-based storage.
Why it's wrong here
Redshift is columnar; normalized schemas are not optimal.
- ✗
Manually define compression encodings for each column.
Why it's wrong here
Redshift automatically applies compression, manual settings are not needed.
- ✗
Stage data in Amazon S3 before loading into Redshift.
Why it's wrong here
Data is typically loaded directly from S3, but it's not a practice for performance; it's a method.
- ✓
Use DISTKEY to distribute data evenly across nodes.
Why this is correct
Even distribution prevents data skew and improves parallelism.
Go deeper
Related to this question
About these practice questions
One of 1,711 original DEA-C01 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 →
Same concept, more angles
2 more ways this is tested on DEA-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 data engineer is designing a data warehouse using Amazon Redshift. The workload includes complex queries that join large tables. The engineer notices that queries are slow due to disk-based operations. Which configuration change would MOST improve query performance?
hard- A.Define appropriate sort keys on the large tables.
- B.Increase the number of slices per node by choosing a different node type.
- ✓ C.Choose an appropriate distribution style (e.g., KEY or ALL) for the tables.
- D.Enable compression on all columns.
Why C: Choosing an appropriate distribution style (KEY or ALL) minimizes data movement between nodes during query execution. In Amazon Redshift, disk-based operations often result from large volumes of data being redistributed across the network for joins. By colocating related data on the same slices via KEY distribution or replicating small tables with ALL distribution, you reduce the need for broadcast or redistribution, which directly alleviates disk-based spills and improves query performance.
Variation 2. A data engineer is responsible for a data warehouse on Amazon Redshift that stores 5 TB of data. The engineer needs to load 50 GB of new data daily from Amazon S3 into Redshift. The current load process uses the COPY command and takes 2 hours, which is within the maintenance window. However, the engineer wants to optimize the load time and reduce the impact on concurrent queries. The engineer notices that the tables are not distributed evenly across the slices. The cluster has 4 nodes of dc2.large. Which approach will best improve load performance?
medium- A.Increase the cluster size to 8 nodes.
- ✓ B.Change the distribution style of the tables to EVEN.
- C.Use GZIP compression on the S3 files.
- D.Add sort keys to the tables based on the load timestamp.
Why B: The COPY command distributes data across slices based on the table's distribution style. With dc2.large nodes, each node has 2 slices, so a 4-node cluster has 8 slices. If tables are not distributed evenly, some slices handle more data, causing bottlenecks. Changing the distribution style to EVEN forces rows to be spread uniformly across all slices, maximizing parallelism during the COPY load and reducing load time.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
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.