User-Defined Functions and Stored Procedures

1. User-Defined Functions and Stored Procedures

In this video you'll learn how to package reusable logic into UDFs and stored procedures, and when to reach for each one.

2. What is a UDF?

Harbr's data team writes the same delivery-scoring logic in three separate queries. Every time a threshold changes, all three need updating. That is the problem UDFs solve. A UDF is a named function you define once and call from anywhere in SQL. It handles custom operations that built-in functions don't cover, supports SQL, Python, Java, Scala and JavaScript, and always returns values — never modifies data. You can also mark it SECURE, which hides the implementation from callers — important for proprietary logic and data sharing.

3. SQL UDFs

SQL UDFs handle most transformation work: CASE logic, expressions, string formatting. They are easy for any SQL developer to read and maintain as demonstrated in the above example.

4. Python & Other Programming Language UDFs

Snowflake also supports Scala, JavaScript, Java, and Python UDFs for cases that require iteration or external libraries such as parsing a weight string or calling a package that has no SQL equivalent. We've demonstrated this with a Python example. Unless you have that specific need, SQL is the right starting point. Simpler to write, simpler to review, and no context-switching for your team.

5. UDFs in the Pipeline

UDFs sit in the transformation layer — between raw storage and your reports and dashboards. A raw table holds numbers; the UDF turns those numbers into business-meaningful labels that every downstream consumer can use consistently. Adding the SECURE keyword hides the function's implementation from other users. They can call it and get results, but they cannot inspect the logic inside. This matters when the function contains proprietary business rules, or when you are sharing data across Snowflake accounts where non-secure functions would expose their definition to consumers of the share.

6. What is a Stored Procedure?

A stored procedure handles multi-step orchestration that a UDF cannot. It can declare variables, loop over results, handle errors, and execute DML across multiple tables. The key difference from a UDF is how you call it: stored procedures use CALL as a standalone operation. Looking at the diagram, a single CALL kicks off a sequence — insert matching rows into an archive, capture how many moved, delete them from the source, and return a summary. A UDF cannot do any of that; it can only compute and return a value per row inside a SELECT.

7. Stored Procedure: Snowflake Scripting

Here's a full stored procedure in Snowflake Scripting. It takes a cutoff value, archives shipments older than that threshold, and returns a summary. Following the five steps — we declare rows_moved upfront, then BEGIN the procedure body. The INSERT copies the matching rows into the archive table, and SQLROWCOUNT captures how many were moved. The DELETE then removes them from the source. Finally, RETURN sends back a confirmation message, and END closes the procedure. The :cutoff syntax is how you reference the input parameter inside the SQL commands.

8. UDFs vs Stored Procedures

So when do you use a UDF versus a stored procedure? The key is understanding what each one is designed for. UDFs are called inside a SELECT — they take an input, return one value per row, and can't touch the data. They're your transformation tool, perfect for encapsulating reusable logic like the delivery tier example we saw earlier. Stored procedures are called with CALL, run independently of a query, and can execute DML — inserts, updates, deletes. They're your orchestration tool, designed to automate multi-step workflows like the archive procedure we just wrote. The languages row is worth noting too — stored procedures support Snowflake Scripting, Python, Scala, and more, making them a full programming environment, not just SQL. The simple rule: if it belongs inside a query, use a UDF. If it's doing work on the database, use a stored procedure.

9. Let's Practice!

You've covered UDFs for reusable row-level transformations, where they sit in the pipeline, and Snowflake Scripting as the recommended approach for stored procedures. Let's put that to the test!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.