Merge Transforms

Merge components on the Snowflake Data Plane allow you to merge data from your upstream component into the existing data in your transform using a Snowflake MERGE statement. To learn more about MERGE statements in Snowflake, check out Snowflake's documentation.

Merge Statement Syntax

Merge statement is where you will write the logic for your merge component. For information on syntax for merge components and how to trigger certain actions on inserts, updates, and deletes of records, check out Snowflake's documentation.

The basic syntax of the MERGE statement in Snowflake is as follows:

MERGE INTO target_table
USING source_table
ON condition
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

Syntax Components

Below is a breakdown of the syntax components:

Snowflake Pricing and Merge Statement

Snowflake's pricing is based on the amount of computing resources (virtual warehouses) used, and the amount of storage consumed. Each SQL statement executed in Snowflake consumes a certain amount of these resources, depending on its complexity and the amount of data it processes.

Therefore, if the MERGE statement is causing a significant increase in resource usage, it could potentially impact costs. However, this would largely depend on the size of the tables being merged and how frequently the operation is being performed.

Writing to Different Databases

When working with Snowflake's MERGE transform, it's possible to write data to a different database from the Transform component.

Triggering the merge after a write: Use SQL to trigger the MERGE operation after a write.

Changing the target location: In some cases, you might need to move data to a different schema, database, or even a different Snowflake account. It's essential to ensure that all necessary permissions are in place and that the impact on existing workflows and applications is carefully considered.

Usage Example

Here's an example of using the Merge Transform in Snowflake:

MERGE INTO merge_test
USING student_data AS source_table
ON merge_test.id = source_table.id
WHEN MATCHED THEN
    UPDATE SET merge_test.name = source_table.name
WHEN NOT MATCHED THEN
    INSERT (id, name, email)
    VALUES (source_table.id, source_table.name, source_table.email)

In the example provided, the merge_test table serves as the target table, while the student_data table acts as the source table. The matching condition between the target and source tables is based on the id column.

When a match is found between the id values in the target and source tables, the name column in the target table is updated with the corresponding value from the source table. This allows for updating existing records with new information.

On the other hand, when no match is found between the id values, a new record is inserted into the target table. This new record includes the id, name, and email values from the source table, allowing for the addition of new data.

The Merge Transform in Snowflake provides a convenient way to merge data from one table into another based on specified conditions. By defining the target and source tables, specifying the matching condition, and defining actions for matched and unmatched records, users can efficiently update and insert data as needed.