# Create AI Function > Create a new custom AI function. Supports table-based or manual input specification, single or variant outputs. Direct AI_COMPLETE calls or additional pre- and post-processing. - Skill: `snowflake-labs/create-ai-function` (Agent Skill) - Install (CLI): `npx skillmds@latest add snowflake-labs/create-ai-function` - Raw SKILL.md: https://api.skillmd.com/api/skills/snowflake-labs/create-ai-function/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - Author: snowflake-labs (https://skillmd.com/u/snowflake-labs) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/snowflake-labs/create-ai-function --- # Create AI Function ## When to Load Load from main skill when user intent matches CREATE: "create", "build", "new" + ai/llm function. ## Information Model Before starting the workflow, scan the user's message and conversation context for pre-provided information. Track what's collected and only prompt for missing required fields. | Field | Required | Default | Confirm | Dependencies | |-------|----------|---------|---------|--------------| | `task_description` | Yes | - | No | - | | `clarifications` | Yes | (gathered) | No | task_description | | `input_source` | Yes | - | No | - | | `source_table` | If from_table | - | No | input_source | | `source_stage` | If multimodal | @{db}.{schema}.AI_FUNCTIONS | No | input_source | | `inputs` | Yes | - | **Research only** | input_source | | `outputs` | Yes | - | **Research only** | - | | `creation_mode` | Yes (direct, research) | - | No | clarifications, inputs, outputs | | `selected_approach` | If research | - | **Yes** | creation_mode | | `system_prompt` | If direct | (generated) | **Yes** | task_description | | `user_prompt_template` | If direct | (generated) | **Yes** | inputs | | `database` | Yes | (from prerequisites) | No | - | | `schema` | Yes | (from prerequisites) | No | - | | `function_name` | Yes | (generated) | No | - | | `model` | Yes | claude-sonnet-4-6 | No | - | | `udf_body_sql` | If research | (generated) | **Yes** | selected_approach, inputs, outputs, model | **Critical fields** (always confirm even if pre-provided): `selected_approach` + `udf_body_sql` (only if research mode), `system_prompt` + `user_prompt_template` (direct mode). In Direct mode, `inputs` and `outputs` are shown in the Step 8 review block and do not need separate confirmation. **Simple fields** (accept silently if pre-provided): `creation_mode`, `input_source`, `source_table`, `database`, `schema`, `function_name`, `model`, `task_description` ## Pre-Collection At workflow start, before prompting: 1. **Parse the user's initial message** for any pre-provided information: - Task description (e.g., "create a function that classifies sentiment...") - Creation mode hints (e.g., "simple function" → direct, "research the best approach" / "I want to add post-processing" → research) - Input/output definitions (e.g., "...takes a TEXT input and returns sentiment and confidence") - Target location (e.g., "...in MY_DB.MY_SCHEMA") - Function name (e.g., "...called CLASSIFY_SENTIMENT") - Model preference (e.g., "...using claude-sonnet-4-5") 2. **Check conversation context** for inherited values: - Database/schema from prerequisites - Function context if coming from another skill 3. **Mark collected fields** and note their source (user message, context, or default) 4. **Identify missing required fields** to determine which steps need prompting --- ## Workflow ### Planning #### Step 1: Gather Intention **If `task_description` already collected** (user described what the function should do): - Accept silently and proceed to Step 2. **If `task_description` not collected**, ask: ``` What should this AI function do? Describe the task in 1-2 sentences. For example: - "Classify customer support tickets into categories" - "Extract named entities from legal documents" - "Score product review quality on a 0-1 scale" ``` #### Step 2: Clarifying Questions **Skip this step unless the task is genuinely ambiguous** — i.e., you cannot generate a reasonable system prompt without more information. Most well-described tasks ("classify sentiment", "extract entities from contracts", "score review quality") do not need clarification. Any minor details (edge cases, formatting preferences) can be adjusted when the user reviews the generated prompt in Step 8. If clarification is truly needed (e.g., the user said "process my data" with no specifics), ask at most 2-3 targeted questions batched together. Do not ask one at a time. If you need to ask, wait for user answers before proceeding. Do not gate this as a mandatory stop — only pause if questions were actually asked. #### Step 3: Define Inputs and Outputs **If the user explicitly asks to process images, documents, or files from a stage**, load `references/multimodal_setup.md` and follow its workflow for multimodal input handling, model selection, and UDF generation. The default is text-only — do not suggest multimodal unprompted. **Determine input source:** **If `inputs` already collected** (user provided input definitions): - Skip input source determination - Show for confirmation **If `source_table` already collected** (user mentioned a table): - Input source is implicitly "from_table" - Run `DESCRIBE TABLE ` and ask user to select input/output columns **If neither collected**, ask: ``` How would you like to define your AI function inputs? 1. **From Table** - Extract schema from a Snowflake table 2. **Manual Spec** - Define inputs manually (names, types) ``` **If From Table:** ```sql DESCRIBE TABLE ``` Show columns and ask user to select which are **inputs** and what **outputs** the function should return. **Detecting file inputs from table schema:** When running `DESCRIBE TABLE`, check each selected input column's data type: - If type is **FILE** → use `sql_type: "FILE"` in the config. The function will accept FILE directly. - If type is **VARCHAR** and sample values look like stage file paths (contain `/`, end with file extensions like `.jpg`, `.pdf`, etc.) → use `sql_type: "STAGE_FILE_PATH"`. The function will cast to FILE internally via `TO_FILE()`. Ask user for the stage name. - Otherwise → regular text/scalar input. The function signature must match the table's column types so `SELECT func(col) FROM table` works without any extra wrapping. Load `references/multimodal_setup.md` for full detection flow and model selection when file inputs are detected. **If Manual Spec:** Collect input parameters and output fields: ``` Input 1: - Name: (e.g., customer_message) - SQL Type: [VARCHAR] (or NUMBER, FLOAT, BOOLEAN, VARIANT, FILE) Output 1: - Name: (e.g., sentiment) - JSON Type: [string] (or number, boolean, array, object) - Description: (e.g., "The detected sentiment") ``` **Input type notes:** Semi-structured inputs (`ARRAY`, `VARIANT`, `OBJECT`) are surfaced into the prompt template as JSON strings — `ARRAY` via `ARRAY_TO_STRING(, ', ')`, `VARIANT` and `OBJECT` via `TO_VARCHAR()`. The evaluate/optimize pipelines do the same conversion, so the prompt sees the JSON serialization rather than native structured data — that is functionally correct, just be aware of it when designing the prompt. Preserve the user's declared SQL types verbatim in the function signature: pass `sql_type: "ARRAY"`, `"VARIANT"`, or `"OBJECT"` to `create_udf.py` exactly as the user described the column; do not silently downcast to `VARCHAR`. For single-output functions, the return type maps from JSON type: `string` -> VARCHAR, `number` -> FLOAT, `integer` -> NUMBER, `boolean` -> BOOLEAN, `array`/`object` -> VARIANT. Multi-output functions always use `RETURNS VARIANT`. **If creation mode will be Research** (user already indicated research, custom SQL, or pre/post-processing): - **⚠️ STOP**: Confirm inputs/outputs before proceeding — research depends on accurate I/O definitions and changing them later wastes work. **If creation mode will be Direct** (the common path): - Do **not** stop here. Inputs/outputs will be shown in the Step 8 review block where the user confirms everything together. Proceed to Step 4. #### Step 4: Select Creation Mode **If any input has a multimodal type (`FILE` or `STAGE_FILE_PATH`)**, Agent Research mode is NOT available — always use Direct mode. Do not offer or mention the research option. Skip to Step 7. **Default to Direct mode** unless the user explicitly requests research, custom SQL, or pre/post-processing. Do not ask the user to choose a mode — infer it: - If the user said "research", "explore approaches", "I want pre-processing", or "custom SQL" → Agent Research mode. Continue to Step 5. - Otherwise → Direct mode. Skip to Create phase (Step 7). If you are unsure and the task seems complex enough to warrant research, you may briefly offer: ``` This looks like a straightforward task — I'll create a Direct AI_COMPLETE function. If you'd prefer I research implementation approaches with SQL pre/post-processing, let me know. ``` Then proceed with Direct mode without waiting for a response unless the user intervenes. #### Step 5: Research and Present Approaches With a clear understanding of the task, research how to best implement it as a Snowflake SQL UDF. **Web search** — Search for state-of-the-art techniques relevant to the customer's specific task. Example queries: - "best approach for [task] with LLMs" - "[task] prompt engineering techniques" - "[task] structured output best practices" Focus on findings that translate to a SQL UDF implementation: prompting strategies, output structuring, validation techniques, and error handling. A realistic combination would be SQL pre and post processings steps. Note, we cannot finetune or directly change model weights. Treat the model as a black box, but we can do Turing complete computation with SQL before and after calling AI_COMPLETE. Synthesize web research and pre-built patterns to identify ~1-3 concrete approaches. Present the approaches to the customer. Each approach must vary in **how the SQL UDF body is structured** — not just in prompt wording. For each approach, show: 1. **Name** and one-line description 2. **SQL UDF body sketch** — the complete `$$ ... $$` body showing where AI_COMPLETE sits relative to other SQL expressions 3. **Pros and cons** — quality, complexity, cost, latency, maintainability 4. **Best for** — when this approach is the right choice 5. **Recommended** - pick one of the approaches and tell the customer that this is the recommended approach If there is high variance in solutions, order them from simplest to most complex. **Always include a final option** after the researched approaches: ``` N+1. **Custom** — None of these fit? Describe your own pre- and post-processing strategy and I'll build the SQL UDF accordingly. ``` If the customer selects Custom, ask them to describe what they have in mind: ``` Describe the approach you'd like to take — what should happen before or after the AI_COMPLETE call? I'll translate your idea into a concrete SQL UDF. ``` **⚠️ STOP**: Wait for customer to select an approach or describe their own. If they want modifications to any option, iterate. #### Step 6: Confirm Approach **Agent Research mode only.** Direct mode skips this step. Confirm the selected or described approach. Summarize what was chosen: ``` Selected approach: [name] I'll build a SQL UDF that: - [key structural characteristic] - [key behavioral characteristic] Proceeding to generate the full function. ``` --- ### Create #### Step 7: Select Target Location and Model Use the target `database` and `schema` values that were already collected during prerequisites. **If `function_name` already collected**, accept silently. **If not collected**: Generate a name (SCREAMING_SNAKE_CASE) based on task description or outputs. Do not ask the user to confirm — the name will be shown in the Step 8 review block where they can edit it along with everything else. **If `model` already collected**, accept silently — unless the user explicitly asked to see model options (e.g., "show me available models"), in which case load `references/model_selection.md` and present the options. **If not collected**: It is MANDATORY to **load** `references/model_selection.md` and follow its full workflow. #### Step 8: Generate UDF **Direct mode:** Generate the system prompt and user prompt template from collected information. **If `system_prompt` not collected**, auto-generate from task description and clarifications: ``` You are an expert [domain] assistant. Your task is to [task_description]. Guidelines: - [specific instruction based on clarifications] - [edge case handling] - Always respond in the specified JSON format ``` **If `user_prompt_template` not collected**, auto-generate from input parameter names: - Example for inputs `[TEXT, LANGUAGE]`: ``` Analyze the following text: Text: {TEXT} Language: {LANGUAGE} ``` **⚠️ STOP**: Present everything as a single review block. This is the only confirmation point for Direct mode — inputs, outputs, function name, and prompts are all shown here: ``` Here's what I'll create: Function: {database}.{schema}.{function_name} Model: {model} Inputs: {input_name} ({sql_type}), ... Outputs: {output_name} ({json_type}), ... Returns: {return_type} System prompt: "{system_prompt}" User prompt template: "{user_prompt_template}" Confirm or edit any of the above? ``` Wait for user to confirm or request edits before proceeding. **Agent Research mode:** Based on the confirmed approach, inputs, outputs, and model, write the complete `CREATE FUNCTION` DDL. Preserve the user's declared SQL types verbatim in the function signature — never silently convert semi-structured inputs (`ARRAY`, `VARIANT`, `OBJECT`) to `VARCHAR`. **The UDF body is NOT limited to a single AI_COMPLETE call.** It should follow the structural pattern selected in the Planning phase, which may include: - SQL pre-processing on **scalar inputs only** (CASE WHEN, CONCAT, IFF, OBJECT_CONSTRUCT) — do NOT pre-process ARRAY or VARIANT inputs as they have indefinite length; pass them directly to AI_COMPLETE - AI_COMPLETE call (exactly one) - SQL post-processing (TRY_PARSE_JSON, COALESCE, type casting, CASE WHEN) - Scalar subqueries for intermediate variables: `(SELECT expr FROM (SELECT AI_COMPLETE(...) AS r))` - REDUCE for iterative transformations on array results - Any other valid SQL expressions **Show the complete DDL to the user:** ```sql CREATE FUNCTION DB.SCHEMA.FUNCTION_NAME(inputs...) RETURNS LANGUAGE SQL COMMENT = '' AS $$ $$; ``` **⚠️ STOP**: Always confirm the complete DDL before executing (critical field). The user may want to adjust the prompt, add edge case handling, change the SQL logic, etc. #### Step 9: Create UDF After confirmation, execute the DDL. **Direct mode** (AI_COMPLETE with structured output, matching the JSON config schema): Pass the confirmed configuration as individual CLI flags: ```bash PYTHONPATH=/src uv run --project python /src/create_udf.py \ --execute \ --connection \ --database \ --schema \ --function-name \ --function-intention '' \ --model \ --system-prompt '' \ --user-prompt-template '' \ --inputs '' \ --outputs '' ``` If any input uses `sql_type: "STAGE_FILE_PATH"`, also pass `--stage-name `. Not needed for native `FILE` column inputs. **Agent Research mode** (arbitrary SQL UDF body): Pass the confirmed DDL directly: ```bash PYTHONPATH=/src uv run --project python /src/create_udf.py \ --execute \ --sql-body '' \ --connection ``` Both modes handle object tagging and query tag logging automatically. After creation, test with a sample query: ```sql SELECT () AS result; ``` **Important — return type awareness for smoke tests:** - **Single-output functions** (one entry in `outputs`): The UDF body already extracts the field (e.g., `:route::VARCHAR`) and `RETURNS` the scalar type (VARCHAR, FLOAT, etc.). Call the function directly — do **NOT** add a `:field_name` accessor on the outside. Example: `SELECT my_func(input) AS result` (correct), NOT `SELECT my_func(input):route` (error: GET on VARCHAR). - **Multi-output functions** (multiple entries in `outputs`): The function `RETURNS VARIANT` containing all fields. Access individual fields with `:field_name::TYPE`. Example: `SELECT my_func(input):sentiment::VARCHAR AS sentiment, my_func(input):confidence::FLOAT AS confidence`. If there are multiple pre- and post-processing steps, we should test edge cases to confirm that the function behaves correctly. Verify execution, format, types, and edge cases. #### Step 10: Next Steps Present to user: ``` Your AI function is ready! **Recommended next step:** Evaluate your function against labeled test data to establish a performance baseline. What would you like to do? 1. **Evaluate** (recommended) - Measure function performance against a labeled dataset 2. **Test** - Run a quick test with sample inputs via SQL 3. **Done** - Exit for now ``` If evaluate → Load `evaluate/SKILL.md` with context: - Preserve function name, database, schema - Note: *"You'll need a test table with input columns and expected outputs to evaluate."* --- ## Best Practices **SQL UDF Bodies:** Custom AI functions are `LANGUAGE SQL` UDFs. The body is a single SQL expression (not a statement block). Use scalar subqueries `(SELECT expr FROM (SELECT AI_COMPLETE(...) AS r))` when the AI_COMPLETE result is referenced multiple times. Inside UDF bodies, use `ARRAY_CONSTRUCT()` (not `[...]`), `OBJECT_CONSTRUCT()` (not `{...}`), and `PARSE_JSON('...')` for the response_format value. AI_COMPLETE is called exactly once per invocation. Do not apply input enrichment to ARRAY or VARIANT inputs — their indefinite length makes them unsuitable for SQL-level transformation; pass them directly to AI_COMPLETE. When the body uses `OBJECT_CONSTRUCT(...)` for multi-output, declare `RETURNS VARIANT` so the function is compatible with the evaluate/optimize pipelines. Do not use PostgreSQL `E'...'` escape string syntax; use regular `'\n'` or `CHAR(10)` for newlines. **AI_COMPLETE calling convention:** Use the unqualified built-in `AI_COMPLETE(...)` — not the legacy `SNOWFLAKE.CORTEX.COMPLETE(...)`, which is a different (older) function and is not supported by the evaluate/optimize pipelines. Always use the named-parameter messages-array syntax; positional arguments are not supported for chat completions. The canonical pattern is: ```sql AI_COMPLETE( model => 'claude-sonnet-4-6', messages => ARRAY_CONSTRUCT( OBJECT_CONSTRUCT('role', 'system', 'content', ''), OBJECT_CONSTRUCT('role', 'user', 'content', '') ) )::VARCHAR ``` Do NOT use the positional form `AI_COMPLETE('model', 'system', 'user')` — it will fail with `Invalid argument types` for chat-based models. **System Prompts:** Be specific, define edge cases, use structure, add examples for complex tasks, keep focused. **JSON Schema:** Use correct types (`string`, `number`, `boolean`, `array`, `object`), mark required fields, define `items` for arrays. **Output Types:** For single-output functions, the return type maps from JSON type: `string` -> VARCHAR, `number` -> FLOAT, `integer` -> NUMBER, `boolean` -> BOOLEAN, `array`/`object` -> VARIANT. Multi-output functions always use `RETURNS VARIANT`. This applies to both Direct mode and Agent Research mode — always use `RETURNS VARIANT` for multi-output so the function is compatible with the evaluate and optimize pipelines. Do not use `RETURNS OBJECT` — the evaluate/optimize SPROCs expect VARIANT. **Testing:** Test typical inputs, edge cases (NULL, empty, long), verify types and structure. **Performance:** Choose appropriate model, keep prompts concise, use structured output. ## Common Patterns - **Classification:** Single string output (category) - **Extraction:** Multiple fields (VARIANT) - **Sentiment:** sentiment (string) + confidence (number) - **Transformation:** Single string output (transformed text) - **Validation:** is_valid (boolean) + errors (array) - **Scoring:** Numeric output with range validation (Output Transformation pattern) - **Conditional Processing:** Different prompts for different input types (Input Enrichment pattern) - **Structured Extraction:** LLM extracts raw fields, SQL reshapes into precise output (Output Transformation - heavy) - **Find-and-Replace:** LLM identifies items, SQL applies REPLACE iteratively (Iterative Transformation pattern) ## Stopping Points Planning: - ✋ Step 3: (research only) Confirm inputs/outputs — skipped for Direct mode since they appear in Step 8 review - ✋ Step 5: (research only) After presenting approaches — wait for selection or custom description Create: - ✋ Step 8: (direct) Confirm combined review (function name, model, system prompt, user prompt template, inputs, outputs) - ✋ Step 8: (research) Confirm complete DDL before execution