Question 90
An analytics engineering team reports this behavior: A business query needs the ten newest transactions but sorts the entire customer history after transforming the timestamp to a string. What is the principal technical cause in this scenario?
Explanation
Correct Answer
The described symptoms indicate that the query does unnecessary ordering work and can lose proper timestamp ordering. The appropriate resolution is: Use a typed timestamp ORDER BY with a suitable LIMIT on the required filtered rows. Confirm with: The ten newest records are correct and sorting work is reduced in Query Profile.
Incorrect Answers
The predicate obscures a simple selective filter and may increase scanned work. “Rewrite the query to filter on the directly comparable typed column where possible” would address that alternative cause, not the condition in this question: the query does unnecessary ordering work and can lose proper timestamp ordering.
“Evaluate Snowflake Search Optimization Service for the supported lookup predicate” responds to this separate finding: selective point lookups have poor access-path selectivity under the current table layout. This question instead needs “Use a typed timestamp ORDER BY with a suitable LIMIT on the required filtered rows”, so the alternative does not meet the stated condition.
Underlying table changes can invalidate eligibility for the persisted query result cache. “Validate freshness and cache eligibility before assuming identical SQL skips execution” applies to that separate problem. The observed case is a business query needs the ten newest transactions but sorts the entire customer history after transforming the timestamp to a string; choosing this alternative would address the wrong failure mode.