I'm a bit new to Snowflake and trying to figure out how to correctly add conditional logic in snowflake. I'm running this script below as part of my CI/CD deployment in snowsql where I'm pre-creating tables depending on the environment I'm in. Here's my script:

ALTER SESSION SET TRANSACTION_ABORT_ON_ERROR = true;

SELECT '&env'; -- returns PROD, STG or DEV

SET database_name = CONCAT('&env','_RAW');
SHOW VARIABLES;
USE IDENTIFIER($database_name);

CREATE SCHEMA IF NOT EXISTS S1;
USE SCHEMA S1;

BEGIN
    IF $database_name <> 'PROD_RAW' THEN

        CREATE TABLE IF NOT EXISTS PM
        CLONE PROD_RAW.S1.PM;

        CREATE TABLE IF NOT EXISTS AP
        CLONE PROD_RAW.S1.AP;
        
    END IF;
END;

------------------------------------------------------------------

ALTER TABLE PM
ADD COLUMN IF NOT EXISTS status BOOLEAN;

------------------------------------------------------------------

CREATE SCHEMA IF NOT EXISTS D1;
USE SCHEMA D1;

------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS DATA_MAPPING (
    key_column VARCHAR,
    scope VARCHAR,
    value_from VARCHAR,
    value_to VARCHAR
);

I need to add this IF statement because for some reason if I'm in my PROD_RAW database, Snowflake doesn't like this kind of creation statements even though I have IF NOT EXISTS check:

CREATE TABLE IF NOT EXISTS {table_name}
CLONE PROD_RAW.S1.{table_name};

Can anyone please advise and help me out?

I'm a bit new to Snowflake and trying to figure out how to correctly add conditional logic in snowflake. I'm running this script below as part of my CI/CD deployment in snowsql where I'm pre-creating tables depending on the environment I'm in. Here's my script:

ALTER SESSION SET TRANSACTION_ABORT_ON_ERROR = true;

SELECT '&env'; -- returns PROD, STG or DEV

SET database_name = CONCAT('&env','_RAW');
SHOW VARIABLES;
USE IDENTIFIER($database_name);

CREATE SCHEMA IF NOT EXISTS S1;
USE SCHEMA S1;

BEGIN
    IF $database_name <> 'PROD_RAW' THEN

        CREATE TABLE IF NOT EXISTS PM
        CLONE PROD_RAW.S1.PM;

        CREATE TABLE IF NOT EXISTS AP
        CLONE PROD_RAW.S1.AP;
        
    END IF;
END;

------------------------------------------------------------------

ALTER TABLE PM
ADD COLUMN IF NOT EXISTS status BOOLEAN;

------------------------------------------------------------------

CREATE SCHEMA IF NOT EXISTS D1;
USE SCHEMA D1;

------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS DATA_MAPPING (
    key_column VARCHAR,
    scope VARCHAR,
    value_from VARCHAR,
    value_to VARCHAR
);

I need to add this IF statement because for some reason if I'm in my PROD_RAW database, Snowflake doesn't like this kind of creation statements even though I have IF NOT EXISTS check:

CREATE TABLE IF NOT EXISTS {table_name}
CLONE PROD_RAW.S1.{table_name};

Can anyone please advise and help me out?

• sql
• snowflake-cloud-data-platform

• HI , you can try using CREATE TABLE IF NOT EXISTS identifier($table_name) CLONE PROD_RAW.S1.($table_name) ; – Himanshu Kandpal Commented Jan 31 at 19:02

2 Answers 2

you can wrap it with EXECUTE IMMEDIATE $$.

Here is what I tried in snowsql and it runs fine and it was able to create a PM table in DEV_RAW.S1 schema.

set env = 'DEV';
SET database_name = CONCAT($env,'_RAW');

use database identifier($database_name);
CREATE SCHEMA IF NOT EXISTS S1;
USE SCHEMA S1;

EXECUTE IMMEDIATE $$
DECLARE 
    database_name varchar ;  
    
begin
    SELECT CONCAT( $env,'_RAW')  into :database_name ;
    IF (database_name <> 'PROD_RAW') THEN 
        CREATE TABLE IF NOT EXISTS PM
        CLONE PROD_RAW.S1.PM;
    END IF;
   
end;
$$
;

You can try this script to execute:

EXECUTE IMMEDIATE
$$
DECLARE
    env VARCHAR := 'dev';
    database_name VARCHAR := :env||'_RAW';
BEGIN
    USE DATABASE IDENTIFIER(:database_name);
    CREATE SCHEMA IF NOT EXISTS S1;
    USE SCHEMA S1;

    IF (:database_name <> 'PROD_RAW') THEN
        CREATE TABLE IF NOT EXISTS PM
        CLONE PROD_RAW.S1.PM;

        CREATE TABLE IF NOT EXISTS AP
        CLONE PROD_RAW.S1.AP;
    END IF;

    ALTER TABLE PM
    ADD COLUMN IF NOT EXISTS status BOOLEAN;

    CREATE SCHEMA IF NOT EXISTS D1;
    USE SCHEMA D1;

    CREATE TABLE IF NOT EXISTS DATA_MAPPING (
        key_column VARCHAR,
        scope VARCHAR,
        value_from VARCHAR,
        value_to VARCHAR
    );
    
    return 'Success';
END;
$$;

you may also refer to this documentation: Understanding blocks in Snowflake Scripting