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
      snow-template.png
    • type project and file name (service name) - for example leave-request-days and leave-request-days-service snow-template-data.png
    • click on Ok button
    • a simple Snowflake Function project will be automatically generated for you
      generated-project.png
  2. 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
  3. 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
  4. 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 publish-all-btn.png
    • After a few seconds, open the Database perspective and check whether the table and its data have been created in the SNOWFLAKE data source
      database-perspective.png 
      SELECT * FROM LEAVE_REQUESTS;

      leave-requests-table-data.png
      You may need to click on the Refresh button to see the newly created table.

  5. 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
  6. 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. leave-requests-before.png

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;

leave-requests-after.png According to the German public holidays calendar for December 2024, it seems that the implemented UDF is working.
german-public-holidays-dec-24.png
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.

Iliyan Velichkov

Iliyan likes SUP, rifle shooting and fishing.