Question 120
For this Snowflake situation — A heavy aggregation spills to remote storage although the reporting warehouse has little concurrent traffic. — the team adopts: Evaluate scaling up the warehouse after confirming the query profile and memory-pressure evidence. Which ongoing practice BEST limits the same problem recurring?
Explanation
Correct Answer
The relevant ongoing safeguard is: Reassess size only after measuring spills and plan regressions. It controls the recurring exposure associated with this mechanism: A larger warehouse can provide more compute and memory to a single demanding query. The original action, evaluate scaling up the warehouse after confirming the query profile and memory-pressure evidence., still needs operational checks when the environment or workload changes.
Incorrect Answers
This routine guards against another mechanism: Query speedups depend on the limiting operation and not all SQL steps scale proportionally with warehouse size. The recurring exposure in the current case is: A larger warehouse can provide more compute and memory to a single demanding query. The matching preventive check is “Reassess size only after measuring spills and plan regressions”.
The suggested review tracks a different technical risk: External tables expose queryable metadata over external files while the data remains in cloud object storage. Here the sustained control must address: A larger warehouse can provide more compute and memory to a single demanding query. Choose “Reassess size only after measuring spills and plan regressions”.
That safeguard is relevant to: Sequences are database schema objects used to generate ordered numeric values, not warehouse resources. The current failure instead follows from: A larger warehouse can provide more compute and memory to a single demanding query. Its prevention measure is “Reassess size only after measuring spills and plan regressions”.