DEA-C01 Data Store Management Practice Question
A data engineer is setting up an Amazon Redshift cluster for a data warehouse. The cluster will store historical sales data and support complex analytical queries. To optimize query performance and manage storage, the engineer needs to choose appropriate distribution styles and sort keys for a large fact table 'sales' and several dimension tables. Which TWO of the following design decisions are BEST practices?
⚠ Common exam trap
Watch out — candidates often confuse EVEN distribution as a universal best practice for all fact tables, overlooking that KEY distribution on the join column is superior for star schema joins, and they may also incorrectly assume ALL distribution is suitable for large fact tables due to its join performance benefits, ignoring the prohibitive storage and write costs.
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 interleaved sort keys on columns that are frequently used in filter predicates (e.g., date, region, product).
Interleaved sort keys in Amazon Redshift give equal weight to each column in the sort key, making them ideal for queries with filter predicates on multiple columns (e.g., date, region, product). This design optimizes zone maps and minimizes the amount of data scanned, significantly improving query performance for complex analytical workloads on large fact 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.
- ✓
Use interleaved sort keys on columns that are frequently used in filter predicates (e.g., date, region, product).
Why this is correct
Interleaved sort keys improve performance for queries filtering on multiple columns.
- ✓
Use EVEN distribution for the fact table 'sales' to ensure an even data distribution across all nodes.
Why this is correct
EVEN distribution is good for large fact tables when no single key is used for all joins.
- ✗
Use ALL distribution for the 'sales' fact table to replicate data to every node and avoid data movement.
Why it's wrong here
ALL distribution is for small dimension tables; replicating a large fact table wastes storage.
- ✗
Use a compound sort key with the most frequently filtered column first.
Why it's wrong here
Compound sort keys are less effective when queries filter on columns other than the first.
- ✗
Choose AUTO distribution style for all tables and let Amazon Redshift automatically assign distribution.
Why it's wrong here
AUTO may not always be optimal; manual selection can be better based on query patterns.
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
1 more way 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 engineering team is managing an Amazon Redshift cluster that is used for BI reporting. The cluster has a mix of large tables (some over 1 TB) and many smaller tables. The team notices that queries on a large fact table are slow. The fact table is distributed using KEY distribution on the customer_id column, which has high cardinality. The team wants to improve query performance. They have the option to change the distribution style and sort key. Which redesign should they implement?
hard- A.Keep the distribution style as AUTO and set the sort key to customer_id.
- B.Change the distribution style to ALL and set the sort key to customer_id.
- C.Change the distribution style to KEY on a different column with high cardinality.
- ✓ D.Change the distribution style to EVEN and set the sort key to a date column used in WHERE clauses.
Why D: Using EVEN distribution ensures data is evenly distributed across all nodes, avoiding data skew that can occur with KEY distribution on a high-cardinality column like customer_id. Setting the sort key to a date column used in WHERE clauses enables range-restricted scans, significantly reducing the amount of data scanned for common BI queries that filter by date. This combination improves query performance by maximizing parallelism and minimizing I/O.
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.