Authors: Rashi RKG & Vijaya Sharma
Introduction:
ETL jobs may fail for a number of reasons. Although this is expected of any job, what’s important for it is to recover from this failure and notify within a finite time period. Any errors occurred in Matillion are logged in server logs and can be downloaded from the Admin menu.
We would want to repeat this error-handling process in multiple jobs, multiple projects, or within the same job a number of times. For this, we can create parameterized jobs that can be further used as it is inside other jobs.
Assumptions:
Snowflake's connection with Matillion is already established.
AWS connection with Matillion is already established.
Component Export:
Within each orchestration component, you can find an export tab that allows you to store some component metadata into variables. There are some standard attributes that are common across all components; these are:
Component
Started At
Completed At
Duration
Message
Status
Row Count
And some of the components have metadata that is specific. For example, “Table Recreated” attribute of the Create Table Component. Similarly, “Time Taken To Load” and “Time Taken To Stage” attributes of the Data Stager Component..
You will need to store those values in some variables for later use. In order to not create a set of variables for every job, you can use environment variables which will be accessible across all jobs, and since you need to access the values in a different branch (line originating at source component), you should select the shared behavior.
Alerts:
Now that we have important metadata such as the message, start time, and status of a component, we can compose a message to notify an administrator. The cloud platform we are using will determine the options available to us.
Amazon SNS (Amazon Redshift or Snowflake on AWS):
We are using SNS for alerting, as it easily allows subscription to topics by email, and SMS as well as allowing us to push messages straight into SQS.
Step 1: Create an SNS topic in AWS with Email alerts and rest default options or as suited for your use case
Step 2: Create a subscription to the topic
Step 3: Accept the invitation for SNS Subscription in your email
Step 4: Add SNS Message component to your job and configure it for Job Success:
Name: Give a name for the component
AWS Region: Select the region where we created the SNS topic in AWS
Topic Name: Select the topic name. It should come in the drop-down.
Subject: Give the Email Alert a Subject line.
Message: Configure your message using the Component Metadata and Environment variables.
Step 5: Add SNS Message component to your job and configure it for Job Failure :
Step 6: Add an End Failure component for the job
The "End Failure" component depicts the overall status of the job as a failure, even if it would have been otherwise successful. It also serves to document the expected end point of a job.
Conclusion
Incorporating Error handling using Email alerts can prove useful for any organization. By following the above steps, one can easily set up email alerts in Matillion. This can prove helpful in improving performance, identifying critical issues at the right time, and reducing financial risk. With such a feature, critical decisions can be made quickly, and serious crises can be averted.
References
https://www.matillion.com/resources/blog/error-handling-in-matillion-etl-best-practices
https://www.matillion.com/resources/blog/error-handling-options-in-matillion-etl-creating-a-shared-job