AWS Certified Data Engineer Associate · Free Practice Question Medium
Question 15
A data engineering team at an online retail company is optimizing the performance of their Amazon Redshift data warehouse. The warehouse contains a large sales table with millions of rows and a smaller products table. Queries often join these two tables, and the team wants to optimize the query performance, especially for these join operations.
Which Redshift distribution style should the team use for the sales and products tables to enhance query performance?
-
A
Configure the sales table to ALL distribution and the products table to EVEN distribution.
-
B
Configure KEY distribution for both the sales and products tables based on the join column.
-
C
Configure the sales table to EVEN distribution and the products table to KEY distribution.
-
D
Configure the sales table to AUTO distribution and the products table to ALL distribution.
Reveal correct answer
Correct answer: D
Explanation
For the large sales table, using AUTO distribution allows Amazon Redshift to automatically select the most appropriate distribution style based on the table's size and the query patterns.
This optimizes performance and storage efficiency for large tables. For the smaller products table, using ALL distribution replicates the entire table to each node in the Redshift cluster.
This significantly speeds up join operations between the sales and products tables, as the products table is present on every node, eliminating the need for costly data redistribution during queries.
CORRECT: "Configure the sales table to AUTO distribution and the products table to ALL distribution" is the correct answer (as explained above.)
INCORRECT: "Configure the sales table to EVEN distribution and the products table to KEY distribution" is incorrect.
EVEN distribution would spread the rows of the sales table evenly across all nodes, but it may not be the most efficient for join operations. KEY distribution for the products table would only be optimal if it is frequently joined on a specific column that is evenly distributed. However, for smaller tables, ALL distribution is often more effective.
INCORRECT: "Configure the sales table to ALL distribution and the products table to EVEN distribution" is incorrect.
Using ALL distribution for a large table like sales is not efficient, as it would replicate the entire table across all nodes, consuming excessive storage space and potentially impacting performance. This approach is more suitable for smaller dimension tables.
INCORRECT: "Configure KEY distribution for both the sales and products tables based on the join column" is incorrect.
While KEY distribution can improve the performance of join operations, it requires careful selection of the distribution key and is generally more beneficial for evenly sized large tables. For scenarios involving a large fact table and a smaller dimension table, combining AUTO or EVEN distribution for the large table with ALL distribution for the smaller table is often more effective.
References:
https://docs.aws.amazon.com/redshift/latest/dg/c_choosing_dist_sort.html
Save time with our AWS cheat sheets:
https://digitalcloud.training/aws-certified-data-engineer-associate-cheat-sheet/
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
