A Data Engineer is investigating a query that is taking a long time to return. The Query Profile shows the following:
What step should the Engineer take to increase the query performance?
Exam SnowPro Advanced Data Engineer All QuestionsBrowse all questions from this exam
Question 1
Correct Answer: B
The query profile indicates that a significant amount of data (37.59 GB) is being spilled to local storage. This is a clear sign that the virtual warehouse does not have enough memory to handle the query efficiently. Spilling to disk is much slower than processing data in memory. To address this, the engineer should increase the size of the virtual warehouse, which will provide more memory and reduce or eliminate the need for disk spilling, thus improving query performance.
Discussion
BigDataBBOption: B
What is disk spilling? When Snowflake warehouse cannot fit an operation in memory, it starts spilling (storing) data first to the local disk of a warehouse node, and then to remote storage. In such a case, Snowflake first tries to temporarily store the data on the warehouse local disk. As this means extra IO operations, any query that requires spilling will take longer than a similar query running on similar data that is capable to fit the operations in memory. Also, if the local disk is not sufficient to fit the spilled data, Snowflake further tries to write to the remote cloud storage, which will be shown in the query profile as "Bytes spilled to remote storage".
rbeamOption: B
Bytes spilled to local means insufficient memory. to increase memory, the suggested way is increasing warehouse size.