DEA-C01 Question 145
Single answerYou are working with Amazon Redshift to analyze a large dataset stored in a table called 'sales'. The table has billions of rows, and your team has noticed that a specific SQL query is taking an unusually long time to execute. The query includes a WHERE clause with a filter on the region column. After investigating, you discover that the region column has low cardinality (few unique values). What should you do to optimize the query's performance?
- A
Create a compound sort key with the
regioncolumn as the leading column. - B
Create an interleaved sort key with the
regioncolumn included. - C
Apply column encoding to the
regioncolumn. - D
Convert the
regioncolumn into a DISTKEY.
Show answer and explanation
Correct answer: C
Explanation
The correct answer is to apply column encoding to the region column. Amazon Redshift automatically applies column encoding when you load data, but explicitly selecting the appropriate encoding for low cardinality columns (e.g., Run Length Encoding) can significantly improve query performance. Encoding reduces storage requirements and enhances query execution by reducing the amount of data scanned. Other options, such as creating sort keys or changing the DISTKEY, do not address the specific challenge posed by a low cardinality column in this scenario.
- A. Incorrect.
Creating a compound sort key with
regionas the leading column is generally beneficial if the query filters data sequentially and theregioncolumn is frequently used in range queries. However, in this case, the low cardinality of theregioncolumn does not make this the most effective solution. - B. Incorrect.
Interleaved sort keys are useful when a query filters on multiple columns with equal importance. Here, the query filters on a single column (
region), so an interleaved sort key is not optimal. - C. Correct.
Applying column encoding to the
regioncolumn is an effective optimization technique. Low cardinality columns benefit significantly from encoding, as it reduces storage size and improves query performance by minimizing I/O. - D. Incorrect.
Assigning the
regioncolumn as a DISTKEY is not ideal because Redshift distribution keys are more effective for evenly distributing rows across nodes. Sinceregionhas low cardinality, it could result in data skew and degraded performance.