Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(1 Rating)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

Snowflake Cloud Data Warehouse Connector Related Queries
Answer

Which connector should be used on IICS?

With the 2019 Summer release, a new version of the Snowflake connector on IICS called “Snowflake Cloud Data Warehouse V2" is available. 

For all-new use cases, it is recommended that you use this connector. If you have been using the earlier version “Snowflake Cloud Data Warehouse,” you can continue to use it for now, but it is recommended that you switch to Snowflake Cloud Data Warehouse V2 at the earliest. 

The earlier version does not include any new features; however, the date for deprecating this version is yet to be identified.

 

Snowflake Cloud Data Warehouse V2 supports a new feature called “File Mass Ingestion.” You can use mass ingestion to ingest data that resides in files from various file storage systems into Snowflake at a much higher speed than when you use mappings tasks. Use mass ingestion tasks for quick uploads. A mass ingestion task does not support transformations. If you need to use transformations, you must use the mapping task instead.


What version is the latest Snowflake Connector available with?

  • Informatica Intelligent Cloud Services Cloud Data Integration– Current.
  • PowerCenter – 10.2  HotFix 2. If you are using 10.2 HotFix 1, apply EBF-11911.
  • Data Quality – 10.2 HotFix 2.
  • Informatica Big Data Management – 10.2.2 (Next upcoming release is 10.4)
     

What license is required to use PowerExchange for Snowflake on Powe​rCenter?

To use PowerExchange for Snowflake on PowerCenter, you need the license SKU “PowerExchange for Cloud and Big Data Applications”. This license allows one instance of a connector of Tier D and one of Tier B. PowerExchange for Snowflake is a Tier D connector. For more information, see the Informatica Connectivity Product Availability Matrix (PAM) on Informatica NetworkAfter you get the license, to connect to Snowflake, you must use PowerExchange for Snowflake. 

 

What does one instance of Snowflake connector license cover?

One instance of the Snowflake connector license applies to one “warehouse” in Snowflake. The number of databases or schema you use have no impact on license requirements. 


 

How do I install PowerExchange for Snowflake on PowerCenter?

PowerExchange for Snowflake is available on PowerCenter 10.2 as a separate installer. In 10.2 HotFix 1,  PowerExchange for Snowflake is included with the installer and is enabled with the license.

Starting with PowerCenter 10.2 HotFix 1, PowerExchange for Snowflake is included in the main installer and enabled using the license. On 10.2, it is available as a separate installer. It is recommended that you use 10.2 HotFix 2.


Does the Snowflake Connector need any additional packages for Cloud Data Integration on Informatica Intelligent Cloud Services?

Snowflake Connector requires the “SDKPatch” package. Verify if you have the licence on the Licence page on Informatica Intelligent Cloud Services. If you do not have the license, contact Global Customer Support. 


How does Informatica access Snowflake?

Informatica uses an interface provided by Snowflake that internally uses the Snowflake JDBC driver. 

Snowflake Connector uses the following version of the Snowflake JDBC driver version:  

  • PowerCenter - 3.0.20 (upgraded to 3.6.4 with 10.2.0 HotFix 2)
  • Data Quality – 3.0.20 (upgraded to 3.6.4 with 10.2.0 HotFix 1).
  • Informatica Intelligent Cloud Services - 3.6.4

Does Informatica support Snowflake on Azure?

Yes, Informatica Intelligent Cloud Services and PowerCenter 10.2 Hotfix 1 supports Snowflake on Azure.


Does Informatica support Snowflake on Google?

This support is currently not available. We plan to support it soon after it is GA.



Is it possible to configure database pushdown optimization?

On Informatica Intelligent Cloud Services and PowerCenter, you can configure pushdown optimization for the Snowflake database using the ODBC connectionThe connector supports both Partial and Full pushdown optimization. You can configure pushdown optimization for within a schema, across multiple schemas, or across multiple databases in Snowflake. For more information about the rules and guidelines for configuring pushdown optimization, see the respective Snowflake connector documentation.  


On Data Quality,  only Filter pushdown optimization is supported. Full pushdown optimization is not supported when you use the Snowflake connection or the ODBC connection.


Is it possible to configure pushdown optimization on the Spark engine?

When you use PowerExchange for Snowflake from Big Data Management. you can configure pushdown optimization for a mapping to run on the Spark engine.


Does Snowflake Connector on Informatica Intelligent Cloud Services support the Informatica Cloud Hosted Agent?

Yes, Snowflake Connector supports the Informatica Cloud Hosted Agent. You need the Hosted Agent license to use the agent with Snowflake Connector.


Is it possible to configure partitioning?

Yes, you can configure key range partitioning while reading data from Snowflake.  You can configure pass-through partitioning along with other target properties to partition data while writing data to Snowflake. For more information, see the respective Snowflake connector guide.


Is it possible to configure a Snowflake connection using proxy?

Yes, you can use proxy with PowerCenter for Snowflake in PowerCenter 10.2 HotFix 1. You can also use proxy for the Snowflake connection in the current Informatica Intelligent Cloud Services release.


Is it possible to configure bulk options to load data?

By default, PowerExchange for Snowflake is designed for bulk load. You can configure the write properties for loading data in bulk. 

For more information, see the respective Snowflake connector guide.


Is it possible to use an SQL Query to read data from Snowflake?

When you use Snowflake Connector on Informatica Intelligent Cloud Services, you can use “Query” as a source object type. 

When you use PowerExchange for Snowflake on PowerCenter,  you can configure " SQL Query".  This feature is available with 10.2 HF2. If you are using 10.2 HF1, contact Global Customer Support for the patch.


Is it possible to use an SQL Override for Snowflake lookups?

When you use Snowflake Connector on Informatica Intelligent Cloud Services, you can configure an SQL override by using the property “SQL Override” in the “Advanced Properties”. 

When you use PowerExchange for Snowflake on PowerCenter, you can use a Pipeline Lookup for Snowflake, which uses an SQL Query as a source. This feature is available with 10.2 HotFix 2. If you are using 10.2 HotFix 1, contact Global Customer Support to get the patch “EBF-11911” to apply on PowerCenter 10.2 HotFix 1.


Is it possible to read data from tables and views in Snowflake?

Yes, you can read data from Snowflake tables and views.


Is it possible to perform data profiling over my data in Snowflake? 

Yes. You can perform data profiling using Informatica Data Quality. You must use the currently supported version. 


What data types are supported?

For information about the various data types that Snowflake Connector supports, see the topic “Snowflake and Transformation Data Types” in the respective on-premise or cloud Snowflake connector guide


Is VARIANT data type supported?

If you are using mappings, you can use Informatica expression language to construct a value to be written to a VARIANT column. If you are reading a VARIANT field, you can write a Snowflake SQL query to read specific attributes from it. You can also load data into a VARIANT column using File Mass Ingestion task.

 

Is it possible to change existing data warehouse sources and targets in PowerCenter mappings with Snowflake sources and targets?

Yes,  you can update existing data warehouse mappings to use Snowflake sources and targets. For more information, see the following KB article:  https://kb.informatica.com/faq/7/Pages/21/547962.aspx 

Note: You can perform this task only from 10.2 HotFix 1 and later​ versions.​ 


More Information

Applies To
Product: PowerExchange; PowerCenter; Data Quality; Big Data Management; Cloud Data Integration
Problem Type: Configuration; Connectivity; Product Feature; Installation
User Type: Business User
Project Phase: Configure; Implement
Product Version:
Database:
Operating System:
Other Software:

Reference

Attachments

Last Modified Date:8/16/2019 3:40 AMID:553165
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)