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:
Migration or building of cloud data warehouses with cloud-native advanced ELT capabilities.
With Informatica’s Cloud Accelerator for Snowflake, organizations can easily ingest, synchronize, integrate, cleanses data and break down enterprise data silos.
Comprehensive data governance for Data Cloud.
Both ELT as well as ETL on Snowflake’s Data Cloud.
Advanced pushdown optimization significantly improves processing speed and reduces operating costs.
Seamlessly ingest enormous volume of data into Snowflake Data Cloud.
Simplify integration and ingestion with hundreds of pre-built connectors and achieve higher productivity with no-code/low-code tools in minutes.
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:
2.https://www.informatica.com/
3.https://www.informatica.com/solutions/explore-ecosystems/snowflake.html