Features

• •Snowflake source
• •Amazon S3 source
• •Google Cloud Storage source¹
• •Microsoft Azure Data Lake Storage Gen2 source

Snowflake Data Cloud sources, targets, and lookups

Source properties

• •Source connection - Parameter, Allow parameter to be overridden at run time
• •Source type - Single object, multiple objects, query, and parameter. You can also use a parameter file to override the Snowflake source connections and objects in a mapping from the mapping task.
Note: When you use the query source type to read from Snowflake, you can choose to retain the field metadata and save the mapping. Even if you edit the query and run the mapping, the field metadata specified at design time is retained.
• •Allow parameter to be overridden at run time.
• •Query options - Filter and Join. You can use both simple and advanced filter conditions. You can use join to join related objects based on existing relationships or you can create an advanced relationship.
• •Database override
• •Schema override
• •Warehouse override
• •Pre-SQL and Post-SQL
• •Role override
• •Table name override
• •SQL override
• •Tracing level

• •Warehouse override
• •Pre-SQL and Post-SQL
• •Role override

• •Query options - Sort
• •Partition

Target properties

• •Target connection - Parameter, Allow parameter to be overridden at run time.
• •Target type - Single object, parameter. You can also use a parameter file to override the Snowflake target connections and objects in a mapping from the mapping task.
• •Allow parameter to be overridden at run time
• •Target object - Existing target, Create new at runtime.
• •Operation - Insert, update, upsert, delete, or data driven
• •Database override
• •Schema override
• •Warehouse override
• •Role override
• •Pre-SQL and Post-SQL
• •Table name override
• •Truncate Target Table
• •Additional Write Runtime Parameters

• •Update Mode
• •Batch row size
• •Number of local staging files
• •Rejected File Path
• •Update Override
• •Forward Rejected Rows

Lookup properties

• •Lookup connection - Parameter, Allow parameter to be overridden at run time
• •Source type - Single object, query, parameter. You can also use a parameter file to override the Snowflake lookup connections and objects in a mapping from the mapping task.
• •Multiple matches - Report Error
• •Database override
• •Schema override
• •Warehouse override
• •Role override
• •Table name override
• •SQL override
• •Tracing level
• •Lookup Data Filter

• •Pre SQL
• •Post SQL

Guidelines for mappings

• •For a target created at runtime, ensure that the Snowflake source does not contain records with the Time data type.
• •When you configure filters, consider the following guidelines:
• - If a mapping contains a Filter transformation and also a filter in the Source transformation, the mapping consolidates the filter conditions from both these transformations to filter the records. However, it is recommended that you use only one of these filters at a time in a mapping.
• - You cannot use system variables in filters.
• - You cannot apply a filter for query and multiple source objects.
• - When you configure an IS_date function in an Expression transformation, specify the format for this function. Else, the mapping populates incorrect data.
• - When you configure two Sequence Generator transformations to write to two Snowflake targets, and the sequence objects have the same sequence name in the custom properties, data populates incorrectly.
• •For mappings that read from and write to Snowflake, consider the following guidelines:
• - You cannot use a query to read from stored procedures.
• - Even if you decrease the precision of the Snowflake String data type in a Source transformation to write to a Snowflake table, the mapping passes without truncating the data.
• - When you configure a mapping for source or partial SQL ELT optimization, do not connect the Source transformation to more than one transformation in the mapping. However, in a mapping enabled with full SQL ELT optimization, the Source transformation can branch out to multiple transformations in the mapping pipeline.
• - You can configure a custom query in a Source transformation to read from Java or SQL user-defined functions (UDF) in Snowflake.
• - When the mapping runs with full or source SQL ELT optimization, some of the queries in the session log are not aliased correctly. The alias for simple queries reflects properly.
• - A mapping or mapping in advanced mode fails to read data from multiple tables joined using related objects, where the tables and column names have case-sensitive, special, and unicode characters.
• - A mapping that reads from multiple Snowflake objects that do not belong to the same database and schema fails.
• - When you use the is_number function, the data populated for some values such as inf, inf and NaN in Snowflake differs with and without SQL ELT optimization applied.
• - When you use the IS_NUMBER function in a transformation and the input data contains d or D, for example, in formats such as +3.45d+32 or +3.45D-32, the function returns False or 0.
• - When you use the IS_DATE function in a transformation, do not use the J, MM/DD/YYYY SSSSS, MM/DD/Y, and MM/DD/RR formats.
• - Mappings that read from or write to Snowflake with multibyte characters in the table or column names might fail. Before you configure a mapping to read from or write data with multibyte characters, set the -DdisablePDOAdvancedAliasing property in the JVM options in the Secure Agent properties.
• - When you pass columns with Null values in a Normalizer transformation, Null values are not written to the target.
• - When you push the DATE_DIFF() function with the date1 and date2 arguments from a mapping task enabled with the Create Temporary View property, the function returns the following different values as compared to a mapping that runs without SQL ELT optimization:
• ▪ The function returns a negative number when the value of date1 is later than the value of date2.
• ▪ The function returns a positive number when the value of date1 is earlier than the value of date2.
To get the correct return values, set the JVM option to -DFixSnowflakeDateDiffForPDO=true for the Secure Agent in Administrator.
• - A mapping or mapping in advanced mode fails to write data to the target when the precision of incoming fields exceeds the precision of target fields.
• - A mapping enabled for SQL ELT optimization fails when you parameterize the advanced source filter and enclose the parameter value within quotes in the parameter file.
A mapping configured without SQL ELT optimization with similar filter configurations does not filter data but runs successfully.

Amazon S3 V2 source

• •Access Key
• •Secret Key

• •Source connection parameter
• •Source Type - Single, parameter
• •Format - Delimited, Avro, ORC, Parquet, and JSON
• •Source Type - File and directory
• •Folder Path
• •File Name
• •Compression Format. - Gzip

Authentication

Create a new target at runtime

• •To write data from file data types such as Avro, ORC, or Parquet from Amazon S3 to Snowflake, you must delete the Filename field.
• •Mappings fails with a casting error when the table name contains Unicode characters.

Data types

• •You cannot write Avro files that contain special characters.
• •You cannot write data that contains the Binary data type.
• •You cannot read data in JSON format that contains special characters. For more information about using identifiers, see Identifiers Syntax in the Snowflake documentation.
• •If you specify any escape character for the S3 file format, the escape character defaults to backslash.
• •ORC files with year 1523 is loaded incorrectly as 1524.
• •When you write data with the Time data types from a Parquet file from Amazon S3 to Snowflake, the value of the time differs in the target.
• •The precision of JSON data must not exceed the precision of the Snowflake target table.
• •If the Amazon S3 source type is a directory and you enable wildcard characters for the directory, the mapping fails. A warning message appears stating that wildcard characters read are not supported with SQL ELT optimization.

Google Cloud Storage V2 source

• •Source connection, connection parameter
• •Source Type - Single, parameter
• •Format - Delimited, Avro, Parquet, and JSON
• •Google Cloud Storage Path
• •Source File Name
• •Is Directory

Microsoft Azure Data Lake Storage Gen2 source

• •Account Name
• •File System Name

• •Source connection, connection parameter
• •Source Type - Single, parameter
• •Format - Delimited, Avro, Parquet, JSON, and ORC
• •Formatting Options
• •Filesystem Name Override
• •Source Type - File, Directory
• •Directory Override - Absolute path; Relative path
• •File Name Override - Source object
• •Compression Format - GZip to read flat files
• •Tracing Level