Snowflake Stored Procedure Tips
A guide on things to look out for when writing stored procedures within Snowflake
Snowflake, being a Data Warehouse is no different, it too, offers users the ability to create Stored Procedures (SP), and with recent announcement around Snowflake, its becoming a popular choice as part of data processing.
In this article, I won’t be showing you how to create SP, rather, my 7 tips and things to look out for when creating them. These are based on my experience having worked on Snowflake and SP extensively.
Note: Many of the examples will relate to Javascript SP but can also apply to Python and Java as well.
1. Determine if it should be OWNERS right or CALLERS right
Before creating the procedure, ALWAYS think about who or what will be using this SP. This is important because it will determine if SP will assume the role and privileges of the executor or its owner.
Using OWNER rights:
You may want to use OWNER rights if you want your developers to run restricted admin tasks without actually granting them access to underlying objects.
E.g. Creating the procedure as an ADMIN (ownership is ADMIN) to allow ANALYSTS to unload their table into a cloud storage without actually giving them access to the Stage and Integration. The stored procedure will use the ADMIN privilege to run the unload task. This practice is done often to restrict the analyst from being able to UNLOAD sensitive data out of Snowflake.
Using CALLER rights:
Use this if you want to create a common procedure that can be used by many users, and within the scope of their privileges. This may be where you have a stored procedure that can be run by anyone to trigger a chain of SQL commands such as create, insert statements.
E.g be a stored procedure that can be used by everyone such as to send notifications to authorised email addresses within Snowflake. Feel free to check out my article on that.
2. CAPITALISED all your procedure parameters everywhere!
When you create a Snowflake parameterised stored procedure, it’s important to take note of case-sensitive parts. As per Snowflake’s documentation, all of the “SQL parts” of Snowflake Procedures will be capitalised unless it is lower-cased and quoted, while in the procedure section such as code written in javascript, will remain as it is written, and case-sensitive.
Below is an example of what will not work:
As Snowflake automatically capitalises SQL part under causing your parameter to not align to how it’s reference in the Javascript section, causing the Proc to fail when called.
In order to use lowercase parameter, just double quote them "input" .
So, I’ll just repeat it, let’s make it easy, safe, … Just capitalised your parameters.
3. Procedures are unique by their name and parameters, empty your bins!
Yes, I know this is documented in Snowflake in many places but I’ve seen cases where there are 10+ stored procedures with the same name but different parameters, likely due to development phases. This becomes quite messy and difficult to track which one is actually being used for production work.
A habit that I got myself in doing is to create drop procedure statements for every create procedure statements I do. This just allows you to keep your environment clean and empties the bin after you finish with the developments. A messy environment with dormant codes makes your code harder to debug, always take care of your code and environment as if you’re going to hand it over to someone one day.
4. Many ways to embed parameters into your SQL, just make it easy to read.
Snowflake provides you many ways to embed your parameters into SQL statements within JavaScript. Choose an option that is most cleaned and easy to read. Your code should be able to document itself as much as it can.
1. Concatenate your strings and add parameters in between. Although this is easy to write, it does make it hard to read if there are a lot of accumulations and concatenations for parameter embeds:
2. I would recommend using variable replacements (i.e template literals for Javascript, f-Strings for Python), this also allows you to present your SQL in multiline without concatenations:
3. If you’re concerned about SQL Injections using the above method, then the variable binding is another good approach. The only issue with this is that you need to know your variable positions:
5. Make your life easier later and just use $$ to define your code portion
There are many ways to indicate the beginning and end of the Stored procedure either with a single quote ' or $$.
When you write your code part, especially trying to create SQL statements to run that contains single quotes for attributes such as insert statement as shown above, just make it the money shot and use $$, it makes it a lot easier for you to copy that SQL statement in and out of the procedure to DEBUG. Also, you don’t have to quote twice.
Remember, write your codes for others, not just for yourself so make it easy to read, run, and debug.
6. Always error handle your code like wrap it up in a try-catch, trust me, just do it..
Think about it, if you fall, you want someone to catch you so you don’t hurt yourself. Well, the same goes for your Python and JavaScript codes. Ideally, you should always wrap your code up in a try and catch so that failures are properly caught.
Say you have a common stored procedure that executes two insert statements, it may be that you want to know which statement failed. A try and catch will be able to assist you with this.
So just do yourself a favor and always head towards an error-handling approach and wrap it in a try-catch.
7. Just keep it simple, seriously
Keep your stored procedure short and simple. A stored procedure should serve a particular purpose. If you let your SP grow, it becomes very challenging to debug your code and identify the cause of your issue. A simple rule is if your stored procedure is performing more than a single task, break it up into child procedures. Create a parent procedure that calls the child procedure. This also makes your code reusable and it really just keeps it simple.
Also, this drives a test-driven approach, ideally you should be unit testing your code to ensure it works as expected based on scenarios, especially when you make code changes.
Thank you kindly for reading my article, I hope you find it useful.
About Me
I love writing medium articles and contribute towards a knowledge-sharing community. Outside making fried chicken, I help businesses build enterprise cloud and data solutions. Feel free to connect with me and say hi, and do let me know you came across my article!
