SOL-C01 · Question #144
A data engineering team uses Snowflake to load and transform large datasets. They have observed that a specific transformation job consistently takes an unacceptably long time to complete, even though
The correct answer is C. Review the query execution plan to identify performance bottlenecks such as full table scans or. Reviewing the query execution plan (C) is correct because it targets the actual root cause of slowness - full table scans, missing clustering keys, data spills to disk, or inefficient joins - rather than blindly throwing more compute at an undiagnosed problem. Without understandi
Question
A data engineering team uses Snowflake to load and transform large datasets. They have observed that a specific transformation job consistently takes an unacceptably long time to complete, even though the warehouse is sized at X-LARGE. Which of the following actions, individually or in combination, would be MOST effective in reducing the job's execution time?
Options
- AUpgrade the warehouse to a 2X-LARGE size. This ensures more compute resources are
- BEnable Multi-cluster warehouse and set the minimum and maximum clusters to 1 and 1
- CReview the query execution plan to identify performance bottlenecks such as full table scans or
- DChange the warehouse from 'Auto-Suspend' to 'Never-suspend' to keep the warehouse active
- EPartition the source data into smaller files. Smaller files are always loaded faster.
How the community answered
(49 responses)- A18% (9)
- B6% (3)
- C69% (34)
- D2% (1)
- E4% (2)
Explanation
Reviewing the query execution plan (C) is correct because it targets the actual root cause of slowness - full table scans, missing clustering keys, data spills to disk, or inefficient joins - rather than blindly throwing more compute at an undiagnosed problem. Without understanding the bottleneck, all other optimizations are guesswork.
Why the distractors fail:
- A (Upgrade to 2X-LARGE): More compute only helps if the job is CPU/memory-bound. A larger warehouse won't fix a cartesian join or a query that can't prune micro-partitions - it just costs more.
- B (Multi-cluster, min=max=1): Setting both min and max to 1 disables multi-cluster scaling entirely. Even if configured correctly, multi-cluster addresses concurrency (many simultaneous queries), not a single slow transformation.
- D (Never-suspend): Auto-suspend affects cold-start latency by a few seconds at most. If a job runs unacceptably long, eliminating a 5-second spin-up time is meaningless.
- E (Smaller files load faster): This is a classic Snowflake misconception. Snowflake recommends files of 100–250 MB compressed; too many tiny files create metadata overhead and degrade performance.
Memory tip: Remember "diagnose before you spend" - Snowflake's Query Profile (in the UI) shows you exactly where time is lost (e.g., spill to disk = need more memory, no partition pruning = need clustering). Fix the plan first; resize the warehouse only if profiling confirms a compute bottleneck.
Topics
Community Discussion
No community discussion yet for this question.