# Merge Rows (diff)

{% hint style="warning" %}
**Workshop - Merge Rows (diff)**

The Merge row (diff) compares the values between the merging rows and sets a ‘flag’.

In this workshop, you compare incoming records with reference 'golden' records to determine whether the record is Identical requires updating, inserting, or deleting:

* Merge rows (diff) stream
* Merge rows (diff) database
  {% endhint %}

{% embed url="<https://www.loom.com/share/cd56088673de462998803583425d0488?hideEmbedTopBar=true&hide_owner=true&hide_share=true&hide_title=true>" %}
Data Change Capture Sync after Merge
{% endembed %}

***

{% hint style="info" %}
**Create a new Transformation**

Any one of these actions opens a new Transformation tab for you to begin designing your transformation.

* By clicking File > New > Transformation
* By using the CTRL-N hot key
  {% endhint %}

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-aeec8e743f45fde1fa8e569ac62271558405463a%2Fmerged%20row%20diff.png?alt=media" alt="" width="563"><figcaption><p>Merge row (diff)</p></figcaption></figure>

{% tabs %}
{% tab title="1. Merged row (diff)" %}
{% hint style="info" %}
**Merge Rows (diff)**

Let's say we're doing a delta load of new data at specific times ..

Based on keys for comparison, we can use this step to merge reference rows (previous data) with compare rows (new data) to create merged output rows.

A flag in the row indicates how the values were compared and merged. Flag values include:

* identical

The key was found in both rows, and the compared values are identical.

* changed

The key was found in both rows, but one or more compared values are different.

* new

The key was not found in the reference rows.

* deleted

The key was not found in the compare rows.

If the rows are flagged as `deleted`, the merged output rows are created based upon the original reference rows stream.

For `identical`, `new`, or `changed` rows, the merged output rows are created based upon the original compare rows stream.
{% endhint %}

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-27c89fbfacb2a7de4939da8bc6b4a1624aba10d7%2Fmerge%20diff%20step.png?alt=media" alt="" width="563"><figcaption><p>Merge rows (diff)</p></figcaption></figure>
{% endtab %}

{% tab title="2. Synchronize after merge" %}
{% hint style="info" %}
**Synchronize after merge**

This step can be used in conjunction with the Merge Rows (diff) transformation step. The Merge Rows (diff) transformation step appends a Flag column to each row, with a value of "identical", "changed", "new" or "deleted".

This flag column is then used by the Synchronize after merge transformation step to carry out updates/inserts/deletes on a connection table.

This step uses the flag value to perform the sync operations on the database table.
{% endhint %}

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-113fd3cc63ae7bc616a49cc9597fc2c087f46d95%2FSync%20after%20merge.png?alt=media" alt="" width="563"><figcaption><p>STG_ORDERS_MERGED</p></figcaption></figure>

{% hint style="info" %}

* Set the Key from both the Table and Stream.
* Get the Table / Stream Fields and ensure mapping is correct.
* <mark style="color:red;">Dont</mark> Update the Keys..!!
  {% endhint %}

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-34d165184c3e27e3a74e964b058a62fa35040287%2FSync%20after%20merge%20-%20adv.png?alt=media" alt="" width="563"><figcaption><p>Synchronize after merge - Advanced tab</p></figcaption></figure>

<table><thead><tr><th width="154.66666666666666">Option</th><th width="421">Description</th><th>Default Value</th></tr></thead><tbody><tr><td>Operation fieldname</td><td>This is a required field. This field is used by the step to obtain an operation flag for the current row.</td><td>flagfield</td></tr><tr><td>Insert when value equal</td><td>Specify the value of the Operation fieldname which signifies that anInsert should be carried out.</td><td>new</td></tr><tr><td>Update when value equal</td><td>Specify the value of the Operation fieldname which signifies that an Update should be carried out.</td><td>changed</td></tr><tr><td>Delete when value equal</td><td>Specify the value of the Operation fieldname which signifies that a Delete should be carried out.</td><td>deleted</td></tr><tr><td>Perform lookup</td><td>Performs a lookup when deleting or updating. If the lookup field is not found, then an exception is thrown. This option can be used as an extra check if you wish to check updates/deletes prior to their execution.</td><td></td></tr></tbody></table>
{% endtab %}

{% tab title="3. RUN" %}
{% hint style="info" %}
**RUN**

This step is aimed at reporting data marts .. delta loads to update the cube. Check out which records have undergone CRUID operations.
{% endhint %}

1. View the data in the Table.

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-3ec742efdc96eaa7807aa77f1996ab23dbb05384%2FSTG_Orders%20-%20orginal.png?alt=media" alt="" width="563"><figcaption><p>STG_ORDERS_MERGED</p></figcaption></figure>

2. Run the Transformation with the hop between the Merge Rows (diff) and Synchronize after merge .. disabled.

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-85035c1036d528b9819e51e32d6f30d2376e9bd5%2FPreview%20-%20Sync%20after%20merge.png?alt=media" alt=""><figcaption><p>Synchronize after merge - FLAG</p></figcaption></figure>

3. Run the Transformation with the hop enabled.
4. Examine and compare the records.

<figure><img src="https://3680356391-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FZpCSy6Skj215f4oWypdc%2Fuploads%2Fgit-blob-e135c66217a1317a311c2cd722e47f719a3ee79a%2FSync%20after%20merge%20-%20results.png?alt=media" alt="" width="563"><figcaption><p>STG_ORDERS_MERGED - Synchronize</p></figcaption></figure>
{% endtab %}
{% endtabs %}