How to setup a tumbling trigger to run a pipeline 4 days in every quarter

Question

How to setup a tumbling trigger to run a pipeline 4 days in every quarter

farooq 0

Hi, I have a requirement to run a pipeline every quarter for 4 days at 8 am using a tumbling window trigger. Is this possible? I know this can be done through schedule trigger, however colleagues built our metadata driven pipeline told me that I should be using tumbling window trigger if I want to delta load the file.

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-05-11T05:58:25.4433333+00:00

Hi farooq ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

1 answer

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-05-11T05:58:25.4433333+00:00

Hi farooq ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

Answer 1

RevelinoB 3,675

Hello, Yes, it is possible to run a pipeline every quarter for 4 days at 8 am using a tumbling window trigger in Azure Data Factory. The tumbling window trigger can be configured to start at 8 am on the first day of the quarter and run for 4 days. This trigger will then repeat every quarter. In order to delta load the file, you will need to configure the pipeline to only process the data that has changed since the last run. This can be done using a combination of a lookup activity to retrieve the last run time and a conditional activity to only process the changed data. You can create the tumbling window trigger and configure the pipeline to delta load the file by using Azure Data Factory's visual interface or by writing JSON code. The configuration will depend on the specifics of your pipeline and data sources.

farooq 0 Reputation points

2023-04-24T16:19:06.3066667+00:00

Can you also let us know how this can be done. In the tumbling window trigger setting page I don't see all the options for what you described above.
RevelinoB 3,675 Reputation points

2023-04-24T18:49:44.3566667+00:00
Hi Farooq, Sorry for the late reply but I had to think about this. If I was you I would try the following: let me explain in a more natural way how to configure the tumbling window trigger and delta load in Azure Data Factory. To configure the tumbling window trigger:

Open your Azure Data Factory and go to the "Author & Monitor" page.

Click on "Author" to open the Azure Data Factory authoring tool.

In the authoring tool, click on the "Author" tab to open the pipeline canvas.

Drag and drop your pipeline onto the canvas.

Click on the pipeline and then click on the "Triggers" tab.

Click on "New/Edit" to create or edit a trigger.

Select "Tumbling window" as the trigger type.

Set the start time to 8 am on the first day of the quarter.

Set the interval to 4 days.

Set the repeat count to "Forever" to ensure that the trigger runs every quarter. To configure delta load in your pipeline:

Add a Lookup activity to retrieve the timestamp of the last successful run of the pipeline. You can use a SQL query or a stored procedure to retrieve the timestamp from your database.

Add a Source activity to read the data from your source system. Use a query to filter the data based on the timestamp of the last run. For example, you can use a WHERE clause to retrieve only the data that has been updated or inserted since the last run.

Add a Conditional activity to check if there is any data to process. Use an expression to check the count of the rows retrieved by the Source activity. If the count is zero, skip the next activities and end the pipeline run. If the count is greater than zero, continue to the next activities.

Add a Sink activity to write the data to your destination system. Depending on the destination system, you may need to use upsert or merge operations to update existing records and insert new records.

Add an Execute SQL task to update the timestamp of the last successful run in your database. Use a SQL statement or a stored procedure to update the timestamp. By following these steps, you can configure your tumbling window trigger to run every quarter for 4 days at 8 am and ensure that your pipeline only processes the data that has changed since the last run. Can you let me know if this helps?
farooq 0 Reputation points

2023-04-25T15:36:19.5+00:00

In step 9, if you are setting it up to run every 4 days, what ensures it runs only 4 days every quarter?
RevelinoB 3,675 Reputation points

2023-04-25T16:00:47.8433333+00:00

Hi Farooq, In step 9, the interval is set to 4 days which means that the pipeline will run every 4 days starting from the specified start time. However, the trigger type is "Tumbling window" which means that the trigger will only run for a specified period, which in this case is one quarter. So, even though the interval is set to 4 days, the trigger will only run for 4 days within each quarter, starting from the specified start time of 8 am on the first day of the quarter. After the 4 days have elapsed, the trigger will reset and wait for the next quarter to start before it can run again for another 4 days. I hope this answers your query?
farooq 0 Reputation points

2023-04-25T16:14:10.82+00:00

Why does it run again next quarter?
RevelinoB 3,675 Reputation points

2023-04-25T16:29:52.5166667+00:00

Hi Farooq, The trigger is configured to run every quarter because the repeat count is set to "Forever" in step 7. This means that the trigger will run indefinitely, and in this case, every quarter. So, after the trigger runs for 4 days during the current quarter, it will wait until the start of the next quarter before it can run again for another 4 days. This cycle will continue indefinitely because the repeat count is set to "Forever". I hope this help with your issue?
farooq 0 Reputation points

2023-04-26T11:52:22.0866667+00:00

Why "in this case, every quarter".? Which setting ensures it happens every quarter? Could you put a screenshot of settings as I did above, by any chance. It is hard to imagine and believe it will work as you suggested.
RevelinoB 3,675 Reputation points

2023-04-26T12:04:51.47+00:00

Hi Farooq, Sorry for any confusion. When I said "in this case, every quarter," I was referring to the specific recurrence pattern that you mentioned in your original question, which was to run the trigger every quarter for 4 days at 8 am. To clarify, the recurrence pattern for a tumbling window trigger is set in the "Schedule" section of the trigger settings. In this case, you would set the recurrence type to "Quarterly" and the start time to "8:00 AM" to ensure that the trigger fires at 8 am on the first day of each quarter. I hope this helps?
farooq 0 Reputation points

2023-04-27T09:44:45.3666667+00:00

To confirm, in tumbling window option you don't have an option to set it to run quarterly. what you suggest can't be done in 'tumbling window' option.
RevelinoB 3,675 Reputation points

2023-04-27T09:51:43.98+00:00
Hi Farooq,

I apologize for the confusion. Upon reviewing my previous response, I realized that I made an error in describing the options for a tumbling window trigger. I apologize for any confusion caused.

In Azure Data Factory, which I assume you are referring to, the tumbling window trigger does not have direct settings for "quarter" or "interval" as mentioned earlier. The options available for defining a tumbling window trigger are limited to the following:

Start time: The start time specifies when the trigger should begin executing the pipeline.

End time: The end time defines when the trigger should stop executing the pipeline.

Frequency: The frequency determines how often the trigger should repeat the execution.

Time zone: The time zone setting allows you to specify the time zone for the trigger's start time.

Given these limitations, it may not be possible to precisely configure a tumbling window trigger to run every quarter for four consecutive days starting at 8 am. The tumbling window trigger in Azure Data Factory is primarily designed for recurring time intervals such as daily, hourly, or minutes, rather than quarters.

To achieve your desired schedule of running the pipeline every quarter for four days at 8 am, you may need to explore alternative solutions or consider using a different trigger type, such as a schedule trigger or a custom trigger that suits your specific requirements. It's recommended to consult the Azure Data Factory documentation or reach out to the Azure support team for further guidance on how to implement your desired schedule using the available trigger options.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-05-03T05:24:40.8433333+00:00

Hi farooq ,

Just checking in to see if the above answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

Share via

How to setup a tumbling trigger to run a pipeline 4 days in every quarter

1 answer

Your answer