Amazon Redshift Compound Sort Key Query Optimization

DEA-C01 By islever Editorial Last updated

Choose a data store.

Short answer

BC Filter on the leading columns of the compound sort key, as predicate order in the WHERE clause does not affect optimization.

Question

A company stores employee data in Amazon Resdshift. A table names Employee uses columns named Region ID, Department ID, and Role ID as a compound sort key. Which queries will MOST increase the speed of query by using a compound sort key of the table? (Choose two.)
  1. Select *from Employee where Region ID=’North America’;
  2. Select *from Employee where Region ID=’North America’ and Department ID=20; Correct Answer
  3. Select *from Employee where Department ID=20 and Region ID=’North America’; Correct Answer
  4. Select *from Employee where Role ID=50;
  5. Select *from Employee where Region ID=’North America’ and Role ID=50;

Key Takeaways

Compound sort keys are tested on leading column prefixes; the order of predicates in the WHERE clause does not impact the execution plan.

A compound sort key in Amazon Redshift improves query performance when filters are applied on the leading columns of the key. This page establishes that predicate order in the WHERE clause does not affect sort key utilization, making queries filtering on the first two sort key columns the most optimized.

Common mistake: Choosing A and B, assuming the predicate order must strictly match the sort key definition, or choosing B and E, incorrectly believing skipping the second column still fully utilizes the third.

Expert Analysis

Why the Answer Is Correct

Options B and C both filter on the first two columns of the compound sort key (Region ID and Department ID). Amazon Redshift compound sort keys are most effective when queries filter on leading columns, and the logical order of predicates in the WHERE clause does not affect the optimizer's ability to leverage the sort key. Therefore, both B and C equally and most significantly increase query speed by utilizing the longest possible sort key prefix available in the options.

Why the Other Options Are Wrong

Option A only filters on the first column, providing less performance improvement than utilizing the first two columns. Option E filters on the first and third columns; because it skips the second column in the sort key, the third column cannot be used for zone map pruning, making it less effective than B or C. Option D filters only on the third column, completely skipping the leading columns, so it derives no benefit from the compound sort key.

Community Comment Notes

Several users correctly pointed out that the predicate order in the SQL query is irrelevant to performance, noting that "the filter order in the query is irrelevant" because the sort key determines storage order. One user confirmed this with testing, stating "even inverting predicate order the explain plan was the same." Others mistakenly focused strictly on the written order of the sort key, leading them to incorrectly include Option A or E.

Official Reference

https://docs.aws.amazon.com/redshift/latest/dg/t_Sorting_data.html

This link opens the official vendor documentation in a new tab.

Exam Strategy

When evaluating Redshift compound sort key effectiveness, identify queries that filter on the longest unbroken prefix of the sort key columns. Remember that the order of conditions in the WHERE clause does not matter; only the presence of the leading columns dictates sort key utilization.

Community Votes

BE
35%
AB
32%
BC
32%

35% of anonymous learners picked answer BE. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 10 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 10

  1. teo2157 👍 7
    To maximize the speed of queries using a compound sort key in Amazon Redshift, you should structure your queries to take advantage of the order of the columns in the sort key. The most efficient queries will filter or join on the columns in the same order as the sort key. Saying that, the most efficient queries would be: SELECT FROM Employee WHERE Region_ID = 'region1' AND Department_ID = 'dept1' AND Role_ID = 'role1'; SELECT FROM Employee WHERE Region_ID = 'region1' AND Department_ID = 'dept1'; SELECT * FROM Employee WHERE Region_ID = 'region1';
  2. antun3ra 👍 6
    To maximize the speed of queries by using the compound sort key (Region ID, Department ID, and Role ID) in the Employee table in Amazon Redshift, the queries should align with the order of the columns in the sort key.
  3. Shanmahi 👍 5
    Based on the order of the compound sort key columns.
  4. Parandhaman_Margan 👍 4
    Answer:AB A:This query filters by Region ID, which is the first column in the compound sort key. Queries filtering on the leading sort key column(s) will benefit from optimized performance because the data can be quickly located. B: This query filters by both Region ID (the first column) and Department ID (the second column) in the sort key. This further narrows down the search space, leading to even faster query performance.
  5. tucobbad 👍 4
    I would vote for B and C. I've tested with a compound sort key (3 columns) and even inverting predicate order the explain plan was the same.
Show 5 more comments
  1. minhhnh 👍 2
    The filter order in the query is irrelevant to the performance because the sort key itself determines the storage order. So the execution plan is the same
  2. RockyLeon 👍 2
    sort key works best with the first column in the sort key and continuing in sequential order
  3. altonh 👍 2
    The execution plan of these 2 queries should be the same.
  4. michele_scar 👍 2
    The order is the key to speed up queries
  5. HagarTheHorrible 👍 1
    E is not optimal bc of skipping of the second column.

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Submitted for moderation before publishing. Keep it helpful and respectful.

Secure Redshift Spectrum Access with VPC Endpoints

S-grade

Athena Federated Query for Multi-Source Iceberg Pipelines

S-grade

Reclaiming Storage in Amazon Redshift Materialized Views

S-grade

Least Overhead Ingestion to OpenSearch via Kinesis

S-grade

Practice All DEA-C01 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DEA-C01 Practice Test →

← Back to DEA-C01 Study Guide