User-defined functions in Snowflake

1. User-defined functions in Snowflake

In the final video of this chapter, we'll take a look at something a little different; user-defined functions.

2. What are user-defined functions (UDFs)?

Snowflake supports this with user-defined functions, or UDFs, to "package" complex or often-repeated logic into a single, reusable function. UDFs help to make queries easier to read. UDFs help to reduce the chance of making a mistake, especially if logic is repeated. User-defined functions also provide an easy way for logic to be shared with other users. On the right is a visual example of a user-defined function. Here, we're taking a long, complicated transformation and turning it into a single function. Let's look a bit closer!

3. Refactoring code with a UDF

On the left is a query before creating a UDF. We're passing six arguments to `CONCAT` to create an email address. This is difficult to understand, lends itself to potential mistakes, and is hard to share with others. On the right is the updated query, which implements the `build_email_address` UDF. Using the `build_email_address` function make this query easier to read and understand, and reduces the chance for errors.

4. Defining a UDF

When defining a function in Snowflake, we can follow the same general pattern each time! First, we'll use the keywords `CREATE OR REPLACE FUNCTION`. This is followed by whatever function name we'd like, and a pair of parentheses. Here's the important part; within the parentheses, we'll specify the values that we can pass to the function, as well as the type of those values. We'll see how that works in a second. Next, we'll declare the type of value the function will return using the `RETURN` keyword, followed by the desired type. After the `AS` keyword are two sets of two dollar signs. This is where we'll implement the actual work that the function does.

5. Building an email address

Let's create our `build_email_address` function! Within parentheses, we specify that `build_email_address` can take three values; `first_name`, `last_name`, and `school_name`. Each of these must be of type `TEXT`. After the parentheses, we declare that the value returned must be `TEXT`. Finally, we get to define the logic! Here, behind the scenes, we're really just using the `CONCAT` function to create an email address composed of the first, last, and school names for each student. This eventually returns the final email address, as a `TEXT` value.

6. Using a UDF

Using the `build_email_address` function looks like this. In a single line of code, we're able to use a student's name and school to create their own email address. Not only does it make this query cleaner and easier to read, but it helps to provide a single tool for other Snowflake developers to use!

7. Let's practice!

Pretty cool, huh? Now, let's put everything you learned 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.