SOL-C01 Question #144: Real Exam Question with Answer & Explanation

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.

Explanation

While increasing warehouse size (A) might help, it's a brute-force approach. Multi-cluster with min/max set to 1 (B) is essentially a single warehouse. 'Never-suspend' (D) only keeps the warehouse running, not faster. Smaller files (E) is relevant during data loading, but the question focuses on transformation. The most effective action is always optimizing the SQL query itself (C). This addresses the root cause of the performance issue, which is likely inefficient query processing, rather than simply throwing more hardware at the problem. Analyzing the query plan will reveal bottlenecks that can be addressed with indexing, query rewriting, or data clustering.

No community discussion yet for this question.