Author: Ramandeep Bhasin
This blog will help you to learn about the Project Creation and Integration of AWS and Snowflake with Matillion. You can create your own project according to your project needs as explained in the blog. Before integrating AWS and Snowflake with Matillion, let's briefly learn about Snowflake, AWS and Matillion.
What is Snowflake?
Snowflake is a great tool which enables data storage, data processing and data analytic solutions which are easier to use and more flexible than traditional offerings. It is a cloud based technology that provides data warehousing solutions. It is a truly Software-as-a-Service (SaaS) platform which can be used as a Data Engineering tool, Data Lake and Data Warehouse. It can also be used for building data related applications. As we know that sharing of data is necessary in the data world. So Snowflake has data sharing capabilities. We can share the data with other snowflake accounts or by creating reader accounts. It has multi-cluster and shared data architecture which is highly scalable. For loading data into Snowflake, one can use various ELT tools which are available in the market. One of the ELT tools is Matillion which is also available in the Snowflake Partner Connect.
What is Matillion?
Matillion is an ELT tool which can be used to load data into Snowflake or any other data warehouse. It is used for extracting data from data sources and loading it into Snowflake. It can also be used for transforming data so that one can generate meaningful insights from the transformed data. It is available in two versions. One is Matillion Data Loader and another is Matillion ETL. Matillion Data Loader is available free of cost and Matillion ETL is not free offering. To know more about the Matillion visit: www.matillion.com
What is AWS?
AWS stands for Amazon Web Services. It is a cloud platform which offers many cloud based services. There are a total 84 availability zones within 26 regions. It offers a free tier for upto 400 credits then AWS will charge you based on Pay-as-you-go Model. For more information about pricing visit: www.aws.amazon.com/pricing/
Create a New Project by connecting AWS and Snowflake
Now we need to create a new project. We need to integrate our AWS and Snowflake account with a new project in our Matillion. We also need to create a new Environment. Follow the below steps to integrate your AWS and Snowflake accounts with the new project.
Step 1: After Sign In to the Matillion account. Click on the Project Menu available on the top left corner of the User Interface.
Step 2: Then Click on Switch Project. After Clicking on the Switch Project, a new dialog box will appear. Now click on the Create Project button available on the button left of the dialog box.
Step 3: After clicking on the Create Project button, a new dialog box will appear. Fill the Project Group, Project Name and Project Description according to your project requirements. Then Click on Next button available on the button right as shown below.
Step 4: After clicking on the Next button, a dialog box will appear. Now click on the Manage button, a new dialog box will appear.
Step 5: Now Click on the + (Plus) button, a new dialog box named Create AWS Credential will appear. Fill the Credential Name, Access key ID, Secret Key of your AWS user. Choose the Encryption type according to your need and then click Ok.
Step 6: Now choose the AWS credential name that you have just created and then Click Ok.
Step 7: After choosing AWS credential, now fill the Environment Name that you want to create and then click on the Next Button which is available on the button right of the Create Project dialog box.
Step 8: After clicking on Next button, fill the Snowflake Connection details. In Account, fill the Snowflake account URL from Account Locator upto Cloud Provider Name. Fill the Username and Password of your Snowflake user that you want to connect to Matillon. Then Click on the Next button available on the bottom right of the dialog box.
Step 9: After Clicking on the Next button, choose the Snowflake Defaults by clicking on the drop down of Default Role, Default Warehouse, Default Database and Default Schema. You will see all the available roles, warehouses, databases and schemas for the user that you have filled in the previous step. If you were not able to see those defaults by clicking on the drop down, then check your credentials once again. If credentials are right then check the role (attached to the user) privileges. After that click on the Finish button available on the button right as shown below.
The Integration of AWS and Snowflake with Matillion is completed. Now you are able to access the objects of the snowflake for which we have privileges. You are also able to access different AWS services such as SQS, SNS, CloudWatch and many more.