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.

• ✗

  Reduce the size of the smaller table in the join by filtering it.

  Why it's wrong here

  Filtering the smaller table might reduce the overall data volume, but local spilling is caused by the join operation exceeding memory on a node. If the smaller table is already small, filtering it further may not significantly reduce the memory required for the join's intermediate results. The spilling likely occurs on the larger table's side or due to skew. While reducing data can help, it is not the most direct solution for local spilling, which is a memory capacity issue.

• ✗

  Add a clustering key on the join column of the larger table.

  Why it's wrong here

  Clustering the larger table on the join column can improve pruning and reduce the amount of data read, but it does not directly reduce local spilling during the join. Local spilling is a memory issue during processing, not a data retrieval issue. Clustering might help if it reduces the data volume per node, but it is not the most direct fix for spilling, especially if the join itself is the memory-intensive step.

• ✗

  Change the join type from a hash join to a sort-merge join.

  Why it's wrong here

  Snowflake's optimizer automatically selects the most efficient join strategy based on statistics and data characteristics. Manually forcing a different join type is not typically supported or recommended. Sort-merge joins can also spill to local storage if the sort operation exceeds memory. Changing the join type does not address the underlying memory constraint; increasing warehouse size is a more reliable way to reduce spilling.

• ✓

  Increase the warehouse size to provide more memory for the join operation.

  Why this is correct

  Local spilling occurs when the join operation's intermediate results exceed the memory available on a warehouse node. Increasing the warehouse size allocates more memory per node, allowing the join to process larger datasets in memory and reducing or eliminating local spilling. This directly addresses the memory constraint without altering the query. Other options like filtering or clustering might reduce data volume but do not directly increase the memory available to the join operation.