DEA-C01 Question 369
Select 2You are designing a data pipeline in AWS that processes terabytes of data daily using Amazon Redshift. The pipeline involves frequent data loading, transformations, and analytical queries. Which of the following best practices should you implement to optimize performance in this scenario? (Select TWO)
- A
Use column encoding to reduce storage requirements and improve query performance.
- B
Disable automatic table distribution styles to manually control data distribution.
- C
Sort your data using appropriate sort keys based on query patterns.
- D
Keep your tables denormalized to minimize JOIN operations.
- E
Use Amazon Redshift Spectrum for frequently accessed data stored in S3.
Show answer and explanation
Correct answers: A, C
Explanation
For performance tuning in Amazon Redshift, using column encoding and selecting appropriate sort keys are critical best practices. Column encoding ensures efficient storage and faster query performance, while sort keys optimize query execution by reducing the amount of data scanned. Other options, such as proper distribution styles and normalization, are context-dependent but not directly focused on the tuning scenario described. Redshift Spectrum, while useful, addresses a different use case.
- A. Correct.
Column encoding is a best practice in Amazon Redshift to reduce data size and improve query performance. Redshift automatically applies optimal encoding during data loading, but it's crucial to review and ensure it's correctly applied for performance tuning.
- B. Incorrect.
Disabling automatic table distribution styles is not a best practice. Using the appropriate distribution style (AUTO, ALL, EVEN, or KEY) based on the use case is recommended to evenly distribute data across nodes.
- C. Correct.
Sort keys are vital for optimizing query performance, especially when queries involve filtering or sorting. Choosing the right sort key based on query patterns can significantly reduce query execution time.
- D. Incorrect.
While denormalization can simplify queries, it is not always a best practice in Amazon Redshift. Normalization or creating appropriate data models, along with efficient distribution styles and sort keys, is often a better approach.
- E. Incorrect.
Amazon Redshift Spectrum is used for querying data stored in S3 without loading it into Redshift. While useful, it is not directly related to performance tuning of Redshift itself but rather for extending query capabilities.