Create a Merge Transform

In order to create a merge transform, you can either select Snowflake Merge from the toolbar on the left, or right click on the component you want to create a merge transform off of, and select Create new Transform.

Step 1: Select and Configure Snowflake Merge Transform.

• Under the TRANSFORM tab of the Tool Pane, select Snowflake Merge.
• Provide a name and optional description.

Step 2: Configure your MERGE statement.

To refer to components in the graph as part of your SQL statement, use the component ID as the table name in curly braces. For example: SELECT * FROM {{my_ascend_component_id}}.

• The below code is an example of a MERGE statement.

MERGE INTO {{target}} as output USING {{Student_Data}} as source_table  
     ON output.id = source_table.id  
     WHEN NOT MATCHED THEN INSERT VALUES (  
         id,  
         name,  
         email,  
         phone,  
         age,  
         ASCEND\_\_PARTITION_ID  
     )  
     when matched then  
        update set output.name = source_table.name;  
        -- comment another comment

Step 3: Configure your Create Table statement.

Ascend will run the following "Create Table" expression to first create the output table that the 'MERGE' statement will then operate on. Reference the name of the output table with {{target}}, e.g. CREATE TABLE {{target}} (amount number);.

create table if not exists {{target}} (
    ID VARCHAR(16777216),
    NAME VARCHAR(16777216),
    EMAIL VARCHAR(16777216),
    PHONE VARCHAR(16777216),
    AGE VARCHAR(16777216),
    ASCEND__PARTITION_ID VARCHAR(16777216)
);

Optional: Configure Data Quality checks and Advanced Settings.

As with Read Connector Components, you can set Data Quality checks and configure custom Spark parameters.

Step 4: Launch your Transform.

• Select CREATE at the top of the configuration pane.