Snowflake targets

Target preparation

1. 1Create a Data Ingestion and Replication user. Use the following SQL statement:
create user INFACMI_User password 'Xxxx@xxx';
2. 2Create a new user role and grant it to the Data Ingestion and Replication user. Use the following SQL statements:
create role INFACMI_superpipe;
grant role INFACMI_superpipe to user INFACMI_User;
3. 3Grant usage on the Snowflake virtual warehouse to the new role. Use the following SQL statement:
grant usage on warehouse warehouse_name to role INFACMI_superpipe;
4. 4Grant usage on the Snowflake database to the new role. Use the following SQL statement:
grant usage on database INFACMI_DB1 to role INFACMI_superpipe;
5. 5Create a new schema. Use the following SQL statements:
use database INFACMI_DB1;
create schema sh_superpipe;
6. 6Grant create stream, create view, and create table privileges on the new Snowflake schema to the new role. Use the following SQL statement:
grant create stream, create view, create table, usage on schema INFACMI_DB1.sh_superpipe to role INFACMI_superpipe;
7. 7Set the default role for the newly created user. Use the following SQL statement:
alter user INFACMI_User set default_role=INFACMI_superpipe;
8. 8Define a Snowflake Data Cloud connection to the target. You must use the KeyPair option as the authentication method. See Connectors and Connections > Snowflake Data Cloud connection properties.
9. 9Generate a private key with OpenSSL version 3.x.x and one of the following ciphers:
• - AES128
• - AES128-CBC
• - AES256
• - AES-256-CBC
10. Use the following openssl commands to generate and format the private key:
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -v1 AES-256-CBC -out rsa_key.p8
11. 10Generate the public key. Use the following openssl command, in which the -in option references the file (rsa_key.p8) that contains the encrypted private key:
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
12. 11In Snowflake, assign the public key to the Snowflake user. Use the following SQL command:
alter user INFACMI_User set rsa_public_key='key_value’;

1. 1Create a Data Ingestion and Replication user. Use one of the following SQL statements:
create user INFACMI_User password 'Xxxx@xxx';
or
replace user INFACMI_User password 'Xxxx@xxx';
2. 2Create a new role and grant the role to the Data Ingestion and Replication user. Use the following SQL statements:
create role INFA_CMI_Role;
grant role INFA_CMI_Role to user INFACMI_User;
3. 3Grant usage on the Snowflake virtual warehouse to the new role. Use the following SQL statement:
grant usage on warehouse CMIWH to role INFA_CMI_Role;
4. 4Grant usage on the Snowflake database to the new role. Use the following SQL statement:
grant usage, CREATE SCHEMA on database CMIDB to role INFA_CMI_Role;
5. 5Set the default role for the newly created user. Use the following SQL statement:
alter user INFACMI_User set default_role=INFA_CMI_Role;

• •Create and configure an external volume in Snowflake to store an identity and access management (IAM) entity that Snowflake can use to connect to your external cloud storage. The steps depend on the type of cloud storage, for example, Amazon S3, Google Cloud Storage, or Azure storage. For instructions, see the Snowflake documentation here.
• •Create a role with the minimum access control privileges required to create the external volume and Iceberg tables and to use the external volume and table schema. For instructions, see the Snowflake documentation here.

Usage considerations

