Introduction to JavaScript UDFs

This topic covers general JavaScript UDF (user-defined function) requirements and usage details, as well as limitations that are specific to JavaScript UDFs.

In this Topic:

Introductory Example

The following example shows how to use a JavaScript UDF to sort an array. This example takes advantage of the fact that JavaScript has a built-in sort() method for arrays.

Note that the JavaScript code must refer to the input parameter names as all uppercase, even if the names are not uppercase in the SQL code.

-- Create the UDF.
CREATE OR REPLACE FUNCTION array_sort(a array)
  RETURNS array
  return A.sort();

-- Call the UDF with a small array.

The output would look similar to:

[1, 2, 3, 4, 5]

JavaScript Data Types

SQL and JavaScript UDFs provide similar, but different, data types, based on their native data type support. Objects within Snowflake and JavaScript are transferred using the following mappings.

Integers and Doubles

JavaScript has no integer type; all numbers are represented as doubles. JavaScript UDFs do not accept or return integer values except through type conversion (i.e. you can pass an integer to a JavaScript UDF that accepts a double).

Both Snowflake SQL and JavaScript support double values. These values are transferred as-is.


Both Snowflake SQL and JavaScript support string values. These values are transferred as-is.

Binary Values

All binary values are converted into JavaScript Uint8Array objects. These typed arrays can be accessed in the same way as regular JavaScript arrays, but they are more efficient and support additional methods.

If a JavaScript UDF returns a Uint8Array object, it is converted into a Snowflake SQL binary value.


All timestamp and date types are converted into JavaScript Date() objects. The JavaScript date type is equivalent to TIMESTAMP_LTZ(3) in Snowflake SQL.

Consider the following notes for JavaScript UDFs that accept a date or time:

  • All precision beyond milliseconds is lost.

  • A JavaScript Date generated from SQL TIMESTAMP_NTZ no longer acts as “wallclock” time; it is influenced by daylight saving time. This is similar to behavior when converting TIMESTAMP_NTZ to TIMESTAMP_LTZ.

  • A JavaScript Date generated from SQL TIMESTAMP_TZ loses time zone information, but represents the same moment in time as the input (similar to when converting TIMESTAMP_TZ to TIMESTAMP_LTZ).

  • SQL DATE is converted to JavaScript Date representing midnight of the current day in the local time zone.

Additionally, consider the following notes for JavaScript UDFs that return DATE and TIMESTAMP types:

  • JavaScript Date objects are converted to the UDF’s result data type, adhering to the same conversion semantics as casts from TIMESTAMP_LTZ(3) to the return data type.

  • JavaScript Date objects nested inside VARIANT objects are always of type TIMESTAMP_LTZ(3).

Variant, Objects, and Arrays

JavaScript UDFs allow easy, intuitive manipulation of variant and JSON data. Variant objects passed to a UDF are transformed to native JavaScript types and values. Any of the previously-listed values are translated into their corresponding JavaScript types. Variant objects and arrays are converted to JavaScript objects and arrays. Similarly, all values returned by the UDF are transformed into the appropriate variant values. Note that objects and arrays returned by the UDF are subject to size and depth limitations.

