Skip to content

Procedure

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

  • Manage the lifecycle of new and existing procedures
  • Manage grants of a procedure

Usage

- procedure:
    name: <procedure-name>
    <configuration-key>: <value>
    database: rel(database.<database-name>)
    schema: rel(schema.<schema-name>)
    grants:
      <privilege>:
        - rel(role.<role-name>)
        - rel(role.<role-name>)
databases:
  <database-name>:
    schemas:
      <schema-name>:
        procedures:
          <procedure-name>:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>
databases:
  <database-name>:
    schemas:
      <schema-name>:
        procedures:
          <procedure-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 The return type of the procedure
statement Required (Optional for Python) String See here for a definition of statement Specifies the JavaScript/Java/Python/SQL code used to create the procedure 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. For Python procedures, this parameter is optional when handler is provided
arguments Optional Object. See here for a definition of argument List of the arguments for the procedure
comment Optional String Specifies a comment for the procedure
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 procedure depends on. Objects defined in the list will be added to the generated HCL dependencies list to ensure proper creation order.
execute_as Optional String Sets execute context - see caller's rights and owner's rights
external_access_integration Optional List of String The external access integration specifies network rules and secrets that specify external locations and credentials (if any) allowed for use by handler code when making requests of an external network, such as an external REST API.
grants Optional Map: See Supported Procedure Grants to Roles List of Privileges and Roles to which privileges are granted on the current procedure.
language Optional String: javascript (default), sql, python Specifies the language of the stored procedure code
manage_mode Optional String: all (default), none, grants Configures what properties to manage for the procedure.
See Changing Manage Mode before changing the value.
null_input_behavior Optional String Specifies the behavior of the procedure when called with null inputs
return_behavior Optional String Specifies the behavior of the procedure when returning results
runtime_version Optional (Required for Python functions) String Specifies the Python runtime version in a string format. For example, specify version 3.12 as the following runtime_version: "3.12"
packages Optional List of String Files to import for Java/Python procedure
handler Optional (Required for Python when statement is not provided) String The handler method for Java/Python procedure. For Python procedures, this parameter is required when statement is not provided
imports Optional List of String: See here for a definition of imports Implementation files to import for Java / Python procedure
snowpark_packages Optional (Required for python functions) String Specifies the Snowpark package version to be used for the procedure

Warning

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

arguments parameters

You can specify the arguments for the procedure.

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

Configuration Key Required / Optional Data Types and Values Description
type Required String Data type of the Column
default Optional String, Boolean, Null, Number (Float or Integer) The default value for the column

Warning

Using the PUT or GET commands in a SQL statement throws an error due to recent updates in Snowflake releases.

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 is used to create the procedure's logic that executes complex operations or tasks within the Snowflake environment.

SQL procedure with statement

dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        procedures:
          SQL_PROCEDURE:
            comment: "return 1 in JavaScript"
            language: sql
            return_type: INT
            execute_as: CALLER
            statement: SELECT 1 AS x;

JavaScript procedure with statement

dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        procedures:
          JAVASCRIPT_PROCEDURE:
            comment: "return 1 in JavaScript"
            language: javascript
            return_type: FLOAT
            execute_as: CALLER
            statement: >-
              var x = 1;
              return x;

Python procedure with statement

dataops/snowflake/databases.template.yml
databases:
  DATABASE:
    schemas:
      SCHEMA:
        procedures:
          PYTHON_PROCEDURE:
            comment: "return 1 in Python"
            language: python
            runtime_version: "3.12"
            handler: example_handler
            packages: ["snowflake-snowpark-python"]
            arguments:
              x:
                type: INT
            return_type: INT
            execute_as: CALLER
            statement: >-
              def example_handler(session, x):
                  return x

Python procedure with code uploaded from a stage

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

dataops/snowflake/databases.template.yml
databases:
  DATABASE:
    schemas:
      SCHEMA:
        procedures:
          PYTHON_PROCEDURE:
            comment: "procedure using imports"
            language: python
            runtime_version: "3.12"
            handler: main
            imports: ['@"DATAOPS_PROD"."SAMPLES"."INTERNAL_STAGE"/project.zip']
            packages: ["snowflake-snowpark-python"]
            arguments:
              x:
                type: INT
            return_type: INT
            execute_as: CALLER
            statement: >-
              def main(session, x):
                  return imported_function(session, x)

Python procedure with handler and imports

