Q77 — AWS DEA-C01 Ch.1

Question 77 of 100 | ← Chapter 1

A retail company stores transactions, store locations, and customer information tables in four reserved ra3.4xlarge Amazon Redshift cluster nodes. All three tables use even table distribution. The company updates the store location table only once or twice every few years. A data engineer notices that Redshift queues are slowing down because the whole store location table is constantly being broadcast to all four compute nodes for most queries. The data engineer wants to speed up the query performance by minimizing the broadcasting of the store location table. Which solution will meet these requirements in the MOST cost-effective way?

• A. Change the distribution style of the store location table from EVEN distribution to ALL distribution. ✓
• B. Change the distribution style of the store location table to KEY distribution based on the column that has the highest dimension.
• C. Add a join column named store_id into the sort key for all the tables.
• D. Upgrade the Redshift reserved node to a larger instance size in the same instance family.

Correct Answer: A. Change the distribution style of the store location table from EVEN distribution to ALL distribution.

Explanation

在这个情境中，问题在于storelocation表的广播导致查询性能下降。选项A是将分布样式从EVEN更改为ALL分布。ALL分布适用于不常更新且参与连接操作较少的大表，可减少广播，成本相对较低。选项B基于最高维度的列将分布样式更改为KEY分布，可能需要重新设计表结构和数据分布，成本较高且复杂性增加。选项C添加连接列到排序键对解决广播问题效果不明显。选项D升级Redshift预留节点的实例大小成本较高，且不能直接解决表广播的问题。综上所述，选项A是最经济有效的解决方案。