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.

• ✗

  Flatten the VARIANT column into a Materialized View.

  Why it's wrong here

  While flattening into a Materialized View can improve performance, it is expensive to maintain for a 50TB table and requires significant extra storage. For simple point lookups, it is often overkill compared to the Search Optimization Service, which provides similar performance benefits with less management overhead and complexity.

• ✓

  Enable Search Optimization on the VARIANT column's specific paths.

  Why this is correct

  Snowflake's Search Optimization Service can be configured to index specific fields within a VARIANT column. This allows the engine to quickly identify which micro-partitions contain the specific nested value, avoiding a full scan of the semi-structured data and providing high-performance lookups on very large datasets.

• ✗

  Cluster the table based on the extracted value from the VARIANT column.

  Why it's wrong here

  Clustering on an expression (like an extracted VARIANT field) is possible, but it requires reordering the entire 50TB table. This is extremely credit-intensive and may not be beneficial if the table is already clustered by a more important dimension like a primary key or a timestamp for other queries.

• ✗

  Use a standard VIEW to pre-parse the VARIANT data for all users.

  Why it's wrong here

  A standard view does not persist the parsed data; it simply provides a shortcut for the SQL logic. When the view is queried, Snowflake still has to scan the 50TB of VARIANT data and parse it on the fly, which does not provide any performance optimization for the underlying scan.