Read parameters from a config file on SnowFlake ( not via SnowSQL )

Question

I am able to read parameters from a local config file on SnowFlake ( using SnowSQL ). But in production environment, SQL will be running in a automated manner (using SnowFlake Tasks).

I have created a task in Snowflake which calls a Stored Procedure. The Stored procedure takes few parameters which I want to read from a config file. So that same Stored Procedure can be used for multiple similar use cases.

Please suggest if there is any work around.

Reference Link : https://docs.snowflake.net/manuals/user-guide/tasks-intro.html

Although it says "Note that a task does not support account or user parameters."

Answer 1

You can't read a config file from a task. The easiest way in my opinion is to put your configuration in a Snowflake table and have your Stored Procedure read any configuration from the table instead.

Answer 2

Since I am not very sure if stored proc can read the config file and hence I agree with the approach @SimonD suggested.

Another alternative (though bit complex design) is to have the config file in JSON format in the S3 bucket which you can load via stage. Refer $ notation to access the respective JSON properties to access the key-value and inject it where needed in the stored procedure. This way, your configuration is still in JSON or text format outside the snowflake and can be managed via S3 (if you are using AWS)

Though I have not tried this approach but looks it should work. This way, snowflake access or accidental DB update can be prevented.

I hope this idea makes sense to you?