DEA-C01 Question 365
Select 2You are working as a Data Engineer at an organization that processes a large volume of clickstream data using Amazon Redshift. The team has noticed performance bottlenecks when querying large datasets. Which of the following best practices should you implement to optimize query performance in Amazon Redshift? (Select TWO.)
- A
Use the DISTKEY and SORTKEY strategically based on query patterns.
- B
Enable cross-region replication for the Redshift cluster.
- C
Compress data using columnar encoding.
- D
Use small-sized nodes instead of larger nodes in the Redshift cluster.
- E
Analyze and vacuum your tables regularly.
Show answer and explanation
Correct answers: A, C
Explanation
Best practices for performance tuning in Amazon Redshift involve optimizing query patterns and reducing the amount of data scanned. Strategic use of DISTKEY and SORTKEY ensures efficient data distribution and faster access. Compression using columnar encoding reduces the data size scanned during queries, directly improving performance. While vacuuming and other maintenance tasks are important, they address storage and table health rather than solving query bottlenecks.
- A. Correct.
Using the DISTKEY and SORTKEY strategically helps optimize data distribution and query performance. DISTKEY determines how data is distributed across nodes, and SORTKEY helps in faster querying by ordering data efficiently.
- B. Incorrect.
Cross-region replication is used for disaster recovery and does not contribute to query performance optimization.
- C. Correct.
Compressing data using columnar encoding reduces the amount of data scanned during queries, improving performance. Redshift automatically applies compression when data is loaded, but reviewing encoding can further optimize performance.
- D. Incorrect.
Using smaller nodes instead of larger ones can increase operational costs and may not necessarily improve performance. The choice of node type should be based on the workload requirements.
- E. Incorrect.
Analyzing and vacuuming tables helps reclaim storage and optimize query plans but is not a direct solution for query performance bottlenecks in Redshift.