DEA-C01 Data Store Management Practice Question
Exhibit
Refer to the exhibit.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10,2)
) DISTSTYLE KEY DISTKEY (customer_id) SORTKEY (order_date);Refer to the exhibit. A data engineer creates an Amazon Redshift table with the above DDL. The engineer runs a query to find all orders for a specific customer within a date range. Which statement about query performance is correct?
⚠ Common exam trap
Candidates often assume the sort key is useless if the filter does not start with the sort key column, but Redshift's zone map pruning works on any column in the sort key, and the distribution key filter can still leverage co-location to reduce data movement.
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 query will benefit from both the distribution key and the sort key to minimize data scanned.
The DDL defines customer_id as the distribution key and order_date as the sort key. When the query filters on both customer_id (distribution key) and order_date (sort key), Redshift can use partition pruning via the sort key to skip blocks that don't match the date range, and the distribution key ensures that data for the same customer is co-located on the same node slice, minimizing data movement. This combination reduces the amount of data scanned and improves query performance.
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 query will be inefficient because the distribution key is not the same as the sort key.
Why it's wrong here
They serve different purposes; it's fine.
- ✗
The table should use DISTSTYLE EVEN to improve performance.
Why it's wrong here
EVEN would cause data shuffling.
- ✓
The query will benefit from both the distribution key and the sort key to minimize data scanned.
Why this is correct
Distribution reduces data movement, sort key reduces data scanned.
- ✗
The sort key will not help because the query filters on customer_id first.
Why it's wrong here
Sort key can still be used if the query also filters on order_date.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DEA-C01 question from scratch — 1,711 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 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.