Blog| Matillion ETL for Snowflake

How to Trigger a Matillion ETL for Snowflake Job from your Google Home device

Editor note: Today’s blog post was prepared by work placement students from Saint Ambrose school in Altrincham, UK.

This blog will walk you through the configuration process for setting up an ‘OK Google’ command to run your Matillion ETL for Snowflake job from a Google Home smart device or ‘OK Google’ enabled mobile phone.

Basic Workflow

To connect the Google Home device with Matillion an API call needs to be made from Google Home which puts a message on an SQS Queue to be picked up by Matillion. The workflow is below. This blog will focus on steps 1-4 for set up and triggering. More details can be found in our Technical Article.

 

Triggering a Matillion ETL for Snowflake job from your Google Home device

 

1. Invocation Command

A user invokes the Google Home Agent to instruct Matillion to run a given job. This is done via Dialogflow.

Dialogflow is a voice and text-based conversational interface. The Google Home device can be controlled via Dialogflow which converts a user query into an Intent which can then trigger an External API call or run a piece of code.

To set this up, first create a new Agent in Dialogflow:

 

Matillion ETL Job from your Google Home device - Agent

 

Add an integration in the Agent to connect to your Google Home device:

 

Matillion ETL Job from your Google Home device - Integrate Agent

 

Go through the configuration steps. For additional details see our Technical Article.

Note: In the Fulfillment enable a webhookcall and mark this as the end of the conversation. The webhook this will be calling is in AWS.

2.  Trigger Function Configuration in AWS Lambda

The Agent triggers a Lambda function using the AWS API.

The Amazon API Gateway allows users to create an endpoint which can act as an entrance point to an application. In the API create a new POST method to integrate to a Lambda function. This Lambda function will be used to put a message on an SQS Queue to start the Matillion job:

 

Matillion ETL Job from your Google Home device - API

 

3. Amazon SQS Message

The Lambda function puts a message on SQS Queue.

In a different tab, navigate to Lambda functions and create a new function from scratch. This should be initiated from the API Gateway and simply takes the job parameter and puts a message on the SQS Queue in the correct format. Further details on using a Lambda function to put a message on SQS Queue is available here.

 

Matillion ETL Job from your Google Home device - SQS

 

Back in the API Gateway, add this Lambda function in the POST Setup. This is done by typing the name of the function into the box:

 

Matillion ETL Job from your Google Home device - Add Lambda

 

Deploy the API from the actions menu and create a new stage.

4. Read Message and Trigger Job

Matillion ETL picks up the message and starts the job.

Once you add the Invoke URL to the Dialogflow you can test your configuration. This can be done either by voice or by typing:

 

Matillion ETL Job from your Google Home device - Run job

 

The result is the Matillion job has been triggered to start:

 

Matillion ETL Job from your Google Home device - Result

 

Useful Links

Dialogflow Service
Configure Dialogflow Agent
Manage SQS Configuration
Technical Article
Triggering ETL from an S3 Event via AWS Lambda

 

Arrange a free demo, or start a free 14-day trial of Matillion ETL for Snowflake to try it out.