 Hey guys welcome to SSunitech so till this side and today we are going to see about the Serogate Key Transformation. What is the Serogate Key Transformation? We will see. Use the Serogate Key Transformation to add an incrementing key value to each row of the data and this is useful when we are designing the dimension tables in the star schema analytical data model. In the star schema each member your dimension tables require a unique key that is the non business key. So this is basically we could see we will be going to have one of the raw data and under that raw data for example it is keeping the information for the employee. So for that it is having the employee data then it is having the address data then it is having the date data. So instead of keeping all those together we can split that like the date data in one table then the address data in one table then employee data in one table. So all those are required a unique row identifier. So for that we can use the Serogate Key. So this is we can create a new column and where we will be going to have the unique values incrementing by one by one. So go to the browser and we will see in the practical. So here this is one of the source like employee data and this employee data is having like employee name employee address department then the gender. It does not have any column which will be going to uniquely identify any row. So we don't have the employee ID here we could see. So either we can use the employee ID here or we are going to split this table into two tables. So one table will be the department table and one table will be the employee table. So both tables are required to have a unique row identifier. So for that we have to use the Serogate Key transformation. So go to in the Azure Data Factory and we will try to implement in practical. So here let me try to add a new data flow and under this data flow now let me call this data flow as Serogate Key transformation. Here let me add a source. So this source for the employee. So let me going to use the inline data set. So the source which is the delimited text so we can select that one. And after that we have to select the link service. So this is the link service that we have created in the earlier videos. So I am going to use the same. Now go to the source option and under the source option we can select the file. So the source file which is available under the input folder and this is the employee data file. So we can click on ok. Now we can scroll down side and first row as header. Now we can go in the projection and try to import the schema. So after importing the schema here it will be going to have all the columns that we have seen in the file. So employee name, employee address, department and gender. We don't have any unique column. So we can click on this plus symbol and here we can see the serogate key. So we can select that one. So this serogate key is going to say the key column. So this should be employee ID or the ID whatever that you want to specify. Then the starting value. So from where we want to start this value. So I am going to start this from 1 and then the incremented value which is the step value. So I want to increment 1 by 1. So first row will be having 1, second row will be 2, then 3, then 4. Going forward will be having values like that. Go to the data preview and try to refresh to check the output of this data. So here we could see the ID which is the 1, 2 and 3. Now go back to the serogate key and we want to increment not 1 by 1. First will be 1, then 3, then 5. So we can specify and try to refresh here. So it will be going to have 1, then 3, then 5 like that. So as you can see under the ID and if we want to start from 10 then we can also start value from 10 and we can refresh. So that will be 10, 12 and 14. So that we can see 10, 12 and 14. So I want to start from 1 and incrementing 1 by 1. Now we can add this into the sync so that we can add in the sync location and here I am going to use the inline so we are not going to create any physical dataset for this. So this should be the delimited text in the output and after that the link service then we can go in the setting and try to select the folder by which we want to keep this file. So I want to keep this under the output folder so we can select and click on OK. Then first row as a header we want so we can select this check box and the file name option. So I want to keep this output to a single file. And the file name that we can call this serocate key transformation. Now we need to go in the optimize and select the single partition because we are using the option for output to single file. Now go to the data preview and try to refresh it. So it should have the data here under the sync. So as we can see all the data here now let me try to publish this click on publish. So publish completed. Now we can go here and try to add a new pipeline and under this pipeline I want to execute the data flow that I have created. So we can select this data flow and here go to the settings and try to select the data flow. That is the serocate key transformation. Now I just want to debug it so we can click on debug. So when this will be executed successfully your output folder will have one of the file with the name of serocate key. So this is we can see now let me try to refresh it. So this is in progress. Now we can see serocate key transformation because your pipeline is executed go to the edit. So this should have the data as we have seen. So last column is the ID which we have created by using the serocate key transformation. So this is all about the serocate key transformation. So thank you so much for watching this video see you in the next video.