 Hey guys, welcome to SSID Tech, so say this side and today we are going to see about the ultra row transformation. So to use the ultra row transformation to set, insert, delete, update and upset policies on your rows, you can add one to many conditions as expressions. So these conditions should be specified in order of the priority as each row will be marked with the policy corresponding to the first matching expression. Each of those conditions can result in a row or rows being inserted, updated, deleted or upset. Ultra row can produce both DDL and DML axis against your database. So the primary requirement for using the ultra row transformation, your destination or the sink should be database or the Cosmos DB. If your destination is not the Cosmos DB or the database, then you cannot use the ultra row transformation. Now the second thing, here we can add the policies, whether we want to insert the data or delete the data or update the data or upset the data. So those policies we can implement by using the ultra row transformation. So go to on the browser and we'll try to see in the practical. So here we are in the Azure Blob Stories and we are under this input folder and this cs.csv file. So this file is having the data for the India, US and UK countries. So we just want to load the data from here to your cs.csv. So go to the cs.csv and here we have this table under this SSU database. So this table is having the data only for these two cell orders, like the first cell order six and second for the four. So if we can compare this data with the source data, so if we can see cell order four, which is from the US country. And if you can see here, so that is for cell order four is having triple X country. So this country is not perfect here. So we just want to update this country by using the cell order from the source. And the second, as we can see six, which is for the UK. So we don't want to insert any data for the UK country. So go to the browser, go to the Azure data factory. And here, let me try to add a new data flow here. And after that, let me click on this add source and this data flow. Let me call this data flow as alter room. And in the source, we have to select the data set as we have already created the data set in the earlier videos. So we can select it. Let me go in the open. So here we can verify it is under the input folder of the says.cc file, the same file it is pointing. Let me close this. Here let me go in the plus symbol of this. And here we can find out for the alter room transformation. So in this transformation, we can check output stream name that is alter one or you can rename whatever you want. In the incoming stream that is source one, that is also OK. Here we can see the alter row condition. So here we can add the condition. So as per the first requirement, we don't want to load the data for the UK department into the distance. So for that, we have to implement the first policy. So that policy will be in the alter row condition. Here we can see the delete if. So we want to delete the data from the incoming source stream. So we will not be loading that into the sink. So let me open the expression builder here. And after that we can filter out for the country that is UK because we don't want to load the data for the UK department. Now we can click on save and finish. So this is the first condition we have added. Let me go in the data preview and try to refresh it. So we will see the data here. So here as we can see, so if you can see in the top side, so number of rows. So insert is 4, update is 0, delete is 2. Because as we can see in the source side, we have two rows for the UK department. So that's why in the delete we have these two and four rows which is not having the UK. So those will be inserting. So that's why in the insert we have four and for the delete we have two and others are zero. Similarly, we can see here like this plus symbol, so which is indicating for the insert and this cross which is indicating for the delete. Now let me try to add another policy here and that policy will be updating whenever we will see for the US. So for the US we want to update. So here we can add one more policy by clicking on this plus symbol. Here we can see update if. So we want to update this whenever we will be having the country that is US. So we can write the US and then we can save and finish. Let me go in the data preview again and we will try to refresh it. So as here we can see in the top side we can verify insert is true, update is true and delete is true. So because we have added the policy for the US for the update. So that's why these two rows are considering for the update and the last two is for delete. So we don't want to load that into the destination and first two for the insert. So this is something we can do by using the alter row transformation. Let me add sync here so we can directly load the data into the sync location and under this sync we can select the data set. So as we have already created the data set for the same so we can select that. So we have selected here now we can go in the mapping instead of auto mapping we have to off this so it will be going to map manually for the source and destination. Go to the setting and under this setting we want to update as well. So we have to select this check box that we can see here. So we are going to allow inside the database for the update. If we are going to update then it should be based on certain condition. So what is that condition? So that condition is based on the column which is the SOID that is the sale order ID. So by using the sale order ID we just want to update into the destination. So after making all the changes we can go in the data preview and here let me try to refresh. So we will be going to see whatever we are going to discuss. So here as well we can see first two rows are going to insert next two rows are going to update and last two will be not inserting into the destination. Let me try to publish this quickly and after that we are going to create a new pipeline for executing this. So let me go here in the pipeline and try to click on the new pipeline. And in this new pipeline let me call this pipeline as pipeline alter row. After that here we can find out the data flow activity for executing the data flow for the alter row. So after that we can directly go here in the setting and try to select the alter row that we can see here. Now we can try to click on this debug to execute this data flow. So once it will be executing your data should be loaded into the destination and updated as well. So pipeline executed successfully. Let me go into the SSMS and here let me try to execute this select statement. So here what we can see the first two rows that we have seen in the source for the India is inserted here. And remember closely for the US instead of the US we were having the triple X here. So that's why we can see triple X. So here the triple X is updated with the US and for the ID 6 it is not doing anything. Now let me try to delete this table again and we will try to implement the absurd as well. So let me quickly delete it from this table and after that let me try to insert it. So before going to insert let me update something here. So this might be 22 and this might be from UK to ABC. Now let me try to execute this and then let me select and we will try to verify it. So here it has 6 and 4 and we just want to implement the absurd on this ID 6. Now let me go into the Azure Data Factory and here let me go into the Altar row and in this Altar row here we can go into the Altar row setting. So under this setting instead of this update if we want to implement the absurd policy. So for implementing the absurd policy we can select the absurd if and here we can add the condition. So the condition for the US we want to update. So for the US that is ID 4 mainly so we want to update that. So let me try to publish it. So once it will be published then we will try to execute that. So in this case what will be happening? So for ID 4 will be updating here and after that the ID 3 will be also inserting. So now it will be having ID 1, 2 for the India then 3 will be inserting for the US and 4 which is already there will be updating for the US and then 6 is already here. So let me go here and here it is publishing. Publish is completed but we did one mistake. So let me go into the sync here go to the setting. Here we have not selected this absurd. So let me try to disable this update only. So we are going to use the absurd this time. Let me publish it again. So now it is going to allow the absurd policy inside the SQL server. So it is in progress so we can wait. So deployment is completed so let me go here and try to execute it. So this time it will be going to insert the data for ID 2, 3 and 1 and for the 4 will be updating as per the absurd policy. So it got executed successfully. Let me go in the SSMS and here let me try to execute the selected statement. So as we could see it is having ID 1, 2 for the India 3 is inserted as per the absurd policy. And for the 4 previously it was having triple X here. Now the triple X is updated from triple X to US. So that we could see. So this is all about the alter row transformation. So thank you so much for watching this video. If you have still any doubt then you can comment your questions in the comment box. I will try to response there. Thank you so much again see you in the next video.