SnowPro Advanced: Architect — Question 145

An Architect needs to improve the performance of reports that pull data from multiple Snowflake tables, join, and then aggregate the data. Users access the reports using several dashboards. There are performance issues on Monday mornings between 9:00am-11:00am when many users check the sales reports.

The size of the group has increased from 4 to 8 users. Waiting times to refresh the dashboards has increased significantly. Currently this workload is being served by a virtual warehouse with the following parameters:

AUTO-RESUME = TRUE -

AUTO_SUSPEND = 60 -

SIZE = Medium -

What is the MOST cost-effective way to increase the availability of the reports?

Answer options

• A. Use materialized views and pre-calculate the data.
• B. Increase the warehouse to size Large and set AUTO_SUSPEND = 600.
• C. Use a multi-cluster warehouse in maximized mode with 2 size Medium clusters.
• D. Use a multi-cluster warehouse in auto-scale mode with 1 size Medium cluster, and set MIN_CLUSTER_COUNT = 1 and MAX_CLUSTER_COUNT = 4.

Correct answer: D

Explanation

The correct answer is D because using a multi-cluster warehouse in auto-scale mode allows the system to automatically adjust resources based on demand, which is ideal for fluctuating workloads during peak times. Option A, while helpful for performance, does not address the immediate resource needs. Option B, increasing to a Large size, is more expensive and does not provide the flexibility needed. Option C does not utilize auto-scaling, limiting its effectiveness during high-demand periods.