Snowflake procedures Javascript or SQL

Question

When writing Snowflake procedures is there any benefit of writing procedures in JavaScript or SQL or vice versa. It depends upon the environment in the organization, if team has experience in JavaScript then they would write in JavaScript.

Is there any recommendation by Snowflake on what to use when ?

Answer 1

Usually you will get the best performance with a pure SQL solution, but not always:

• A UDTF in JS/Java/Python can be faster as it can use less resources (manage memory requirements): https://towardsdatascience.com/sql-puzzle-optimization-the-udtf-approach-for-a-decay-function-4b4b3cdc8596

• Sometimes some complicated SQL UDFs don't work inside a larger query, then JS/Java/Python can solve the problem: https://stackoverflow.com/a/65299548/132438

• Java has a nice packaging mechanism to move from a software engineer's environment to Snowflake: Use a .jar.