Snowflake rerunnable script for CICD

Question

How to execute a script with the below steps in Snowflake:

1.) check the environment if dev, qa, sit, prod using account url, current_account function. 2.) if the current_account is qa.snowflakcomputig.com then 3.) check if there is a table named TABLEA 4.) if YES, then rename table to TABLEB

I want to achieve this using Snowflake SQL. I know we cannot write anonymous block, could this be achieved using just SQL, session variables and any new rich functions that snowflake might provide.

Answer 1

1. SELECT current_account() gets you this.
2. you should know which account you are connected to, so we'll skip this piece.
3. skip this, since you can accomplish the same thing by the next step.
4. ALTER TABLE IF EXISTS tablename RENAME TO tablename_new would be the easiest way to accomplish this. If the table doesn't exist, nothing will happen.

This logic could easily be wrapped into a stored procedure, but at the moment that would have to be Javascript, not SQL.