Reshift table cardinality and sort key

[Collie]

hello,

I recently did a practice test on the AWS site and came across the question below which I’d like to share for feedback.

I was originally looking at answer B but was then drawn to answer C because of the cardinality highest->lowest. However I don’t find anything on “reverse compound sort key”, I am curious which one it should be and why?

Question:

A product business team is creating a sales dashboard for the company’s newest product line using data in Amazon Redshift. During a test run of the dashboard, a data analyst notices sluggish query response times. Upon closer inspection the following is discovered about the data model and query history:

1. The orders table is often searched by date

2. The product_details table is often joined with the orders table

Which optimizations should the data analyst verify in the data model?

A. Verify the orders and product_details tables are distributed using the KEY diststyle using different key columns that minimize data and processing skew. Also verify that the orders table has an interleaved sort key on product_id

B. Verify the orders and product_details tables are distributed using the
KEY diststyle using identical key columns that minimize data and
processing skew. Also verify that the orders table has a compound sort key that includes the order_date column at a minimum and is ordered lowest cardinality to highest cardinality

C. Verify the orders and product_details tables are distributed using the
KEY diststyle using identical key columns that minimize data and
processing skew. Also verify that the orders table has a reverse compound sort
key that includes, at a minimum, the order_date column and is ordered
highest-cardinality to lowest-cardinality

D. Verify the orders table is distributed using the
EVEN diststyle with minimal data and
processing skew. Also verify that the product_details table has a compound sort key based on the first date column in the table

Thanks!

Colin

• This discussion was modified 2 years, 5 months ago by  Collie.