AI_COMPLETE practically

When AI_COMPLETE is the right tool

Reach for AI_COMPLETE when:

• You need open-ended reasoning with custom rules in the prompt
• One call should do multiple things together — classification plus extraction plus reasoning
• Correctness matters enough that a frontier-model price tag pays off: high-stakes records, low-volume but high-value rows, anything writing to a final-state table where re-running is expensive

Skip it when:

• A specialized AISQL function (AI_SENTIMENT, AI_CLASSIFY, AI_FILTER, AI_EXTRACT, and friends) already does the job. They’re cheaper, faster, and more consistent.
• A deterministic SQL transformation would work fine.

Calling shapes

The single-string form covers 90% of work:

SELECT AI_COMPLETE(
  model  => 'claude-haiku-4-5',
  prompt => 'Summarize this review: ' || review_text
) FROM reviews;

There are two other forms: prompt + file (for image and document inputs) and prompt object (for multi-image or mixed content). One gotcha to know up front — response_format, the feature that makes structured output possible, works only with the single-string form. If you need both image input and structured output, you’ll need two passes.

The parameters you’ll actually touch

A quick tour before the bigger features.

model_parameters

The model knobs live in one object:

• temperature — 0 for extraction and classification, 0.7+ for creative writing. AI_COMPLETE defaults to 0, which is the right default.
• max_tokens — set it explicitly. Default truncation surprises people.
• top_p — leave it alone unless you know why.
• guardrails — see below.

response_format

Schema-constrained structured output. This is the main event; see the next section.

show_details

Setting show_details => TRUE returns the full inference payload — choices, tokens, timing, and which model actually ran — instead of a plain string. To pull the message back out:

AI_COMPLETE(..., show_details => TRUE):choices[0]:messages::STRING

The reason to use it isn’t the response itself — it’s everything around the response. The returned object exposes per-row token usage and the model name, which unlocks a few governance patterns:

• Cost attribution per row. Persist the usage object alongside your result. Then you can answer “how much did this customer’s enrichment cost last month?” by summing prompt_tokens + completion_tokens and joining to a model-price table. Without this, you’re stuck with account-level aggregates.
• Outlier detection. A row using 30K input tokens when the median is 800 is a signal — malformed input, runaway prompt, content type your pipeline wasn’t designed for. Worth alerting on before it becomes a bill.
• Guardrails audit. When guardrails => TRUE, the response includes guardrail_tokens in the usage block. Any row with guardrail_tokens > 0 had its output filtered — useful both for compliance reporting and for spotting prompt-injection attempts in user-facing apps.
• Drift detection over time. Average input tokens trending up week over week usually means your source data is growing (longer support emails, more verbose documents). Catch the trend before you catch the invoice.
• Model attribution. The returned model field tells you exactly which model handled the row. Useful if you use cross-region inference or have conditional model selection — the model that actually ran might not be the one you nominally requested.

Worth pairing with QUERY_TAG to attribute spend by use case across the warehouse, so you can roll cost up by team or pipeline without parsing query history.

return_error_details

By default, when a row errors, AI_COMPLETE returns NULL for that row and the query keeps running — multi-row failures don’t take down the whole statement. That’s the right default, but a silent NULL is easy to miss. Setting return_error_details => TRUE gives you back {value: …, error_message: …} where exactly one is non-null. Zero cost overhead. Use it in any pipeline that touches more than a thousand rows — it turns “why are 3% of my outputs NULL?” from a forensics problem into a one-line filter.

Pár slov ke guardrails

Setting model_parameters => {‘guardrails’: TRUE} routes the model’s output through Meta’s Llama Guard 3. If the response gets flagged for unsafe content, it’s replaced with the exact string “Response filtered by Cortex Guard” — every time, byte-identical.

Here’s the actually-useful part: modern LLMs already refuse most unsafe prompts on their own. So why pay for the filter? The reason is deterministic refusal detection. Without guardrails, every model phrases its refusals differently and the wording drifts across versions — detecting “did the model refuse?” downstream requires NLP. With guardrails on, your check is one string compare:

WHERE response = 'Response filtered by Cortex Guard'

Worth turning on for user-facing apps and content moderation. Worth skipping for internal batch enrichment on data you control. Just be careful about the response format which changes depending on the parameters used.

Putting it together

SELECT
  email_id,
  AI_COMPLETE(
    model => 'claude-haiku-4-5',
    prompt => 'Extract the issue: ' || body,
    model_parameters => {'temperature': 0, 'max_tokens': 500},
    return_error_details => TRUE
  ) AS result
FROM emails;

That’s the production shape of a call. Everything from here on builds on it.

Structured outputs — the killer feature

The problem it solves

