Stored Procedures and User-Defined Functions in Snowflake

Sixteenth Chapter: Stored Procedures & User-Defined Functions for the SnowPro Core Certification

Sometimes, we may want to perform operations that Snowflake does not allow us to do with the built-in, system-defined functions. This will not be a problem as we can use Stored Procedures and User-Defined Functions. Let’s see them and understand their differences in one of the shortest chapters of the course!

1. Introduction
2. Stored Procedures
3. User-Defined Functions (UDFs)
4. User-Defined Table Functions (UDTFs)
5. Typical Exam Questions

Remember that all the chapters from the course can be found in the following link:

INTRODUCTION

Store Procedures & User-Defined Functions (UDFs) are like functions in any other programming language. You can use JavaScript, SQL, Java, Python, and Scala to extend Snowflake functionality. The differences between them are shown in the following table, although let’s see them in detail in the following sections.

STORE PROCEDURES

Stored procedures allow you to extend Snowflake SQL by combining it with JavaScript so that you can include programming constructs such as branching and looping. Using the Snowpark library, you can also write them in Python, Java or Scala. They return either a SINGLE Value or nothing. The returned values CANNOT be used directly in a SQL statement.

USER-DEFINED FUNCTIONS (UDFs)

User-defined functions (UDFs) let you extend the system to perform operations that are not available through Snowflake’s built-in, system-defined functions. You can use SQL, JavaScript, Java, Python, and Scala (this last one as Preview Feature).

The difference with Store procedures is that:

• It returns one output row for each input row. The returned row consists of a single column/value.
• It must return something.
• The returned values CAN be used directly in the SQL statement.

---- Function definition ----
create or replace function add5 (n number)
  returns number
  as 'n + 5';

---- Calling the function ----
SELECT add5(1)

---- Result ----
+---------+
| ADD5(1) |
|---------|
|       6 |
+---------+

USER-DEFINED TABLE FUNCTIONS (UDTFs)

UDTFs can return multiple rows for each input row; that’s the only difference with UDFs.

---- Function definition ----
create function t()
    returns table(msg varchar)
    as
    $$
        select 'Hello'
    $$;

---- Calling the function ----
select msg 
    from table(t())
    order by msg;

---- Result ----
+-------+
| MSG   |
|-------|
| Hello |
| World |
+-------+

TYPICAL EXAM QUESTIONS

Which Snowflake object returns a set of rows instead of a single, scalar value, and can be accessed in the FROM clause of a query?

1. UDF
2. UDTF
3. Stored procedure

Solution: 2.

Do UDFs support both SQL & JavaScript in Snowflake?

1. True
2. False

Solution: 1. They also added Python and Java recently!

Are UDFs, UDTFs, and Stored Procedures account or schema-level objects in Snowflake?

1. Schema level
2. Account-level

Solution: 1

Thanks for Reading!

If you like my work and want to support me…

1. The BEST way is to follow me on Medium here.
2. Feel free to clap if this post is helpful for you! :)
3. More Snowflake SnowPro Core practice exam questions? Find them at FullCertified.com!