Snowflake conditional code: adding new column(idempotent script)

Solution 1:

You can use something like this. It will report the failure to add the column if it already exists, but it will handle the error so it won't interfere with the execution of a sql script:

create or replace procedure SafeAddColumn(tableName string, columnName string, columnType string)
returns string
language JavaScript
as
$$
    var sql_command = "ALTER TABLE IF EXISTS " + TABLENAME + " ADD COLUMN " + COLUMNNAME + " " + COLUMNTYPE + ";";
    var strOut;
    try {
        var stmt = snowflake.createStatement( {sqlText: sql_command} );
        var resultSet = stmt.execute();
        while (resultSet.next())  {
            strOut = resultSet.getColumnValue(1);
        }
    }
    catch (err)  {
        strOut = "Failed: " + err;   // Return a success/error indicator.
    }
    return strOut;
$$;

CREATE OR REPLACE TABLE tab(i INT PRIMARY KEY);
INSERT INTO tab(i) VALUES(1),(2),(3);
SELECT * FROM tab;

call SafeAddColumn('tab', 'col', 'varchar(10)');
select * from tab;
call SafeAddColumn('tab', 'col', 'varchar(10)');

Solution 2:

Although Snowflake has implemented a pretty rich mix of DDL and DML for their SQL implementation, when it comes to procedural code they seem to be relying on JavaScript, at least at this point. But you should be able to accomplish your idempotent ALTER script through a JavaScript stored procedure.

I'm afraid I lack the JavaScript skills to provide you with a working sample myself at this point. The organization I'm with recently adopted Snowflake, though, so I'll share some of my research.

Here's a recent blog post on just this question:

Snowflake Control Structures – IF, DO, WHILE, FOR

Snowflake's overview documentation regarding stored procedures:

Stored Procedures

On the page above, what is currently the third link down contains extensive sample code.

Working With Stored Procedures