Skip to main content

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

Matillion ETL: Ask a Solution Architect

Matillion ETL: Ask a Solution Architect

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:

 

Complex schedules in Matillion ETL: If component job setup

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

 

'If' component: javascript

 

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:

 

Complex schedules in Matillion ETL: Python job setup

 

Here are the settings for the job variables:

 

Settings for job variables in Python

 

Here is the Python 3 script to use:

 

Complex jobs in Matillion ETL: Python 3 job script

 

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

 

Python: 'If' component job conditions

 

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.