Function

You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with Function:

Manage the lifecycle of new and existing functions
Manage grants of a function

Usage

Default Configuration
Data Products Configuration
Overloading

databases:
  <database-name>:
    schemas:
      <schema-name>:
        functions:
          <function-name>:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>

databases:
  <database-name>:
    schemas:
      <schema-name>:
        functions:
          <function-name>| unique identifier:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>

Overloading means you can add multiple functions with the same name but with different arguments. Use a pipe | after the function name. If you add more than two functions, each function's text after the pipe must be unique. See Examples.

Each function must have a unique signature. A signature is generated based on the following parameters:

Functions FQN (fully qualified name), which is made up of:
- Database name
- Schema name
- External Function name
Number of arguments
Data types of arguments
The sequence of arguments (mainly data type)

The function is treated as duplicated if you have identical values for the above parameters.

warning

Due to a Snowflake limitation, you can't create a UDF and procedure with the same signature in the same schema.

- function:
    name: <function-name>
    <configuration-key>: <value>
    database: rel(database.<database-name>)
    schema: rel(schema.<schema-name>)
    grants:
      <privilege>:
        - rel(role.<role-name>)
        - rel(role.<role-name>)

Supported parameters

The engine supports the parameters listed below.

Configuration Key	Required/Optional	Data Types and Values	Description
`return_type`	Required	String	Specifies the data type returned by the function
`statement`	Required	String See here for a definition of `statement`	Specifies the JavaScript/Java/SQL/Python code used to create the function with either raw code or a file path using `!dataops.include`, with the file located either in the `$CI_PROJECT_DIR` or in the directory where the YAML configuration file is located
`argument`	Optional	Object: See here for a definition of `argument`	List of the arguments for the function
`comment`	Optional	String	Specifies a comment for the function
`deleted`	Optional	Boolean: `True` enables deletion prevention, `False` does nothing	Specifies what objects are allowed to be deleted
`grants`	Optional	Map: See Supported Function Grants to Roles	Lists Privileges and Roles to which privileges are granted on the current function
`handler`	Optional	String	The handler method for Java function
`imports`	Optional	List of String: See here for a definition of `imports`	Implementation files to import for Java function
`language`	Optional	String: `java`, `javascript`, `sql`, `python`	The language of the statement
`manage_mode`	Optional	String: `all` (default), `none`, `grants`	Configures what properties to manage for the function. See Changing Manage Mode before changing the value.
`null_input_behavior`	Optional	String	Specifies the behavior of the external function when called with null inputs
`packages`	Optional	List of String	Jar files to import for Java function
`return_behavior`	Optional	String	Specifies the behavior of the function when returning results
`runtime_version`	Optional	String	Required for Python functions. It specifies the Python runtime version in a string format. For example, 3.8 should be specified as `runtime_version: "3.8"`
`target_path`	Optional	String	The target path for compiled jar file for Java function

warning

During subsequent pipeline runs, a force-replacement behavior might be observed in PLAN and APPLY phases for a few parameters like argument, return_type, statement, language, etc.

`argument` parameter

In the argument parameter, you can specify the arguments for the function.

You can list multiple arguments in an object format in the argument parameter, with each having the following supported parameters:

TYPE: Data Type of the Column
- REQUIRED
- Configuration key: type
- Data Type: String

Example

arguments:
  ARG_1:
    type: VARCHAR
  ARG_2:
    type: DATE

`statement` parameter

You can define the statement parameter by directly inputting raw SQL/JavaScript/Java/Python code or referencing a file path containing the desired code using the !dataops.include directive. However, ensure the file is stored either in the $CI_PROJECT_DIR directory or in the parent directory of the YAML configuration file.

This code creates the function that performs a specific task within the Snowflake environment.

SQL function with `statement`

dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          SQL_FUNCTION:
            comment: "Function using SQL language"
            return_type: NUMBER(38,0)
            language: sql
            arguments:
              x:
                type: INT
            statement: "x * 2"

JavaScript function with `statement`

dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          JAVASCRIPT_FUNCTION:
            comment: "Function using JavaScript language"
            return_type: FLOAT
            language: javascript
            arguments:
              x:
                type: FLOAT
            statement: >-
              function multiplyByTwo(x) {
                  return x * 2;
              }
              let result = multiplyByTwo(5);
              console.log("Result: " + result);

Python function with `statement`

Defines a function multiply_by_two in the statement and uses handler to invoke it.

dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          PYTHON_FUNCTION:
            comment: "Function using Python language"
            return_type: INT
            language: python
            runtime_version: "3.8"
            handler: multiply_by_two
            arguments:
              x:
                type: INT
            statement: >-
              def multiply_by_two(x):
                return x * 2

Python function with code uploaded from a stage

In a complex application where numerous dependency files are uploaded to a stage, you can simplify function creation by accessing these files using a Python handler with imports. The handler parameter specifies what function to call, while the imports statement imports the module.

dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          PYTHON_FUNCTION:
            comment: "Function using Python handler with import"
            return_type: INT
            language: python
            runtime_version: "3.8"
            handler: main
            imports: ['@"DATAOPS_PROD"."SAMPLES"."INTERNAL_STAGE"/project.zip']
            arguments:
              x:
                type: INT
            statement: >-
              def main(x):
                return imported_function(x)

Python function with `handler` and `imports`

dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          example_python_function_with_imports:
            return_type: INT
            language: python
            runtime_version: "3.8"
            handler: main
            imports: ['@"DATAOPS_PROD"."SAMPLES"."INTERNAL_STAGE"/project.zip']
            arguments:
              x:
                type: INT
            statement: >-
              def main(x):
                return imported_function(x)

`imports` parameter

This parameter helps specify the implementation files to import for the Java function, where the implementation is in a binary Python package or Jar files. However, when importing a Python module, you must have the handler and packages keywords in the DDL.

Example

databases:
  my_db:
    schemas:
      my_schema:
        functions:
          skimage_parser_fn:
            language: PYTHON
            return_type: VARIANT
            arguments:
              image_fl:
                type: VARCHAR
            runtime_version: "3.8"
            packages:
              [
                "snowflake-snowpark-python",
                "numpy",
                "pandas",
                "scikit-learn",
                "scikit-image",
              ]
            imports: ["@named_stage/scripts/skimage_handler_fn.py"]
            handler: skimage_handler_fn.main

Supported function grants to roles

Following are the privileges you can grant to roles in the function definition:

USAGE
OWNERSHIP

Examples

JavaScript function

Default Configuration
Data Products Configuration

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          JAVASCRIPT_FUNCTION:
            comment: "Function using JavaScript language"
            language: javascript
            arguments:
              ARG_1:
                type: VARCHAR
              ARG_2:
                type: DATE
            return_type: VARCHAR
            return_behavior: IMMUTABLE
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "return 1"
          JAVASCRIPT_FUNCTION|11:
            comment: "Function using JavaScript language"
            language: javascript
            arguments:
              ARG_1:
                type: VARCHAR(16777216)
            return_type: VARCHAR
            return_behavior: IMMUTABLE
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "return 1"
          JAVASCRIPT_FUNCTION|VARCHAR(16777216)|VARCHAR(16777216):
            comment: "Function using JavaScript language"
            language: javascript
            arguments:
              ARG_1:
                type: VARCHAR(16777216)
              ARG_2:
                type: VARCHAR(16777216)
            return_type: VARCHAR
            return_behavior: IMMUTABLE
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "return 1"
          JAVASCRIPT_FUNCTION|VARCHAR(16777216)|VARCHAR(16777216)|DATE:
            comment: "Function using JavaScript language"
            language: javascript
            arguments:
              ARG_1:
                type: VARCHAR(16777216)
              ARG_2:
                type: VARCHAR(16777216)
              ARG_3:
                type: DATE
            return_type: VARCHAR
            return_behavior: IMMUTABLE
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "return 1"

- function:
    name: JAVASCRIPT_FUNCTION
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Function using JavaScript language"
    language: javascript
    return_type: VARCHAR
    return_behavior: IMMUTABLE
    null_input_behavior: "RETURNS NULL ON NULL INPUT"
    statement: "return 1"

Java function

Default Configuration
Data Products Configuration

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          JAVA_FUNCTION:
            comment: "Function using Java language"
            language: java
            return_type: VARCHAR
            return_behavior: IMMUTABLE
            handler: "CoolFunc.test"
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: 'class CoolFunc {public static String test(int n) {return "hello!";}}'
            arguments:
              ARG1:
                type: NUMBER

- function:
    name: JAVA_FUNCTION
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Function using Java language"
    language: java
    return_type: VARCHAR
    return_behavior: IMMUTABLE
    handler: "CoolFunc.test"
    null_input_behavior: "RETURNS NULL ON NULL INPUT"
    statement: 'class CoolFunc {public static String test(int n) {return "hello!";}}'
    arguments:
      ARG1:
        type: NUMBER

Python function

Default Configuration
Data Products Configuration

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          PYTHON_FUNCTION:
            comment: "Function using Python language"
            language: python
            return_type: NUMBER(38,0)
            return_behavior: VOLATILE
            null_input_behavior: "CALLED ON NULL INPUT"
            runtime_version: "3.8"
            handler: "add_py"
            statement: "def add_py(i): return i+1"
            arguments:
              ARG1:
                type: NUMBER

- function:
    name: PYTHON_FUNCTION
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Function using Python language"
    language: python
    return_type: NUMBER(38,0)
    return_behavior: VOLATILE
    null_input_behavior: "CALLED ON NULL INPUT"
    runtime_version: "3.8"
    handler: "add_py"
    statement: "def add_py(i): return i+1"
    arguments:
      ARG1:
        type: NUMBER

SQL function

Default Configuration
Data Products Configuration

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          SQL_FUNCTION:
            comment: "Function using SQL language"
            language: sql
            return_type: NUMBER(38,0)
            return_behavior: IMMUTABLE
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "a * b"
            arguments:
              A:
                type: NUMBER
              B:
                type: NUMBER

