SOL-C01 · Question #169
You have a Snowflake table named 'SALES DATA' that you load data into daily from a CSV file using Snowsight. Recently, the load times have increased significantly. You suspect the Virtual Warehouse…
The correct answer is D. Keep the X-Large warehouse but investigate other potential bottlenecks, such as the file format. Option D is correct because the Query History reveals COPY INTO commands are consuming only a small fraction of the X-Large warehouse's resources - meaning the warehouse is not the bottleneck. Increasing load times despite an underutilized warehouse strongly signals the problem…
Question
You have a Snowflake table named 'SALES DATA' that you load data into daily from a CSV file using Snowsight. Recently, the load times have increased significantly. You suspect the Virtual Warehouse size is the bottleneck. You have the following Virtual Warehouse sizes available: X- Small, Small, Medium, Large, X-Large. Choosing the best size depends on cost and speed. You examine the Query History in Snowsight and notice that the COPY INTO commands are consistently using only a small fraction of the X-Large warehouse's compute resources. Which of the following actions would be the MOST cost- effective while also potentially improving (or at least maintaining) the data loading performance?
Options
- AIncrease the Virtual Warehouse size to 2X-Large. This will guarantee faster load times.
- BDecrease the Virtual Warehouse size to Small or Medium. Since the current warehouse is
- CEnable auto-suspend on the X-Large warehouse if it isn't already enabled. This will reduce costs
- DKeep the X-Large warehouse but investigate other potential bottlenecks, such as the file format
- ESwitch to using Snowpipe for continuous data ingestion instead of COPY INTO. This may improve
How the community answered
(42 responses)- A2% (1)
- B21% (9)
- C5% (2)
- D60% (25)
- E12% (5)
Explanation
Option D is correct because the Query History reveals COPY INTO commands are consuming only a small fraction of the X-Large warehouse's resources - meaning the warehouse is not the bottleneck. Increasing load times despite an underutilized warehouse strongly signals the problem lies elsewhere: mismatched file format settings (e.g., wrong delimiter, missing compression), insufficient file splitting, staging area latency, or table-level issues like clustering. Investigating these root causes is both free and targeted, making it the most cost-effective first step.
Why the distractors fail:
- A - Scaling up to 2X-Large when the current warehouse is already underutilized is wasteful; more compute won't fix a non-compute bottleneck.
- B - While downsizing is tempting when resources are underused, it addresses cost but not the increased load times, and could make performance worse without diagnosing the actual cause.
- C - Auto-suspend reduces idle costs but has no effect on performance during an active load; it doesn't solve the slowdown.
- E - Snowpipe is designed for continuous micro-batch ingestion, not daily bulk loads. Switching architectures without identifying the root cause is premature and adds complexity.
Memory tip: Think "size ≠ speed if the warehouse is starved for work." When Query History shows low resource utilization, the fix is always investigate first, resize second - the bottleneck is hiding in format, file layout, or staging, not compute.
Topics
Community Discussion
No community discussion yet for this question.