SnowPro Specialty: Gen AI Question 125
Single answerVECTOR_L2_DISTANCEA data engineering team stores product description embeddings in a Snowflake table named PRODUCT_EMBEDS with columns PRODUCT_ID and EMBED_VEC. EMBED_VEC is a VECTOR(FLOAT, 768). For a semantic search feature, the application generates a query embedding at runtime and wants to return the 10 most similar products using Euclidean distance. Which SQL pattern correctly uses VECTOR_L2_DISTANCE for this requirement?
- A
SELECT PRODUCT_ID FROM PRODUCT_EMBEDS ORDER BY VECTOR_L2_DISTANCE(EMBED_VEC, :query_vec) ASC LIMIT 10;
- B
SELECT PRODUCT_ID FROM PRODUCT_EMBEDS ORDER BY VECTOR_L2_DISTANCE(EMBED_VEC, :query_vec) DESC LIMIT 10;
- C
SELECT PRODUCT_ID FROM PRODUCT_EMBEDS WHERE VECTOR_L2_DISTANCE(EMBED_VEC, :query_vec) > 0.9 LIMIT 10;
- D
SELECT PRODUCT_ID FROM PRODUCT_EMBEDS ORDER BY VECTOR_L2_DISTANCE(TO_ARRAY(EMBED_VEC), :query_vec) ASC LIMIT 10;
Show answer and explanation
Correct answer: A
Explanation
VECTOR_L2_DISTANCE computes Euclidean distance between two vectors and is used when embeddings are represented as Snowflake VECTOR data. For retrieval tasks, the closest matches are the rows with the smallest distance, so results should be ordered ascending by VECTOR_L2_DISTANCE and then limited to the desired count. A key implementation requirement is that both operands must be compatible vectors with the same dimensionality, such as VECTOR(FLOAT, 768) compared with another 768-dimensional vector. In Snowflake best practice for vector similarity search, choose the distance function that aligns with your embedding model and ranking strategy, and remember that distance metrics rank lower values as better matches.
- A. Correct.
Correct. VECTOR_L2_DISTANCE returns the Euclidean distance between two vectors of the same dimension. In similarity search using distance, smaller values indicate more similar vectors, so the correct pattern is to sort in ascending order and return the top results. This is the standard way to perform nearest-neighbor style retrieval with L2 distance in Snowflake SQL when embeddings are stored in VECTOR columns.
- B. Incorrect.
Incorrect. Sorting by descending L2 distance returns the farthest vectors first, which is the opposite of semantic similarity retrieval. This is a common mistake when someone confuses similarity scores, where larger may be better, with distance metrics, where smaller is better.
- C. Incorrect.
Incorrect. A fixed threshold such as greater than 0.9 does not implement a top-10 nearest-neighbor search and is directionally wrong for distance-based similarity in many cases. Lower distances indicate higher similarity, so filtering on values greater than a threshold would tend to keep less similar rows. Also, without ORDER BY, the query does not guarantee the nearest 10 results.
- D. Incorrect.
Incorrect. VECTOR_L2_DISTANCE is intended to operate on VECTOR values. Converting a VECTOR column to ARRAY is not the correct pattern for this function and introduces a type mismatch with the query vector unless the query vector is also converted appropriately. The practical and supported approach is to keep both operands as VECTOR values of the same element type and dimension.