Q53 — AWS DEA-C01 Ch.1

Question 53 of 100 | ← Chapter 1

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?

• 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. Use the ALL distribution style for rarely updated small tables. Specify primary and foreign keys for all tables.

Explanation

在AmazonRedshift中，数据的分布方式对查询性能有重要影响。由于预算限制不能增加集群大小，数据工程师需要优化现有资源。A选项：继续使用EVEN分布风格对所有表，这可能导致大表的数据分散在多个节点上，增加查询时的数据扫描量，不是最优选择。B选项：对大表使用ALL分布风格，意味着大表的数据会完全复制到集群的每个节点上，这在大表场景下会非常消耗存储，且对于预算受限的情况并不适用。C选项：对小表（很少更新的）使用ALL分布风格，意味着小表的数据也会完全复制到每个节点，但小表的数据量小，存储消耗可接受，且能提高小表查询的性能。同时指定所有表的主键和外键可以帮助优化查询性能。D选项：指定所有表的分布、排序和分区键可能不是最优的，因为不是所有表都需要这些键来优化性能，且可能增加管理复杂性。因此，C选项是最合适的解决方案。