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.

Table of Contents

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

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

Integrated Platform: Snowflake is a fully managed cloud infrastructure and is available as Software-as-a-Service via paid subscriptions.

Result Caching: Snowflake works on the caching model so that when the same query is re-issued, it quickly results from the cache. Snowflake uses persisted(within the session) query results to avoid re-generating the output when nothing has changed.

Data Security:  Snowflake provides a high level of security to the data being stored and processed. It also allows secure sharing of data through one account to another.

File formats Support:  Snowflake has excellent support for structured and semi-structured data such as JSON, Avro, ORC, Parquet, and XML data.

Standard and Extended SQL Support: Snowflake has excellent support for ANSI SQL and supports advanced SQL functionality like Merge, Lateral view, statistical functions, and many more.

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.
Hevo, A Simpler Alternative to Integrate your Data for Analysis

Hevo offers a faster way to move data from databases or SaaS applications into your data warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Get Started with Hevo for Free

Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line. 

Check out some of the cool features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Real-time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Connectors: Hevo supports 100+ integrations to SaaS platforms, files, databases, analytics, and BI tools. It supports various destinations including Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; and MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL databases to name a few.  
  • Scalable Infrastructure: Hevo has in-built integrations for 100 plus sources that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

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. 
  1. 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
Download the Cheatsheet on How to Set Up ETL to Snowflake
Download the Cheatsheet on How to Set Up ETL to Snowflake
Download the Cheatsheet on How to Set Up ETL to Snowflake
Learn the best practices and considerations for setting up high-performance ETL to Snowflake

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.

View the data

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.

View the data

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 100+ 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.

However, when it comes to fully managed ETL, you can’t find a better solution than Hevo. It is a No-code Data Pipeline that will help you move data from multiple data sources to your destination.

However, when it comes to fully managed ETL, you can’t find a better solution than Hevo. It is a No-code Data Pipeline that will help you move data from multiple data sources to your destination. It is consistent and reliable. It has pre-built integrations from 100+ sources. So, Sign Up for a 14-day free trial to give Hevo a try.

Share your thoughts on Snowflake Merge in the comments below!

Vishal Agrawal
Freelance Technical Content Writer, Hevo Data

Vishal has a passion towards the data realm and applies analytical thinking and a problem-solving approach to untangle the intricacies of data integration and analysis. He delivers in-depth researched content ideal for solving problems pertaining to modern data stack.

No-code Data Pipeline for Snowflake

Get Started with Hevo