Informatica Intelligent Cloud Services(IICS) Integration With Snowflake

Author: Onkar Bongirwar

Informatica Intelligent Cloud Services (IICS) is a cloud-based data integration platform that provides a variety of features such as business data integration, application integration, and API management between cloud and local applications.

It is a next-generation iPaaS (Integration Platform as a Service) solution offered by Informatica

Source: Thinketl

Snowflake provides a Data Cloud that is powered by an advanced data platform provided as Software-as-a-Service (SaaS). Snowflake enables data storage, processing, and analytic solutions that are faster, easier to use, and far more flexible than traditional database offerings.

Integration of Informatica Cloud Intelligent services with Snowflake

Informatica is a Snowflake partner which enables customers to ingest, transform and govern enormous data on Snowflake Data Cloud to uncover meaningful insights using analytics & AI at scale.

Snowflake combined with Informatica offers:

1. Migration  or building of cloud data warehouses with cloud-native advanced ELT capabilities.
2. With Informatica’s Cloud Accelerator for Snowflake, organizations can easily ingest, synchronize, integrate, cleanses data and break down enterprise data silos.
3. Comprehensive data governance for Data Cloud.
4. Both ELT as well as ETL on Snowflake’s Data Cloud.
5. Advanced pushdown optimization significantly improves processing speed and reduces operating costs.
6. Seamlessly ingest enormous volume of data into Snowflake Data Cloud.
7. Simplify integration and ingestion with hundreds of pre-built connectors and achieve higher productivity with no-code/low-code tools in minutes.
8. And Many more..

Source: Informatica

Demo where we will be using:

1. Source Database: MySQL Workbench Download Here

2. Data Integration Platform: Informatica Intelligent cloud service(IICS) 

 3. Target Data Warehouse: Snowflake

Steps:

1. Create a new Snowflake Account on Snowflake Trial

2. Spinning up Informatica cloud from Snowflake partner connect

Snowflake Partner Connect feature lets you create trial accounts with selected Snowflake business partners and integrate these accounts with Snowflake

3. Once you get successfully logged in to Informatica, Select Data Integration from various services Informatica offers.

4. Clicking on the Data integration service, will take up to the new palette.

5. MySQL Source Configuration:

Go to Administrator–>Connections–>New Connection

And fill all the necessary fields and test the connection setup.

6. For the target database, As we have spun up the IICS from Snowflake, Connection will be automatically setup in Informatica and a new database”PC_INFORMATICA_DB” will be created in snowflake

Informatica snippet:

Snowflake Snippet:

7. Create a new mapping in data integration service as shown  steps below:

Go to Data Integration–>New–>Mappings–>Mapping

8. Scenario is to extract the data from the source MySQL workbench.

Requirements:

A)Filter the orders from the order details table which has a price of less than 100. 

B)Get rid of the column “OrderlineNumber” column from the table.

C)Introduce a new column “Expensive” to check whether the order is expensive or not at a  target snowflake database.?

Sample data from database:classicmodels,Table:orderdetails

Total count of records:2996 rows

9. IICS caters to various transformations to work upon.

   We will be achieving all the above logic through some below transformations in Informatica mapping designer.

–Source

–Filter transformation

–Expression Transformation

–Target

A) Source Configuration:

 To connect to MySQL Workbench order details table.

B) Filter Transformation Properties:

 To filter the orders from the order details table which has a price less than 100 at a target Snowflake database. 

C) Expression Transformation Properties:

–To Get rid of the column “OrderlineNumber” column from the table at a target snowflake database.

  Set up the property by excluding the OrderlineNumber column as shown below:

— To Introduce a new column “Expensive” to check whether the order is expensive or not?

   Set up the logic by adding a new “Expensive” column with the below logic and validate it.

D) Target  Configuration:

To create a new table “orderdetails” at Snowflake target database

10. Save, Validate and Run the mapping

11. Check the status of mapping, it should be succeeded.

12. Check for the desired result in the Snowflake target database:

–New table”orderdetails” is created in the “PC_INFORMATICA_DB” database and “PUBLIC” schema.

–Records have got filtered with prices less than 100

  Original count(Source)-2996

   New count(Target)- 1087

–New “Expensive” column has been added with the flag ‘Y’ or ‘N’.

References:

1.https://docs.snowflake.com/

2.https://www.informatica.com/

3.https://www.informatica.com/solutions/explore-ecosystems/snowflake.html