Exam AWS Certified Data Engineer - Associate DEA-C01 topic 1 question 139 discussion

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';

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.

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

E is not optimal bc of skipping of the second column.

The execution plan of these 2 queries should be the same.

sort key works best with the first column in the sort key and continuing in sequential order

The order is the key to speed up queries

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.

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.

Based on the order of the compound sort key columns.