Introduction
Sometimes you have processes that you don’t need to run in certain scenarios. The past year I’ve been working with market trading data. Trading in exchanges happens every weekday but they don’t happen on weekends or national holidays as they’re usually closed.
This posses a problem to usual ADF trigger patterns where you can schedule quite complex triggerscenarios such as the 1st Sunday of every month. However, no concept of national holidays exist, so we need to handle this after the trigger point.
Problem
So lets restate the problem within the context of ADF. We need to trigger our process everyday, then we must validate whether this date is a UK Bank Holiday and run the business logic.
Solution
Government API
The UK government have started to build a useful catalog of APIs for consumption, one of which contains all public holidays. The api returns the following json data.
curl 'https://www.gov.uk/bank-holidays.json'{"england-and-wales":{"division":"england-and-wales","events":[{"title":"New Year’s Day","date":"2017-01-02","notes":"Substitute day","bunting":true},{"title":"Good Friday","date":"2017-04-14","notes":"","bunting":false},{"title":"Easter Monday","date":"2017-04-17","notes":"","bunting":true},{"title":"Early May bank holiday","date":"2017-05-01","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2017-05-29","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2017-08-28","notes":"","bunting":true},{"title":"Christmas Day","date":"2017-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2017-12-26","notes":"","bunting":true},{"title":"New Year’s Day","date":"2018-01-01","notes":"","bunting":true},{"title":"Good Friday","date":"2018-03-30","notes":"","bunting":false},{"title":"Easter Monday","date":"2018-04-02","notes":"","bunting":true},{"title":"Early May bank holiday","date":"2018-05-07","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2018-05-28","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2018-08-27","notes":"","bunting":true},{"title":"Christmas Day","date":"2018-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2018-12-26","notes":"","bunting":true},{"title":"New Year’s Day","date":"2019-01-01","notes":"","bunting":true},{"title":"Good Friday","date":"2019-04-19","notes":"","bunting":false},{"title":"Easter Monday","date":"2019-04-22","notes":"","bunting":true},{"title":"Early May bank holiday","date":"2019-05-06","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2019-05-27","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2019-08-26","notes":"","bunting":true},{"title":"Christmas Day","date":"2019-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2019-12-26","notes":"","bunting":true},{"title":"New Year’s Day","date":"2020-01-01","notes":"","bunting":true},{"title":"Good Friday","date":"2020-04-10","notes":"","bunting":false},{"title":"Easter Monday","date":"2020-04-13","notes":"","bunting":false},{"title":"Early May bank holiday (VE day)","date":"2020-05-08","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2020-05-25","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2020-08-31","notes":"","bunting":true},{"title":"Christmas Day","date":"2020-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2020-12-28","notes":"Substitute day","bunting":true},{"title":"New Year’s Day","date":"2021-01-01","notes":"","bunting":true},{"title":"Good Friday","date":"2021-04-02","notes":"","bunting":false},{"title":"Easter Monday","date":"2021-04-05","notes":"","bunting":true},{"title":"Early May bank holiday","date":"2021-05-03","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2021-05-31","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2021-08-30","notes":"","bunting":true},{"title":"Christmas Day","date":"2021-12-27","notes":"Substitute day","bunting":true},{"title":"Boxing Day","date":"2021-12-28","notes":"Substitute day","bunting":true},{"title":"New Year’s Day","date":"2022-01-03","notes":"Substitute day","bunting":true},{"title":"Good Friday","date":"2022-04-15","notes":"","bunting":false},{"title":"Easter Monday","date":"2022-04-18","notes":"","bunting":true},{"title":"Early May bank holiday","date":"2022-05-02","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2022-06-02","notes":"","bunting":true},{"title":"Platinum Jubilee bank holiday","date":"2022-06-03","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2022-08-29","notes":"","bunting":true},{"title":"Bank Holiday for the State Funeral of Queen Elizabeth II","date":"2022-09-19","notes":"","bunting":false},{"title":"Boxing Day","date":"2022-12-26","notes":"","bunting":true},{"title":"Christmas Day","date":"2022-12-27","notes":"Substitute day","bunting":true},{"title":"New Year’s Day","date":"2023-01-02","notes":"Substitute day","bunting":true},{"title":"Good Friday","date":"2023-04-07","notes":"","bunting":false},{"title":"Easter Monday","date":"2023-04-10","notes":"","bunting":true},{"title":"Early May bank holiday","date":"2023-05-01","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2023-05-29","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2023-08-28","notes":"","bunting":true},{"title":"Christmas Day","date":"2023-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2023-12-26","notes":"","bunting":true}]},"scotland":{"division":"scotland","events":[{"title":"2nd January","date":"2017-01-02","notes":"","bunting":true},{"title":"New Year’s Day","date":"2017-01-03","notes":"Substitute day","bunting":true},{"title":"Good Friday","date":"2017-04-14","notes":"","bunting":false},{"title":"Early May bank holiday","date":"2017-05-01","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2017-05-29","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2017-08-07","notes":"","bunting":true},{"title":"St Andrew’s Day","date":"2017-11-30","notes":"","bunting":true},{"title":"Christmas Day","date":"2017-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2017-12-26","notes":"","bunting":true},{"title":"New Year’s Day","date":"2018-01-01","notes":"","bunting":true},{"title":"2nd January","date":"2018-01-02","notes":"","bunting":true},{"title":"Good Friday","date":"2018-03-30","notes":"","bunting":false},{"title":"Early May bank holiday","date":"2018-05-07","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2018-05-28","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2018-08-06","notes":"","bunting":true},{"title":"St Andrew’s Day","date":"2018-11-30","notes":"","bunting":true},{"title":"Christmas Day","date":"2018-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2018-12-26","notes":"","bunting":true},{"title":"New Year’s Day","date":"2019-01-01","notes":"","bunting":true},{"title":"2nd January","date":"2019-01-02","notes":"","bunting":true},{"title":"Good Friday","date":"2019-04-19","notes":"","bunting":false},{"title":"Early May bank holiday","date":"2019-05-06","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2019-05-27","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2019-08-05","notes":"","bunting":true},{"title":"St Andrew’s Day","date":"2019-12-02","notes":"Substitute day","bunting":true},{"title":"Christmas Day","date":"2019-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2019-12-26","notes":"","bunting":true},{"title":"New Year’s Day","date":"2020-01-01","notes":"","bunting":true},{"title":"2nd January","date":"2020-01-02","notes":"","bunting":true},{"title":"Good Friday","date":"2020-04-10","notes":"","bunting":false},{"title":"Early May bank holiday (VE day)","date":"2020-05-08","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2020-05-25","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2020-08-03","notes":"","bunting":true},{"title":"St Andrew’s Day","date":"2020-11-30","notes":"","bunting":true},{"title":"Christmas Day","date":"2020-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2020-12-28","notes":"Substitute day","bunting":true},{"title":"New Year’s Day","date":"2021-01-01","notes":"","bunting":true},{"title":"2nd January","date":"2021-01-04","notes":"Substitute day","bunting":true},{"title":"Good Friday","date":"2021-04-02","notes":"","bunting":false},{"title":"Early May bank holiday","date":"2021-05-03","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2021-05-31","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2021-08-02","notes":"","bunting":true},{"title":"St Andrew’s Day","date":"2021-11-30","notes":"","bunting":true},{"title":"Christmas Day","date":"2021-12-27","notes":"Substitute day","bunting":true},{"title":"Boxing Day","date":"2021-12-28","notes":"Substitute day","bunting":true},{"title":"New Year’s Day","date":"2022-01-03","notes":"Substitute day","bunting":true},{"title":"2nd January","date":"2022-01-04","notes":"Substitute day","bunting":true},{"title":"Good Friday","date":"2022-04-15","notes":"","bunting":false},{"title":"Early May bank holiday","date":"2022-05-02","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2022-06-02","notes":"","bunting":true},{"title":"Platinum Jubilee bank holiday","date":"2022-06-03","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2022-08-01","notes":"","bunting":true},{"title":"Bank Holiday for the State Funeral of Queen Elizabeth II","date":"2022-09-19","notes":"","bunting":false},{"title":"St Andrew’s Day","date":"2022-11-30","notes":"","bunting":true},{"title":"Boxing Day","date":"2022-12-26","notes":"","bunting":true},{"title":"Christmas Day","date":"2022-12-27","notes":"Substitute day","bunting":true},{"title":"New Year’s Day","date":"2023-01-02","notes":"Substitute day","bunting":true},{"title":"2nd January","date":"2023-01-03","notes":"Substitute day","bunting":true},{"title":"Good Friday","date":"2023-04-07","notes":"","bunting":false},{"title":"Early May bank holiday","date":"2023-05-01","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2023-05-29","notes":"","bunting":true},{"title":"Summer bank holiday","date":"2023-08-07","notes":"","bunting":true},{"title":"St Andrew’s Day","date":"2023-11-30","notes":"","bunting":true},{"title":"Christmas Day","date":"2023-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2023-12-26","notes":"","bunting":true}]},"northern-ireland":{"division":"northern-ireland","events":[{"title":"New Year’s Day","date":"2017-01-02","notes":"Substitute day","bunting":true},{"title":"St Patrick’s Day","date":"2017-03-17","notes":"","bunting":true},{"title":"Good Friday","date":"2017-04-14","notes":"","bunting":false},{"title":"Easter Monday","date":"2017-04-17","notes":"","bunting":true},{"title":"Early May bank holiday","date":"2017-05-01","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2017-05-29","notes":"","bunting":true},{"title":"Battle of the Boyne (Orangemen’s Day)","date":"2017-07-12","notes":"","bunting":false},{"title":"Summer bank holiday","date":"2017-08-28","notes":"","bunting":true},{"title":"Christmas Day","date":"2017-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2017-12-26","notes":"","bunting":true},{"title":"New Year’s Day","date":"2018-01-01","notes":"","bunting":true},{"title":"St Patrick’s Day","date":"2018-03-19","notes":"Substitute day","bunting":true},{"title":"Good Friday","date":"2018-03-30","notes":"","bunting":false},{"title":"Easter Monday","date":"2018-04-02","notes":"","bunting":true},{"title":"Early May bank holiday","date":"2018-05-07","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2018-05-28","notes":"","bunting":true},{"title":"Battle of the Boyne (Orangemen’s Day)","date":"2018-07-12","notes":"","bunting":false},{"title":"Summer bank holiday","date":"2018-08-27","notes":"","bunting":true},{"title":"Christmas Day","date":"2018-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2018-12-26","notes":"","bunting":true},{"title":"New Year’s Day","date":"2019-01-01","notes":"","bunting":true},{"title":"St Patrick’s Day","date":"2019-03-18","notes":"Substitute day","bunting":true},{"title":"Good Friday","date":"2019-04-19","notes":"","bunting":false},{"title":"Easter Monday","date":"2019-04-22","notes":"","bunting":true},{"title":"Early May bank holiday","date":"2019-05-06","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2019-05-27","notes":"","bunting":true},{"title":"Battle of the Boyne (Orangemen’s Day)","date":"2019-07-12","notes":"","bunting":false},{"title":"Summer bank holiday","date":"2019-08-26","notes":"","bunting":true},{"title":"Christmas Day","date":"2019-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2019-12-26","notes":"","bunting":true},{"title":"New Year’s Day","date":"2020-01-01","notes":"","bunting":true},{"title":"St Patrick’s Day","date":"2020-03-17","notes":"","bunting":true},{"title":"Good Friday","date":"2020-04-10","notes":"","bunting":false},{"title":"Easter Monday","date":"2020-04-13","notes":"","bunting":false},{"title":"Early May bank holiday (VE day)","date":"2020-05-08","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2020-05-25","notes":"","bunting":true},{"title":"Battle of the Boyne (Orangemen’s Day)","date":"2020-07-13","notes":"Substitute day","bunting":false},{"title":"Summer bank holiday","date":"2020-08-31","notes":"","bunting":true},{"title":"Christmas Day","date":"2020-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2020-12-28","notes":"Substitute day","bunting":true},{"title":"New Year’s Day","date":"2021-01-01","notes":"","bunting":true},{"title":"St Patrick’s Day","date":"2021-03-17","notes":"","bunting":true},{"title":"Good Friday","date":"2021-04-02","notes":"","bunting":false},{"title":"Easter Monday","date":"2021-04-05","notes":"","bunting":true},{"title":"Early May bank holiday","date":"2021-05-03","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2021-05-31","notes":"","bunting":true},{"title":"Battle of the Boyne (Orangemen’s Day)","date":"2021-07-12","notes":"","bunting":false},{"title":"Summer bank holiday","date":"2021-08-30","notes":"","bunting":true},{"title":"Christmas Day","date":"2021-12-27","notes":"Substitute day","bunting":true},{"title":"Boxing Day","date":"2021-12-28","notes":"Substitute day","bunting":true},{"title":"New Year’s Day","date":"2022-01-03","notes":"Substitute day","bunting":true},{"title":"St Patrick’s Day","date":"2022-03-17","notes":"","bunting":true},{"title":"Good Friday","date":"2022-04-15","notes":"","bunting":false},{"title":"Easter Monday","date":"2022-04-18","notes":"","bunting":true},{"title":"Early May bank holiday","date":"2022-05-02","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2022-06-02","notes":"","bunting":true},{"title":"Platinum Jubilee bank holiday","date":"2022-06-03","notes":"","bunting":true},{"title":"Battle of the Boyne (Orangemen’s Day)","date":"2022-07-12","notes":"","bunting":false},{"title":"Summer bank holiday","date":"2022-08-29","notes":"","bunting":true},{"title":"Bank Holiday for the State Funeral of Queen Elizabeth II","date":"2022-09-19","notes":"","bunting":false},{"title":"Boxing Day","date":"2022-12-26","notes":"","bunting":true},{"title":"Christmas Day","date":"2022-12-27","notes":"Substitute day","bunting":true},{"title":"New Year’s Day","date":"2023-01-02","notes":"Substitute day","bunting":true},{"title":"St Patrick’s Day","date":"2023-03-17","notes":"","bunting":true},{"title":"Good Friday","date":"2023-04-07","notes":"","bunting":false},{"title":"Easter Monday","date":"2023-04-10","notes":"","bunting":true},{"title":"Early May bank holiday","date":"2023-05-01","notes":"","bunting":true},{"title":"Spring bank holiday","date":"2023-05-29","notes":"","bunting":true},{"title":"Battle of the Boyne (Orangemen’s Day)","date":"2023-07-12","notes":"","bunting":false},{"title":"Summer bank holiday","date":"2023-08-28","notes":"","bunting":true},{"title":"Christmas Day","date":"2023-12-25","notes":"","bunting":true},{"title":"Boxing Day","date":"2023-12-26","notes":"","bunting":true}]}}The schema can be broken down into having two key pieces of information:
- The applicable country with the values england-and-wales, scotland & northern-ireland at the root level.
- An array of Events that contains several properties such as Title (the Holiday’s Name) and Date
ADF Pipeline
So our goal is to select the appropriate region for our data and then to filter the dates to validate our input date doesn’t match any of the ones listed in the above API.
Our final pipeline will look like this consisting of 3 activities and parameterised so it can be reused as a child pipeline in wider orchestration flows. The code for this is re-produced at the bottom.
First we need to setup our pipeline with the parameters and variables.
We need to configure a parameter to accept a date input which could be from a parent process or trigger. We have called this date
Next we need a variable called BankHoliday to store the outcome of the question Is It a Bank Holiday for this date?
A web activity which calls the https://www.gov.uk/bank-holidays.jsonendpoint. It is configured like so
The output of this should look like
The Filter Activity allows use to iterate through that events array and find matching items.
We must first define our items. I’ve manually selected the england-and-wales events but this could easily be made to be dynamic and extended to other regions on demand. The ADF expression for Items is
{.adf expression} @activity('Get Bank Holidays').output['england-and-wales']['events']The conditions we will use to filter these selected items is
{.adf expression} @contains(item().date, formatDateTime(pipeline().parameters.date,'yyyy-MM-dd'))We check that the item contains the parameter date. We reformat the input date to ensure there are no inconsistencies in the comparison. e.g. yyyy-mm-DD != mm-yyyy-dd
Which outputs the following structure:
ItemsCount - The Number of items we searched to try and find a match
FilteredItemsCount - The Number of Matches we found for said date
Value - An Array of Matches for Said Date
An example of our code when we don’t find a match using the date of 2021-07-07.
An example of a successful match using the date of 2021-04-05 which is Easter Monday in the UK!
You will notice that FilteredItemsCount has incremented which is the property we will use in the next activity.
So now we have found our matches or not, now we need to set our variable to answer the question! We will use a set variable activity. Our logic should be pretty simple, does the FilteredItemCount = 1
The ADF expression looks like
{.adf expression} @equals(activity('Search Bank Holidays').output.FilteredItemsCount,1)Conclusion
Now you have a Azure Data Factory pipeline that can calculate whether a given day is a United Kingdom Bank Holiday Or Regional Bank Holiday (Wales, Scotland or Northern Ireland).