DEA-C01 Question 368
Select 3A data engineering team is designing an Amazon Redshift cluster to process a large dataset for their analytics workload. They want to optimize query performance and minimize disk I/O while ensuring efficient storage. Which of the following best practices should they follow to achieve this?
- A
Use SORTKEYs and DISTKEYs aligned with query patterns.
- B
Disable compression on columns to reduce processing overhead.
- C
Use COPY command with proper file format and compression.
- D
Distribute data evenly across nodes by choosing the AUTO distribution style.
- E
Run VACUUM and ANALYZE commands regularly.
Show answer and explanation
Correct answers: A, C, E
Explanation
Performance tuning in Amazon Redshift involves using SORTKEYs and DISTKEYs to optimize data distribution and retrieval, applying compression to reduce disk I/O, using efficient data loading techniques like the COPY command with compression, and maintaining the cluster with VACUUM and ANALYZE commands. These practices collectively ensure that the cluster operates efficiently and queries are executed quickly.
- A. Correct.
Using SORTKEYs and DISTKEYs aligned with query patterns allows Amazon Redshift to optimize how data is stored and retrieved, reducing query execution time.
- B. Incorrect.
Disabling compression on columns is not a best practice. Compression reduces storage requirements and improves query performance by reducing I/O operations.
- C. Correct.
Using the COPY command with proper file format and compression ensures efficient ingestion of data into Redshift, reducing processing time and storage requirements.
- D. Incorrect.
Choosing AUTO distribution style may not always result in evenly distributed data, especially for specific query or table patterns. Instead, carefully choosing the right distribution style is recommended.
- E. Correct.
Running VACUUM and ANALYZE commands regularly ensures that data is compacted and the query planner has updated statistics for accurate execution plans.