SnowPro Specialty: Gen AI Question 92
Single answerCOMPLETE Structured OutputsA data engineering team is building a Snowflake SQL pipeline that uses the COMPLETE function to extract support ticket details into downstream tables. Each model response must be machine-readable and contain exactly these fields: "category" (string), "priority" (one of HIGH, MEDIUM, LOW), and "customer_impact" (integer from 1 to 5). The team wants to minimize brittle post-processing and ensure invalid responses are rejected as early as possible. Which approach best meets this requirement when using COMPLETE Structured Outputs?
- A
Call COMPLETE with a response format that includes a JSON schema defining the required properties, enum values for priority, and numeric constraints for customer_impact.
- B
Call COMPLETE with a prompt that says "Return valid JSON with category, priority, and customer_impact" and rely on TRY_PARSE_JSON after generation.
- C
Call COMPLETE without structured outputs, then use REGEXP_SUBSTR to extract the three fields from the returned text before loading the table.
- D
Call COMPLETE with a response format that requests CSV output because tabular text is easier to validate than JSON in SQL.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use COMPLETE Structured Outputs with a JSON schema in the response format. In Snowflake, Structured Outputs are intended to make LLM responses predictable and machine-consumable, which is especially important for ETL and SQL-driven pipelines. Compared with prompt-only JSON instructions, schema-guided generation provides stronger guarantees around required properties and valid values. This is aligned with best practices for production GenAI pipelines: constrain output shape at generation time, then store or transform the result with minimal custom parsing. Regex extraction and free-form text handling are common anti-patterns because they are fragile and difficult to maintain. Candidates should recognize that when the requirement is strict contract enforcement for COMPLETE output, schema-based Structured Outputs are the most appropriate solution.
- A. Correct.
Correct. COMPLETE Structured Outputs are designed for this exact use case: enforcing a defined JSON structure at generation time. By supplying a JSON-schema-based response format, the team can require the presence of specific fields, constrain "priority" to allowed enum values, and restrict "customer_impact" to an integer range. This reduces downstream parsing errors and is the most reliable way to produce machine-readable output for SQL pipelines.
- B. Incorrect.
Incorrect. Prompting the model to return JSON can help, but prompt-only formatting is not as reliable as structured outputs. TRY_PARSE_JSON only validates whether the result is syntactically valid JSON; it does not by itself guarantee required fields, allowed enum values, or numeric bounds unless additional validation logic is added afterward. This approach increases post-processing and does not reject invalid structure as early as possible.
- C. Incorrect.
Incorrect. Regex-based extraction from free-form model text is brittle and contrary to the requirement to minimize post-processing. It can break when wording changes, when fields appear in a different order, or when the model adds commentary. Structured Outputs exist specifically to avoid this kind of fragile parsing pattern.
- D. Incorrect.
Incorrect. CSV is not the appropriate choice when the requirement is to enforce typed, named fields with constraints such as enums and numeric ranges. JSON structured outputs are better suited for schema validation and downstream machine processing in Snowflake. CSV also makes nested or strictly typed validation harder and does not directly address the need for field-level constraints.