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

Question

A data engineering team is experiencing significant delays during their nightly ETL process in Snowflake. The process involves loading data from several external cloud storage locations (AWS S3, Azure Blob Storage) into a Snowflake table, transforming the data, and then loading it into multiple target tables. Monitoring shows the virtual warehouse CPU utilization is consistently at 100% during the peak ETL hours. Which of the following strategies would be MOST effective in reducing the ETL processing time and improving resource utilization?

Options

• AIncrease the virtual warehouse size (e.g., from MEDIUM to LARGE) and monitor performance.
• BEnable auto-suspend on the virtual warehouse to reduce credits consumed during idle time.
• CImplement multi-clustering on the virtual warehouse, setting both MIN_CLUSTER_COUNT and
• DReduce the number of micro-partitions in the source data files by consolidating smaller files into
• ERepartition the Snowflake table into smaller micro-partitions to improve query performance.

Explanation

Multi-clustering allows Snowflake to automatically scale out the virtual warehouse by adding more compute resources when the workload increases. This is the most effective way to handle high CPU utilization during peak ETL hours. Increasing the warehouse size (A) can help, but multi- clustering provides more dynamic scalability. Auto-suspend (B) doesn't address the performance issue. The micro- partition size of external source files (D) may impact initial load performance, but not the subsequent transformations and loading. Repartitioning the Snowflake table (E) may improve query performance, but not necessarily the ETL process itself.

No community discussion yet for this question.