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

Rules and guidelines in mappings in SQL ELT mode

Rules and guidelines in mappings in SQL ELT mode

Consider the following rules and guidelines when you run mappings in SQL ELT mode:
General guidelines
  • A mapping that uses input parameters for a custom query to read from a Snowflake table fails.
  • To process a data type used in the Expression transformation, ensure that the precision of the data type matches the precision of the data type returned by Snowflake. Otherwise, the mapping fails.
Data types
  • When you read from and write to Snowflake, ensure that the source data doesn't contain the Time and TIMESTAMPTZ data types.
  • A mapping that reads the Array data type from Microsoft Azure Data Lake Storage Gen2 and writes to Snowflake fails, if the Array data type contains the boolean data.
  • When you use input parameters for the source filter, ensure that you use only the String data type in the filter expression.
  • You can't read the Array data type from the Amazon S3 V2 source in a mapping in SQL ELT mode.
  • A mapping that reads the Array or Object data type from a Snowflake source and write to a Snowflake target created at run time fails.
  • When you run a mapping to write data of the DATE or TIMESTAMPLTZ data type to a Snowflake target created at run time, the mapping converts the date/time data type to the TIMESTAMPNTZ data type and writes the TIMESTAMPNTZ data type with a default precision of 9.
Functions
  • When you pass date or time values in the Date or Time function, the mapping fails. To run mapping successfully, select the required fields from the date and time function list to define the field expression and run the mapping.
  • When you pass the date_or_time_part argument in the date or time function, ensure that you enclose the date_or_time_part argument with the single quote character.
    For example, when you use the DATEADD function, use the following format:
    DATEADD('Year', <Value>, <Field name>)
  • When you configure an expression for the Context function, even though the expression validates the Integer, Bigint, Decimal, and Double data types, the mapping fails at run time.
  • When you use the TO_NUMERIC or TO_NUMBER function to process the boolean data type, you need to configure the field that contains the boolean data type as an argument to the function.
    For example, when you use the TO_NUMBER function, use the following syntax in the Expression transformation:
    TO_NUMBER(TO_BOOLEAN(bool_field))
  • Ensure that you pass the uuid and name arguments for the UUID_STRING function.
    For example, when you use the UUID_STRING function, use the following format:
    UUID_STRING(uuid,name)
    If you pass the UUID_STRING function without any argument, Snowflake returns a random UUID.
  • When you configure the SEQ1, SEQ2, SEQ4, or SEQ8 function in an expression, you can pass values either 0 or 1 in the optional arguments. If you pass any other value, the expression validates the argument, but the mapping fails at run time.
  • If you configure the UNIFORM function with an expression that contains the cast operator (::), the expression validation fails.
Target operations
  • When you define the warehouse name and role assigned to the user in the Target transformation, the mapping doesn't honor the value specified in the
    Warehouse
    and
    Role
    target properties. You need to specify the warehouse name and role in the Snowflake Data Cloud connection.
  • To perform an upsert operation on the target, you need to select the target operation as
    Update
    and update mode as
    Update Else Insert
    in the Snowflake Target transformation.

0 COMMENTS

We’d like to hear from you!