Informatica Intelligent Cloud Services (IICS) integration with Snowflake

  • December 03, 2021

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’s a next-generation integration platform as a service (iPaaS) solution offered by Informatica. (Source: ThinkETL)

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

Integrating IICS with Snowflake

As a Snowflake partner, Informatica enables customers to ingest, transform and govern enormous volumes of data on Snowflake Data Cloud to uncover meaningful insights using analytics and artificial intelligence at scale.

Among the benefits Snowflake combined with Informatica offers are the following:

  • 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 and cleanse data, as well as break down enterprise data silos
  • Comprehensive data governance for Data Cloud.
  • Both ELT and ETL on Snowflake Data Cloud
  • Advanced pushdown optimization, which significantly improves processing speed and reduces operating costs
  • Simplify integration and ingestion with hundreds of prebuilt connectors and achieve higher productivity with no-code/low-code tools in minutes

This demo uses the following tools:

  1. Source database: MySQL Workbench — download here
  2. Data integration platform: IICS
  3. Target data warehouse: Snowflake

Steps

  1. Create a new Snowflake Account on Snowflake Trial
  2. Spin up Informatica Cloud from Snowflake Partner Connect — the Snowflake Partner Connect feature lets you create trial accounts with selected Snowflake business partners and integrate these accounts with Snowflake
  3. Log in to Informatica and then select Data Integration from the services offered
  4. Click Data integration, which takes you to the new palette
  5. Configure the MySQL source:
    • Go to Administrator–>Connections–>New Connection
    • Fill in all necessary fields and test the connection setup
  6. For the target database: Because the IICS is spun up from Snowflake, the connection will be set up automatically in Informatica and a new database — “PC_INFORMATICA_DB” — will be created in Snowflake
  7. Create a new mapping in the Data Integration service; go to Data Integration–>New–>Mappings–>Mapping
  8. Extract the data from the source MySQL workbench:
    1. Filter the orders from the Order Details table for anything with a price less than $100
    2. Delete the “OrderlineNumber” column from the table
    3. Create a new “Expensive” column to check whether an order is expensive or not at a target Snowflake database (NOTE: The total records count is 2,996 rows)
  9. IICS caters to various transformations; the above logic will be achieved through transformations in the Informatica Mapping Designer:
    • Source
    • Filter transformation
    • Expression transformation
    • Target
    1. Source configuration: Connect to the MySQL Workbench order details table
    2. Filter the transformation properties — filter the orders from the Order Details table for anything with a price less than $100
    3. Expression transformation properties:
      • Delete the “OrderlineNumber” column from the table at a target Snowflake database
      • Set up the property by excluding the OrderlineNumber column
      • Create a new “Expensive” column to check whether the order is expensive or not
      • Set up the logic by adding a new “Expensive” column and validate it
    4. Target configuration — create a new “orderdetails” table at a Snowflake target database
  10. Save, validate and run the mapping
  11. Check the status of mapping, it should be successful
  12. Check for the desired result in the Snowflake target database:
    • New “orderdetails” table is created in the “PC_INFORMATICA_DB” database and “PUBLIC” schema
    • Records are filtered with prices less than $100
      • Original count (Source): 2,996
      • New count (Target): 1,087
    • New “Expensive” column is 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

    4. — By Onkar Bongirwar