Integrating Slack with Matillion ETL

Slack is a popular messaging tool for teams to communicate and to automate notifications from third-party applications. Matillion ETL can send messages to Slack via Amazon SNS (Simple Notification Service) messages. This can be used to create a Slack channel that provides updates on the status of Matillion ETL job runs.

Setting Up Slack

To configure Matillion ETL to work with Slack, the first step is to set up a Slack channel to hold all of the Matillion ETL messages, and to configure it to listen to the SNS messages using an Incoming WebHook Slack app.

From the Slack menu, access the Apps page and search for Incoming WebHooks:
Click Add to Slack to add a new Incoming WebHooks configuration.
Select an existing Slack channel to post to, or click Create a new channel if the channel you want to use doesn't already exist. In this example, a channel called "aws-sns" has already been created in Slack.
Click Add Incoming WebHooks Integration to access the setup instructions. Copy or make a note of the Webhook URL as you will need this later. You do not need to configure anything on this page, so leave the defaults as they are and click Save Settings.

The next step is to create an SNS topic for Matillion ETL to send the messages to. This is done in the AWS console, as explained in the next section.

Setting Up AWS

In AWS you will create an SNS topic for Matillion ETL to send messages to, and a lambda function to control pushing the SNS messages to Slack.

In the AWS console, select Simple Notification Service and then select Create Topic.
Select Standard as the topic type, and give the topic a unique name (e.g. "Slack"). You do not need to configure anything else on this screen, so click Create topic to proceed.
Copy or make a note of the topic's ARN, as you will need this when configuring the lambda function.
In the AWS console, select Lambda → Create function → Author from scratch.
Enter a name for the function. This must be a unique name that you have not used for any other lambda function.
You need an execution role with basic permissions. By default a New role with basic Lambda permissions will be created, so you can simply confirm that this option is selected and then click Create function. Alternatively, you can use an existing role that has been created with appropriate permissions, or create a new role from AWS policy templates. You can learn more about these options in the AWS documentation.
The next step is to add an SNS trigger. Click Add Trigger, and then choose SNS from the drop-down list of trigger options. Paste the ARN of the SNS topic you created previously, and then click Add.

8. Ensure your lambda function is using Nodejs18.x. On the Code tab, replace the sample code in the Code source box with the following:

console.log('Loading function');

const https = require('https');
const url = require('url');
const slack_url = '&lt;webhook url&gt;';
const slack_req_opts = url.parse(slack_url);
slack_req_opts.method = 'POST';
slack_req_opts.headers = {'Content-Type': 'application/json'};

exports.handler = function(event, context) {
    (event.Records || []).forEach(function (rec) {
        if (rec.Sns) {
            var req = https.request(slack_req_opts, function (res) {
                if (res.statusCode === 200) {
                    context.succeed('sns posted to slack');
                } else {
                    context.fail('status code: ' + res.statusCode);
                }
            });
            req.on('error', function(e) {
                console.log('problem with request: ' + e.message);
                context.fail(e.message);
            });
            req.write(JSON.stringify({text: rec.Sns.Message}));
            req.end();
        }
    });
};

Replace the "const slack_url" string in this code with the Webhook URL generated when you configured the Slack WebHook app.

Click File → Save from the menu at the top of the Code source box to save your index.mjs file.
Now you need to publish the function. Click Actions in the upper-right of the screen and then click Publish new version. Optionally, enter a description of the version being published (for example, "Matillion to Slack initial version"), and then click Publish.
Copy the Function ARN as you will now need to create a subscription on your SNS topic that uses this.
Return to the Amazon SNS dashboard and select the topic you previously created. Click Create subscription.
For Protocol, select AWS Lambda, and paste the ARN of your lambda function into the Endpoint field.

14. Click Create subscription.

Testing your lambda function

If you have set up the lambda function in AWS, it can now be tested to see if it sends messages to Slack correctly.

Select the Test tab.
Select SNS Topic Notification in the Template drop-down field and enter a suitable name in the Name field.
In the code field, replace both the "EventSubscriptionArn" and "TopicArn" parameters with the ARN of the SNS topic you have previously created, and enter a suitable test message as highlighted here:
Click Create.
Either on the Test tab or back on the Code tab, click the Test button and then verify that the test message has been sent to your chosen Slack channel.

You can now go on to configure Matillion ETL to use this SNS topic to send messages to Slack.

Setting Up Matillion ETL

Matillion ETL can now send messages to the SNS topic, and these messages will appear in Slack. An example job that will output messages is shown below.

Example job

This orchestration job runs a transformation job. The transformation job has been configured to export the duration of the job to a global environment variable called "time".

Variable "time"

The "Post To Slack component" is an SNS Message component that has been configured to put a message on the SNS "Slack" topic created previously, as follows.

SNS Message component

Topic Name: The name of the SNS topic configured previously, in this example "Slack".
Message: The message that you want to post to your Slack channel when this job runs, for example:

Message to post to Slack

This example would post the following message to the Slack channel:

Slack channel