You can configure full SQL ELT optimization to push a Lookup transformation to process in Snowflake. You can push both a connected and an unconnected lookup.
When the mapping contains an unconnected lookup, you can also nest the unconnected lookup function with other expression functions. For example,
:LKP.U_LOOKUP(Upper(argument1), argument)
Lookup objects
Consider the following rules when you configure lookups:
You can configure a lookup for Snowflake when the Source transformation uses the following sources:
Amazon S3
Google Cloud Storage
*
Microsoft Azure Data Lake Storage Gen2
Snowflake source
*Doesn't apply to mappings in advanced mode.
You can configure a lookup for an Amazon S3, Google Cloud Storage, Microsoft Azure Data Lake Storage Gen2, or Snowflake object only when the Source transformation uses the corresponding Amazon S3, Google Cloud Storage, Microsoft Azure Data Lake Storage Gen2, or Snowflake source.
Connected lookup
In a mapping with a Snowflake Data Cloud source and target, set the
Multiple Matches
option for the lookup object to
Return all rows
.
Unconnected lookup
When you configure an unconnected Lookup transformation, consider the following rules:
Do not configure an expression for an output received from an unconnected lookup.
In a mapping with a Snowflake Data Cloud source and target where some of input fields from the unconnected lookup transformation is not mapped to the Snowflake target object, the select query includes all the unmapped fields.
Multiple matches behavior in a connected and unconnected lookup
If you enable SQL ELT optimization for a mapping that contains a connected or unconnected lookup, you must follow these guidelines:
In an unconnected lookup, ensure that you always select the
Multiple Matches
option to
Report Error
. When you look up data and the lookup condition finds multiple matches, all the matching rows are selected and the task runs with SQL ELT optimization. If you enabled
Multiple Matches
to any option other than
Report Error
, the mapping runs without SQL ELT optimization.
In a connected lookup, you can set the
Multiple Matches
option to
Return all rows
or
Report Error
. When you set the
Multiple Matches
option to
Report Error
, you can set the
Lkp_apdo_allow_report_error
custom flag in the task advanced session properties to determine how Data Integration handles multiple matches:
When you set the property to
Yes
and if there are multiple matches in the data, the multiple match policy is ignored and the job runs successfully with SQL ELT optimization.
When you do not set the property, and if there are multiple matches in the data, Data Integration considers the policy and displays a warning message. SQL ELT optimization is ignored and the task fails.
FileName port
When you configure a lookup for an Amazon S3 source in a mapping that contains an Amazon S3 source and Snowflake target, remove the filename port from both the Amazon S3 source and lookup object. The FileName port is not applicable.
Lookup query object
When you use a lookup object as a query in a Lookup transformation in a mapping to lookup data in Snowflake, specify the database and schema in the advanced lookup properties or in the additional JDBC URL parameters in the Snowflake Data Cloud connection.