It can be thrilling and intimidating to start the process of integrating your business systems and data. SnapLogic, as a data-oriented iPaaS, can make this task an easy and straight-forward one. In this step-by-step tutorial, we will walk you through the steps of developing your first SnapLogic integration. This tutorial will guide you through every step of the integration development process, from setting up the accounts to orchestrating data flow and automating critical business activities.
Our use case in this article will be retrieving company information from Snowflake, segregating those companies into large, medium, and small ones, then persisting the large and medium into different files while neglecting the small companies. We’ll focus on the build part here, then, in future articles, we’ll cover other topics such as troubleshooting, scheduling, production deployment, monitoring, etc.
Snowflake Setup
Before we implement the integration in SnapLogic, we’ll have to prepare the Snowflake environment. If you have a Snowflake account, log in to that account, else, you can easily create a trial account here.
After logging in, first thing to do is to install the “Free Company Dataset” from the Marketplace as per the snapshot below
Once that is done, you can create a worksheet and run queries against this database as seen below.
That would be it for Snowflake. Now we can head to SnapLogic to create the pipeline that would connect to this database and run the same query to retrieve the companies
Creating the Pipeline
The first step in building an integration is to create a pipeline. Once you log in to the SnapLogic platform, you can click the “+” sign on the top of the middle canvas to create a pipeline as follows
Fill the pipeline name and parent project then click “Save”
Retrieving the Data
As explained in our first article, Getting started to SnapLogic, the pipeline consists of a series of activities, or snaps, connected to each other. Those snaps are the foundational building blocks of performing any logic in the pipeline. Since our first step is to retrieve the data from Snowflake, search for snowflake among the plethora of snaps available in the platform, then drag-drop the “Snowflake – Select” into the canvas and click on it to open the snap properties in order to configure it. Then click on “Add Account” to configure the connection to Snowflake. Once the account is configured, it becomes a reusable component that can reuse in any other pipeline.
Depending on where your Snowflake account exists, the configuration could be different, however for our case, we’ll select S3 Database Account and click “Continue”
Populate the Snowflake account properties then validate and apply the changes
Now you can go back to the “Settings” tab to configure what data you want this particular snap to retrieve. Here you can select the schema and table name, the where clause, and other configurations. For our case, we will focus on retrieving Australian IT companies data, however this can be different for your use case.
Once you save and close the configuration window, the snap will be automatically validated turning green.
Moreover, a sample dataset will actually be retrieved on spot so that you can actually see the results of your query by clicking the dark green document icon at the end of your snap
Manipulating the Data
What we want to do next is pick up some of the input fields to make it into the next steps of the pipeline. To do that we’ll use the mapper snap as depicted in the snapshot below
Click on the mapper snap to open the configuration. Note how the input schema is pre-populated. This is because the mapper will look at the previous snaps and later snaps to determine what are the incoming and outgoing schemas. Select the fields you want to map, drag-drop them into the mapping table (middle-section), map each field to an output field, then save. Note how SnapLogic automatically populates the input and output views which is very helpful to determine the output of your mappings on the spot.
After saving and closing the mapping configuration, SnapLogic will validate the changes and similar to what happened in the first snap, you can click the green document icon after the mapping snap to view how your data looks like now. Note how SnapLogic’s AI system (Iris) suggests the next snap to be used (Snowflake Insert), however we don’t want to do that for this use case.
Next, let’s sort the results by the year the company was founded, again, this is very easy to be done using the Sort snap, as you can see below
Click on the Sort snap to configure it to sort the data by founded year in descending order, as follows:
Again, if you click on the green icon after the sort snap, you should be able to see how the results look like now
Now that our data is filtered and sorted, we want to route it to different outputs based on the size of the companies. To do that, we’ll have to use a route snap. Go on and drag-drop a route snap into the canvas and connect it to the previous snap
Then open the configuration, go to the views tab and configure the views as follows
Go back to the “Settings” tab and configure the routing rules as follows. Don’t forget to check the “First match” checkbox so that the route will act as a nested if/else statements
You can open the expression builder to build each of those expressions by clicking on the icon next to the expression field. Here’s the expression builder window for both expressions above
Expression 1: Large companies where size is above 10,000 employees
Expression 2: Medium companies where size is between 1,000 and 10,000 employees
Save and close the route snap. This is how your pipeline will look now
Persisting the Data
Now that the data is routing to the correct path, all what is left is to throw a CSV Formatter and File Writer snaps into the first two route paths, while neglecting the third path as we are not interested in small companies.
Configure the large file writer as follows to create a file hosted in your SnapLogic account with the specified name:
In a similar fashion, configure the medium file writer snap. The pipeline should look like the following now:
Test the Pipeline
After the build is done, we can test the pipeline now. To do that, click on the “play” icon in the pipeline menu and wait for the pipeline to finish executing. Notice how the color of the snaps turns yellow while executing indicating they are currently running
Validate the Results
Once the execution finishes, the pipeline turns dark green. If there’s any exception, the failing snap turns red.
Click on “Manager” in the top menu to navigate into the administration section. There you can view all of your projects and artifacts such as the previously created files. Navigate to your project and click the “Files” tab to view the files saved in your project. Click the icon next to the files created to view or download the file as follows
Once you click “View”, you’ll be able to view the saved file and verify the results
Conclusion
Congratulations on completing your first SnapLogic integration! In this step-by-step tutorial, we have created our first pipeline in SnapLogic which retrieves data from Snowflake, manipulates it, then stores it to disk. We have only covered the build and test aspect. We’ll cover other concerns in future articles, such as deployment, monitoring, detailed troubleshooting and error handling. Our journey in SnapLogic has just begun, and we’ll be digging deep into this data-driven platform in the future to build on the knowledge we accumulated in this article.
Pingback: Creating an API in SnapLogic