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
  • Links
  1. Basic usage (CLI + YAML)
  2. YAML configs

FUNCTION

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

Example:

arguments:
  input_val: OBJECT

returns: VARCHAR(1000)

body: |-
  GET(input_val, COALESCE('BOOKINGS_LANG', 'en'))::varchar(1000)
arguments:
  x: VARCHAR(10000)

returns: VARCHAR(255)
language: java

imports:
  - stage: test_internal_stage
    path: /lib/zero-allocation-hashing-0.15.jar

handler: SnowHash.xxHash

body: |-
  import net.openhft.hashing.LongHashFunction;

  class SnowHash {
      public static LongHashFunction hash_func = LongHashFunction.xx();

      public static String xxHash(String x) {
          return Long.toHexString(hash_func.hashChars(x));
      }
  }
language: python
runtime_version: "3.8"

returns: VARIANT

packages:
  - numpy
  - pandas
  - xgboost==1.5.0

handler: udf

body: |-
  import numpy as np
  import pandas as pd
  import xgboost as xgb

  def udf():
    return [np.__version__, pd.__version__, xgb.__version__]

Schema

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

  • runtime_version (str) - used to specify version of Python, Java, etc.

  • arguments (dict)

    • {key} (ident) - argument name

    • {value} (dict)

      • 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) - function body

  • is_secure (bool) - is function SECURE

  • is_aggregate (bool) - is function AGGREGATE

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

  • is_immutable (bool) - is function IMMUTABLE (same input always produced the same output)

  • is_memoizable (bool)

  • 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)

  • secrets (dict)

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

  • 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.

Links

PreviousFILE FORMATNextHYBRID TABLE

Last updated 23 days ago

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

type (str) - argument

{items} (ident) - name of

{value} (ident) - name of object

Snowflake supports of function names. Multiple functions 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_function(number).yaml, my_function(varchar,number).yaml

Files for imports should be maintained using .

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

📦
data type
data type
external access integration
secret
overloading
STAGE FILES
custom YAML tag
CREATE FUNCTION
SHOW USER FUNCTIONS
Parser & JSON Schema (GitHub)