Snowflake Connector Guide

Snowflake Connector Guide

Rules and Guidelines for Pushdown Optimization

Rules and Guidelines for Pushdown Optimization

Use the following rules and guidelines when you configure pushdown optimization to a Snowflake database:
  • To push the TRUNC(DATE) function to the Snowflake database, you must define the date and format arguments.
  • The Snowflake aggregate functions accept only one argument, which is a field set for the aggregate function. The agent ignores any filter condition defined in the argument. In addition, ensure that all fields mapped to the target are listed in the GROUP BY clause.
  • To push the TO_CHAR() function to the Snowflake database, you must define the date and format arguments.
  • When you push the SYSTIMESTAMP() and SYSDATE() functions to the Snowflake database, do not specify any format. The Snowflake database returns the complete time stamp.
  • You cannot push the TO_BIGINT() or TO_INTEGER() function with more than one argument to the Snowflake database.
  • When you push the REPLACECHR() or REPLACESTR() function to the Snowflake database, the agent ignores the caseFlag argument.
    For example, both REPLACECHR(false, in_F_CHAR, 'a', 'b') and REPLACECHR(true, in_F_CHAR, 'a', 'b') return the same value.
  • You cannot use millisecond and microsecond values when you push functions to the Snowflake database.
  • You cannot push time data types to the Snowflake database.
  • You can use nanosecond values in the ADD_TO_DATE() and TRUNC(DATE) functions only.
  • To push the TRUNC(DATE), GET_DATE_PART(), and DATE_DIFF() functions to the Snowflake database, you must use the following time formats as arguments:
    • D
    • DDD
    • HH
    • MI
    • MM
    • SS
    • YYYY
    For example, TRUNC(<datefieldname>, 'dd').
  • When you enable pushdown optimization for a mapping to read data from two Snowflake tables and you define a filter condition for a column for one of the Snowflake tables and a similar column is available in the other table, ensure that the column name specified in the filter is fully qualified. For example, specify the column name as tablename.colname.

Updated September 05, 2019


Explore Informatica Network