SnowPro Specialty: Gen AI Question 35
Single answerSQLA data engineering team is building a retrieval pipeline in Snowflake for a customer-support copilot. They store support articles in a table named KB_ARTICLES with columns ARTICLE_ID, TITLE, BODY, and CATEGORY. The team wants to generate one text field per row that can be sent to an embedding function from SQL. They need the SQL expression to meet these requirements: preserve readable labels for each field, avoid returning NULL for the full text when BODY is NULL, and include line breaks between sections so chunking produces cleaner text. Which SQL expression best meets these requirements?
- A
TITLE || BODY || CATEGORY
- B
CONCAT('Title: ', TITLE, '\nBody: ', BODY, '\nCategory: ', CATEGORY)
- C
CONCAT_WS('\n', CONCAT('Title: ', COALESCE(TITLE, '')), CONCAT('Body: ', COALESCE(BODY, '')), CONCAT('Category: ', COALESCE(CATEGORY, '')))
- D
ARRAY_CONSTRUCT(TITLE, BODY, CATEGORY)
Show answer and explanation
Correct answer: C
Explanation
The best answer is Option 3 because it addresses all three operational requirements for Gen AI preprocessing in Snowflake SQL: readable field labels, protection against NULL propagation, and explicit separators for cleaner chunking and retrieval. In Snowflake, string concatenation and CONCAT can produce NULL results when any input is NULL, so COALESCE is a best practice when assembling text from nullable columns. CONCAT_WS is also a practical choice because it inserts a delimiter between each segment, which helps create consistent multi-section documents for embedding generation. This pattern aligns with common Snowflake SQL practices for preparing unstructured or semi-structured business text before using vectorization or retrieval workflows.
- A. Incorrect.
Incorrect. Using the concatenation operator without labels makes the generated text harder for downstream retrieval and debugging because field boundaries are unclear. More importantly, if any concatenated value is NULL, the result of the expression becomes NULL in Snowflake SQL. That violates the requirement to avoid a NULL full-text value when BODY is NULL.
- B. Incorrect.
Incorrect. This option adds readable labels and line breaks, but CONCAT in Snowflake returns NULL if any argument is NULL. If BODY is NULL, the entire result becomes NULL, which fails the stated requirement. This is a common mistake when preparing text for embedding generation in SQL pipelines.
- C. Correct.
Correct. This expression preserves labels, inserts line breaks between sections, and uses COALESCE so a NULL value in BODY does not nullify the entire assembled text. CONCAT_WS is appropriate for joining multiple string segments with a delimiter, and wrapping each source column with COALESCE ensures the final text remains usable for embedding and chunking. This is the most robust SQL pattern for creating structured text from nullable columns.
- D. Incorrect.
Incorrect. ARRAY_CONSTRUCT creates a semi-structured ARRAY value rather than a readable text string. Embedding workflows in SQL typically need a single VARCHAR input containing the textual content to embed. While arrays can be useful in other contexts, this does not meet the requirement to produce labeled text with line breaks.