DEA-C01 Data Store Management Practice Question
Exhibit
CREATE TABLE sales (
id INT NOT NULL,
product_id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2),
region VARCHAR(20)
) DISTKEY(product_id) SORTKEY(sale_date);
-- Query:
SELECT region, SUM(amount)
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY region;Refer to the exhibit. A data engineer is analyzing a query performance issue on an Amazon Redshift table. The table 'sales' has 100 million rows. The query is performing a full table scan. Which optimization should the engineer apply to improve query performance?
⚠ Common exam trap
It's easy for candidates to confuse distribution keys (which control data placement across nodes) with sort keys (which control row order within a node), leading them to choose DISTKEY or DISTSTYLE changes when the real issue is block pruning from a full table scan.
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 a compound sort key on (sale_date, region).
The query is performing a full table scan, which indicates that Redshift cannot efficiently prune blocks. A compound sort key on (sale_date, region) orders rows by sale_date first, then by region, allowing Redshift to skip large portions of data when filtering on the leading column (sale_date). This minimizes the number of blocks scanned and dramatically improves query performance for range-based or equality filters on the leading column.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Change DISTKEY to region.
Why it's wrong here
Distribution key affects data distribution across nodes, not scan efficiency for this query.
- ✗
Use an interleaved sort key on (sale_date, region).
Why it's wrong here
Interleaved sort keys are for multi-dimensional queries; range queries may not benefit as much.
- ✓
Use a compound sort key on (sale_date, region).
Why this is correct
Compound sort key on sale_date first enables efficient range restriction, then region for aggregation.
- ✗
Change DISTSTYLE to ALL.
Why it's wrong here
ALL distribution replicates data to all nodes, increasing storage and not improving scan.
Go deeper
Related to this question
About these practice questions
This DEA-C01 question is part of Courseiva's 1,711-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 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.