SnowPro Specialty: Gen AI Question 134
Single answerTRY_COMPLETEA data engineering team uses Cortex AISQL to enrich support tickets with short AI-generated summaries before loading them into a downstream reporting table. They have found that some prompts occasionally fail because of malformed input or transient model issues, but they do not want the entire SQL statement to fail. They also want failed generations to be stored as NULL so they can be reviewed later. Which approach best meets this requirement when generating completions in Snowflake?
- A
Use TRY_COMPLETE so that completion errors return NULL instead of failing the SQL statement.
- B
Use COMPLETE with a CASE expression; COMPLETE automatically converts model errors into empty strings.
- C
Wrap COMPLETE inside TRY_CAST so Snowflake treats AI generation failures as SQL conversion errors and returns NULL.
- D
Use TRY_COMPLETE only in JavaScript stored procedures, because it is not supported in standard SQL queries.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use TRY_COMPLETE. In Snowflake Cortex AISQL, TRY_COMPLETE provides fault-tolerant text generation by returning NULL when the completion call fails, allowing the rest of the query or pipeline to continue processing. This is especially valuable in ETL or enrichment workloads where a small number of bad prompts or transient failures should not block the entire batch. By contrast, COMPLETE is used when the caller wants standard completion behavior and is willing to let errors surface. Best practice in production workflows is to use TRY_COMPLETE when resilience is more important than strict fail-fast behavior, then monitor NULL outputs for remediation or reprocessing.
- A. Correct.
Correct. TRY_COMPLETE is designed for this exact pattern: it behaves like COMPLETE but returns NULL when the completion operation fails instead of raising an error that aborts the statement. This is useful in production pipelines where the team wants resilient batch processing and wants to identify failed rows later by checking for NULL results.
- B. Incorrect.
Incorrect. COMPLETE does not automatically suppress generation errors or convert them to empty strings. If COMPLETE encounters a failure, the SQL statement can fail. A CASE expression can control prompt logic, but it does not transform model or service errors into safe NULL outputs by itself.
- C. Incorrect.
Incorrect. TRY_CAST handles SQL data type conversion issues, not AI inference or model execution failures. Wrapping COMPLETE in TRY_CAST would not provide the desired error-handling behavior for completion failures. This reflects a common misconception that all TRY_* functions are interchangeable across unrelated failure types.
- D. Incorrect.
Incorrect. TRY_COMPLETE is intended for use in SQL contexts where developers want fault-tolerant completion behavior. It is not limited to JavaScript stored procedures. This option incorrectly narrows its supported usage and would lead teams away from the simplest SQL-native solution.