With the 2019 Summer release, a new version
of the Snowflake connector on IICS called “Snowflake Cloud Data Warehouse V2" is available.
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.
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 Network. After you get the license, to connect to Snowflake, you must use PowerExchange for Snowflake.
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.
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.
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:
Yes, Informatica Intelligent Cloud Services and PowerCenter 10.2 Hotfix 1 supports Snowflake on Azure.
This support is currently not available. We plan to support it soon after it is GA.
On Informatica Intelligent Cloud Services and PowerCenter, you can configure pushdown optimization for the Snowflake database using the ODBC connection. The 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.
When you use PowerExchange for Snowflake from Big Data Management. you can configure pushdown optimization for a mapping to run on the Spark engine.
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.
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.
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.
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.
What can we do to improve this information (2000 or fewer characters)