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.

• ✗

  Use a materialized view that pre-filters on the 'status' column.

  Why it's wrong here

  A materialized view can precompute results, but if the query filters on 'status' with only 5 values, a materialized view would need to store a large portion of the table for each status, which is inefficient. Materialized views are better for aggregations or joins, not for low-selectivity filters. They also add storage and maintenance costs, and the optimizer may not use them if the query is not a match. This is not a recommended optimization for this scenario.

• ✗

  Add a search optimization service on the 'status' column.

  Why it's wrong here

  Search optimization service is designed for point lookups on high-cardinality columns, such as finding a specific ID. It is not intended for low-cardinality columns like 'status' where a query returns a large fraction of rows. Using it here would not help because the query likely returns many rows, and the search optimization index would not provide significant pruning. It is also an additional cost and not a general performance fix for low-selectivity filters.

• ✗

  Create a clustering key on the 'status' column.

  Why it's wrong here

  Clustering on a low-cardinality column like 'status' is generally ineffective because clustering works best on high-cardinality columns that align with common filter predicates. With only 5 distinct values, each micro-partition will contain a mix of statuses, and pruning will not be able to skip many partitions. Clustering on such a column adds maintenance overhead without significantly improving pruning, so it is not the best choice.

• ✓

  There is no effective optimization for this query; full scan is expected.

  Why this is correct

  When filtering on a low-cardinality column with only 5 distinct values, the query will typically return a large percentage of the table (e.g., 20% per value). Snowflake's micro-partition pruning is ineffective because most partitions contain all statuses. Thus, a full table scan is unavoidable and is the expected behavior. Other optimizations like clustering or search optimization do not help. The engineer should accept that the scan is necessary unless the query can be rewritten to filter on a more selective column.