Bring-your-own model for AI_COMPLETE

AI_COMPLETE supports running inference against a user-deployed model inference service on SPCS along with Snowflake’s Cortex models. To use this feature, the model argument needs to point to an SPCS model inference service that hosts a text generation model.

The function accepts either of the following text inputs:

FILE input and multimodal prompts are not supported. See Limitations.

Prerequisites

Before you can use this feature, log a text generation model from Hugging Face using Hugging Face pipeline, then deploy it as an SPCS model inference service using the Snowsight UI or the Python API.

For more examples on creating SPCS inference services, see Example workflows.

Syntax

The function accepts the same arguments as AI_COMPLETE (Single string) and AI_COMPLETE (Prompt object). The only change is that the model argument is an SPCS service name instead of the name of a Cortex model.

Using AI_COMPLETE with a single string input:

AI_COMPLETE(
    <model>, <prompt_string> [ , <model_parameters>, <response_format>, <show_details> ] )

Using AI_COMPLETE with a prompt object:

AI_COMPLETE(
    <model>, PROMPT( <template>, <text_arg> [ , ... ] ) [ , <model_parameters>, <response_format>, <show_details> ] )

Arguments

model

Name of an SPCS model inference service which hosts a text generation model from Hugging Face.

prompt

A string prompt or a prompt object built with PROMPT. FILE input and multimodal prompts are not supported. See Limitations.

model_parameters

An object containing zero or more model hyperparameters, such as temperature, top_p, and max_tokens. For full details, see Arguments.

The guardrails option is not supported. See Limitations.

response_format

The format that the response should follow, specified as a JSON schema or a SQL TYPE literal. For full details, see Arguments.

show_details

A boolean flag that indicates whether to return a serialized JSON object containing the response and additional inference details. For full details, see Arguments.

Returns

By default, returns a string containing the model’s response.

When the response_format argument is specified, returns an object that follows the provided format. When the show_details argument is set to TRUE, returns a JSON object containing the response and inference metadata. For full details on each return shape, see Returns.

Error behavior

The function follows the same error behavior as the standard AI_COMPLETE function. For the full table of return values for the return_error_details argument, see Error behavior.

Unsupported inputs listed in Limitations cause the entire query to fail with an error, regardless of the return_error_details setting. They aren’t reported as per-row NULL values or as {value, error} envelopes.

Examples

The examples in this section use a SQL variable for the service name:

SET service_name = '<db>.<schema>.<service>';

You can confirm the service exists with the following command:

DESC SERVICE IDENTIFIER($service_name);

Single response

SELECT AI_COMPLETE($service_name, 'Say hello in five words.');

Responses from a table column

CREATE OR REPLACE TABLE prompts (id INT, q VARCHAR);
INSERT INTO prompts VALUES
  (1, 'Translate "good morning" to French.'),
  (2, 'What is 2+2?'),
  (3, 'Summarize: Snowflake is a cloud data platform.');

SELECT
  id,
  q,
  AI_COMPLETE($service_name, q) AS reply
FROM prompts
ORDER BY id;

Controlling model parameters

SELECT AI_COMPLETE(
  $service_name,
  'Write a haiku about Snowflake.',
  OBJECT_CONSTRUCT(
    'temperature', 0.7,
    'top_p',       0.9,
    'max_tokens',  64
  )
) AS haiku;

Using named arguments

SELECT AI_COMPLETE(
  model            => $service_name,
  prompt           => 'Three benefits of unit tests:',
  model_parameters => OBJECT_CONSTRUCT('temperature', 0.2, 'max_tokens', 128)
) AS answer;

Using a PROMPT() template (text-only arguments)

SELECT AI_COMPLETE(
  $service_name,
  PROMPT('Translate {0} into {1}.', 'hello world', 'French')
) AS translation;

Joining with a table

CREATE OR REPLACE TABLE customers (id INT, name VARCHAR, city VARCHAR);
INSERT INTO customers VALUES
  (1, 'Alice', 'Paris'),
  (2, 'Bob',   'Tokyo'),
  (3, 'Carol', 'Lima');

SELECT
  c.id,
  c.name,
  c.city,
  AI_COMPLETE(
    $service_name,
    'Suggest a one-line dinner recommendation for ' || c.name ||
    ' visiting ' || c.city || '.'
  ) AS suggestion
FROM customers c
ORDER BY c.id;

Limitations

The following AI_COMPLETE inputs are not yet supported when the function points to an SPCS service.

FILE input

The single-file overload of AI_COMPLETE is rejected with the error: NOT_YET_IMPLEMENTED: multimodal file input for an AI function calling an SPCS service.

-- Positional (single-file overload: AI_COMPLETE(model, prompt, file)).
SELECT AI_COMPLETE(
  $service_name,
  'Describe this image in one sentence.',
  TO_FILE('@my_stage', 'cat.png')
);

-- Column-typed FILE.
SELECT AI_COMPLETE($service_name, 'Describe this:', file_col)
FROM my_table_with_file_col;

Multimodal PROMPT() and PROMPT_FORMAT() input

FILE, OBJECT, or ARRAY arguments inside PROMPT can potentially make the template multimodal and are rejected with the error: multimodal prompt input for an AI function calling an SPCS service.

-- PROMPT() with a FILE argument.
SELECT AI_COMPLETE(
  $service_name,
  PROMPT('Describe {0}', TO_FILE('@my_stage', 'cat.png'))
);

-- PROMPT_FORMAT() with a FILE argument.
SELECT AI_COMPLETE(
  $service_name,
  PROMPT_FORMAT('Describe {0}', TO_FILE('@my_stage', 'cat.png'))
);

-- Column-typed FILE inside PROMPT().
SELECT AI_COMPLETE(
  $service_name,
  PROMPT('Caption {0}', file_col)
)
FROM my_table_with_file_col;

-- Pure-data OBJECT or ARRAY arguments also trigger the rejection.
SELECT AI_COMPLETE(
  $service_name,
  PROMPT('Process {0}', OBJECT_CONSTRUCT('key', 'value'))
);

SELECT AI_COMPLETE(
  $service_name,
  PROMPT('Pick from {0}', ARRAY_CONSTRUCT('a','b','c'))
);

guardrails model parameter

The guardrails option in model_parameters is rejected.

-- Using OBJECT_CONSTRUCT.
SELECT AI_COMPLETE(
  model            => $service_name,
  prompt           => 'hello world',
  model_parameters => OBJECT_CONSTRUCT('guardrails', TRUE)
);

-- Using OBJECT literal syntax.
SELECT AI_COMPLETE(
  $service_name,
  'hello world',
  {'guardrails': TRUE, 'temperature': 0.7}::OBJECT
);

Access control requirements

The user must have the USAGE privilege on the SPCS service and on its inference function.

Refer to Snowflake AI and ML for legal notices.