Creating Snowflake UDFs and Stored Procedures

 

1. Introduction

• Overview of User-Defined Functions (UDFs) and Stored Procedures in Snowflake.
• When to use UDFs vs. Stored Procedures.
• Benefits of writing reusable functions for data transformation and automation.

2. Understanding User-Defined Functions (UDFs)

What Are UDFs?

• UDFs allow custom logic to be executed in SQL queries.
• Supports languages: SQL, JavaScript, Python, Java, and Scala.

Creating a SQL-Based UDF

sql

CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
AS 
$$ a + b $$;

Usage:

sql

CopyEdit

SELECT add_numbers(10, 20);

Creating a JavaScript-Based UDF

sql

CREATE FUNCTION get_uppercase(input_str STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS 
$$
  return input_str.toUpperCase();
$$;

Usage:

sql

SELECT get_uppercase('snowflake');

Creating a Python UDF

sql

CREATE FUNCTION square_num(x FLOAT)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'square'
AS
$$
def square(x):
    return x * x
$$;

Usage:

sql

SELECT square_num(5);

3. Understanding Stored Procedures

What Are Stored Procedures?

• Stored procedures execute a series of SQL statements inside Snowflake.
• Supports JavaScript & Python.
• Can perform control flow operations (loops, conditions, exceptions).

Creating a JavaScript-Based Stored Procedure

sql

CREATE PROCEDURE insert_new_employee(name STRING, age INT, department STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS 
$$
  var sql_command = `INSERT INTO employees (name, age, department) VALUES ('${name}', ${age}, '${department}')`;
  snowflake.execute({sqlText: sql_command});
  return 'Employee added successfully';
$$;

Usage:

sql

CALL insert_new_employee('Alice', 30, 'Engineering');

Creating a Python-Based Stored Procedure

sql

CREATE PROCEDURE get_employee_count()
RETURNS INT
LANGUAGE PYTHON
RUNTIME_VERSION = '3.8'
HANDLER = 'employee_count'
AS
$$
def employee_count():
    import snowflake.snowpark as snowpark
    session = snowpark.Session.builder.getOrCreate()
    result = session.sql("SELECT COUNT(*) FROM employees").collect()
    return result[0][0]
$$;

Usage:

sql

CALL get_employee_count();

4. When to Use UDFs vs. Stored Procedures

FeatureUDFsStored ProceduresReturn TypeScalar valuesTable results or execution logsBest forSimple calculations, transformationsComplex logic, transactionsSupportsSQL, JavaScript, PythonJavaScript, PythonExecution ScopeSingle rowMultiple queries and transactions

5. Best Practices for Snowflake UDFs & Stored Procedures

✅ Optimize UDF performance (avoid excessive loops).
✅ Use SQL-based UDFs for better performance over JavaScript/Python.
✅ Limit stored procedure complexity to improve maintainability.
✅ Test functions before deploying in production.

6. Conclusion

Snowflake’s UDFs and Stored Procedures enable powerful custom logic and automation for data processing. Understanding when to use UDFs vs. stored procedures helps optimize performance and maintainability.

WEBSITE: https://www.ficusoft.in/snowflake-training-in-chennai/