Amazon Redshift Foreign Keys - Sort Or Interleaved Keys

We plan to import OLTP Relational tables into AWS Redshift. The CustomerTransaction table joins to multiple lookup tables. I only included 3, but we have more. What should Sort K

Solution 1:

The general rules of thumb are:

• Set the DISTKEY based on what you commonly GROUP BY
• Set the SORTKEY based on what you commonly use in WHERE statements
• Avoid Interleaved Sort Keys (they are only optimal in rare circumstances and require frequent VACUUM)

From Choose the Best Distribution Style - Amazon Redshift:

• Distribute the fact table and one dimension table on their common columns
• Choose the largest dimension based on the size of the filtered data set
• Choose a column with high cardinality in the filtered result set
• Change some dimension tables to use ALL distribution

So, it is not easy to recommend a particular DISTKEY and SORTKEY because it depends on how you use the tales. Merely seeing the DDL is not sufficient to recommend the best way to optimize the tables.

Other references:

• Amazon Redshift Best Practices for Designing Tables
• Top 10 Performance Tuning Techniques for Amazon Redshift | AWS Big Data Blog