Snowflake - UPSERT from staged files

I'm working on building a BULK UPSERT functionality for personal use. I'm currently uploading multiple files into S3, and from there I'm creating a stage using CREATE STAGE command.

CREATE OR REPLACE TEMPORARY STAGE {stage} URL={s3_dir} CREDENTIALS=(aws_key_id='{aws_key_id}' aws_secret_key='{aws_secret_key}') {file_format} 

Now, I'd like to use this stage to do an UPSERT in the destination table in Snowflake. Can somebody provide an example of the same?

TIA

2 Answers

Snowflake uses MERGE command for UPSERT. I believe the example you're after can be found here:

and here's the actual merge doc:

Apparently, MERGE supports following kind of DB objects to move data from source to destination. Sources supported are:

• Internal stage
• External stage
• Table
• Subquery

Following is the SQL snippet that I've used in the codebase:

CREATE OR REPLACE TEMPORARY STAGE DUMMY_STAGE FILE_FORMAT=( TYPE=CSV COMPRESSION=AUTO FIELD_DELIMITER='^' VALIDATE_UTF8=TRUE EMPTY_FIELD_AS_NULL=TRUE ENCODING=UTF8 ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE RECORD_DELIMITER='\r\n' ESCAPE=NONE ESCAPE_UNENCLOSED_FIELD=NONE FIELD_OPTIONALLY_ENCLOSED_BY='"' NULL_IF = ('0000-00-00', '', 'None') ); PUT file://data_files/random.txt @DUMMY_STAGE; MERGE INTO DUMMY_A USING ( SELECT $1 foo , $2 bar FROM @DUMMY_STAGE ) DUMMY_TMP ON DUMMY_A.FOO=DUMMY_TMP.FOO WHEN MATCHED THEN UPDATE SET foo = DUMMY_TMP.foo WHEN NOT MATCHED THEN INSERT ( foo , bar ) VALUES ( DUMMY_TMP.foo , DUMMY_TMP.bar ) ;