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.

• ✓

  Increase the warehouse size to provide more memory per node.

  Why this is correct

  Even with the small table as the build side, the probe side or other concurrent operations may exceed memory, causing remote spilling. Increasing warehouse size adds memory per node, which can eliminate the spill by allowing the join to process more data in memory. This is a direct way to address memory pressure without changing the query.

• ✗

  Change the join type from inner join to left join to reduce data shuffling.

  Why it's wrong here

  Changing the join type alters the semantics of the query and does not reduce data shuffling or memory usage. A left join may actually process more rows if there are unmatched rows. The remote spilling is a memory issue, not a join type issue, and changing it would produce incorrect results.

• ✗

  Use a clustering key on the join column of the large table to reduce the build side size.

  Why it's wrong here

  Clustering on the join column can improve pruning for selective filters, but it does not reduce the size of the build side, which is the small table. The build side is already small. Clustering the large table may help with probe-side pruning if there are filters, but it does not directly address the memory pressure causing remote spilling.

• ✗

  Add a filter to the small table to reduce its size further.

  Why it's wrong here

  The small table is already the build side and likely fits in memory. Reducing its size further would not address the remote spilling, which is caused by the probe side or overall memory pressure. The spill occurs because the join operator as a whole exceeds memory, not because the build side is too large.