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).

user_12756381 · Answer

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

user_5790584 · Answer

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

user_5070879 · Answer

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

user_38368 · Answer

Building on Lukasz answer, to include database in condition you can use:

execute immediate $$
BEGIN
  IF (
    NOT EXISTS(
      SELECT * 
      FROM "INFORMATION_SCHEMA"."COLUMNS"
      WHERE 
        "TABLE_CATALOG" = 'DB_NAME'
        AND "TABLE_SCHEMA" = 'SCHEMA_NAME'
        AND "TABLE_NAME" = 'TABLE_NAME' 
       AND "COLUMN_NAME" = 'col_name'
    )
  ) THEN
    ALTER TABLE IF EXISTS "DB_NAME"."SCHEMA_NAME"."TABLE_NAME"
      ADD COLUMN "col_name" VARCHAR NULL;
  END IF;
END;
$$;

Snowflake conditional code: adding new column(idempotent script)

4 Answers