 Hey guys welcome to SSMinutex, so see this side and today we are going to see about the tumbling window trigger dependency. So in the last video we have seen about the tumbling window trigger. So if you haven't watched that video, so before going forward I would strongly recommend to watch that video. So you will get a clear picture about the dependencies on the tumbling window trigger. So let's get started. So what is tumbling window trigger dependency? So for creating the dependency chain, actually it makes sure a trigger is executed only after successfully execution of another trigger. And we can achieve it by using the tumbling window dependency option. So what it means? So it means we have two triggers like trigger 1 and trigger 2 and the second trigger we want to execute only if the first trigger will be executed successfully. So like here we are having the dependency on the trigger 2. It only be executing if trigger 1 will be executed successfully. So this is the dependency and we will try to create that dependency in this video. Next is a tumbling window trigger can dependant on maximum of two other triggers. So like we cannot add the dependency with more than two triggers. So we can only add the maximum of two other triggers. Next we will be going to use the below system variables that you can see the trigger output window start time and trigger output window end time. So these two variables will help us to execute your pipelines. So now try to understand about the requirement. So here for example we are having two Excel files. So these Excel files are available in the Blob Stories. So in the Blob Stories we are getting the data on the early basis. So first we are getting the customer information and second we are getting the sale information. So in the sale information as we can see we are having the sale order ID, sale order date, customer ID after that we have item ID, quantity and value. So here we can see the customer ID. So this customer ID is totally dependent with the customer data. So the customer data is available in the this Excel file. So while we are going to execute our trigger for this sales, so this will be going to processed only if your trigger which is going to process the data for the customer will be available there while it is required. So let us assume we will be going to create the invoice. So that invoice will be having the customer ID and customer name and what address and my number. These information that invoice will be holding and if your customer data is not going to publish first, so it is possible your customer detailed table will not have the updated data. It might be your customer table is having the old address and customer has updated our address or the new customer is added there. So these are the certain possibilities. So that is why first we need to publish the customer detail and after that we need to publish for the sale detail. So here the sale information is totally dependent on the customer file. So first customer file will be processed and after that your sale file will be processed. So we can achieve this by using the tumbling window trigger with the dependency option. So how we can do that? Go to on the browser and we will try to do this in practical example. So here we are in the browser and here let me try to create one pipeline. So let me call this pipeline name as customer and this will be going to execute on the early basis. Now we need to add one more pipeline and this pipeline is going to process for the sale information. So this is for the sale info and this again going to execute on the early basis. But first we have to set up this customer. So for example, let me try to publish this customer. So this customer is going to load the data from your blob storage to your SQL server table. So this is the first step. So here this customer early pipeline will be going to execute early basis. So for that we have to create the tumbling window trigger. So let me try to go here and click on the new or edit. Here I want to create a new trigger and this trigger is the tumbling window trigger and this is for the customer file. Now here the type that should be tumbling window. Here we can see the start date. So this start date we want to start from today that is the 7th of March and here we can see the time. So we want to start this at 5 o'clock maybe and it should be 0 0. Click on OK. So this trigger will be going to start at 5 o'clock and this should be executing on every hour. So this is the hour. So now here we have specified the end date. Now we can click on OK and let me save this. So this trigger is created and it is going to execute on every hour and it will be going to start by 5 o'clock. So now go to on the sale info early pipeline. So in this pipeline this should be going to execute the early basis but it should be checking first your customer early pipeline will be executed and after that this should be executed. So let me try to add a new trigger here. Type that should be a new and here we can call this as tumbling window with the item in function. Now the type that should be tumbling window trigger and this I want to start by today with the same time that is 17 0 0 0 0 let me click on OK. So this will be going to start at the same time. Here we want to execute this on every hour. So for the end date we can set this by tomorrow. So here let me set for tomorrow this and here this should be 17 and 0 0 and after that we can set this as 0 0 and let me click on OK. So this is our start date and this is your end date. Now here we have to set the dependency. So for setting the dependency we have to go in this advanced option and here we can see the add dependencies and this plus symbol. So we can click on this plus symbol and here we can see the trigger. So this trigger should be dependent on the trigger for the customer which we have created and let me try to set the offset as 0. So why we are going to set this as 0 and what is the use of that we will see in a bit below options we have already covered in the previous video. So this 0 should be set like in this format. Now we can click on OK and here we can save this. So for understanding about that we can go in the Microsoft website for this offset one. So here we can see this dependency offset option. So this is your customer trigger the trigger B and the trigger A which is our item trigger. So the item trigger is totally dependent on the customer trigger which is trigger B. So if we are going to set the offset value as 0 then here we can see the 10 to 11. So this window is directly dependent on the customer trigger of the 10 to 11 window. So first your customer will be executed and after that your item will be executed. Next if we are going to set the offset value as minus 1 that you can see. So in this case your customer window which is the previous 1R. So it means here that is 9 to 10. So 9 to 10 window is executed first after completion of that window your trigger A which is the item trigger will be executing. So that is for the offset value of minus 2. Go to here and here we can see the dependency size. So what it means? If we are not going to specify any size then on that case what we have understood here that is the same thing. But if we are going to set the size as 2Rs in this case we can see. So it means your offset will be checking first so it is minus 1. So it is going to start from 9. So that we can see the window which is 9 to 10 will be the offset. But we have specified the size as 2Rs. So it will be going to start from 9 and adding 2Rs from here so that will be 11. So it is going to check the execution of the trigger B which is the customer one. Like it should be executed 9 to 10 window and 10 to 11 window. And after that your 10 to 11 window of the item trigger will be executing. So this is about the dependency in the size. So go here and go to on the minus tab and here go to the triggers and we have added this item trigger let me go there. So here go to the advanced one now here we can see we have added the offset value as 00 and the window size that is again 00. So we can set the window size here and we can also put the value in the minus in the offset. So this is all about the dependency in the trigger. Next we have the self dependent. So what is the self dependent? So self dependent trembling window trigger is going to depend on its own and we have to provide the offset for this in negative values only. So we cannot specify the positive value. So what it means? So go to the Microsoft website and we will try to understand about that. So here so as we can see if we are going to specify the offset value is minus 1. So it means first it is checking before going to execute the window between 10 to 11 it is going to check the window of 9 to 10. So if 9 to 10 window is executed successfully then it will be start executing 10 to 11 window. Similarly if we are going to set the offset value is minus 2 hours and size is 2 hours. So on that case it will be going to start from 8 o'clock and here it will be going to check for as we have set the size as 2 hours. So it is going to start from 8 and checking up to 10. So both the windows like 8 to 9 and 9 to 10 should be executed before executing 10 to 11 windows. So this is all about the self dependent trembling window trigger. So go and we will try to create the self dependent. So here add the dependency one more time and this time we want to add the dependency on the TW trembling window item info the same one and here we can specify the offset value in negative that you can see negative offset for the self dependent not provided. So we have to specify only negative values here. So how we can set that let me put minus 1 then 0 0, 0 0 and 0 0. So it is going to set as minus 1 that you can see. So this is all about the tumbling window trigger tumbling window dependencies how we can set the self dependent trigger. So thank you so much for watching this video. If you really like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. See you in the next video.