Snowflake Data Cloud Connector

Back Next

Optimize the staging performance of a mapping

Data Integration, by default, creates a flat file locally in a temporary folder to stage the data before reading from or writing to Snowflake. You can set Data Integration to optimize the staging performance of the read and write operations.

If you do not set the staging property, Data Integration performs staging without the optimized settings, which might impact the performance of the task.

Setting the staging property

You can optimize the mapping performance of both the read and write operations.

You need to set the following staging property in the agent properties based on the operation that you want to optimize:

To optimize the read operation, set the following property:

INFA_DTM_RDR_STAGING_ENABLED_CONNECTORS

To optimize the write operation, set the following property:

INFA_DTM_STAGING_ENABLED_CONNECTORS

Perform the following tasks to set the staging property for the Tomcat in the Secure Agent properties:

In Administrator, click

Runtime Environments

Edit the Secure Agent for which you want to set the property.

In the

System Configuration Details

section, select the

Service

Data Integration Server

and the type as

Tomcat

Set the value of the Tomcat property to the plugin ID of Snowflake Data Cloud Connector.

You can find the plugin ID in the manifest file located in the following directory:

<Secure Agent installation directory>/downloads/<Snowflake package>/CCIManifest

The following image shows the property set for the Secure Agent for the read and write operations:

When you run the mapping, the flat file is created in the following directory in your machine based on the operation for which you set the property:

Read operation:

C:\Windows\Temp\StagingReader\<Source_Name><data_.csv>

Write operation:

C:\Windows\Temp\snowflake\stage\<Snowflake_Target.txt>

You can check the session logs.

Read operation: If the staging is done through the flat file successfully, Data Integration logs the following message in the session log:

Staging mode is enabled to read data.

Write operation: If the staging is done through the flat file successfully, Data Integration logs the following message in the session log:

The INFA_DTM_STAGING is successfully enabled to use the flat file to create local staging files.

Rules and guidelines

When you enable the staging property for the read operation, you cannot configure a custom query to call a stored procedure from Snowflake. If you run a query to call a stored procedure, the task fails.

Consider the following guidelines when you enable the staging property for the write operation:

If you run a mapping enabled for SQL ELT optimization, the mapping runs without SQL ELT optimization.

If the data contains timestamp data types with time zone, the job runs without staging the data in the local flat file.

If the mapping contains Oracle CDC as the source and Snowflake as the target, the job runs without staging the data in the local flat file.

You can determine the behavior of writing data with empty and null values to the target when you enable the staging property and set the copyEmptyFieldAsEmpty in the Additional Write Runtime Parameters
field in the Target transformation properties.

The table describes the behavior of empty and null values when you set these properties:

Additional Write Runtime Parameters	Enable staging optimization	Disable staging optimization
copyEmptyFieldAsEmpty=FALSE	Empty -> NULL NULL -> NULL	Empty -> NULL NULL -> NULL
copyEmptyFieldAsEmpty=TRUE	Empty -> Empty NULL -> Empty	Empty -> Empty NULL -> Empty
Property not set	Empty -> Empty NULL -> NULL	Empty -> Empty NULL -> NULL

If you run a mapping to write data of the TIMESTAMPNTZ data type with microsecond or nanosecond precision to a Snowflake target, add the DTM_STAGING_TIMESTAMPNTZ_FORMAT property and set the value in the

Additional JDBC URL Parameters

field of the Snowflake Data Cloud connection.

By default, you can write data of the TIMESTAMPNTZ data type with millisecond precision to a Snowflake target.

For more information about values that you can set for the DTM_STAGING_TIMESTAMPNTZ_FORMAT property, see JDBC URL parameters.

For information on the performance comparison of the execution time before and after setting the staging property, see the following How-To Library article: Performance Tuning and Best Practices for Snowflake Data Cloud Connector

Rename Saved Search

Table of Contents

Snowflake Data Cloud Connector

Snowflake Data Cloud Connector

Optimize the staging performance of a mapping

Optimize the staging performance of a mapping

Setting the staging property

Rules and guidelines