Stored Procedures (SQL & JavaScript)
While SQL is incredibly powerful for querying and transforming data, it is not a procedural language. Standard SQL cannot easily say, "If this table is empty, do X; else, loop through these records and execute Y."
To add procedural logic, variables, loops, and error handling to your data pipelines, Snowflake supports Stored Procedures.
Topics Covered
- Stored Procedures vs UDFs
- Snowflake Scripting (SQL Procedures)
- JavaScript Procedures
- Caller's Rights vs Owner's Rights
Stored Procedures vs UDFs
It is critical to understand the difference between a UDF (User-Defined Function) and a Stored Procedure:
- UDFs return a value and are used inside a SQL query (e.g.,
SELECT my_udf(col) FROM table). They cannot perform DDL or DML (they cannot create tables or insert data). - Stored Procedures execute administrative tasks, run DDL/DML (create tables, insert data), and perform complex orchestration. They are called independently using the
CALLcommand.
Snowflake Scripting (SQL Procedures)
Snowflake Scripting is native procedural SQL. It is the easiest way to write procedures if you come from an Oracle PL/SQL or Microsoft T-SQL background.
Let's assume our data team in Pune needs a procedure to dynamically archive old records.
1. Database Setup
(Run this in your Snowflake worksheet)
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_PROCEDURE_DB;
USE SCHEMA BEEMS_PROCEDURE_DB.PUBLIC;
CREATE OR REPLACE TABLE ACTIVE_ORDERS (ORDER_ID INT, STATUS STRING, ORDER_DATE DATE);
CREATE OR REPLACE TABLE ARCHIVED_ORDERS (ORDER_ID INT, STATUS STRING, ARCHIVED_DATE DATE);
INSERT INTO ACTIVE_ORDERS VALUES
(1, 'DELIVERED', '2023-01-01'),
(2, 'PENDING', CURRENT_DATE()),
(3, 'DELIVERED', '2023-02-15');
2. Creating the SQL Procedure
We will write a procedure that accepts a cut-off date, moves older delivered orders to the archive table, and deletes them from the active table.
(To verify your active role before creating the procedure, run SELECT CURRENT_ROLE();)
CREATE OR REPLACE PROCEDURE ARCHIVE_OLD_ORDERS(cutoff_date DATE)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
rows_archived INT DEFAULT 0;
BEGIN
-- 1. Insert into Archive
INSERT INTO ARCHIVED_ORDERS (ORDER_ID, STATUS, ARCHIVED_DATE)
SELECT ORDER_ID, STATUS, CURRENT_DATE()
FROM ACTIVE_ORDERS
WHERE STATUS = 'DELIVERED' AND ORDER_DATE < :cutoff_date;
-- 2. Capture the number of rows affected
rows_archived := SQLROWCOUNT;
-- 3. Delete from Active
DELETE FROM ACTIVE_ORDERS
WHERE STATUS = 'DELIVERED' AND ORDER_DATE < :cutoff_date;
-- 4. Return a success message
RETURN 'Successfully archived ' || rows_archived || ' orders.';
EXCEPTION
WHEN OTHER THEN
RETURN 'Error occurred during archiving: ' || SQLERRM;
END;
$$;
3. Calling the Procedure
CALL ARCHIVE_OLD_ORDERS('2023-12-31'::DATE);
JavaScript Stored Procedures
Before Snowflake Scripting was introduced, JavaScript was the primary language for procedures. It is still heavily used when you need complex array manipulation or JSON parsing that is difficult in SQL.
JavaScript procedures use the snowflake.execute() method to run SQL commands.
CREATE OR REPLACE PROCEDURE JS_ARCHIVE_ORDERS(cutoff_date STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
try {
// Build the dynamic SQL strings
var insert_sql = `INSERT INTO ARCHIVED_ORDERS (ORDER_ID, STATUS, ARCHIVED_DATE)
SELECT ORDER_ID, STATUS, CURRENT_DATE()
FROM ACTIVE_ORDERS
WHERE STATUS = 'DELIVERED' AND ORDER_DATE < '${CUTOFF_DATE}'`;
var delete_sql = `DELETE FROM ACTIVE_ORDERS
WHERE STATUS = 'DELIVERED' AND ORDER_DATE < '${CUTOFF_DATE}'`;
// Execute the SQL
var stmt1 = snowflake.createStatement({sqlText: insert_sql});
stmt1.execute();
// Retrieve the row count
var rows_affected = stmt1.getRowCount();
var stmt2 = snowflake.createStatement({sqlText: delete_sql});
stmt2.execute();
return "Successfully archived " + rows_affected + " orders using JavaScript.";
} catch (err) {
return "Failed: " + err.message;
}
$$;
Caller's Rights vs Owner's Rights
This is the most critical security concept regarding Stored Procedures.
When you create a procedure, you must specify whose privileges it executes with:
-
Owner's Rights (Default): The procedure executes using the privileges of the role that created it.
- Example: A
SYSADMINcreates a procedure to drop tables. A junior analyst (ANALYST_ROLE) calls the procedure. Even though the analyst doesn't have permission to drop tables, the procedure succeeds because it runs asSYSADMIN. - Use Case: Allowing lower-privileged users to perform highly specific, controlled administrative tasks safely.
- Example: A
-
Caller's Rights: The procedure executes using the privileges of the role that calls it.
- Example: If the same junior analyst calls a Caller's Rights procedure to drop tables, it will fail, because the analyst does not have the privileges to drop tables.
- Use Case: Auditing and ensuring strict RBAC compliance.
You specify this during creation:
CREATE OR REPLACE PROCEDURE DROP_OLD_TABLES()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER -- or EXECUTE AS OWNER
AS
$$ ... $$;
Summary
- Stored Procedures execute procedural logic and DDL/DML, unlike UDFs which only return values for
SELECTqueries. - Snowflake Scripting allows you to use standard SQL logic (
DECLARE,BEGIN,EXCEPTION). - JavaScript allows complex string and array manipulation before dynamically generating SQL.
- Owner's Rights procedures run with the creator's permissions; Caller's Rights procedures run with the executor's permissions.