DEA-C01 Data Operations and Support Practice Question

A company's Amazon Redshift cluster is running slowly. The data engineer suspects that table design is the cause. Which TWO design practices can improve query performance? (Choose TWO.)

⚠ Common exam trap

DEA-C01 often tests the misconception that resizing a cluster increases slices per node or that query syntax changes (GROUP BY vs. DISTINCT) are table design practices — the exam wants you to focus on sort keys and distribution keys as the core performance levers.

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

✓

Define appropriate sort keys on frequently filtered columns.

Option A is correct because defining an appropriate sort key on columns that are frequently used in WHERE filters (especially range and equality predicates) lets Redshift use zone maps to skip scanning irrelevant blocks, dramatically reducing I/O and improving query performance. Option C is correct because choosing an appropriate distribution key (for example, DISTRIBUTING BY the join column) collocates matching rows on the same slice, enabling collocated joins that avoid expensive data redistribution (broadcast or shuffle) across nodes during query execution. Option B is not a table design practice and, while DISTINCT and GROUP BY can differ in execution, it does not address the suspected table-design cause. Option D is incorrect because the number of slices per node is determined by the node type and cannot be changed by resizing; resizing changes node count or type, not slices per node. Option E is incorrect because CHAR and VARCHAR have essentially equivalent performance in Redshift, and CHAR is not a cause of the reported slowness.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✓
Define appropriate sort keys on frequently filtered columns.
Why this is correct
Sort keys physically order rows on disk by the chosen columns, so range-restricted and equality filters on frequently filtered columns skip whole blocks via zone maps. This directly addresses the slow-query symptom attributed to table design, unlike compression or vacuum, which affect storage and maintenance rather than filter pruning.
✗
Use GROUP BY instead of DISTINCT in queries.
Why it's wrong here
GROUP BY and DISTINCT are logically equivalent for deduplication, so swapping one for the other changes no execution plan and yields no performance gain. It is tempting because DISTINCT is often assumed costlier, and would be correct only where GROUP BY also computes aggregates in the same pass.
✓
Define appropriate distribution keys to collocate joins.
Why this is correct
Distribution keys control how rows are hashed across compute nodes. Choosing a key present in both joined tables collocates matching rows on the same slice, eliminating broadcast or redistribution traffic during joins. This directly targets the join-heavy slowness attributed to table design in the stem.
✗
Increase the number of slices per node by resizing the cluster.
Why it's wrong here
Resizing changes node count, not slice count per node; slice count is fixed by node type, so this cannot tune table design. It is tempting because resizing adds compute capacity, which is the right remedy when the cluster is genuinely under-provisioned rather than poorly designed.
✗
Use VARCHAR instead of CHAR for fixed-length strings.
Why it's wrong here
VARCHAR and CHAR both consume the declared length in Redshift, so switching does not reduce storage or I/O; distribution and sort keys drive performance. It is tempting because in other databases VARCHAR avoids padding overhead, making it correct for variable-length text elsewhere.

Go deeper

DEA-C01 Data Operations and Support Practice Question

Why each option matters

Option-by-option breakdown

Related to this question