SnowDDL
  • 👋Introduction
  • 🚩Getting started
  • 📋Main features
  • 🪤SnowDDL vs. Declarative DCM
  • In-depth guides
    • 👓Object identifiers
    • 📐Data types
    • 📦Object types
    • 🎭Role hierarchy
    • 🚧Permission model
    • 🔦Other guides
      • Administration user
      • Integrations
      • Inbound shares
      • Object OWNERSHIP
      • Safe & unsafe DDL
      • Dependency management
      • Short hash explained
      • Env Prefix explained
      • Team workflow
      • Limitations & workarounds
      • Fivetran
      • Airbyte
      • Encrypt user passwords
      • Iceberg Tables
  • Basic usage (CLI + YAML)
    • 💻CLI interface
    • 📦YAML configs
      • ACCOUNT PARAMETER
      • ACCOUNT POLICY
      • AGGREGATION POLICY
      • ALERT
      • AUTHENTICATION POLICY
      • BUSINESS ROLE
      • DATABASE
      • DYNAMIC TABLE
      • EVENT TABLE
      • EXTERNAL ACCESS INTEGRATION
      • EXTERNAL FUNCTION
      • EXTERNAL TABLE
      • FILE FORMAT
      • FUNCTION
      • HYBRID TABLE
      • ICEBERG TABLE
      • MASKING POLICY
      • MATERIALIZED VIEW
      • NETWORK POLICY
      • NETWORK RULE
      • PERMISSION MODEL
      • PIPE
      • PLACEHOLDER
      • PROCEDURE
      • PROJECTION POLICY
      • RESOURCE MONITOR
      • ROW ACCESS POLICY
      • SCHEMA
      • SECRET
      • SEQUENCE
      • SHARE (outbound)
      • STAGE
      • STAGE FILE
      • STREAM
      • TABLE
      • TASK
      • TECHNICAL ROLE
      • USER
      • VIEW
      • WAREHOUSE
    • 🏷️YAML placeholders
    • 📬YAML tag !include
    • 🔐YAML tag !decrypt
  • Single DB
    • 🦀Overview
  • Advanced usage (Python)
    • ⚙️Programmatic config
    • 🐍Architecture overview
      • 🔵Blueprints
      • 🟣Config
      • 🟠Parsers
      • 🟢Resolvers
      • 🔴Engine
    • 🏗️Query builder & formatter
  • Breaking changes log
    • 0.45.0 - March 2025
    • 0.41.0 - January 2025
    • 0.37.0 - December 2024
    • 0.36.0 - November 2024
    • 0.33.0 - October 2024
    • 0.27.0 - May 2024
  • Links
    • GitHub repository
    • PyPI package
    • YouTube tutorials
    • Changelog
    • LinkedIn profile
Powered by GitBook
On this page
  • Schema
  • Usage notes
  • Additional privileges
  • Links
  1. Basic usage (CLI + YAML)
  2. YAML configs

PROCEDURE

PreviousPLACEHOLDERNextPROJECTION POLICY

Last updated 6 months ago

Config path: /<database>/<schema>/procedure/<name>(<dtypes>).yaml

Example:

arguments:
  ticket_no: VARCHAR(13)
  flight_id: NUMBER(10,0)
  boarding_no: NUMBER(10,0)
  seat_no: VARCHAR(4)

returns: BOOLEAN

body: |-
  BEGIN
    INSERT INTO boarding_passes (ticket_no, flight_id, boarding_no, seat_no)
    VALUES (:TICKET_NO, :FLIGHT_ID, :BOARDING_NO, :SEAT_NO);

    RETURN TRUE;
  END;

Schema

  • language (str) - language of function (default: SQL)

  • arguments (dict)

    • {key} (ident) - argument name

    • {value} (str) - argument --- OR ---

    • {value} (dict)

      • type (str) - argument

      • default (str) - default SQL expression for optional argument

  • returns (str) - for single return value, return data type --- OR ---

  • returns (dict) - for table return values

    • {key} (ident) - return column name

    • {value} (str) - return column data type

  • body (str) - procedure body

  • is_strict (bool) - is procedure STRICT (always returns NULL on NULL input)

  • is_execute_as_caller (bool) - is function executed "as caller" (default "as owner")

  • imports (list) - files to import (usually JAR packages)

    • {items} (dict)

      • stage (ident) - name of stage

      • path (str) - path to file

  • packages (list) - Snowflake system packages to import as dependencies

    • {items} (str) - name of package, with optional version of package

  • handler (str) - name of class and method to be called

  • external_access_integrations (list)

    • {items} (ident) - name of

  • secrets (dict)

    • {key} (str) - secret variable name used in procedure code

    • {value} (ident) - name of object

  • comment (str)

Usage notes

  1. If function body is empty, handler and imports with pre-compiled JAR or Python code are required.

  2. runtime_version should be specified as string with explicit double-quotes (e.g. "3.8"). Otherwise YAML parser may confuse it with number, which may cause some unwanted effects.

  3. In order to omit return values for table procedures, use the "empty dict syntax": returns: {}

Additional privileges

  • owner_schema_read - to read objects in other schemas;

  • owner_integration - to access objects in EXTERNAL STAGE linked to STORAGE INTEGRATION;

Links

Snowflake supports of procedure names. Multiple procedures may have the same name as long as they have different arguments. It is required to use comma-separated base data types of arguments in config names. For example: my_procedure(number).yaml, my_procedure(varchar,number).yaml

Make sure to read & fully understand for stored procedures. It is very important for security.

Files for imports should be maintained using .

You may use !include to store procedure body in a separate file instead of storing it inside YAML.

Procedures with without is_execute_as_caller: True are executed with "schema owner role" privileges. If you want to access objects in other schemas, make sure to specify additional owner grant parameters in config. For example:

📦
data type
data type
external access integration
secret
overloading
"caller rights" and "owners rights"
STAGE FILES
custom YAML tag
SCHEMA
CREATE PROCEDURE
SHOW PROCEDURES
DESC PROCEDURE
Working with Stored Procedures
Parser & JSON Schema (GitHub)