Programmability & Semi-Structured Data
Snowflake as an application platform: server-side logic, custom functions, and the semi-structured data types that let JSON, Avro, and Parquet live natively alongside relational columns.
Stored Procedures
A stored procedure encapsulates procedural logic, loops, conditionals, multiple statements, error handling, in JavaScript, Python, Java, Scala, or Snowflake Scripting SQL, invoked with CALL. Unlike a function, it can perform DDL and DML as a first-class part of its job.
CREATE PROCEDURE archive_old_ orders( cutoff_ date DATE) RETURNS STRINGLANGUAGE SQLAS$$BEGININSERT INTO orders_archive SELECT * FROM orders WHERE order_date < :cutoff_ date; DELETE FROM orders WHERE order_date < :cutoff_ date; RETURN 'Archive complete';END;$$;CALL archive_old_ orders('2025-01-01');
Owner's rights vs caller's rights
By default, a stored procedure runs with the privileges of its owner (owner's rights), regardless of who calls it, useful for giving a limited role safe access to a narrow, controlled operation on objects it couldn't otherwise touch directly. Declaring EXECUTE AS CALLER flips this: the procedure runs with whatever privileges the calling role holds, appropriate when the procedure should only be able to do what the caller could already do themselves, and no more.
Use caller's rights when the procedure needs to run with the privileges of the role that invoked it, so its access is bounded by whatever that caller already has. Use owner's rights (the default) when the procedure needs to operate on objects the caller doesn't have direct privileges on, a common pattern for giving a restricted role safe, narrow access to sensitive operations.
USERAminerole ANALYSTCALL archive_old_orders()
- CALL
OWNER'S RIGHTS · THE DEFAULT
Body runs asDATA_ENGINEERthe procedure owner
DELETE succeedsthe owner may delete
- CALL
EXECUTE AS CALLER
Body runs asANALYSTthe caller
DELETE refusedANALYST can’t delete
Owner's rights is controlled elevation: Thomas exposes one safe, narrow operation to roles that couldn't touch the tables directly, and the procedure body is the fence. Caller's rights is for utilities that should never do more than the person invoking them can, and it's the only mode that can read the caller's session state (current schema, session variables).

