A Simple Parameterized Query in Snowflake Scripting? Not So Easy :(

I remember last February, when they announced the availability of Snowflake Scripting. Oracle had PL/SQL, Microsoft SQL Server had Transact-SQL, it was about time for Snowflake to come up with their own procedural SQL language. All in the general effort of bringing data processing closer to where the data is. And making this data processing less verbose and more intuitive.

Read here the post for free if you do not have a Medium subscription.

It was very exciting for me, as I just had to design and implement — for a large client — a huge generic Snowflake data pipeline with …heavy stored procedures in JavaScript (the only available alternative at that time).

However, this is the first time since that I had a few hours for a rather deep dive. And, despite the obvious utility of this API, …

…I have strong doubts at this moment that Snowflake Scripting is mature enough for production.

Simple Requirements

My need was for a complex generic query encapsulated in a secure tabular function, to be exposed through a secure data share. To keep it simple here, let’s illustrate this with a very simple UNION query, that basically duplicates the rows of any table, when you pass the table name as argument…