AWS Certified Data Engineer – Associate (DEA-C01) — Question 51

A company's data engineer needs to optimize the performance of table SQL queries. The company stores data in an Amazon Redshift cluster. The data engineer cannot increase the size of the cluster because of budget constraints.
The company stores the data in multiple tables and loads the data by using the EVEN distribution style. Some tables are hundreds of gigabytes in size. Other tables are less than 10 MB in size.
Which solution will meet these requirements?

Answer options

• A. Keep using the EVEN distribution style for all tables. Specify primary and foreign keys for all tables.
• B. Use the ALL distribution style for large tables. Specify primary and foreign keys for all tables.
• C. Use the ALL distribution style for rarely updated small tables. Specify primary and foreign keys for all tables.
• D. Specify a combination of distribution, sort, and partition keys for all tables.

Correct answer: C

Explanation

The correct answer is C because using the ALL distribution style for small and rarely updated tables can enhance query performance by ensuring that all nodes have a complete copy of the data, reducing the need for data redistribution during joins. The other options suggest using the EVEN distribution style or applying ALL to larger tables, which may not optimize performance effectively under the given constraints.