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.

• ✗

  Define a clustering key on (ORDER_DATE, CUSTOMER_ID) and let automatic clustering maintain it.

  Why it's wrong here

  A clustering key on ORDER_DATE and CUSTOMER_ID helps range pruning and aggregate scanning, but it does not create the specialized access path that makes single-value lookups on ORDER_ID and CUSTOMER_ID fast. It also introduces continuous reclustering cost and credits consumption as new data arrives. The scenario explicitly wants to avoid clustering maintenance overhead, so this approach conflicts with the stated requirement.

• ✗

  Add a unique constraint on ORDER_ID and rely on the optimizer to use it for pruning.

  Why it's wrong here

  Unique constraints in Snowflake are declarative metadata used by the optimizer for join elimination and integrity purposes; they are not enforced and do not create a physical access path for point lookups. Adding one on ORDER_ID would not make equality filtering on CUSTOMER_ID fast, nor would it bypass the scan of micro-partitions. It therefore does not satisfy the performance goal.

• ✗

  Create a materialized view that pre-aggregates the table by ORDER_DATE and CUSTOMER_ID.

  Why it's wrong here

  A materialized view that pre-aggregates by ORDER_DATE and CUSTOMER_ID can speed up the reported aggregations, but it does not accelerate point lookups on ORDER_ID, which is not part of the grouping. It also adds storage and refresh cost, and its results are only usable when the query matches the view definition closely enough for the optimizer to rewrite. It therefore misses the point-lookup requirement.

• ✓

  Enable the search optimization service on the CUSTOMER_ID and ORDER_ID columns.

  Why this is correct

  Search optimization builds a persistent search access path that accelerates equality and IN predicates on the designated columns, which is exactly the point-lookup pattern described. It is maintained automatically as new micro-partitions are added by nightly loads, so no manual reclustering work is required. Clustering keys would instead target range pruning and require background maintenance, so enabling search optimization is the appropriate low-overhead choice here.