dataops/snowflake/databases.template.yml
databases:
  DATABASE:
    schemas:
      SCHEMA:
        procedures:
          example_python_procedure_with_imports:
            comment: "procedure using imports"
            language: python
            runtime_version: "3.12"
            handler: main
            imports: ['@"DATAOPS_PROD"."SAMPLES"."INTERNAL_STAGE"/project.zip']
            packages: ["snowflake-snowpark-python"]
            arguments:
              x:
                type: INT
            return_type: INT
            execute_as: CALLER
            statement: >-
              def main(session, x):
                  return imported_function(session, x)

imports parameter

This parameter helps specify the implementation files to import for the Java / Python procedure, 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.

Supported procedure grants to roles

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

  • USAGE
  • OWNERSHIP

Examples

JavaScript procedure

- procedure:
    name: JAVASCRIPT_PROCEDURE
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Procedure using JavaScript language"
    return_type: VARCHAR
    language: javascript
    execute_as: CALLER
    null_input_behavior: "RETURNS NULL ON NULL INPUT"
    external_access_integration:
      - rel(external_access_integration.EXTERNAL_INTEGRATION_1)
    statement: "return 1"
    arguments:
      ARG_1:
        type: VARCHAR
      ARG_2:
        type: DATE
dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        procedures:
          JAVASCRIPT_PROCEDURE:
            comment: "Procedure using JavaScript language"
            return_type: VARCHAR
            language: javascript
            execute_as: CALLER
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "return 1"
            arguments:
              ARG_1:
                type: VARCHAR
              ARG_2:
                type: DATE
dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        procedures:
          JAVASCRIPT_PROCEDURE|11:
            comment: "Procedure using JavaScript language"
            return_type: VARCHAR
            language: javascript
            execute_as: CALLER
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "return 1"
            arguments:
              ARG_1:
                type: VARCHAR(16777216)
          JAVASCRIPT_PROCEDURE|VARCHAR(16777216)|VARCHAR(16777216):
            comment: "Procedure using JavaScript language"
            return_type: VARCHAR
            language: javascript
            execute_as: CALLER
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "return 1"
            arguments:
              ARG_1:
                type: VARCHAR(16777216)
              ARG_2:
                type: VARCHAR(16777216)
          JAVASCRIPT_PROCEDURE|VARCHAR(16777216)|VARCHAR(16777216)|DATE:
            comment: "Procedure using JavaScript language"
            return_type: VARCHAR
            language: javascript
            execute_as: CALLER
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: "return 1"
            arguments:
              ARG_1:
                type: VARCHAR(16777216)
              ARG_2:
                type: VARCHAR(16777216)
              ARG_3:
                type: DATE

SQL procedure

- procedure:
    name: SQL_PROCEDURE
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Procedure using SQL language"
    language: sql
    return_type: VARCHAR
    execute_as: CALLER
    statement: >-
      DECLARE
      radius_of_circle FLOAT;
      area_of_circle FLOAT;
      BEGIN
      radius_of_circle := 3;
      area_of_circle := pi() * radius_of_circle * radius_of_circle;
      RETURN area_of_circle;
      END;
dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        procedures:
          SQL_PROCEDURE:
            comment: "Procedure using SQL language"
            language: sql
            return_type: VARCHAR
            execute_as: CALLER
            statement: >-
              DECLARE
              radius_of_circle FLOAT;
              area_of_circle FLOAT;
              BEGIN
              radius_of_circle := 3;
              area_of_circle := pi() * radius_of_circle * radius_of_circle;
              RETURN area_of_circle;
              END;
- procedure:
    name: TEST_PROCEDURE
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES_SCHEMA)
    return_type: VARCHAR(16777216)
    statement: >-
      var rs = snowflake.execute( { sqlText:
       `INSERT INTO rel(database.DATABASE_1,schema.TEST_SCHEMA,table.table1)("column1")
            SELECT 'value 1' AS "column 1" ;`
        } );
       return 'Done.';
    arguments: {}
    execute_as: OWNER
    language: javascript
    manage_mode: all
dataops/snowflake/databases.template.yml
databases:
  SALES_RECORD:
    schemas:
      SALES_SCHEMA:
        procedures:
          TEST_PROCEDURE:
            return_type: VARCHAR(16777216)
            statement: >-
              var rs = snowflake.execute( { sqlText:
               `INSERT INTO table1.column1
                    SELECT 'value 1' AS "column 1" ;`
                } );
               return 'Done.';
            arguments: {}
            execute_as: OWNER
            language: javascript
            manage_mode: all

Python procedure

