Config path: /<database>/<schema>/procedure/<name>(<dtypes>).yaml
Example:
arguments:
ticket_no: VARCHAR(13)
flight_id: NUMBER(10,0)
boarding_no: NUMBER(10,0)
seat_no: VARCHAR(4)
returns: BOOLEAN
body: |-
BEGIN
INSERT INTO boarding_passes (ticket_no, flight_id, boarding_no, seat_no)
VALUES (:TICKET_NO, :FLIGHT_ID, :BOARDING_NO, :SEAT_NO);
RETURN TRUE;
END;language (str) - language of function (default: SQL)
returns (str) - for single return value, return data type --- OR ---
returns (dict) - for table return values
{key} (ident) - return column name
{value} (str) - return column data type
body (str) - procedure body
is_strict (bool) - is procedure STRICT (always returns NULL on NULL input)
is_execute_as_caller (bool) - is function executed "as caller" (default "as owner")
imports (list) - files to import (usually JAR packages)
{items} (dict)
stage (ident) - name of stage
path (str) - path to file
packages (list) - Snowflake system packages to import as dependencies
{items} (str) - name of package, with optional version of package
handler (str) - name of class and method to be called
external_access_integrations (list)
{items} (ident) - name of external access integration
secrets (dict)
{key} (str) - secret variable name used in procedure code
{value} (ident) - name of secret object
comment (str)
Snowflake supports overloading of procedure names. Multiple procedures may have the same name as long as they have different arguments. It is required to use comma-separated base data types of arguments in config names.
For example: my_procedure(number).yaml, my_procedure(varchar,number).yaml
Make sure to read & fully understand "caller rights" and "owners rights" for stored procedures. It is very important for security.
Files for imports should be maintained using STAGE FILES.
If function body is empty, handler and imports with pre-compiled JAR or Python code are required.
runtime_version should be specified as string with explicit double-quotes (e.g. "3.8"). Otherwise YAML parser may confuse it with number, which may cause some unwanted effects.
You may use custom YAML tag !include to store procedure body in a separate file instead of storing it inside YAML.
In order to omit return values for table procedures, use the "empty dict syntax": returns: {}
Procedures with without is_execute_as_caller: True are executed with "schema owner role" privileges. If you want to access objects in other schemas, make sure to specify additional owner grant parameters in SCHEMA config. For example:
owner_schema_read - to read objects in other schemas;
owner_integration - to access objects in EXTERNAL STAGE linked to STORAGE INTEGRATION;
Last updated