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.

• ✗

  The fast column has a lower cardinality, so the optimizer can use metadata to skip partitions.

  Why it's wrong here

  Lower cardinality generally makes pruning less effective, not more, because each distinct value appears in many partitions. Snowflake stores per-column min and max metadata, but a low-cardinality column rarely narrows the candidate partitions much. The observed speed difference is better explained by physical clustering than by cardinality alone, so this reasoning is incorrect for the scenario.

• ✓

  The fast column is a clustering key, so the optimizer can prune micro-partitions for that predicate.

  Why this is correct

  Clustering keys cause related rows to be co-located in the same micro-partitions, so equality predicates on the clustering column allow the optimizer to skip partitions that cannot contain matches. The other column lacks that physical ordering, so the scan must read all partitions. This explains the difference in performance between the two equality filters and is the most likely cause.

• ✗

  The fast column is part of the table's primary key, which automatically prunes partitions.

  Why it's wrong here

  Primary keys in Snowflake are declarative and not enforced, and they do not by themselves reorganize data or enable pruning. While a primary key might correlate with a clustering choice, the pruning benefit comes from physical clustering, not from the constraint. The scenario describes performance driven by data layout, so the primary key explanation is not the underlying cause.

• ✗

  The fast column is defined with a NOT NULL constraint, which enables partition skipping.

  Why it's wrong here

  NOT NULL constraints affect integrity and can influence null-handling and some optimizations, but they do not create the partition-pruning behavior described. Pruning depends on how data is physically organized across micro-partitions relative to predicate values. A NOT NULL attribute by itself does not explain why one equality filter avoids a full scan while the other does not.