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?

Question

Exhibit

Options

• AAdd additional virtual warehouses.
• BIncrease the size of the virtual warehouse.
• CRewrite the query using Common Table Expressions (CTEs).
• DChange the order of the joins and start with smaller tables first.

• A
  5% (2)
• B
  81% (34)
• C
  5% (2)
• D
  10% (4)

Explanation

Increasing virtual warehouse size directly adds more compute resources (CPU, memory) to process a slow query faster - this is the right lever when the Query Profile shows the bottleneck is compute-bound (e.g., high execution time, spilled data to disk, or long operator times).

• A is wrong because adding more virtual warehouses doesn't help a single query - additional warehouses handle concurrent workloads, not a single slow query running on one warehouse.
• C is wrong because CTEs in Snowflake are typically inlined by the optimizer and don't inherently improve performance; rewriting logic helps only if there's a structural query inefficiency, which the Query Profile would need to specifically indicate.
• D is wrong because Snowflake's query optimizer automatically determines join order - manually reordering joins is a legacy optimization from databases that don't have cost-based optimizers.

Memory tip: Think of it this way - one query, one warehouse. If that one query is slow, make its warehouse bigger (scale up). If you need to handle more queries at once, add more warehouses (scale out).

No community discussion yet for this question.