SnowPro Specialty: Gen AI Question 126
Single answerVECTOR_L2_DISTANCEA retail company stores product description embeddings in a Snowflake table named PRODUCT_EMBEDDINGS, where the column EMBEDDING is defined as VECTOR(FLOAT, 768). A data engineer receives a 768-dimension query embedding from an application and needs to return the 10 most semantically similar products directly in Snowflake SQL. The engineer wants to use Snowflake's VECTOR_L2_DISTANCE function correctly and avoid ranking mistakes. Which SQL pattern best meets this requirement?
- A
SELECT PRODUCT_ID, VECTOR_L2_DISTANCE(EMBEDDING, :QUERY_VEC) AS DIST FROM PRODUCT_EMBEDDINGS ORDER BY DIST ASC LIMIT 10;
- B
SELECT PRODUCT_ID, VECTOR_L2_DISTANCE(EMBEDDING, :QUERY_VEC) AS DIST FROM PRODUCT_EMBEDDINGS ORDER BY DIST DESC LIMIT 10;
- C
SELECT PRODUCT_ID, VECTOR_L2_DISTANCE(EMBEDDING, :QUERY_VEC) AS SIMILARITY FROM PRODUCT_EMBEDDINGS WHERE VECTOR_L2_DISTANCE(EMBEDDING, :QUERY_VEC) > 0.95 ORDER BY SIMILARITY DESC LIMIT 10;
- D
SELECT PRODUCT_ID, VECTOR_L2_DISTANCE(TO_ARRAY(EMBEDDING), :QUERY_VEC) AS DIST FROM PRODUCT_EMBEDDINGS ORDER BY DIST ASC LIMIT 10;
Show answer and explanation
Correct answer: A
Explanation
Snowflake's VECTOR_L2_DISTANCE function computes Euclidean distance between two vectors of the same dimensionality. In nearest-neighbor style retrieval, smaller L2 distance means the embeddings are closer, so the correct ranking is ORDER BY distance ascending. Candidates should recognize that distance functions are not similarity functions: unlike cosine similarity-style outputs where higher can mean more similar, L2 distance works in the opposite direction. In practice, both operands should be VECTOR values with matching dimensions, such as VECTOR(FLOAT, 768) compared against a 768-dimension query vector. A correct implementation for top-k retrieval therefore computes VECTOR_L2_DISTANCE and sorts ascending with LIMIT 10. This aligns with Snowflake vector function usage and common retrieval best practices for embedding search workloads.
- A. Correct.
Correct. VECTOR_L2_DISTANCE returns a distance value, where smaller values indicate vectors that are closer together. To retrieve the most semantically similar products using L2 distance, the results should be ordered in ascending order and limited to the top 10. This option correctly applies the function to the stored vector column and the query vector, then sorts from smallest distance to largest.
- B. Incorrect.
Incorrect. This reverses the ranking logic. With VECTOR_L2_DISTANCE, larger values mean vectors are farther apart, not more similar. Ordering by DIST DESC would return the least similar products first. This is a common mistake when people confuse distance metrics with similarity scores.
- C. Incorrect.
Incorrect. VECTOR_L2_DISTANCE produces a distance, not a similarity score, so aliasing it as SIMILARITY is misleading. In addition, filtering on values greater than 0.95 and sorting descending would favor less similar items, not more similar ones. This option reflects a common misconception that all vector comparison functions return higher-is-better scores.
- D. Incorrect.
Incorrect. VECTOR_L2_DISTANCE is intended to operate on VECTOR values of compatible dimensions. Converting the VECTOR column to an ARRAY with TO_ARRAY changes the data type and does not represent the standard correct usage pattern for this function. The misconception here is assuming that semistructured array conversion is interchangeable with native VECTOR operations.