Snowflake conditional code: adding new column(idempotent script)

Question

Let's assume we have a table that contains data as below:

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

Now my goal is to create SQL script that will add a new column to existing table:

ALTER TABLE IF EXISTS tab ADD COLUMN col VARCHAR(10);

Everything works as intended. Except the fact I would like to be able to run script multiple times but the effect should take place only once(idempotence).

If I try to run it again I will get:

SQL compilation error: column COL already exists

---

Normally I would use one of these approaches:

a) Using control structure IF to check metadata tables before executing query:

-- (T-SQL)
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
              WHERE TABLE_NAME='TAB' AND COLUMN_NAME = 'COL')
BEGIN
   ALTER TABLE tab ADD col VARCHAR(10);
END;

db<>fiddle demo

I have not found IF statement in Snowflake's documentation.

b) SQL dialect that supports IF NOT EXISTS syntax:

-- PostgreSQL
ALTER TABLE IF EXISTS tab ADD COLUMN IF NOT EXISTS col VARCHAR(10);

db<>fiddle demo

Most of Snowflake SQL commands contain IF EXISTS/OR REPLACE clauses which means it was written in a way to allow running scripts multiple times.

---

I was considering using code like:

CREATE OR REPLACE TABLE tab
AS
SELECT i, CAST(NULL AS VARCHAR(10)) AS col
FROM tab;

This approach on other hand causes unnecessary table creation and does not preserve metadata(like primary key).

---

Is there a way to achieve similar effect on Snowflake? Preferably by using conditional code(add column is an example).

Answer 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)');

Answer 2

It is possible to write conditional code using Snowflake Scripting.

Working with Branching Constructs

Snowflake Scripting supports the following branching constructs:

• IF-THEN-ELSEIF-ELSE

• CASE

Setup:

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

Code that can be rerun multiple times(subsequent runs will take no effect):

-- Snowsight
BEGIN
  IF (NOT EXISTS(SELECT * 
                 FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_NAME = 'TAB' 
                   AND TABLE_SCHEMA = 'PUBLIC'
                   AND COLUMN_NAME = 'COL')) THEN
    ALTER TABLE IF EXISTS tab ADD COLUMN col VARCHAR(10);
  END IF;
END;

EXECUTE IMMEDIATE is required is run using "classic web interface":

EXECUTE IMMEDIATE $$
BEGIN
  IF (NOT EXISTS(SELECT * 
                 FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_NAME = 'TAB' 
                   AND TABLE_SCHEMA = 'PUBLIC' 
                   AND COLUMN_NAME = 'COL')) THEN
    ALTER TABLE IF EXISTS tab ADD COLUMN col VARCHAR(10);
  END IF;
END;
$$

After:

SELECT * FROM tab;
-- i  col
-- 1  NULL
-- 2  NULL