Default LLM output is a blob of text. To use it as data, you’d cram “RETURN ONLY VALID JSON, NO MARKDOWN, NO PREAMBLE, START WITH {” into the prompt, hope, and then TRY_PARSE_JSON defensively. With response_format, the engine validates every output token against your schema. Output comes back as a real OBJECT/VARIANT. No string parsing.

Two syntaxes for the same thing

TYPE literal — SQL-native, concise:

response_format => TYPE OBJECT(
  sentiment STRING,
  score     FLOAT,
  topics    ARRAY(STRING)
)

JSON schema — more expressive:

response_format => {
  'type': 'json',
  'schema': {
    'type': 'object',
    'properties': {
      'sentiment': {'type': 'string', 'enum': ['positive','negative','neutral']},
      'score':     {'type': 'number'},
      'topics':    {'type': 'array', 'items': {'type': 'string'}}
    },
    'required': ['sentiment', 'score']
  }
}

Both produce the same governed output. TYPE is shorter and reads like a SQL CREATE TABLE. JSON gives you what TYPE can’t — enum, description, $ref, and the optional-vs-required distinction. In a TYPE literal, every field you define is expected to appear in the output — there’s no syntax for marking a field optional. In JSON schema, only fields listed in required are mandatory; the rest are optional and the model can omit them when they don’t apply to the input. If you need that flexibility (e.g. an affected_skus array that should be absent when no SKUs are mentioned), you need the JSON form.

What the schema controls (in JSON form)

The engine guarantees the shape of the output. Within that shape, the useful features are:

• type — string, number, integer, boolean, array, object
• enum — output must be one of the listed values, no exceptions
• required — listed fields must appear, or the call errors
• properties — the named fields inside an object and their types
• items — the type of elements inside an array
• description — passed to the model as a hint per field (not validated)
• $ref / $defs — schema reuse, Pydantic-compatible since May 2025

The mental model: Snowflake guarantees the shape. Values within the shape are your job. Anything like number ranges, string lengths, or regex formats — validate those in SQL after the call.

Worked example: support ticket triage

SELECT 
  email_id,
  AI_COMPLETE(
    model => 'claude-haiku-4-5',
    prompt => 'Extract a triage record from this support email:\n\n' || email_body,
    model_parameters => {'temperature': 0, 'max_tokens': 1000},
    response_format => {
      'type': 'json',
      'schema': {
        'type': 'object',
        'properties': {
          'priority': {
            'type': 'string',
            'enum': ['low', 'medium', 'high', 'critical'],
            'description': 'critical = production down; high = blocked workflow; medium = degraded; low = question'
          },
          'category': {
            'type': 'string',
            'enum': ['billing', 'auth', 'data_quality', 'performance', 'feature_request', 'other']
          },
          'summary': {
            'type': 'string',
            'description': 'One neutral sentence, no opinions.'
          },
          'is_escalation': {
            'type': 'boolean',
            'description': 'True if customer asks for a manager, refund, or threatens to leave.'
          }
        },
        'required': ['priority', 'category', 'summary', 'is_escalation']
      }
    }
  ) AS triage
FROM support_emails;

Unpacking the result is plain SQL — no PARSE_JSON, no defensive TRY_:

SELECT
  email_id,
  triage:priority::STRING       AS priority,
  triage:category::STRING       AS category,
  triage:summary::STRING        AS summary,
  triage:is_escalation::BOOLEAN AS is_escalation
FROM extracted_tickets;

Schema design tips

• Every categorical field should be an enum. Free strings join badly with reference tables, and the model will drift between “high”, “High”, and “HIGH!!” if you let it.
• description is for the model, not for you. Write it like you’re explaining the rule to a new analyst. Snowflake’s own docs call this out as the highest-leverage way to improve accuracy.
• Descriptions are tokens. They’re billed as input on every row. Tighten them, or drop them, once you’ve validated quality on a sample.

Tokenomics

AI_COMPLETE cost comes down to a few decisions: which model, how big your inputs are, and how many calls you make per row. None of them are independent — they trade off against each other in ways that matter once you scale past a few thousand rows.

Picking a model

Model choice has roughly a 60x spread between the cheapest open-source models and the frontier ones. The economics deserve their own article. Default rule of thumb: start with smaller models like llama or claude-haiku, and escalate only when quality fails on a real sample.

But check the context window first — it’s often the binding constraint, not “smartness.” Every model has a hard token ceiling that has to cover the prompt, the input text, the schema, and the output, all combined. Rough reference points: llama-3.1-8b tops out at 8K tokens, llama-3.3-70b at 128K, claude-haiku-4-5 at 200K, claude-sonnet-4-8 at 1M. If your input doesn’t fit, the call errors out — it doesn’t silently truncate, and the rows that fail tend to be the longest, most interesting ones. Use AI_COUNT_TOKENS(‘model’, text) to check the 99th percentile of your input lengths before picking. A bigger-context model that handles your data beats a smaller, smarter model that 2% of rows quietly break on.

Bundling vs specialized: a tradeoff worth thinking about

A real choice you’ll face: should one AI_COMPLETE call with a schema do the work of five specialized AISQL calls (AI_SENTIMENT, AI_CLASSIFY, AI_EXTRACT, and friends)? It depends, and it’s worth working out the math for your own data.

Start with the reliability tradeoff, because it usually decides which way you go. Bundling concentrates failure. A single schema-validation failure on a row loses all five fields at once. Five separate specialized calls might fail on one and still give you four. If those fields feed a final-state table where partial completeness matters — or where re-running is expensive — that’s a real cost you have to weigh.

Now the cost math. Specialized functions still send the input text through a model under the hood. Calling five of them on the same row means the input is tokenized and processed five times. A rough per-row cost model:

specialized:  5 × (input_tokens × price + small_output × price + overhead)
bundled:          (input_tokens × price + larger_output × price + overhead)

For a 1,500-token input, the specialized path pays ~7,500 input tokens just on duplication, before any output cost. The bundled path pays 1,500. That gap is usually wide enough that the saved budget pays for stepping up to a bigger, smarter model on the bundled call — and you still come out ahead.

Cases where it doesn’t always make sense to bundle:

• Inputs are very short. A product name has barely any duplication penalty.
• The tasks are unrelated and naturally run at different pipeline stages.
• You only need some of the fields some of the time. Paying to compute all five every row is wasteful if three are needed for 5% of rows.

The honest summary: bundling is a cost win, not a reliability win. Big inputs with related fields → bundle. Short inputs or independent fields → the specialized functions stay competitive and keep the failure surface smaller.é vstupy nebo nezávislá pole → specializované funkce zůstávají konkurenceschopné a udržují menší prostor pro selhání.

Error handling

The structured-output docs (here) document three failure modes, each with a different fix.

Request validation failed (400) — your response_format argument is malformed. Common causes: wrong syntax (response_format = instead of response_format =>), passing a string where a SQL OBJECT was expected, missing the ‘type’: ‘json’ wrapper.

Fix: read the literal error message. Snowflake’s parser is decent about saying which key is missing or malformed.

Input schema validation failed (400) — the schema is structurally OK but its contents are bad. The most common cause is using a JSON Schema keyword that Snowflake doesn’t support.

Fix: strip the unsupported keywords. Move value-level checks — ranges, lengths, regex — out of the schema and into a SQL validation pass after the call.

Model output validation failed (422) — engine and schema are fine, but the model couldn’t produce conforming output. Most often this means it ran out of max_tokens mid-generation and the JSON is truncated.

Fix, cheapest to most expensive: raise max_tokens first. If that doesn’t help or you can’t do that, you can simplify the schema (drop optional fields, flatten nesting). The obvious fix is trying a bigger model. If you’re already on a frontier model with generous tokens and still failing, the prompt is asking for something the source text doesn’t actually contain — the data or the prompt needs work, not the call.

The quarantine pattern

When you’re processing millions of rows, a small percentage will always error. The clean pattern is a three-stage pipeline: cheap-model first pass with return_error_details => TRUE, second step splits clean rows from errored rows, lastly retry the errored rows on a bigger model.

The small model handles the 95% that works. The expensive model only touches the 5% that didn’t. Don’t run frontier models on rows an 8B model would have nailed.

Practical tips

Two non-obvious things that compound at scale:

• Prompts are tokens, and prompt tokens are billed on every row. “Please carefully analyze the following text and provide me with the customer’s most significant pain point” → “give customer’s key pain point.” Same result, roughly 60% fewer input tokens. On 10 million rows the math compounds into real money. Think about prompts as “The minimum necessary to be correct.”
• Warehouse sizing barely matters for Cortex calls. Inference runs on Snowflake’s GPU infrastructure, not on your warehouse. Scaling up to XL won’t make AI_COMPLETE faster — you’ll just pay more for the same throughput. XS or S is plenty for the orchestration work. Save the bigger warehouses for the SQL that wraps the Cortex call.

AI_COMPLETE opens up a lot of work that wasn’t possible in SQL before — reasoning, extraction, classification, summarization, all in a single column expression. It also opens up a lot of new ways to lose money: silent NULL failures, ten-million-row runaway bills, schemas that look right but quietly produce malformed rows. 

None of this is a reason to skip AI_COMPLETE — it’s the reason this article exists.

Jan Šulc

Business Analyst at Revolt BI with hands-on experience in Snowflake and modern data platforms. 

---

He designs scalable data models, automates data pipelines, and works on applying AI capabilities within enterprise analytics environments. Jan bridges business needs with technical architecture to help organizations operationalize data and AI.

Book a free 30-minute consultation. We’ll pick one use case from your data and show you exactly what it will cost in credits.