Skip to content

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

- function:
    name: <function-name>
    <configuration-key>: <value>
    database: rel(database.<database-name>)
    schema: rel(schema.<schema-name>)
    depends_on:
      - rel(function.<dependency-function-name>)
      - rel(view.<dependency-view-name>)
    grants:
      <privilege>:
        - rel(role.<role-name>)
        - rel(role.<role-name>)
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 the examples section on this page.

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.

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
depends_on Optional List of String: See Object Dependencies for a definition of depends_on List of SOLE-managed objects that this function depends on. Objects defined in the list will be added to the generated HCL dependencies list to ensure proper creation order.
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
is_memoizable Optional Boolean Specifies if a function is memoizable
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.12 should be specified as runtime_version: "3.12"
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: "return Number(arguments[0]) * 2;"

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.12"
            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.12"
            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.12"
            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.12"
            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

- 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"
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"

Java function

- 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
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

Python function

- 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.12"
    handler: "add_py"
    statement: "def add_py(i): return i+1"
    arguments:
      ARG1:
        type: NUMBER
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.12"
            handler: "add_py"
            statement: "def add_py(i): return i+1"
            arguments:
              ARG1:
                type: NUMBER

SQL function

- 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
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

SQL function that returns a table

- 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"
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"

Memoizable SQL Function

- function:
    name: SQL_TABLE_FUNCTION
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Function using SQL language with memoizable"
    language: sql
    is_memoizable: true
    return_type: NUMBER(38,0)
    return_behavior: IMMUTABLE
    statement: "a * b"
    arguments:
      A:
        type: NUMBER
      B:
        type: NUMBER

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
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
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

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
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.12"
    statement: !dataops.include common/files/test.py
    handler: is_prime
    arguments:
      ARG1:
        type: NUMBER
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.12"
            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;
}
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
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