SQL and MAP Transformation are two transformation is important transformation used in different business scenarios.

SQL Transform: Similar to Query Transform It projects the data source fields to a subset of the same set.  it is used in single or with other transformations based on the business scenario. But Query transform generates optimized SQL, while SQL transform allows directly entering SQL queries

But in SQL transformation the Query which we use to project or filter the data that is executed and mapped into output. Complex logic needs to extract data from procedures or Table functions based on certain input parameters. In those scenarios SQL transformation is beneficial.

In our case, we will implement the same business scenario where we populate the employees outside India having a salary of more than 10000.

Source Table :

The design :

The Editor :

The result should be the same as what we got using the query editor –

MAP Transformation :

Map transformation is a very important transformation. Map Operation modifies Opcodes (I/U/D) operations. Allows conversion between opcodes provided as a input from previous transformation. After map operation use can only use transformation that understand opcode or a permanent table. Query transformation is optional to use. The ‘Map_Operation’ transform changes the operation codes on the input data sets.

In Simple word If any row updated on the source table , using map transformation you can update or insert the data in the target data set.

Important Topic :  When we will use MAP without Table comparison and when with Table comparison?

Map Operation doesn’t identify the new or updated rows from the source automatically.

Table Comparison return Opp. Code for Insert, Update and Delete for each source row after comparing with target table data as an output.

So we Can implement Insert, Update, and Delete operations using a single flow for a data source if we use Map with table comparison.  But if we want to used a Single MAP operation We need to use Parallel flows for each operation (insert, update, and delete).

The transformation Table Comparison able to identify the records that need to be updated or inserted in the target table by comparing the source. If there is a requirement of  mix of insert and updates use TC along with Map operation.

But if you know all the records need updates then use Map only. Also, say you want to delete a set of records based on some condition then the map will work better.

Scenario 1 :

A. Map Operation with Table comparison :

In this scenario if a row is updated in the target system. The source will be updated. If a  row is inserted or deleted in the source it will insert to and delete from the target.

It is similar result we can obtain from History preservation and Row generation along with table comparison –

A. Map Operation with Table comparison : 

For a simple update, Insert and delete we can use the below flow with the default MAP Opp code setting –

But Without using Table Compare and Generation we can do the same update, insert and Delete. using below flow and opcode :

For Normal Opcode Setting –

For Insert Opcode Setting –

For Update  Opcode Setting –

For Delete Opcode Setting –

In the next blog I will discuss about the Data Integration Transform like History Preservation, Table comparison Row generation etc.