SnowPro Specialty: Gen AI Question 133
Single answerTRY_COMPLETEA data engineering team is enriching support tickets in Snowflake by calling a Cortex text-generation model from SQL. They need a nightly batch job to process millions of rows without failing the entire statement when an individual model call encounters an error, such as invalid input or a transient model issue. They also want downstream SQL to be able to identify which rows failed and which succeeded. Which approach best meets these requirements?
- A
Use COMPLETE so Snowflake automatically skips failed rows and returns NULL for those records.
- B
Use TRY_COMPLETE so rows with successful model inference return a result, while rows with errors return NULL instead of aborting the statement.
- C
Wrap COMPLETE inside a TRY_CAST expression so generation errors are converted to NULL values at runtime.
- D
Use AI_CLASSIFY instead of text generation functions, because classification functions do not fail row by row.
Show answer and explanation
Correct answer: B
Explanation
The key requirement is row-level fault tolerance during large-scale batch inference in SQL. TRY_COMPLETE is the appropriate choice when the team wants generation to proceed for valid rows while returning NULL for rows where inference fails, rather than aborting the entire statement. This pattern aligns with Snowflake's broader TRY_* design philosophy: return NULL on failure so downstream SQL can handle exceptions explicitly. In practice, teams often pair TRY_COMPLETE with logic such as IS NULL checks, COALESCE, error-tracking columns, or retry workflows. COMPLETE is appropriate when a hard failure should stop execution, but it is not the best fit for resilient batch processing. For Snowflake Cortex AISQL, the distinction between COMPLETE and TRY_COMPLETE is important for production pipelines that need graceful degradation and post-run observability.
- A. Incorrect.
Incorrect. COMPLETE does not provide the fail-safe row-level behavior described here. If a call errors during query execution, the statement can fail rather than quietly returning NULL for only the problematic rows. The misconception is assuming COMPLETE behaves like Snowflake's TRY_* family of functions.
- B. Correct.
Correct. TRY_COMPLETE is designed for resilient generation workflows where the caller wants SQL execution to continue even if some model invocations fail. Instead of raising an error that aborts the statement, TRY_COMPLETE returns NULL for failed invocations. This allows downstream logic, such as CASE expressions, filters, or audit columns, to distinguish successful rows from failed ones in batch pipelines.
- C. Incorrect.
Incorrect. TRY_CAST only applies to data type conversion, not model inference errors from COMPLETE. It cannot intercept failures produced by the Cortex generation function itself. Someone might choose this if they are familiar with TRY_CAST handling conversion issues and mistakenly generalize that pattern to AI function execution errors.
- D. Incorrect.
Incorrect. AI_CLASSIFY is a different function for classification tasks and does not replace a text-generation requirement. Also, switching functions does not address the core requirement of safely handling row-level inference failures in a batch generation workload. This distractor reflects the misconception that another AI function inherently provides better error handling for unrelated use cases.