Testing Snowflake UDFs

Depot makes Snowflake User Defined Functions (UDF) development easier with automatic detection and bundling of UDFs with Depot Package schemas.

You write your UDFs using TypeScript in a Depot Package project. The Package is then imported into your Depot CDK project, and UDFs matching schema names are automatically bundled and merged into the schema namespace, ready for use in your Dataset(s).

Write your TypeScript UDFs as modules which you export in order to make them easily testable.

Snowflake UDF module test example

Here is a simple Depot JavaScript UDF definition for use in a Snowflake backed Dataset. The UDF has two inputs - firstName and lastName, and returns a number (a score value based on the lengths of the first and last name inputs).

my.contacts.Score.yml

my.contacts.Score:
  type: function
  language: javascript
  arguments:
    - name: firstName
      type: string
    - name: lastName
      type: string
  returns: number

The function's implementation in TypeScript looks like this:

my.contacts.Score.sfk.ts

export function main(firstName: string, lastName: string): number {
    // Assigns a person a score based on how long their name is.
    let score = 0;
    const multiplier = 10;

    score += firstName.length * multiplier;
    score += lastName.length * multiplier;
    score = addBonus(score, 5);

    return score;
}

export function addBonus(input: number, amount: number): number {
    return input + amount;
}

note

The TypeScript file must have an extension of .sfk.ts in order for bundling to pick it up and include in in the exported Package. The entrypoint should also be named main and exported.

This function's logic can now be easily tested with jest by using the exported main entrypoint for the function.

my.contacts.Score.test.ts

import * as score from "../src/my.contacts.Score.sfk";

describe("UDF test examples", () => {
    it("first and last names should result in expected score", () => {
        const result = score.main("John", "Connor");
        expect(result).toEqual(105);
    });
});