DEA-C02 Question #55: Real Exam Question with Answer & Explanation

Question

A Data Engineer is building a pipeline to transform a 1 TB table by joining it with supplemental tables. The Engineer is applying filters and several aggregations leveraging Common Table Expressions (CTEs) using a size Medium virtual warehouse in a single query in Snowflake. After checking the Query Profile, what is the recommended approach to MAXIMIZE performance of this query if the Profile shows data spillage?

Options

• AEnable clustering on the table.
• BIncrease the warehouse size.
• CRewrite the query to remove the CTEs.
• DSwitch to a multi-cluster virtual warehouse.

Explanation

Data spillage in Snowflake's Query Profile means the warehouse ran out of memory and had to write intermediate data to local disk (or remote storage), which dramatically slows query execution. Increasing the warehouse size (B) adds more memory, eliminating the spill and allowing the entire operation to run in-memory - this is the direct, recommended fix for spillage.

Why the distractors are wrong:

• A (Clustering): Clustering improves partition pruning and scan efficiency, but does nothing to address an in-flight memory shortage causing spillage.
• C (Remove CTEs): CTEs are logical constructs that Snowflake's optimizer typically handles well; rewriting them rarely resolves spillage, and Snowflake materializes CTEs only when beneficial.
• D (Multi-cluster warehouse): Multi-cluster scales out to handle more concurrent queries, not to give a single query more memory - that requires scaling up.

Memory tip: Think of it this way - spillage = overflow = not enough memory = scale UP the warehouse size. "Spill" sounds like a cup overflowing; the fix is a bigger cup (larger warehouse), not more cups (multi-cluster).

No community discussion yet for this question.