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 virtual warehouse size so more compute nodes are available to evaluate the filter predicate in parallel.

  Why it's wrong here

  Warehouse size changes how many threads evaluate the predicate, not how many micro-partitions must be read. If pruning is ineffective, every node still receives partitions that cannot match, so a larger warehouse only scans the same unpruned data faster at higher credit cost, leaving the high scanned-partition ratio unchanged.

• ✗

  Convert the table to a view over the same data so the optimizer can push the filter predicate down into the view definition.

  Why it's wrong here

  A view is a stored query, not a physical reorganization, so it cannot change which micro-partitions exist or their value ranges. Predicate pushdown may occur, but with no clustering or constant-bounded predicate the scan still touches the same partitions. This adds a layer of indirection without improving pruning.

• ✓

  Define a clustering key on the frequently filtered high-cardinality column so related values are co-located in the same micro-partitions.

  Why this is correct

  Clustering physically orders rows by the chosen column so that a selective filter can skip micro-partitions whose value ranges fall outside the predicate. When the profile shows most partitions being scanned for a selective filter, clustering on that filter column is the direct way to raise the pruning ratio and cut the scanned data volume.

• ✓

  Ensure the filter predicate references the column directly with a constant or a value known at compile time rather than a non-deterministic expression.

  Why this is correct

  Pruning relies on comparing stored partition min and max values against a constant bound. If the predicate uses a non-deterministic or runtime-only expression, the optimizer cannot derive that bound and must scan broadly. Using a direct comparison to a constant lets pruning metadata eliminate partitions that cannot contain matching rows.

• ✗

  Apply a filter transformation inside the query that wraps the filtered column in a function such as UPPER or CAST before comparing it to a literal.

  Why it's wrong here

  Wrapping the filtered column in a function prevents the optimizer from using micro-partition pruning metadata, because the stored min and max values no longer match the transformed expression. This typically forces a full scan and makes the pruning problem worse, so it is the opposite of what the scenario requires.