• •Database Ingestion and Replication provides alternative methods of moving data to a Snowflake Data Cloud target:
• - If you select the Superpipe option when defining an ingestion task, the ingestion job uses the Snowpipe Streaming API to stream rows of data directly to the target tables with low latency. This method is available for all load types. You must use KeyPair authentication.
• - If you do not use Superpipe, ingestion jobs first write the data to data files in an internal stage, which has the name you specify in the task definition.
• •If you do not use Superpipe and the internal stage specified in the target properties for an ingestion job does not exist, Database Ingestion and Replication automatically creates the stage by running the following SQL command:
Create stage if not exists "Schema"."Stage_Bucket"”
For the command to run successfully, the following privilege must be granted to your user role:
GRANT CREATE STAGE ON SCHEMA "Schema" TO ROLE <your_role>;
• •You can enable database ingestion and replication jobs to store data in an open format in Apache Iceberg managed tables in an external cloud Amazon S3, Google Cloud Storage, or Azure Storage location instead of in Snowflake. For more information, see Using Snowflake-managed Iceberg tables to store data in external cloud storage.
• •When you define a connection for a Snowflake Data Cloud target, you must set the Additional JDBC URL Parameters field to database=target_database_name. Otherwise, when you try to define the target in the database ingestion and replication task wizard, an error message reports that the list of schemas cannot be retrieved.
• •When you define a connection for a Snowflake target using the KeyPair option as the authentication method and you generate the private key with OpenSSL 3.x.x version, use AES128, AES128-CBC, AES256, or AES-256-CBC cipher while generating the private key. Run the following command:
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -v1 AES-256-CBC -out rsa_key.p8
OpenSSL recommends using version 3.x.x.
Data Ingestion and Replication supports the following ciphers:
• - AES128
• - AES128-CBC
• - AES256
• - AES-256-CBC
• - CAMELLIA-256-CBC
• - CAMELLIA-256-CTR
• - ARIA-256-CTR
• •Database ingestion and replication incremental load jobs and combined initial and incremental load jobs generate a recovery table named INFORMATICA_CDC_RECOVERY on the target to store internal service information that prevents jobs restarted after a failure from propagating previously processed data again. This recovery table is generated in the same schema as the target tables.
• •Database ingestion and replication incremental load and combined load jobs that have a Snowflake target create transient _LOG tables to stage data on the target instead of permanent tables, by default. To prevent permanent tables from being created, the Snowflake Time Travel and Fail-safe features are disabled by default. The use of transient tables helps reduce _LOG table storage consumption and associated costs. With transient tables, however, you can't use the Superpipe option. If you need to create permanent tables, you can set the target snowflakeStagingTransientTable custom property to false.
• •For Snowflake targets, you cannot alter the scale of NUMBER fields or change the data type of an existing field to a different data type because Snowflake does not support these actions.
• •If the source tables contain columns that have special characters in the name, you can replace the special characters with a provided choice of string in the Snowflake target.
To replace the special characters with a provided choice of string in the generated target schema, perform the following actions before deploying the database ingestion and replication task:
1. 1Create a properties file to be used at the metadata-manager layer. Add the targetReplacementValue=<string> key-value pair to the properties file.
2. 2Open the Runtime Environment page in Administrator and edit the Secure Agent. Create a custom property in the Custom Configuration Details area:
• ▪ Select the Database Ingestion service.
• ▪ Select the DBMI_AGENT_ENV type.
• ▪ Enter DBMI_TASK_OVERRIDE_PROPERTIES as the property name.
• ▪ Enter the properties file location as the property value.
3. 3Set the targetReplacementValue=<string> custom property in the Target page of the task wizard.
Before running the task, add <jobname>. to the targetReplacementValue key in the properties file:
<jobname>.targetReplacementValue=<string>
If the property affects all jobs, add "alljobs." to the targetReplacementValue key:
alljobs.targetReplacementValue=<string>
• •Before stopping a database ingestion and replication incremental load or combined load job that has a Snowflake target and uses the Superpipe option, ensure that all ingested data in the change stream has been merged into the target. Otherwise, if the job is not restarted for an extended period or before the stream expires, any data that remains in the stream is lost and can’t be recovered.
• •For incremental load and combined load jobs with a Snowflake target that uses the Superpipe option, change data rows might be merged and applied to the Snowflake target tables before the merge interval set in the target properties elapses. This behavior occurs whenever mutated rows are detected. The mutation triggers merge apply processing to begin right away, without waiting for the merge interval.
• •If database ingestion and replication jobs replicate data from source JSON columns to Snowflake, Snowflake add slashes (/) within the data on the target. You can set a target custom property to avoid the extraneous slashes. For more information, contact Informatica Global Customer Support.

Using Snowflake-managed Iceberg tables to store data in external cloud storage

• •Ability to use query processing engines other than Snowflake to process the data
• •Interoperability across different data processing and analytics tools
• •Better performance
• •Robust security and governance

• •writerTargetTableFormat. Required. Set this custom property to iceberg to enable the use of Snowflake-managed Iceberg tables.
• •writerTargetTableExternalVolumeName. Required. Enter the name of the external volume that you created in the Snowflake account.
• •writerTargetTableExternalStorageBaseLocation. Optional. The base URL for the external storage location. If you do not specify this value, the base URL from the external volume definition is used.

• •You must use permanent Snowflake-managed Iceberg tables, not transient or temporary tables.
• •The external volume must be in the same cloud and region as the Snowflake account.
• •Char and varchar data types with a limited size specification are not supported.
• •Time and timestamp data types have a microsecond precision.

Configure private connectivity to Snowflake