MuleSoft Composer: Connecting Snowflake Data Platform with clicks, not code

Introduction:

MuleSoft Composer is a product offering from MuleSoft that enables business and citizen developers to automate business processes with clicks, not code. This empowers business users to build their own automations and reduce their mundane tasks allowing them to focus more on value adding core business development work.

Snowflake is a SaaS application that provides a single platform for Data management across the enterprise. Its offerings span across Data Warehousing, Data Lakes, Data Engineering, Data Science, Data Application development and many more. Briefly, Snowflake can be the one stop solution for the end-to-end data management requirement across a large organization.

Businesses that work with large quantities of data who are looking for true empowerment through automation should consider the following in order to keep up with their fast paced, and ever-changing environments:

• A zero code (or near to zero code) automation platform that businesses can use to build intelligent and automated processes with clicks and without any dependency to the IT team.
• An IT Team that can focus on reusable asset creations that business can use as a building block to assemble and develop intelligent business processes — a true adoption of Hyperautomation culture.
• The automation platform’s capability to seamlessly connect to all major business applications easily. In turn this gives more flexibility to engineer business processes with a higher degree of efficiency and allows for a better experience for employees and customers.
• Automation platform out of the box ability to connect with the enterprise data management platform to include data centricity and data insight for intelligent decision making as part of the smart business process development.

A platform like MuleSoft Composer is a perfect answer for an enterprise looking to adopt a Hyperautomation strategy across their Business and IT teams. In addition to that, an out of the box connector for Snowflake offered by MuleSoft Composer makes the proposition more lucrative for businesses to bring data insight much more easily and seamlessly in the developed business process on their own (without direct dependency on the IT team).

This article is an attempt to share how to configure and use the Snowflake connector from MuleSoft Composer. Let's take a deep dive into the topic.

Configuring Snowflake Connector:

Configuring the Snowflake connector in MuleSoft Composer is easy. The steps are as follows —

1. Snowflake Connection can be created as part of trigger step or action action step in the flow -

2. Click on the System Action and select the Snowflake connector

3. Add a new connection —

4. Configure the connection parameters -

5. If you are using Snowflake trial Version then the necessary information can be obtained from the Snowflake worksheet console —

6. Provide the account name from the link you received as part of your trial version activate mail or from the hostname of the Snowflake worksheet

7. Once all parameters are provided (including the Snowflake account password) then click on connect. If the configuration is successful it will create the connection. Now this connection will be used to listen to the Snowflake event (Trigger) or connect to Snowflake for data manipulation (action).

Configuring Snowflake Connector as Trigger activity:

To trigger a MuleSoft Composer flow on an event like new row insertion or on updating a new row on Snowflake is possible by configuring the starting point of the Composer flow with the Snowflake system event.

1. Snowflake connector supports two events to trigger a composer flow. “New Or Updated Row” will trigger the flow if any new row gets inserted or an existing row gets updated within Snowflake. “New Row” will trigger the flow if any new row gets inserted.

2. Then provide the table name. If the Insert (or Upsert if the other event is chosen) event happens then the flow will get triggered.

3. As per standard, Snowflake tables should have some audit columns like Created Date and Updated Date. MuleSoft Composer also expects a column for “Start time” to trigger the flow if any row gets inserted into the Snowflake table after that defined time then the MuleSoft Composer Flow will be triggered.

For reference, the Snowflake table was created with Created date and Updated date column.

Also MuleSoft Composer configuration auto populates all date time columns from the selected Table. The user only needs to select the column based on which MuleSoft Composer flow will listen to the Snowflake event and get triggered.

4. Once the flow gets triggered then down the line “System action” can be called to pull the data from the target table and do further processing.

A detail demo can be referred from the below YouTube video to understand how to fetch rows from Snowflake table for down the line processing —

Configuring Snowflake Connector as Action in MuleSoft Composer:

Snowflake connector is also used for system action. The configuration steps are as follow —

1. Click on System Action

2. Then click on the Snowflake system.

3. Select the Snowflake connector (it will be saved with the same name it was created initially).

4. Select an action that will be performed to develop the flow. The options are Delete, Insert, Select and Update.

5. Next click on the Edit SQL String to create the SQL string for insertion (if Insert action is selected).

Use parameterized Insert statements to send data for insertion on the fly.

6. Based on the parameters defined on the SQL string, the Composer flow automatically creates fields to populate with variables to insert values to the Snowflake table.

A detailed demo can be referred from the below YouTube video to understand how to insert rows to the Snowflake table from MuleSoft Composer. In this video it has been shown how data from Google Sheet can be inserted into a Snowflake Table using MuleSoft Composer—

Conclusion:

Manipulating Data from MuleSoft Composer on a Data Platform like Snowflake is easy yet a powerful means to create a robust business process. This is a significant offering from MuleSoft Composer for organizations to adopt Hyperautomation in critical business processes that deal with data from a data lake or data warehouse. This article’s intent is to give others step by step instructions on how to configure the Out of the box Snowflake connector from MuleSoft Composer and how the same can be used to trigger a Composer flow or used as an action inside of a Composer flow. My hope is that this will help others who are just starting to use the Snowflake connector within MuleSoft Composer.