Snowflake Merge: Examples and Performance

The Snowflake Merge command is used to perform Insert, Update and Delete on the target table with the changes made onto source tables. In this article, we aim to discuss the Snowflake Merge command, its syntax, the required parameters, and a few examples.

Introduction to Snowflake

Snowflake is a cloud implemented data warehouse that uses Amazon Web Services, Google Cloud Platform, or Azure to manage the infrastructure. It is available as a Software-as-a-Service offering.

Snowflake Architecture has three main components:

Database Storage: Snowflake uses the file system of cloud infrastructure to store encrypted, compressed, and distributed data to optimize the performance. The data stored in the file systems can only be accessed via SQL Interface to view, update, and insert the data. 

Query Processing: Snowflake processes the queries issued into its Virtual compute cluster embedded with MPP engines to execute the job parallelly and independently without affecting the other cluster groups. Snowflake automatically manages the cluster’s scale up and down that allows you to shut down or pause the cluster when not in use.

Cloud Services:  Cloud Services manages and holds all the services offered by Snowflake. It manages sessions, authentication, encryption, infrastructure management, etc.

Key Features of Snowflake

• Scalable Architecture: Automatically scales up or down to handle any volume of data or query workload.
• Data Sharing: Allows secure sharing of data across organizations without copying it.
• Support for Multiple Workloads: Enables data warehousing, data engineering, data lakes, and analytics in one platform.
• Secure Data: Offers robust security features like encryption, multi-factor authentication, and compliance with industry standards.
• Near-Zero Maintenance: No need to manage infrastructure, as Snowflake is fully managed and handles updates automatically.

In this blog post, you’ll learn about the Snowflake Merge command, its syntax, and how to use it, and a few examples.

What is Snowflake Merge

The Snowflake Merge command allows you to perform merge operations between two tables. The Merge includes Insert, Delete, and Update operations on the record in the table based on the other table’s values. Snowflake Merge command performs the following: 

• Update records when the value is matched.
• Insert records when the conditions are not matched.
• Delete records when the conditions are matched.

The Need for Snowflake Merge

Snowflake Merge command is required when you want to perform some alterations on the table to update the existing records, deleting the old/inactive records, or adding new rows from another table.

Merge is usually helpful when you want to perform Change Data Capture with Slow changing dimensions.

Snowflake offers two clauses to perform Merge:

1. Matched Clause – Matched Clause performs Update and Delete operation on the target table when the rows satisfy the condition. 

2. Not Matched Clause – Not Matched Clause performs the Insert operation when the row satisfying conditions are not matched. The rows from the source table that are not matched with the target table will be inserted

Best Practices: High Performance ETL to Snowflake

Download for Free

Snowflake Merge Command

You can use the MERGE command by using the following syntax:

MERGE INTO <target_table> USING <source> ON <join_expr> { matchedClause | notMatchedClause }

Where:

matchedClause ::=
  WHEN MATCHED [ AND <case_predicate> ] THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> ... ] | DELETE } [ ... ]

notMatchedClause ::=
   WHEN NOT MATCHED [ AND <case_predicate> ] THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )

Fill the following parameters to perform Merge operation in Snowflake.

Required Parameters

<target table> – This specifies the target table to be merged.

<source> – This specifies the source table or subquery from where the records are referenced.

<join_expr> – Specifies the join condition between source and target.

matchedClause (for Update and Delete)

WHEN MATCHED … THEN UPDATE <col_name> = <expr> | DELETE – Performs Update or Delete when condition matches.

SET <col_name> = <expr> – Specifies the column in the target table to be updated or deleted based on the condition.

notmatchedClause (for Insert)

WHEN NOT MATCHED … THEN INSERT – Specifies Insert action to perform when the values do not match with the target.

( col_name [ , … ] ) – Specifies the column names in the target table to be inserted.

VALUES ( expr [ , … ] ) – Specifies the corresponding expressions for the inserted column values.

Example, and Usage

You can perform various operations on Snowflake Merge, but first, let’s create a source and target table and insert a few rows.

create table target_table (id integer, description varchar);
create table source_table (id integer, description varchar);

Insert Target table with description as – “This is old value”.

insert into target_table (id, description) values
    (10, 'this is the old value'),
    ;

Insert source table with the description as – “This is new value”. Snowflake Merge will update the target table value by source table value.

insert into source_table (id, description) values
    (10, 'this is the new value')
    ;

Perform Merge Operation – Update

merge into target_table using source_table 
    on target_table.id = source_table.id
    when matched then 
        update set target_table.description = source_table.description;

After the successful execution of the above query, you can run the select command to view the data in the target table.

| id  | description           |
|-----|-----------------------|
| 10  | this is the new value |

Perform Merge Operation – Insert + Update

Insert few records into the source table: 

insert into source_table (id, description) values
(11, 'Value1 from source'),
(12, 'Value2 from source'),
(13, 'Value3 from source)');

Perform Merge (Insert + Update).

merge into target_table using source_table 
    on target_table.id = source_table.id
    when matched then 
        update set target_table.description = source_table.description
    when not matched then 
        insert (id, description) values (source_table.id, source_table.description);

The above operation will insert 3 new rows into the target table as new IDs are not present.

| id  | description          |
|-----|----------------------|
| 10  | this is the old value|
| 11  | Value1 from source   |
| 12  | Value2 from source   |
| 13  | Value3 from source   |

Perform Merge Operation (Update, Delete and Insert)

Insert few records into the source table: 

truncate table source_table;
insert into source_table (id, description) values

(14, 'Value1 to Insert'),
(15, 'Value2 to Insert);

Perform Merge (Insert + Update + Delete).

merge into target_table using source_table 
    on target_table.id = source_table.id
when matched and source_table.id = 10 then delete
    when matched and source_table.id >10 then 
        update set target_table.description = source_table.description
    when not matched then 
        insert (id, description) values (source_table.id, source_table.description);

Using Hevo Data to Load Data into Snowflake

Hevo is a No-code Data Pipeline. It can efficiently load Snowflake’s Data by setting Snowflake’s warehouse as the source and extracts the raw data to transform and visualize.

Steps to load data into Snowflake using Hevo Data

1. Sign up on the Hevo Data platform.
2. Connect to sources with the help of 150+ available connectors to extract data.
3. Select Snowflake warehouse, database, and schema as the destination.
4. Provide the user credentials and connect to the server and start loading data.

Conclusion

Snowflake is a powerful, fully managed platform used for data warehousing and analytics. In this blog post, you have learned about the Snowflake Merge command, its use, along with some examples. Use the Snowflake INSERT command to add new records seamlessly. Discover more at Snowflake Insert.

FAQ