Implement Snowflake UDF for leave request days calculation

Discover how to implement a UDF (User-defined function) in Snowflake with Kronos by codbex, calculating leave request days and factoring in holidays from OpenHolidays API.

Prerequisites

To implement the UDF logic you will need to deploy a Kronos instance on Snowflake.
Here you can find a blog which will help you with this task.

Implementation steps

Follow the steps below or watch the recorded video.

1. Create a Snowflake UDF project

• open Kronos and login
• at the Welcome view search for Snowflake and select Snowflake Function API template. If the view is missing - Window -> Show View -> Welcome
  
• type project and file name (service name) - for example leave-request-days and leave-request-days-service
• click on Ok button
  
• a simple Snowflake Function project will be automatically generated for you
  

1. Let's see what was generated
   • api/leave-request-days-service.ts is a simple REST service which complies with the Snowflake UDF specification. This service will be called when the UDF is used.
   • api/function-data-dto.ts describes the REST service parameter and result types
   • security/api-constraints.access allows calling the generated service without authentication from Kronos side
2. Define a table in our application which will hold the leave requests
   • create folder named db
   • create file leave-requests.table in folder db
   • right-click on the file and select Open With -> Code Editor
   • paste this content
   • save the file
3. Add sample data to the LEAVE_REQUESTS table
   • Define data
     • create file LEAVE_REQUESTS.csv in folder db
     • right-click on the file and select Open With -> Code Editor
     • paste this content
     • save the file
   • Configure the CSV import
     • create file sample-data.csvim in folder db
     • right-click on the file and select Open With -> Code Editor
     • paste this content
     • save the file
   • Publish the project by clicking on Publish All button
   • After a few seconds, open the Database perspective and check whether the table and its data have been created in the SNOWFLAKE data source
     

     SELECT * FROM LEAVE_REQUESTS;

     
     You may need to click on the `Refresh` button to see the newly created table.
4. Implement the logic for leave request days calculation. In folder api:
   • create data util file date-util.ts with this content
   • add OpenHolidays API client in file open-holidays-api-client.ts which returns all holidays for a given period. Here is the content of the file.
   • let’s use the API client in the UDF logic located in the leave-request-days-service.ts file by replacing its content with this
   • save all files and publish the changes
5. Define Snowflake UDF
   • go to Database perspective (alternatively, you can use a Snowflake worksheet)
   • select SNOWFLAKE data source
   • create function calculate_leave_request_days using the following sql

     USE ROLE CONTAINER_USER_ROLE;
     USE DATABASE CONTAINER_HOL_DB;
     
     CREATE OR REPLACE FUNCTION calculate_leave_request_days (countryIsoCode string, fromDate date, toDate date)
       RETURNS integer
       SERVICE=codbex_kronos
       ENDPOINT='app-endpoint'
       AS '/public/ts/leave-request-days/api/leave-request-days-service.ts';

     It is important that SERVICE and ENDPOINT match the configurations of your Kronos instance. Also, make sure that the REST path in AS matches your implementation.
     CREATE FUNCTION reference here.

Test implemented Snowflake UDF

We have the following sample data in table LEAVE_REQUESTS.

Let's use the created function to calculate the number of leave request days for our entries. Execute the following sql to calculate column REQUESTED_DAYS

UPDATE LEAVE_REQUESTS
SET REQUESTED_DAYS = calculate_leave_request_days(COUNTRY_ISO_CODE, FROM_DATE, TO_DATE);

Now, let's see whether the days were calculated correctly.

SELECT * FROM LEAVE_REQUESTS;

According to the German public holidays calendar for December 2024, it seems that the implemented UDF is working.

Congratulations, you have implemented Snowflake UDF using Kronos!

---

Summary

Using Kronos you can

• easily implement simple and complex Snowflake UDFs
• use languages like TypeScript, JavaScript or ABAP in your implementation
• use the comprehensive codbex SDK which uses different modern open source projects for messaging, jobs scheduling, REST, OData, mails etc.
• benefit from the codbex platform, tooling and modules

The project we implemented can be found in this GitHub repository.

---

I hope you enjoyed this blog. Stay tuned for more great functionality by codbex!
If you have any questions, ideas or want to contribute, feel free to contact us.