SnowPro Specialty: Gen AI Question 119
Single answerVector functionsA retail company stores product-description embeddings in a Snowflake table named PRODUCT_EMBEDDINGS with columns PRODUCT_ID and EMBED VECTOR(FLOAT, 768). At query time, an application generates a 768-dimension embedding for the user's search text and passes it into Snowflake. The team wants to return the 10 most semantically similar products using Snowflake SQL vector functions, while avoiding mistakes that could invert ranking logic. Which SQL pattern is the BEST choice?
- A
SELECT PRODUCT_ID FROM PRODUCT_EMBEDDINGS ORDER BY VECTOR_COSINE_SIMILARITY(EMBED, :query_vec) DESC LIMIT 10;
- B
SELECT PRODUCT_ID FROM PRODUCT_EMBEDDINGS ORDER BY VECTOR_L2_DISTANCE(EMBED, :query_vec) DESC LIMIT 10;
- C
SELECT PRODUCT_ID FROM PRODUCT_EMBEDDINGS WHERE VECTOR_INNER_PRODUCT(EMBED, :query_vec) < 0 ORDER BY PRODUCT_ID LIMIT 10;
- D
SELECT PRODUCT_ID FROM PRODUCT_EMBEDDINGS ORDER BY VECTOR_COSINE_SIMILARITY(EMBED, :query_vec) ASC LIMIT 10;
Show answer and explanation
Correct answer: A
Explanation
For Snowflake vector functions, the key practical skill is choosing both the correct metric and the correct ordering logic. VECTOR_COSINE_SIMILARITY is commonly used for semantic search because higher similarity scores indicate closer matches, so DESC is appropriate. By contrast, VECTOR_L2_DISTANCE is a distance function, so lower values are better and would need ASC ordering to support nearest-neighbor retrieval. VECTOR_INNER_PRODUCT may also be usable depending on embedding model behavior and normalization strategy, but the query must still rank on that value correctly. In real implementations, teams often make ranking mistakes by confusing similarity functions with distance functions. Snowflake documentation for vector data types and vector similarity functions emphasizes using VECTOR_COSINE_SIMILARITY, VECTOR_L2_DISTANCE, and VECTOR_INNER_PRODUCT appropriately based on the embedding/search design.
- A. Correct.
Correct. VECTOR_COSINE_SIMILARITY returns higher values for more similar vectors, so ordering in descending order returns the nearest semantic matches first. This pattern directly matches the requirement to retrieve the top 10 most semantically similar products and avoids a common ranking mistake of sorting in the wrong direction.
- B. Incorrect.
Incorrect. VECTOR_L2_DISTANCE is a distance metric, so smaller values indicate more similar vectors. Ordering by DESC would return the farthest vectors first, which inverts the desired ranking. A candidate might choose this option because L2 distance is valid for similarity search, but the sort direction here is wrong.
- C. Incorrect.
Incorrect. VECTOR_INNER_PRODUCT can be used in some retrieval approaches, but this query does not actually rank by similarity. Instead, it filters to only negative inner products and then orders by PRODUCT_ID, which is unrelated to semantic closeness. This reflects a common misconception that simply using a vector function somewhere in the query is enough.
- D. Incorrect.
Incorrect. With VECTOR_COSINE_SIMILARITY, larger values mean vectors are more alike. Sorting ascending would place the least similar results first. This is a realistic error because cosine similarity and cosine distance are often confused.