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

Question

A data engineering team is experiencing performance issues with their nightly ETL pipeline in Snowflake. The pipeline involves complex transformations on a large dataset (5TB) and is executed within a single Snowflake virtual warehouse (size: Large). The team notices that the warehouse is frequently hitting resource limits (CPU and Memory) during peak processing times, even though the overall execution time is only 2 hours. Which of the following strategies would BEST address the performance bottleneck and optimize resource utilization, considering cost- effectiveness?

Options

• AUpgrade the virtual warehouse size to X-Large to provide more CPU and memory resources. This
• BImplement scaling policies for the virtual warehouse. Configure it to automatically scale up to X-
• CBreak down the ETL pipeline into smaller, independent tasks and use multiple smaller virtual
• DOptimize the SQL queries within the ETL pipeline by identifying and rewriting inefficient queries,
• EMigrate the entire ETL pipeline to a different data processing platform like Apache Spark, as

Explanation

Option C (Breaking down the ETL pipeline) is a strong choice as it leverages Snowflake's multi- cluster architecture for parallel processing, improving performance and resource utilization. Option D (Optimizing SQL queries) is also crucial. Inefficient queries can significantly impact performance. Options A and B address the problem, but not as efficiently as C and D. While upgrading the warehouse (A) might provide temporary relief, it doesn't fundamentally address inefficiencies. Auto-scaling (B) is good, but splitting the load provides true parallelism. Option E is an extreme measure and likely unnecessary with proper optimization.

No community discussion yet for this question.