There are different types of variables in Matillion and it is possible to transfer variable values from one job to another. We will understand how we can allow the communication of variables across the different jobs in the same environment. All types of variables be it grid, job or environment can be transferred between the jobs.
There will be two types of jobs calling (parent) and called (child) job. To transfer variables, they should be present in both the jobs or there should be corresponding environment variables and those variables can have different names but should be of same datatype in both the jobs. The variables in child job will act as placeholders for the values coming from parent job. Which means if we are sending some values out then there should be some container to hold those values. There are two possible scenarios for this transfer. Following is the way to deal with scenarios in Matillion.
Scenario 1: Parent (Calling Job) to Child (Called Job): Use Set Scalar /Grid Variables
For this case, we have two jobs one orchestration named “AUDIT_LOG” (parent) and transformation “AUDIT_LOG Table Entry” (child). The transformation job is called inside orchestration job. Now, I have created grid variable in both the jobs with same columns and datatype but with different names just to separate and better understanding. Below is the grid variable in parent job with name AUDIT_LOG.
The same grid variable is created in child job with name as “AUDIT_LOG_ENTRY”. This variable will be passed to child job using set grid variables tab of properties tab of selected component (child job). This will be clear from below picture.
When you will click on Set Grid variable, then you will see the dialog as below to select the corresponding variables.
Finally, this variable will be received at child job and mapping will be done (below image). This will be set in the values of any component in child job and there we have to select the desired variable.
Scenario 2: Child(Called Job) to Parent(Calling Job): Export tab of called job in parent job
This is a case when we have to bring values from child job to parent calling job. This can be easily handled in Matillion using the export tab of that component through which we called the job. This component can be “Run Transformation” or “Run Orchestration”. By default, Matillion provides few values already which includes the start time, complete time, duration or row count etc. automatically. These values can be fetched and saved to environment or job variables. If we want to export addition information or value then we can take the help of job variables which is to be created in child job and those will be available in export tab in parent job. And from parent job they can mapped to parent job’s variables. The variables /values that we want to send from child to parent that should be created in child job. For demonstration, I have created one job variable in “Test Trans” job with name test_export_var (below picture). We can create any number of variables here and all these variables will be visible in export tab of component in parent job.
We can select the environment or job variable of same datatype in target variable drop down. I hope this article has given you the understanding of how variables can be transferred between the jobs.
Written by: Prabhjot Kaur