Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Integration Connectors
  • All Products

Table of Contents

Search

  1. Preface
  2. Part 1: Getting Started with Snowflake Data Cloud Connector
  3. Part 2: Data Integration with Snowflake Data Cloud Connector
  4. Part 3: SQL ELT with Snowflake Data Cloud Connector
  5. Appendix A: Data type reference
  6. Appendix B: Additional runtime configurations
  7. Appendix C: Upgrading to Snowflake Data Cloud Connector

Snowflake Data Cloud Connector

Snowflake Data Cloud Connector

Back Next

Optimize the staging performance of a mapping

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:
  1. In Administrator, click
    Runtime Environments
    .
  2. Edit the Secure Agent for which you want to set the property.
  3. In the
    System Configuration Details
     section, select the
    Service
     as
    Data Integration Server
     and the type as
    Tomcat
    .
  4. 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
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
0 COMMENTS
We’d like to hear from you!
Dave Hofer - August 01, 2023

The section entitled, "Improve the staging performance for a read operation" provides no such guidance.  It simply states what you can't do.  

Informatica Documentation Team - August 03, 2023

Hi Dave Hofer, 

We’re working to address your comments and will get back to you. 

Thanks, 
Informatica Documentation team

Informatica Documentation Team - August 03, 2023

Hi Dave Hofer, 

You can find the steps to set the staging optimization property INFA_DTM_RDR_STAGING_ENABLED_CONNECTORS for the read operation under the “Setting the staging property” section in the following topic: Optimize the staging performance of a mapping

Thanks,

Informatica Documentation Team