Skip to main 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

note

We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. The major difference is in how you define Snowflake objects in the configuration file. Rather than having a grouped collection of objects, SOLE for Data Products goes for modular, self-describing, and explicit object definition.
Learn more about SOLE for Data Products, currently available as a private preview.

We have also introduced Data products as an extra layer on top of the Dataops.live platform capabilities making managing data products easier than ever. Learn more about Data Products, currently available as a private preview.

databases:
<database-name>:
schemas:
<schema-name>:
procedures:
<procedure-name>:
<configuration-key>: <value>
grants:
<privilege>:
- <role-name>
- <role-name>

Supported parameters

The engine supports the parameters listed below.

Configuration KeyRequired/OptionalData Types and ValuesDescription
return_typeRequiredStringThe return type of the procedure
statementRequiredStringSpecifies the JavaScript / SQL code used to create the proceduree's source code
argumentsOptionalObject. See here for a definition of argumentList of the arguments for the procedure
commentOptionalStringSpecifies a comment for the procedure
deletedOptionalBoolean: True enables deletion prevention, False does nothingSpecifies what objects are allowed to be deleted
execute_asOptionalStringSets execute context - see caller's rights and owner's rights
grantsOptionalMap: See Supported Procedure Grants to RolesList of Privileges and Roles to which privileges are granted on the current procedure.
languageOptionalString: javascript (default), sqlSpecifies the language of the stored procedure code
manage_modeOptionalString: all (default), none, grantsConfigures what properties to manage for the procedure.
See Changing Manage Mode before changing the value.
null_input_behaviorOptionalStringSpecifies the behavior of the procedure when called with null inputs
return_behaviorOptionalStringSpecifies the behavior of the procedure when returning results
caution

During subsequent pipeline runs a force-replacement behavior might be observed in PLAN and APPLY phase for 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 KeyRequired / OptionalData Types and ValuesDescription
typeRequiredStringData type of the Column
caution

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

Supported procedure grants to roles

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

  • USAGE
  • OWNERSHIP

Examples

JavaScript Procedure

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

SQL Procedure

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 having $ character in the statement

caution

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 ${"$"}.

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