SnowPro Advanced: Data Engineer — Question 50

A Data Engineer is building a pipeline to transform a 1 TB table by joining it with supplemental tables. The Engineer is applying filters and several aggregations leveraging Common Table Expressions (CTEs) using a size Medium virtual warehouse in a single query in Snowflake.
After checking the Query Profile, what is the recommended approach to MAXIMIZE performance of this query if the Profile shows data spillage?

Answer options

• A. Enable clustering on the table.
• B. Increase the warehouse size.
• C. Rewrite the query to remove the CTEs.
• D. Switch to a multi-cluster virtual warehouse.

Correct answer: B

Explanation

Increasing the warehouse size (option B) provides more resources, which can help process larger datasets and complex queries more efficiently, reducing the risk of data spillage. Enabling clustering (option A) may improve performance but is less effective than simply increasing the warehouse size for this scenario. Rewriting the query to remove CTEs (option C) could simplify the query but does not directly address the spillage issue, and switching to a multi-cluster virtual warehouse (option D) may be unnecessary if scaling up the existing warehouse suffices.