Snowflake - UPSERT from staged files

Question

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

Answer 1

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

https://support.snowflake.net/s/article/how-to-perform-a-mergeupsert-from-a-flat-file-staged-on-s3

and here's the actual merge doc:

https://docs.snowflake.com/en/sql-reference/sql/merge.html

Answer 2

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