Snowflake: Function & Procedure Support

Atlas now supports Snowflake functions and procedures as first-class resources, with support for multiple languages, overloading, table-returning functions, and OUT parameters.

Atlas now supports functions and procedures as first-class Snowflake resources. Both can be defined, diffed, and migrated alongside other schema objects like tables and views.

Functions

The function block defines a Snowflake function. Atlas supports all Snowflake function languages: SQL, JavaScript, Python, and Java.

function "sql_add" {
  schema = schema.PUBLIC
  lang   = SQL
  arg "a" {
    type = NUMBER(38)
  }
  arg "b" {
    type = NUMBER(38)
  }
  return = NUMBER(38)
  as     = "a + b"
}

Functions written in Python, Java, or JavaScript use the runtime_version and handler attributes:

function "py_double" {
  schema          = schema.PUBLIC
  lang            = PYTHON
  runtime_version = "3.11"
  handler         = "py_double_handler"
  arg "x" {
    type = NUMBER(38)
  }
  return = NUMBER(38)
  as     = <<-PYTHON
def py_double_handler(x):
    return x * 2
PYTHON
}

Table-Returning Functions

Functions can return tabular results using return_table instead of return:

function "get_numbers" {
  schema = schema.PUBLIC
  lang   = SQL
  arg "n" {
    type = NUMBER(38)
  }
  return_table {
    column "id" {
      type = NUMBER(38)
    }
    column "value" {
      type = NUMBER(38)
    }
  }
  as = "SELECT seq4() AS id, seq4() * 3 AS value FROM TABLE(GENERATOR(ROWCOUNT => n))"
}

Procedures

The procedure block defines a Snowflake stored procedure. Procedures support attributes like secure, execute_as, and comment:

procedure "secure_sum" {
  schema = schema.PUBLIC
  lang   = SQL
  secure = true
  arg "a" {
    type = NUMBER(38)
  }
  arg "b" {
    type = NUMBER(38)
  }
  return = NUMBER(38)
  as     = <<-SQL
BEGIN
  RETURN a + b;
END;
SQL
}

OUT Parameters

Procedure arguments support mode = OUT for output parameters, and the return block can specify null constraints:

procedure "swap_values" {
  schema = schema.PUBLIC
  lang   = SQL
  arg "a" {
    type = NUMBER(38)
    mode = OUT
  }
  arg "b" {
    type = NUMBER(38)
    mode = OUT
  }
  return {
    type = VARCHAR(134217728)
    null = false
  }
  as = <<-SQL
BEGIN
  LET tmp := a;
  a := b;
  b := tmp;
  RETURN 'swapped';
END;
SQL
}

Atlas fully manages the lifecycle of functions and procedures: creating, altering, dropping, and diffing them alongside other schema objects. Function overloading (multiple functions with the same name but different argument types) is also supported.