Skip to main content

User Defined Functions (SQL UDFs)

As a Data Engineer, you will often find yourself writing the exact same complex SQL logic across dozens of different views and pipelines. For example, calculating net revenue after applying a dynamic state tax, or formatting phone numbers.

Instead of copying and pasting that logic (which creates a maintenance nightmare), you should encapsulate it into a User-Defined Function (UDF).

Topics Covered​

  • Scalar UDFs Syntax
  • UDF Overloading
  • Secure UDFs
  • Performance Considerations

What is a Scalar SQL UDF?​

A Scalar UDF takes zero, one, or multiple input arguments and returns exactly one value per row.

Let's assume our retail company needs to calculate the final price of an item after applying an 18% GST (Goods and Services Tax).

1. Database Setup​

(Run this in your Snowflake worksheet)

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_UDF_DB;
USE SCHEMA BEEMS_UDF_DB.PUBLIC;

-- Create Mock Data
CREATE OR REPLACE TABLE SALES (
ITEM_ID INT,
BASE_PRICE_INR NUMBER(10,2)
);

INSERT INTO SALES VALUES (1, 1000.00), (2, 500.00);

2. Creating the UDF​

We create a function that accepts a number and returns a number.

(To verify your active role before creating functions, run SELECT CURRENT_ROLE();)

CREATE OR REPLACE FUNCTION CALCULATE_GST(base_price NUMBER(10,2))
RETURNS NUMBER(10,2)
AS
$$
base_price + (base_price * 0.18)
$$;

3. Using the UDF​

You use the UDF in a SELECT statement exactly as you would use a native Snowflake function like UPPER() or ROUND().

SELECT 
ITEM_ID,
BASE_PRICE_INR,
CALCULATE_GST(BASE_PRICE_INR) AS FINAL_PRICE_INR
FROM SALES;

UDF Overloading​

What if some products have an 18% tax, while others have a 12% tax? We can create another function with the exact same name, but with a different number of arguments. This is called Overloading.

-- Create an overloaded version of the function that accepts a custom tax rate
CREATE OR REPLACE FUNCTION CALCULATE_GST(base_price NUMBER(10,2), tax_rate NUMBER(5,2))
RETURNS NUMBER(10,2)
AS
$$
base_price + (base_price * tax_rate)
$$;

-- Snowflake automatically knows which function to use based on the arguments provided
SELECT
CALCULATE_GST(1000) AS DEFAULT_TAX, -- Uses the 1-argument function
CALCULATE_GST(1000, 0.12) AS CUSTOM_TAX; -- Uses the 2-argument function

Secure UDFs​

By default, any user with permission to use your UDF can see the underlying SQL logic by running GET_DDL('FUNCTION', 'CALCULATE_GST(NUMBER)').

If your UDF contains proprietary business logic, secret algorithms, or hardcoded sensitive values, you must create it as a Secure UDF.

-- Create a secure function
CREATE OR REPLACE SECURE FUNCTION CALCULATE_PROFIT_MARGIN(revenue NUMBER, cost NUMBER)
RETURNS NUMBER
AS
$$
-- Proprietary formula goes here
(revenue - cost) / revenue
$$;

Now, if a standard user tries to view the DDL, Snowflake will return an empty string or an error.

Performance Considerations​

While SQL UDFs are convenient, you must be careful not to abuse them.

  1. Inlining: Snowflake's query optimizer is very smart. If you write a simple SQL UDF (like the math examples above), Snowflake will "inline" the logic. It essentially copies the SQL from the UDF and pastes it into the main query during execution, resulting in zero performance penalty.
  2. Complex UDFs: If your SQL UDF contains complex logic, subqueries, or calls to other UDFs, Snowflake might not be able to inline it. This forces Snowflake to execute the function row-by-row, which is significantly slower than native vectorized SQL.
  3. Avoid UDFs for simple formatting: Do not create a UDF just to wrap a native TO_CHAR() or UPPER() function. The overhead of calling the UDF is not worth the syntactic sugar.

Summary​

  • Scalar SQL UDFs encapsulate reusable logic and return one value per row.
  • Overloading allows you to create multiple functions with the same name but different parameters.
  • Use SECURE FUNCTION to hide proprietary logic from end-users.
  • Simple SQL UDFs are inlined by the optimizer, making them just as fast as raw SQL.