AWS Certified Data Engineer Associate · Free Practice Question Hard
Question 54
Your organization uses Amazon Redshift for data warehousing and has recently been experiencing frequent and unpredictable performance issues when executing queries across multiple columns of a large dataset. The dataset is frequently updated and queries often involve filtering and aggregation on numerous columns. Your team has been tasked with optimizing query performance to ensure consistency. Which of the following actions would be most effective in improving the performance of these queries? (select TWO)
-
A
Convert frequently joined tables to use Redshift Spectrum.
-
B
Implement sort keys on the columns that are most frequently used in the WHERE and ORDER BY clauses.
-
C
Increase the number of Redshift nodes to scale up the compute resources.
-
D
Apply the VACUUM command regularly to all tables to reclaim space and resort rows.
Reveal correct answers
Correct answers: B, D
A. Converting frequently joined tables to use Redshift Spectrum can offload query processing to Amazon S3, which can help improve performance by reducing the load on the Redshift cluster. However, this may not directly address the performance issues related to querying across multiple columns of a large dataset.
B. Implementing sort keys on the columns that are most frequently used in the WHERE and ORDER BY clauses can significantly improve query performance by organizing data in a way that aligns with the query patterns. This optimization technique can reduce the amount of data scanned during queries and improve overall query efficiency.
C. Increasing the number of Redshift nodes can help scale up compute resources, but it may not directly address the performance issues related to querying across multiple columns of a large dataset. It can improve overall system performance but may not specifically target query optimization for filtering and aggregation on numerous columns.
D. Applying the VACUUM command regularly to all tables can help reclaim space and resort rows, which can improve query performance by optimizing data storage and organization. This maintenance operation can help address performance issues related to frequent updates and ensure data is stored efficiently for querying.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
