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.

• ✗

  Enable the Search Optimization Service on the base table so the exploding operator can skip micro-partitions and emit fewer rows.

  Why it's wrong here

  Search Optimization Service accelerates point-lookup and selective predicate access paths; it does not reduce the row count produced by a join or aggregation step. The profile already shows the explosion is in operator output, not in scanned partitions, so adding this service would spend credits without changing the intermediate cardinality.

• ✓

  Reduce the number of rows produced by the exploding operator by moving the aggregation to a separate earlier stage or by rewriting the join/grouping logic so cardinality grows later in the plan.

  Why this is correct

  An operator whose output rows massively exceed its input rows indicates an unintended row explosion, usually from a fan-out join or an incorrect grouping key. Because the explosion happens before the aggregation, the downstream operator must process every duplicated row, so fixing the cardinality growth is the only action that addresses the actual bottleneck.

• ✗

  Disable the query result cache at the account level so the exploding operator re-executes and produces a smaller result set.

  Why it's wrong here

  Result caching only affects whether a previously executed identical query returns a stored result; it never changes how an operator computes its output rows. Disabling it would force re-execution and add latency and cost without altering the row multiplication that the Query Profile clearly shows, so it cannot fix the bottleneck.

• ✗

  Increase the size of the virtual warehouse to add more compute nodes so the exploding operator can process more rows per second.

  Why it's wrong here

  Scaling up the virtual warehouse adds parallel compute capacity, but it does not change the number of rows an operator emits. The row explosion would still occur on every additional node, so more compute merely processes the same inflated intermediate result faster while consuming proportionally more credits, leaving the root cause untouched.