Snowflake Gen AI Functions · Topic 3 of 26
Structured Outputs for AI_COMPLETE - JSON Schemas and Type Literals
Force AI_COMPLETE to return JSON that follows your schema. Know response_format, JSON schema rules, type literals, model notes, limits and the error codes.
- 4 min
- Structured outputs use
response_formatwith a JSON schema or aTYPEliteral. - The schema is verified token by token; it adds no compute cost, though bigger schemas mean more tokens.
- Top level must be an object;
requiredfields that cannot be extracted cause an error.
What it is
Structured outputs make AI_COMPLETE return a result that follows a schema you define, instead of free text. You give the function a response_format, and Snowflake verifies each generated token against that definition. The result is an object you can read with normal semi structured SQL, so a pipeline does not need to parse and repair model text.
This is the feature to name when a scenario says "the downstream process needs valid JSON with fixed fields". The same capability exists in the legacy SNOWFLAKE.CORTEX.COMPLETE through its options object, but AI_COMPLETE is the current surface.
How it works
Two ways to describe the format
| Method | How | Notes |
|---|---|---|
| JSON schema | response_format is an object with 'type': 'json' and a 'schema' | Top level type is object, with properties and required |
| SQL type literal | response_format starts with the keyword TYPE, for example TYPE OBJECT(name STRING, age INT) | Top level must be an OBJECT; works only with the single string version of AI_COMPLETE |
A JSON schema example:
SELECT AI_COMPLETE(
model => 'claude-sonnet-4-6',
prompt => 'Extract the person from: Maria Lopez, 34, works in Madrid.',
response_format => {
'type': 'json',
'schema': {
'type': 'object',
'properties': {
'name': {'type': 'string', 'description': 'Full name'},
'age': {'type': 'integer'},
'city': {'type': 'string'}
},
'required': ['name', 'age', 'city']
}
}
);
Schema fields
typecan beobject,array,string,number,integer,booleanornull.propertiesdefines the fields, and each can carry adescriptionthat guides the model.requiredlists the field names that must appear. If a required field cannot be filled, the call raises an error.additionalPropertiesmust befalsein every schema node for OpenAI (GPT) models, andrequiredmust then list every property. Other models do not need either setting.$defsand$reflet you define a reusable object once and reference it as#/$defs/name. References to external schemas are not supported.
Property names may use letters, digits, hyphens and underscores, up to 64 characters, and cannot contain spaces.
Type literal rules
STRING and VARCHAR map to JSON strings, and the length of a VARCHAR is not guaranteed to be honored. A FIXED number without a scale maps to a JSON integer, and other numeric types map to JSON numbers. OBJECT() with no fields, VARIANT, MAP and date or time types are not allowed.
Unsupported constraints
Keywords that add range or size rules cause an input schema validation error.
| Type | Unsupported keywords |
|---|---|
| integer | multipleOf |
| number | multipleOf, minimum, maximum, exclusiveMinimum, exclusiveMaximum |
| string | minLength, maxLength, format |
| array | uniqueItems, contains, minContains, maxContains, minItems, maxItems |
| object | patternProperties, minProperties, maxProperties, propertyNames |
Models and quality
Every model supported by AI_COMPLETE supports structured output. More powerful models generally produce better answers, especially for complex reasoning.
Practical tips from the documentation:
- Add clear
descriptiontext to each field. - Mark essential fields as
required. - For a medium complexity task, add "Respond in JSON" to the prompt.
- For complex reasoning, use a stronger model and describe the schema in the prompt too.
- Set
temperatureto 0 for the most consistent results.
A quick scenario test: if the answer must be a fixed set of fields that downstream SQL reads, use response_format, not a longer prompt asking for JSON.
Errors
| HTTP status | Cause |
|---|---|
| 400 | Request validation failed, for example the format object has no type |
| 400 | Schema validation failed, such as an unsupported keyword, an invalid property name or a bad $ref |
| 422 | The model output did not match the schema |
For the legacy function, TRY_COMPLETE returns NULL instead of raising model errors.
Cost
Billing follows tokens processed. Schema verification adds no extra compute cost, but a large or deeply nested schema increases input and output tokens and so raises the bill.
Do not rely on minItems, maximum or maxLength to bound an answer. They are rejected, so enforce limits in SQL after the call.
Common mistakes
Putting minimum or maxLength in the schema. These keywords raise a validation error.
Using a type literal with the prompt object form. Type literals work only with the single string version.
Leaving additionalProperties out with a GPT model. OpenAI models need it set to false in every node, with all properties listed as required.
Using property names with spaces. They are not allowed.
Treating a 422 as a syntax error. It means the model output did not match the schema; try a stronger model or a clearer description.
What to remember for the exam
- Structured outputs use
response_formatwith a JSON schema or aTYPEliteral. - The schema is verified token by token; it adds no compute cost, though bigger schemas mean more tokens.
- Top level must be an object;
requiredfields that cannot be extracted cause an error. - GPT models need
additionalProperties: falseeverywhere and every property inrequired. - Range and length keywords such as
minimum,maxLengthandminItemsare not supported. - Errors: 400 for bad requests or schemas, 422 for output that does not match.
Check yourself
Which argument of
AI_COMPLETEenforces a JSON shape?response_format, as a JSON schema object or aTYPEliteral.What extra schema settings do OpenAI GPT models need?
additionalPropertiesset tofalsein every node and every property listed inrequired.Why does a schema with
maxLengthfail?Length and range constraint keywords are unsupported and cause an input schema validation error.
Topic 3 of 26 in Snowflake Gen AI Functions
Test yourself on Snowflake Gen AI Functions
The full Snowflake GES-C02 question bank has 585 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.