SnowPro Specialty: Gen AI Question 124
Single answerVECTOR_L1_DISTANCEA retail company stores product embeddings in a Snowflake table named PRODUCT_EMBEDDINGS, where the EMBEDDING column is a VECTOR(FLOAT, 768). A data engineer is building a similarity search query to find the closest products to a shopper's query embedding that is also represented as VECTOR(FLOAT, 768). The engineer wants to use Manhattan distance and ensure the query returns the most similar products first. Which SQL approach correctly meets this requirement?
- A
SELECT PRODUCT_ID, VECTOR_L1_DISTANCE(EMBEDDING, :QUERY_EMBEDDING) AS DIST FROM PRODUCT_EMBEDDINGS ORDER BY DIST ASC LIMIT 10;
- B
SELECT PRODUCT_ID, VECTOR_L1_DISTANCE(EMBEDDING, :QUERY_EMBEDDING) AS DIST FROM PRODUCT_EMBEDDINGS ORDER BY DIST DESC LIMIT 10;
- C
SELECT PRODUCT_ID, VECTOR_L1_DISTANCE(TO_ARRAY(EMBEDDING), TO_ARRAY(:QUERY_EMBEDDING)) AS DIST FROM PRODUCT_EMBEDDINGS ORDER BY DIST ASC LIMIT 10;
- D
SELECT PRODUCT_ID, VECTOR_L1_DISTANCE(EMBEDDING, :QUERY_EMBEDDING) AS DIST FROM PRODUCT_EMBEDDINGS WHERE DIST < 0.5 ORDER BY DIST ASC LIMIT 10;
Show answer and explanation
Correct answer: A
Explanation
VECTOR_L1_DISTANCE in Snowflake returns the Manhattan distance between two VECTOR values. In retrieval scenarios, distance functions are interpreted so that lower scores mean higher similarity. Therefore, the standard pattern is to compute the distance and sort ascending to get the nearest matches. Both inputs must be compatible VECTOR values with the same element type and dimensionality, which fits the scenario's VECTOR(FLOAT, 768) data. Snowflake's vector similarity functions are used directly on VECTOR data types, so converting embeddings to arrays is not the correct approach. Also, when filtering on computed distances, remember standard SQL scoping rules: a SELECT-list alias such as DIST is not available in the WHERE clause of the same SELECT. This question reflects practical usage of Snowflake vector functions for semantic search and nearest-neighbor retrieval.
- A. Correct.
Correct. VECTOR_L1_DISTANCE computes the L1 (Manhattan) distance between two vectors of the same type and dimension. For distance-based similarity search, smaller values indicate more similar vectors, so ordering by DIST ASC returns the nearest products first. This is the appropriate pattern for top-K nearest-neighbor style retrieval using Manhattan distance in Snowflake SQL.
- B. Incorrect.
Incorrect. Although VECTOR_L1_DISTANCE is the right function for Manhattan distance, ordering by DIST DESC would return the least similar products first because larger L1 distances mean the vectors are farther apart.
- C. Incorrect.
Incorrect. VECTOR_L1_DISTANCE is intended to operate on VECTOR values, not ARRAY values. Converting vectors to arrays is unnecessary and does not match the expected usage pattern for Snowflake vector similarity functions. A common misconception is that embeddings must be transformed before comparison, but Snowflake's vector functions are designed to work directly with VECTOR columns and VECTOR query values.
- D. Incorrect.
Incorrect. The use of VECTOR_L1_DISTANCE and ascending sort is conceptually correct, but this query is invalid as written because the SELECT alias DIST cannot be referenced in the WHERE clause at the same query level in standard Snowflake SQL. A developer would need to repeat the expression, use a subquery, or use QUALIFY/HAVING where appropriate. The issue here is SQL evaluation order, not vector math.