An Easy Way to Set Up Stage Tables for Snowpipe

Snowpipe is a powerful tool for automating your data loading process. However, it's not always easy to setup all the transformations you may need in the COPY statement. You might need to load data in a temporary table and/or perform some data filtering. If so, let's take a closer look at a how to set that process up using some stored procedures.

To get started, you will need to replicate the structure of the target table. Snowflake offers the CREATE TABLE … LIKE operation which creates an empty copy of an existing table. This is super useful because with one simple line you can replicate the table structure.

With respect to constraint enforcement, Snowflake only enforces the NOT NULL constraint. For data loading, you might need to turn it off. To do so, you can use a Stored Procedure like the following:

CREATE OR REPLACE PROCEDURE DROP_NOT_NULLS(TABLE_SCHEMA VARCHAR, TABLE_NAME VARCHAR) 
RETURNS STRING
AS 
$$
DECLARE
    cols cursor for SELECT COLUMN_NAME, IS_NULLABLE FROM    INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=?;
BEGIN
    LET COLUMN_NAME VARCHAR := '';
    LET CMD VARCHAR := '';
    open cols using (TABLE_SCHEMA, TABLE_NAME);
    for record in cols do
        COLUMN_NAME := record.COLUMN_NAME;
        CMD := 'ALTER TABLE ' || TABLE_SCHEMA || '.' || TABLE_NAME || ' ALTER COLUMN ' || COLUMN_NAME || ' DROP NOT NULL';
        execute immediate :CMD;
    end for;
END;
$$;

Now you can call the procedure with this line of code:
CALL DROP_NOT_NULLS('TABLE_SCHEMA','TABLE_NAME');

Finally, you can use some simple code like this to setup your stage tables.

-- FIRST CREATE A TEST TABLE
CREATE OR REPLACE TABLE TEST1(A VARCHAR NOT NULL, B VARCHAR);
-- NOW CREATE A NEW TABLE WITH THE SAME STRUCTURE
CREATE OR REPLACE TABLE TEST2 LIKE TEST1;
-- CHECK its columns. Column A is null? N
DESCRIBE TABLE TEST2;
-- CALL THE UTILITY PROC
CALL DROP_NOT_NULLS('PUBLIC','TEST2');
-- CHECK table columns. Both Column A and B are null? Y 
DESCRIBE TABLE TEST2;

Hope this helps as you navigate your way through the Snowflake Snowpipe. And while you're doing all of that navigating, you should do it as a part of BlackDiamond Studio by Mobilize.Net, the Code Cloud for Snowflake's Data Cloud.