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
In a nutshell
  • Structured outputs use response_format with a JSON schema or a TYPE literal.
  • The schema is verified token by token; it adds no compute cost, though bigger schemas mean more tokens.
  • Top level must be an object; required fields 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

MethodHowNotes
JSON schemaresponse_format is an object with 'type': 'json' and a 'schema'Top level type is object, with properties and required
SQL type literalresponse_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
Prompttext to analyzeplusresponse_formatJSON schema or TYPEverified per tokenAI_COMPLETEreturnsOBJECTmatches the schema

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

  • type can be object, array, string, number, integer, boolean or null.
  • properties defines the fields, and each can carry a description that guides the model.
  • required lists the field names that must appear. If a required field cannot be filled, the call raises an error.
  • additionalProperties must be false in every schema node for OpenAI (GPT) models, and required must then list every property. Other models do not need either setting.
  • $defs and $ref let 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.

TypeUnsupported keywords
integermultipleOf
numbermultipleOf, minimum, maximum, exclusiveMinimum, exclusiveMaximum
stringminLength, maxLength, format
arrayuniqueItems, contains, minContains, maxContains, minItems, maxItems
objectpatternProperties, 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 description text 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 temperature to 0 for the most consistent results.
Exam tip

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 statusCause
400Request validation failed, for example the format object has no type
400Schema validation failed, such as an unsupported keyword, an invalid property name or a bad $ref
422The 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.

Watch out

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_format with a JSON schema or a TYPE literal.
  • The schema is verified token by token; it adds no compute cost, though bigger schemas mean more tokens.
  • Top level must be an object; required fields that cannot be extracted cause an error.
  • GPT models need additionalProperties: false everywhere and every property in required.
  • Range and length keywords such as minimum, maxLength and minItems are not supported.
  • Errors: 400 for bad requests or schemas, 422 for output that does not match.

Check yourself

  • Which argument of AI_COMPLETE enforces a JSON shape?

    response_format, as a JSON schema object or a TYPE literal.

  • What extra schema settings do OpenAI GPT models need?

    additionalProperties set to false in every node and every property listed in required.

  • Why does a schema with maxLength fail?

    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.