SnowPro Advanced: Data Engineer free question 4 / 10 · Domain 2: Performance Optimization
After the midday rush passed, you reduced a running Large warehouse to Medium. Immediately afterward, the afternoon's regular dashboard queries were slow for a while and then returned to their former speed. Which reason follows the documentation?
- New queries were held back while the resize was in progress, and waiting for it to finish made the dashboard queries run slower
- Reducing the size caused the table statistics to be rebuilt, and queries were slow until the new plans settled down again
- The table-data cache attached to the removed compute resources was discarded, and queries were slow until it was rebuilt
- Going to Medium removed the persisted result cache for the dashboards, so the same statements had to be executed again from scratch
Show the answer and explanation
Answer: C
Why this is correct
When you reduce the size of a running warehouse, compute resources are removed, and the table-data cache attached to those resources is also dropped. The documentation says this can affect performance. The cache is rebuilt as queries are processed, so the slowness is temporary and returns to normal in time.
Why the other options are wrong
"Going to Medium removed the persisted result cache for the dashboards, so the same statements had to be executed again from scratch" … The result cache (persisted query results) is not attached to the warehouse's compute resources and expires after 24 hours. Changing the size does not remove it.
"New queries were held back while the resize was in progress, and waiting for it to finish made the dashboard queries run slower" … The documentation says you can resize a warehouse at any time, even while it is running. It has no statement that new queries are held during a reduction.
"Reducing the size caused the table statistics to be rebuilt, and queries were slow until the new plans settled down again" … The documentation's explanation of resizing has no statement that table statistics or execution plans are rebuilt. The reason the documentation gives for slowness is that the cache attached to the removed resources is dropped.
Design takeaway
The documentation recommends deciding by balancing the credits saved by reducing the size against protecting the cache by keeping the current size. When you reduce from 5X-Large or 6X-Large to 4X-Large or smaller, both the old and new warehouses are charged for the short time until the old warehouse quiets down. When you increase the size, the added resources are used after they are ready.