-- flatten all arrays and values of objects into a single array
-- order of objects may be lost
CREATE OR REPLACE FUNCTION flatten_complete(v variant)
  RETURNS variant
  AS '
  // Define a function flatten(), which always returns an array.
  function flatten(input) {
    var returnArray = [];
    if (Array.isArray(input)) {
      var arrayLength = input.length;
      for (var i = 0; i < arrayLength; i++) {
        returnArray.push.apply(returnArray, flatten(input[i]));
    } else if (typeof input === "object") {
      for (var key in input) {
        if (input.hasOwnProperty(key)) {
          returnArray.push.apply(returnArray, flatten(input[key]));
    } else {
    return returnArray;

  // Now call the function flatten() that we defined earlier.
  return flatten(V);

select value from table(flatten(flatten_complete(parse_json(
  {"key1" : [1, 2], "key2" : ["string1", "string2"]},
  {"key3" : [{"inner key 1" : 10, "inner key 2" : 11}, 12]}

   VALUE   |
 1         |
 2         |
 "string1" |
 "string2" |
 10        |
 11        |
 12        |

JavaScript Arguments and Returned Values

Arguments may be referenced directly by name within JavaScript. Note that an unquoted identifier must be referenced with the capitalized variable name. As arguments and the UDF are referenced from within JavaScript, they must be legal JavaScript identifiers. Specifically, UDF and argument names must begin with a letter or $, while subsequent characters can be alphanumeric, $, or _. Additionally, names can not be JavaScript-reserved words.

The following three examples illustrate UDFs that use arguments referenced by name:

-- Valid UDF.  'N' must be capitalized.
  RETURNS double
  AS 'return N + 5;';

select add5(0.0);

-- Valid UDF. Lowercase argument is double-quoted.
CREATE OR REPLACE FUNCTION add5_quoted("n" double)
  RETURNS double
  AS 'return n + 5;';

select add5_quoted(0.0);

-- Invalid UDF. Error returned at runtime because JavaScript identifier 'n' cannot be resolved.
CREATE OR REPLACE FUNCTION add5_lowercase(n double)
  RETURNS double
  AS 'return n + 5;';

select add5_lowercase(0.0);

NULL and Undefined Values

When using JavaScript UDFs, pay close attention to rows and variables that might contain NULL values. Specifically, Snowflake contains two distinct NULL values (SQL NULL and variant’s JSON null), while JavaScript contains the undefined value in addition to null.

SQL NULL arguments to a JavaScript UDF will translate to the JavaScript undefined value. Likewise, returned JavaScript undefined values translate back to SQL NULL. This is true for all data types, including variant. For non-variant types, a returned JavaScript null will also result in a SQL NULL value.

Arguments and returned values of the variant type distinguish between JavaScript’s undefined and null values. SQL NULL continues to translate to JavaScript undefined (and JavaScript undefined back to SQL NULL); variant JSON null translates to JavaScript null (and JavaScript null back to variant JSON null). An undefined value embedded in a JavaScript object (as the value) or array will cause the element to be omitted.

Create a table with one string and one NULL value:

create or replace table strings (s string);
insert into strings values (null), ('non-null string');

Create a function that converts a string to a NULL and a NULL to a string:

CREATE OR REPLACE FUNCTION string_reverse_nulls(s string)
    RETURNS string
    AS '
    if (S === undefined) {
        return "string was null";
    } else
        return undefined;

Call the function:

select string_reverse_nulls(s) 
    from strings
    order by 1;
| string was null         |
| NULL                    |

Create a function that shows the difference between passing a SQL NULL and passing a variant JSON null:

      AS '
      if (V === undefined) {
        return "input was SQL null";
      } else if (V === null) {
        return "input was variant null";
      } else {
        return V;
select null, 
       variant_nulls(cast(null as variant)),
| NULL | input was SQL null                   | input was variant null            |

Create a function that shows the difference between returning undefined, null, and a variant that contains undefined and null (note that the undefined value is removed from the returned variant):

      RETURNS variant
      AS $$
      if (V == 'return undefined') {
        return undefined;
      } else if (V == 'return null') {
        return null;
      } else if (V == 3) {
        return {
            key1 : undefined,
            key2 : null
      } else {
        return V;
select variant_nulls('return undefined'::VARIANT) AS "RETURNED UNDEFINED",
       variant_nulls('return null'::VARIANT) AS "RETURNED NULL",
| NULL               | null          | {                                                                         |
|                    |               |   "key2": null                                                            |
|                    |               | }                                                                         |

Type Conversion within JavaScript

JavaScript will implicitly convert values between many different types. When any value is returned, the value is first converted to the requested return type before being translated to a SQL value. For example, if a number is returned, but the UDF is declared as returning a string, this number will converted to a string within JavaScript. Keep in mind that JavaScript programming errors, such as returning the wrong type, may be hidden by this behavior. In addition, if an error is thrown while converting the value’s type, an error will result.

JavaScript Number Range

The range for numbers with precision intact is from

-(2^53 -1)


(2^53 -1)

The range of valid values in Snowflake NUMBER(p, s) and DOUBLE data types is larger. Retrieving a value from Snowflake and storing it in a JavaScript numeric variable can result in loss of precision. For example:

  RETURNS string
  return A;
select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
|                    A | B                    |      A - B |
| -4730168494964875235 | -4730168494964875000 | -235.00000 |

The first two columns should match, and the third should contain 0.0.

The problem applies to JavaScript user-defined functions (UDFs) and stored procedures.

If you experience the problem in stored procedures when using getColumnValue(), you might be able to avoid the problem by retrieving a value as a string, e.g. with:


You can then return the string from the stored procedure, and cast the string to a numeric data type in SQL.

JavaScript Errors

Any errors encountered while executing JavaScript appear to the user as SQL errors. This includes parsing errors, runtime errors, and uncaught error thrown within the UDF. If the error contains a stacktrace, it will be printed along with the error message. It is acceptable to throw an error without catching it in order to end the query and produce a SQL error.

When debugging, you may find it useful to print argument values along with the error message so that they appear in the SQL error message text. For deterministic UDFs, this provides the necessary data to reproduce errors in a local JavaScript engine. One common pattern is to place an entire JavaScript UDF body in a try-catch block, append argument values to the caught error’s message, and throw an error with the extended message. You should consider removing such mechanisms prior to deploying UDFs to a production environment; recording values in error messages may unintentionally reveal sensitive data.

The function can throw and catch pre-defined exceptions or custom exceptions. A simple example of throwing a custom exception is here.

See also Troubleshooting JavaScript UDFs.

JavaScript UDF Limitations

To ensure stability within the Snowflake environment, Snowflake places the following limitations on JavaScript UDFs. These limitations are not invoked at the time of UDF creation, but rather at runtime when the UDF is called.

Code Size

Snowflake limits the maximum size of the JavaScript source code in the body of a JavaScript UDF. Snowflake recommends limiting the size to 100 KB. (The code is stored in a compressed form, and the exact limit depends on the compressibility of the code.)


JavaScript UDFs will fail if they consume too much memory. The specific limit is subject to change. Using too much memory will result in an error being returned.


JavaScript UDFs that take too long to complete will be killed and an error returned to the user. In addition, JavaScript UDFs that enter endless loops will result in errors.

Stack Depth

Excessive stack depth due to recursion will result in an error.

Global State

Snowflake usually preserves the JavaScript global state between iterations of a UDF. However, you should not rely on previous modifications to the global state being available between function calls. Additionally, you should not assume that all rows will execute within the same JavaScript environment.

In practice, the global state is relevant with:

  • Complex/expensive initialization logic. By default, the provided UDF code is evaluated for every row processed. If that code contains complex logic, this might be inefficient.

  • Functions that contain code that is not idempotent. A typical pattern would be:

    Date.prototype._originalToString = Date.prototype.toString;
    Date.prototype.toString = function() {
      /* ... SOME CUSTOM CODE ... */

    The first time that this code is executed, it changes the state of toString and _originalToString. Those changes are preserved in the global state, and the second time that this code is executed, the values are changed again in a way that creates recursion. The second time that toString is called, the code recurses infinitely (until it runs out of stack space).

For these situations, a recommended pattern is to guarantee that relevant code is evaluated only once, using JavaScript’s global variable semantics. For example:

var setup = function() {

if (typeof(setup_done) === "undefined") {
  setup_done = true;  // setting global variable to true

Note that this mechanism is only safe for caching the effects of code evaluation. It is not guaranteed that after an initialization the global context will be preserved for all rows, and no business logic should depend on it.


JavaScript UDFs support access to the standard JavaScript library. Note that this excludes many objects and methods typically provided by browsers. There is no mechanism to import, include, or call additional libraries. All required code should be embedded within the UDF.

Additionally, the built-in JavaScript eval() function is disabled.

Returned Variant Size and Depth

Returned variant objects are subject to size and nesting-depth limitations:


Currently limited to several megabytes, but subject to change.


Currently limited to a nesting depth of 1000, but subject to change.

If any object is too large or too deep, an error is returned when the UDF is called.

JavaScript UDF Security

JavaScript UDFs are designed to be safe and secure by providing several layers of query and data isolation:

  • Compute resources within the virtual warehouse that executes a JavaScript UDF are accessible only from within your account (i.e. warehouses do not share resources with other Snowflake accounts).

  • Table data is encrypted within the virtual warehouse to prevent unauthorized access.

  • JavaScript code is executed within a restricted engine, preventing system calls from the JavaScript context (e.g. no network and disk access) and constraining the system resources available to the engine, specifically memory.

As a result, JavaScript UDFs can access only the data needed to perform the defined function and can not affect the state of the underlying system, other than consuming a reasonable amount of memory and processor time.

Back to top