A Data Engineer is evaluating the performance of a query in a development environment. Based on the Query Profile, what are some performance tuning options the Engineer can use? (Choose two.)

Question

Exhibit

Options

• AAdd a LIMIT to the ORDER BY if possible
• BUse a multi-cluster virtual warehouse with the scaling policy set to standard
• CMove the query to a larger virtual warehouse
• DCreate indexes to ensure sorted access to data
• EIncrease the MAX_CLUSTER_COUNT

• A
  71% (17)
• B
  8% (2)
• D
  4% (1)
• E
  17% (4)

Explanation

A is correct because adding a LIMIT to an ORDER BY allows Snowflake to stop sorting once the required rows are found, dramatically reducing one of the most expensive operations a query can perform - a full sort is unnecessary if only the top N rows are needed.

C is correct because a larger virtual warehouse provides more compute nodes, memory, and CPU, directly addressing resource bottlenecks visible in the Query Profile (e.g., spilling to disk, slow processing nodes).

B and E are wrong for the same reason: multi-cluster warehouses and MAX_CLUSTER_COUNT address concurrency - spinning up additional clusters to handle many simultaneous users - not the performance of a single query being tuned in dev.

D is wrong because Snowflake does not support traditional indexes; it uses micro-partition pruning and clustering keys instead. Indexes are a concept from row-store databases like PostgreSQL or MySQL.

Memory tip: Split the distractors into two buckets - "wrong database concept" (D = indexes don't exist in Snowflake) and "solves the wrong problem" (B and E = concurrency, not single-query speed). The correct answers follow the simple rule: reduce work (A) or add resources (C).

No community discussion yet for this question.