Ask a Solution Architect: How do I add a complex schedule to Matillion ETL?

Q: I want to be able to schedule a job to run every other week on Tuesday. But I can’t seem to add it into the Matillion ETL job scheduler. Is there a way to add schedules that aren’t straightforward, like starting a job once per week? 

A: Great question, as we all have schedules that can be a bit more complex. The Matillion inbuilt scheduler is a cron-like scheduler. It is great for scheduling simple intervals for Matillion ETL jobs from within the tool itself.  But there are some complex schedules that cannot be specified in the scheduler – for example the schedule you mentioned or a job that needs to be scheduled to run on the last day of the month. But there is a way to add these schedules, even if the scheduler does not natively handle them.

Scheduling complex jobs in Matillion ETL

For example, let’s use your schedule: run a job every two weeks, on Tuesday. There are two options to create that schedule – one that uses Javascript, and another for Python. In either of the two options you can use; you would need to  schedule the wrapper job (seen below) to run every Tuesday, and then leverage the ‘If’ component to evaluate conditions to run the job with the scheduling requirement of running it every other week on Tuesdays.

Option 1: Using ‘If’ component in Advanced mode

The ‘If’ component, with the Mode property set to Advanced, can be used to evaluate a Javascript expression. Using this functionality, we can build a Javascript expression to evaluate the day of the week and the week number.

Here is the job setup:

Here is the javascript expression used in the ‘If’ component:

The first condition evaluates if the day is a Tuesday (day numbers go from 1 to 7, with Monday being 1) and the second condition evaluates if the week is an even week.

Option 2: Using the Python Script component along with the ‘If’ component in Basic mode

We can also use the Python Script component to calculate the day number and the week to set job variable values. Those variables can then be used in the ‘If’ component to evaluate the values and run the subsequent jobs and steps per the scheduling requirement.

This is the job setup for Option 2:

Here are the settings for the job variables:

Here is the Python 3 script to use:

And here are the conditions in ‘If’ component (Simple Mode):

With either of these two options, you should be able to accommodate most complex schedules by changing the conditions per the scheduling requirement. 

To learn more about how Matillion ETL enables you to schedule jobs to extract, load, and transform data, schedule a demo today.