- procedure:
    name: PYTHON_PROCEDURE
    database: rel(database.DATABASE)
    schema: rel(schema.SCHEMA)
    comment: "Procedure using Python language"
    return_type: VARCHAR
    language: python
    RUNTIME_VERSION: "3.12"
    snowpark_package: "1.14.0"
    external_access_integration:
      - rel(external_access_integration.EXTERNAL_INTEGRATION_1)
    packages:
      [
        "numpy",
        "pandas",
        "scikit-learn",
        "scikit-image",
      ]
    HANDLER: run
    execute_as: CALLER
    null_input_behavior: "RETURNS NULL ON NULL INPUT"
    statement: |
      def run(session):
        result = 1
        return result
dataops/snowflake/databases.template.yml
databases:
  DATABASE:
    schemas:
      SCHEMA:
        procedures:
          PYTHON_PROCEDURE:
            comment: "Procedure using Python language"
            return_type: VARCHAR
            language: python
            RUNTIME_VERSION: "3.12"
            snowpark_package: "1.14.0"
            packages:
              [
                "numpy",
                "pandas",
                "scikit-learn",
                "scikit-image",
              ]
            HANDLER: run
            execute_as: CALLER
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: |
              def run(session):
                result = 1
                return result

Procedure having $ character in the statement

Warning

Due to a known limitation that might lead to an error, the $ character should be escaped if present in the statement parameter of the procedure definition. Instead of $, you should use ${"$"}.

- procedure:
    name: JAVASCRIPT_PROCEDURE
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    comment: "Procedure using JavaScript language"
    return_type: VARCHAR
    language: javascript
    execute_as: CALLER
    null_input_behavior: "RETURNS NULL ON NULL INPUT"
    statement: '
    const application = "DataOps.live";
    const message = `This is ${"$"}{application}`;
    return message;
    '
    arguments:
      ARG_1:
        type: VARCHAR
      ARG_2:
        type: DATE
dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        procedures:
          JAVASCRIPT_PROCEDURE:
            comment: "Procedure using JavaScript language"
            return_type: VARCHAR
            language: javascript
            execute_as: CALLER
            null_input_behavior: "RETURNS NULL ON NULL INPUT"
            statement: '
            const application = "DataOps.live";
            const message = `This is ${"$"}{application}`;
            return message;
            '
            arguments:
              ARG_1:
                type: VARCHAR
              ARG_2:
                type: DATE

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
declare
  radius_of_circle float;
  area_of_circle float;
begin
  radius_of_circle := 3;
  area_of_circle := pi() * radius_of_circle * radius_of_circle;
  return area_of_circle;
end;
dataops/snowflake/procedures.template.yml
- procedure:
    name: TEST_PROCEDURE
    database: rel(database.DATAOPS_TEST_DATABASE_1_{{ env.DATAOPS_ENV_NAME }})
    schema: rel(schema.SCHEMA_1)
    return_type: VARCHAR(16777216)
    statement: !dataops.include common/files/test.sql
    arguments: {}
    execute_as: OWNER
    language: SQL
    manage_mode: all
dataops/snowflake/databases.template.yml
databases:
  DATAOPS_TEST_DATABASE_1_{{ env.DATAOPS_ENV_NAME }}:
    schemas:
      SCHEMA_1:
        procedures:
          TEST_PROCEDURE:
            return_type: VARCHAR(16777216)
            statement: !dataops.include common/files/test.sql
            arguments: {}
            execute_as: OWNER
            language: SQL
            manage_mode: all

Javascript function

common/files/test.js
var my_sql_command = "select * from table1";
var statement1 = snowflake.createStatement({ sqlText: my_sql_command });
var result_set1 = statement1.execute();
// Loop through the results, processing one row at a time...
while (result_set1.next()) {
  var column1 = result_set1.getColumnValue(1);
  var column2 = result_set1.getColumnValue(2);
}
return 0.0; // Replace with something more useful.
dataops/snowflake/procedures.template.yml
- procedure:
    name: TEST_PROCEDURE
    database: rel(database.DATABASE_1)
    schema: rel(schema.SCHEMA_1)
    return_type: VARCHAR(16777216)
    statement: !dataops.include common/files/test.js
    arguments: {}
    execute_as: OWNER
    language: SQL
    manage_mode: all
dataops/snowflake/databases.template.yml
databases:
  DATABASE_1:
    schemas:
      SCHEMA_1:
        procedures:
          TEST_PROCEDURE:
            return_type: VARCHAR(16777216)
            statement: !dataops.include common/files/test.js
            arguments: {}
            execute_as: OWNER
            language: SQL
            manage_mode: all