- function:
    name: SQL_FUNCTION
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Function using SQL language"
    language: sql
    return_type: NUMBER(38,0)
    return_behavior: IMMUTABLE
    null_input_behavior: "RETURNS NULL ON NULL INPUT"
    statement: "a * b"
    arguments:
      A:
        type: NUMBER
      B:
        type: NUMBER

SQL function that returns a table

Default Configuration
Data Products Configuration

databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          SQL_TABLE_FUNCTION:
            comment: "Function using SQL language that returns table"
            language: sql
            return_type: "TABLE (A NUMBER, B NUMBER)"
            statement: "select 1, 2"

- function:
    name: SQL_TABLE_FUNCTION
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Function using SQL language that returns table"
    language: sql
    return_type: "TABLE (A NUMBER, B NUMBER)"
    statement: "select 1, 2"

Examples of the `!dataops.include` directive in configuration files

You can specify the statement in a file external to the YAML configuration. If the value provided through !dataops.include is a file path, that path must be relative to the git repository root ($CI_PROJECT_DIR). However, if the value is just a file name, the engine will look for that file in the directory where the configuration file is located.

The correct way to specify that path is either !dataops.include common/files/test.sql or !dataops.include test.sql.

Examples that will not lead to the desired result are:

!dataops.include /test.sql - note the leading /
!dataops.include /home/user/workspace/common/files/test.sql - attempting to use the full path to the workspace

SQL function

common/files/test.sql
SELECT 1, 2
UNION ALL
SELECT 3, 4

Default Configuration
Data Products Configuration

dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          SQL_TABLE_FUNCTION:
            comment: "Function using SQL language that returns table"
            language: sql
            return_type: "TABLE (A NUMBER, B NUMBER)"
            statement: !dataops.include common/files/test.sql

dataops/snowflake/functions.template.yml
- function:
    name: SQL_TABLE_FUNCTION
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Function using SQL language that returns table"
    language: sql
    return_type: "TABLE (A NUMBER, B NUMBER)"
    statement: !dataops.include common/files/test.sql

Python function

common/files/test.py
def is_prime(number):
    if number <= 1:
        return 0
    for i in range(2, int(number**0.5) + 1):
        if number % i == 0:
            return 0
    return 1

Default Configuration
Data Products Configuration

dataops/snowflake/databases.template.yml
databases:
  DATAOPS_TEST_DATABASE_1_{{ env.DATAOPS_ENV_NAME }}:
    schemas:
      SCHEMA_2:
        functions:
          PYTHON_FUNCTION:
            comment: "Function using Python language"
            language: python
            return_type: NUMBER(38,0)
            return_behavior: VOLATILE
            null_input_behavior: "CALLED ON NULL INPUT"
            runtime_version: "3.8"
            statement: !dataops.include common/files/test.py
            handler: is_prime
            arguments:
              ARG1:
                type: NUMBER

dataops/snowflake/functions.template.yml
- function:
    name: PYTHON_FUNCTION
    database: rel(database.DATAOPS_TEST_DATABASE_1_{{ env.DATAOPS_ENV_NAME }})
    schema: rel(schema.SCHEMA_2)
    comment: "Function using Python language"
    language: python
    return_type: NUMBER(38,0)
    return_behavior: VOLATILE
    null_input_behavior: "CALLED ON NULL INPUT"
    runtime_version: "3.8"
    statement: !dataops.include common/files/test.py
    handler: is_prime
    arguments:
      ARG1:
        type: NUMBER

Javascript function

common/files/test.js
if (D <= 0) {
  return 1;
} else {
  var result = 1;
  for (var i = 2; i <= D; i++) {
    result = result * i;
  }
  return result;
}

Default Configuration
Data Products Configuration

dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        functions:
          javascript_function:
            return_type: DOUBLE
            statement: !dataops.include common/files/test.js
            arguments:
              d:
                type: DOUBLE
            language: JAVASCRIPT
            null_input_behavior: STRICT
            manage_mode: all

dataops/snowflake/functions.template.yml
- function:
    name: javascript_function
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    return_type: DOUBLE
    statement: !dataops.include common/files/test.js
    arguments:
      d:
        type: DOUBLE
    language: JAVASCRIPT
    null_input_behavior: STRICT
    manage_mode: all

Usage​

Supported parameters​

argument parameter​

Example​

statement parameter​

SQL function with statement​

JavaScript function with statement​

Python function with statement​

Python function with code uploaded from a stage​

Python function with handler and imports​

imports parameter​

Example​

Supported function grants to roles​

Examples​

JavaScript function​

Java function​

Python function​

SQL function​

SQL function that returns a table​

Examples of the !dataops.include directive in configuration files​

SQL function​

Python function​

Javascript function​

Usage

Supported parameters

`argument` parameter

Example

`statement` parameter

SQL function with `statement`

JavaScript function with `statement`

Python function with `statement`

Python function with code uploaded from a stage

Python function with `handler` and `imports`

`imports` parameter

Example

Supported function grants to roles

Examples

JavaScript function

Java function

Python function

SQL function

SQL function that returns a table

Examples of the `!dataops.include` directive in configuration files

SQL function

Python function

Javascript function