 Hey guys, welcome to SSUnitech Society site and today we are going to see about the aggregate transformation in Azure Data Factory. So what is aggregate transformation? So the aggregate transformation defines aggregation of columns in your data stream. Using the expression builder, you can define different type of aggregation such as aggregate functions, sum, min, max, count, group by existing and computed columns. So computed columns as we have already seen inside the derived column transformation. Then what is the group by? So for example, if you are having three columns and we want to do grouping based on two columns and the third column will be having the aggregated values. So this is the group by. So select an existing column or create a new computed column to use the group by clause for your aggregation. To use an existing column, select it from the drop down so that we will see in the practical. Don't worry. So go to on the browser and we will try to implement this in practical. So here go to the blog stories and under the blog stories we have this file. So this file is containing the information of the employee like employee ID, employee name, employee address and employee department. Similarly, if you can go and check inside the payroll file. So payroll file is having the information for the salary amount, tax amount, payroll month and employee ID. So what we want to see? We want to see the data as per the employee department and pay month. So first we have to join between employee and payroll table and after that we can use the aggregate transformation. So first we have to use the joint transformation so that we have already seen inside the joint transformation in this playlist. So first go to the data flow and try to create a new data flow here. And let me quickly call this data flow as aggregate transformation now here we are required to add the source. So first source which is the employee. So here we have already created the data set for the employee. So I am going to select that one. Similarly we need to go and try to add the payroll data set here. So we have already created the payroll one so this is the payroll data set we can select. So we have selected for the employee as well as the payroll. Now we need to click on this plus symbol and need to add the joint transformation to do the join between these two. So here under the joint transformation we are required to use the inner join. Left stream is source one and right stream is source two. So we can select it and the joint based on the employee ID. So we can select the employee ID from here and employee ID from source two. So this is all about the joint transformation. Now we are required to add the aggregate transformation but before aggregate transformation let me quickly go in the source two and here we can go under the projection. Here we can see the salary amount and tax amount values as string. So these values should be in the integer not string. So we can change it from here because we are going to do the aggregation based on the salary amount and tax amount. And that can be done either in the decimal or integer data types. So that is why we have updated. Now in this joint transformation let me click on this plus symbol and here we can see the aggregate. So we can add this aggregate here. Now here we can see the incoming stream that is joint one that is okay. Here we can see two options group by and aggregates. So under the group by we are required to select the department name the first one and second on the payroll month. So we can click on this plus symbol and we need to select the payroll month from here. The payroll month is available in the source two. So we can select it from here. So we are doing the group by based on these two columns and aggregate. So we can go under the aggregate and here we can select the column which is the salary amount and here we can see the expression. So under the expression we can open the expression builder. So we want to check the total amount. So we have to use the sum of the employee salary. So that is the salary amount. Now click on save and finish. Similarly I want to see another column. So we can click on this plus symbol and here the add column. So this will be adding another column and that column is tax amount. So we can select it. Now here we can open expression builder and let me use the sum here of the tax amount. So we can select the tax amount and save and finish. So we have done for the aggregate transformation like this is doing the aggregate based on department name and payroll month and whatever the salary amount and tax amount. So the total of that. So we can go in the data preview and try to refresh it. So under this we will be going to see four columns first two from the grouping and last two for the aggregate. So as we can see the IT department in the payroll month of this the total salary which is this one and here we can see the tax amount which is this. Similarly in the HR here it is 15 and the tax amount is 14,000. So this is not the correct data actually. So don't worry about that. Now we need to load this data into the destination. So we can select here the sync part and under the sync I am going to use the inline query select it like in delimited text file. So we can select that in the link service we can select it go to the settings and here we are required to select the output folder. So we can go in the output folder and after that we can click on ok. Below of that we can use the first row as header so that is true. Now here the file name option that is output to single file. So the output file name so this should be salary by department. So that's it. Now we can go under the data preview and here let me try to check this. By using the refresh. So this should be having the data which we have seen in the aggregate. So that's it. Now we can publish this and ok. So we need to go in the sync part again and here go to the optimize and this should be partition option should be single partition. Now let me try to publish this. So it will be published. Now we can add a new pipeline to execute this data flow. So in this pipeline we can use the data flow activity so we can drag and drop it here as we can see it is published completed so we can go in the settings and under the setting we can select the data flow for the aggregate. Now we can try to debug it. So once this will be completed your blob storage output folder will have one file that is salary by department. So it is executing we can wait. So it got executed successfully. Now go back to the output folder of this try to refresh it. Here we can see salary by department. Let me try to open this file and we'll see the data under this. So go to the edit and under this edit we can see like the department name per old month and salary amount and tax amount whatever the data we have already seen there. Let me close this. So this is all about the aggregate transformation. So thank you so much for watching this video. See you in the next video.