How to Create UDFs in Snowflake

Galaxy Glossary

How do I create and use UDFs in Snowflake?

Snowflake UDFs let you package reusable SQL or JavaScript logic as functions that run directly inside the warehouse.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What problem do Snowflake UDFs solve?

UDFs centralize complex calculations—tax, discount, data cleansing—so every analyst reuses the same tested logic instead of copy-pasting SQL.

How do I create a SQL UDF?

Use CREATE OR REPLACE FUNCTION plus the function name, parameters, return type, and body. Bind your logic with RETURN.

Example: calculate discount

The code in the Example Query section shows a UDF that returns the discounted total for an order.

How do I call a UDF?

Call it like any built-in function: SELECT schema.udf_name(args);. Combine it with joins, CTEs, or views for maximum reuse.

Can UDFs be JavaScript?

Yes. Swap LANGUAGE SQL with LANGUAGE JAVASCRIPT and wrap the body in $$ ... $$. JavaScript UDFs handle loops and complex branching easily.

What are best practices?

Keep UDFs small, deterministic, and well-commented. Store them in a dedicated schema like util. Version them with CREATE OR REPLACE scripts in Git.

Common mistakes and fixes

Implicit casting: Always declare precise parameter types. Add ::NUMBER(38,2) casts when mixing numeric scales.

Over-using JavaScript UDFs: SQL UDFs run faster because they leverage Snowflake’s vectorized engine. Reserve JavaScript for logic SQL cannot express.

FAQ

Do UDFs add cost?

They run inside standard warehouses, so charges equal the compute time you would pay for equivalent inline SQL.

Can a UDF call another UDF?

Yes. Nesting is allowed, but keep call depth shallow to ease debugging.

Why How to Create UDFs in Snowflake is important

How to Create UDFs in Snowflake Example Usage


-- Apply discount to all orders placed today
WITH discounted AS (
    SELECT  o.id,
            util.order_discount(o.id, 10) AS discounted_total
    FROM    Orders o
    WHERE   o.order_date = CURRENT_DATE()
)
SELECT * FROM discounted ORDER BY discounted_total DESC;

How to Create UDFs in Snowflake Syntax


CREATE OR REPLACE FUNCTION util.order_discount(
    order_id        NUMBER,
    pct_discount    NUMBER(5,2)
)
RETURNS NUMBER(12,2)
LANGUAGE SQL
STRICT
AS
$$
    /* Compute discounted total for one order */
    SELECT (total_amount * (1 - pct_discount/100))::NUMBER(12,2)
    FROM   Orders
    WHERE  id = order_id;
$$;

Common Mistakes

Implicit data-type casting: forgetting to cast numeric scales causes precision loss. Fix by adding explicit casts like ::NUMBER(12,2).
Using JavaScript UDFs for simple math: SQL UDFs are faster. Reserve JavaScript for logic SQL cannot express.