Creating Snowflake UDFs with ChatGPT: A Guide for Analysts
As data analysts, we often find ourselves needing specialized functions in Snowflake. Working in Financial services, there are specific Excel functions that provide significant value. I had a need but developer resources were not readily available… Until ChatGPT changed everything!
What is Chat GPT?
ChatGPT is a large language model trained by OpenAI that has taken the world by storm with its ability to generate human-like text. ChatGPT can also generate code for various programming languages, including JavaScript, one of the supported languages used to create UDFs for Snowflake.
How ChatGPT Can Makes Snowflake Data Analysts Workers Smarter?
Now, with the help of ChatGPT even non-developers can prototype and experiment and contribute powerful capabilities. For Snowflake User Defined Functions (UDFs) in particular, ChatGPT is a game changing resource for self paced learning, debugging, and translating existing concepts and patterns you know into Snowflake.
Prompting Chat GPT to Translate Excel to Snowflake
I found quickly that if I provided ChatGPT with a clear set of instructions, I could easily create UDFs that mimic the behavior of Excel formulas. Having a dire need for temporal analysis functions, I was able to create, validate, document, and deploy 3 UDFs based on Excel functions within a couple of hours.
● WORKDAY — Adds or subtracts days (excludes weekends and holidays) from a date
● NETWORKDAYS — Calculates the difference between 2 dates (excludes weekends and holidays)
● NETWORKWEEKS — Technically not an Excel function, this calculates the difference between 2 dates in weeks (excludes weekends and holidays).
The resulting code, examples, and documentation are available on GitHub for anyone to use. My only regret is ChatGPT seemed to wipe out the full chat history. I will make sure to capture and contribute the the specific chat history in the future.
https://github.com/DataToolsPro/SnowUDFExcel
Let the Machine Do Documentation
The beauty of Chat GPT is the value and time savings does not stop with writing code. After completing the function, I asked it to document and comment the code.
Guide to Success
One of the challenges using ChatGPT to create UDFs is ensuring that the generated code is accurate and free of errors.
Here are my recommendations to consider when building Snowflake functions with ChatGPT:
● ChatGPT is not fully up to date on supported data types for UDF arguments. A an example, had to tell it to swap out integers with strings.
● If you get JS errors, don’t worry.. You an feed the error back into ChatGPT and let it re-factor the code.
● Test and validate thoroughly. Technically you can ask it it to generate multiple test cases. As an example, I found the Javascript code did not support negative numbers until I asked it to.
● When you want to update or enhance your code at a later date, you can feed your function back into ChatGPT.
What’s Next?
I am excited to test some more sophisticated functions moving forward. What kind of experiments you have or plan to build with ChatGPT and Snowflake?
