hiexam
snowflake · SnowPro-Advanced-Data-Engineer · Q602 · multiple_choice · topic_1

A Data Engineer is investigating a query that is taking a long time to return. The Query Profile shows the following: /…

A Data Engineer is investigating a query that is taking a long time to return. The Query Profile shows the following: //IMG// What step should the Engineer take to increase the query performance?
  • A.Add additional virtual warehouses.
  • B.Increase the size of the virtual warehouse.
  • C.Rewrite the query using Common Table Expressions (CTEs).
  • D.Change the order of the joins and start with smaller tables first.
Explanation
Selected Answer: 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".

Reference: examtopics_top_comment

Practice with progress tracking

Sign in to track wrong answers, get spaced-repetition reminders, and run timed exam mode.

Start tracked practicePricing