DEA-C02 Performance Optimization Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  The query is missing a filter, and scaling out will fix it.

  Why it's wrong here

  Scaling out (adding clusters) does not increase memory per query; it only increases concurrency capacity. If a single query is spilling, adding more clusters will not provide the extra memory needed to perform the sort in RAM. Scaling up (increasing warehouse size) is required instead.

• ✓

  The query requires a large sort buffer; scale up the warehouse.

  Why this is correct

  Window functions perform memory-intensive operations like sorting. If the dataset exceeds the memory capacity of the current warehouse size, spilling occurs. Scaling up to a larger warehouse increases the memory available to the individual nodes, allowing the sort operation to complete in RAM without disk involvement.

• ✗

  The table is not clustered, which causes excessive scanning.

  Why it's wrong here

  Clustering helps reduce the number of partitions scanned, which improves performance, but it does not directly manage the memory required for the internal sorting process of a window function. Even with perfect clustering, a large window function operation could still cause spilling if memory is insufficient.

• ✗

  The query needs a dedicated 'Snowpark-optimized' warehouse.

  Why it's wrong here

  While Snowpark-optimized warehouses are useful for memory-intensive Python or Java code, standard SQL window functions can be optimized by simply using a larger standard warehouse. A Snowpark-optimized warehouse is an expensive choice that is generally overkill for pure SQL window function operations.