Use the COMPLETE function of Snowflake for structured outputs

Introduction

The Cortex COMPLETE Structured Outputs feature, publicly available since February 11, 2025, enables Snowflake users to receive Language Model responses in a structured format, specifically in JSON. With this feature, building data pipelines for artificial intelligence becomes more efficient by eliminating the need for post-processing.

What is Cortex COMPLETE Structured Outputs?

The Cortex COMPLETE Structured Outputs allows users to obtain responses formatted according to a specified JSON schema. Key features include:

• Responses formatted according to the defined JSON schema;
• Integration into data pipelines without additional processing;
• Automatic validations of each token against the provided schema.

Basic Usage

To utilize this function, the response_format parameter must be included in the arguments of the COMPLETE function:

SELECT SNOWFLAKE.CORTEX.COMPLETE('model_name', [
    {'role':'user','content':'prompt'}
], {
    'temperature':0,
    'max_tokens':1000,
    'response_format':{
        'type':'json',
        'schema':{
            'type':'object',
            'properties':{
                'property_name':{'type':'string'}
            },
            'required':['required_property_name']
        }
    }
});

Practical Examples with Game Data Generation

Example 1: Generating Rooms in Roguelike RPG

Game developers can use this feature to generate information about dungeon rooms. See the query below:

SELECT SNOWFLAKE.CORTEX.COMPLETE('claude-3-5-sonnet', [
    {'role':'user','content':'Generate one room for a dark fantasy themed roguelike RPG.'}
], {
    'temperature':0.7,
    'max_tokens':800,
    'response_format':{
        'type':'json',
        'schema':{
            'type':'object',
            'properties':{
                'room_name':{'type':'string','description':'Name of the room'},
                'description':{'type':'string','description':'Detailed description of the room'}
            },
            'required':['room_name','description']
        }
    }
});

Executing this query can yield data such as:

{
  "room_name":"Altar of Corruption",
  "description":"What was once a sacred altar is now shrouded in corruption and darkness..."
}

This data highlights attributes like the room's name and a detailed description, adhering to the requested JSON schema.

Example 2: Generating Character Skills

This example illustrates the automatic generation of RPG characters with detailed specifications:

SELECT SNOWFLAKE.CORTEX.COMPLETE('claude-3-5-sonnet', [
    {'role':'user','content':'Generate a random player character for a fantasy RPG.'}
], {
    'temperature':0.8,
    'max_tokens':1000,
    'response_format':{
        'type':'json',
        'schema':{
            'type':'object',
            'properties':{
                'name':{'type':'string','description':'English name within 6 characters'},
                'race':{'type':'string','description':'Elf, Dwarf, Human, Orc, etc.'}
            },
            'required':['name','race','class','stats']
        }
    }
});

This command can return a character with information such as name, race, and class as requested.

Conclusion

The Cortex COMPLETE Structured Outputs functionality proves valuable not only for games but for various practical applications, such as in business data analysis. It can be used to summarize unstructured data into clear reports or for the automatic generation of FAQs. Companies are encouraged to explore and adapt this innovation to their specific context.

Promotions

Snowflake Updates on X

For more information about Snowflake, follow the "What's New" updates on X.

English Version

Snowflake What's New Bot (English Version)

Japanese Version

Snowflake's What's New Bot (Japanese Version)

Change Log

(20250225) Initial publication

Original Article in Japanese

https://zenn.dev/tsubasa_tech/articles/6ecf997a77c7c0