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

Lookup transformation

Lookup transformation

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.

0 COMMENTS

We’d like to hear from you!