If you specify an SQL override query in a Lookup transformation and configure the Multiple Matches property to Return first row or Return last row, the mapping fails. If you configure the Multiple Matches property to Return any row, Return all rows, or Report error, the Secure Agent displays an error message.

When you use a Google BigQuery V2 connection in hybrid mode in a Lookup transformation, you cannot configure a lookup condition for fields of bytes, boolean, record, or repeated data types.

When you specify a custom query to import a lookup table, you cannot configure an uncached lookup.

When you use a Google BigQuery V2 connection in simple mode in a Lookup transformation, you cannot configure a lookup condition for fields of numeric, date, or datetime data types.

You must set the

On Multiple Matches

property to

Report Error

when you use a dynamic lookup cache. To reset the property, change the dynamic lookup to a static lookup, change the property, and then change the static lookup to a dynamic lookup.

When you configure a lookup transformation to read data from a column DataTime data type from a Google BigQuery source, ensure that the year value is lesser than 2200. Otherwise, the lookup returns null value.

When you configure a Lookup transformation and you specify a column of Float data type in the lookup condition, the Secure Agent fails to match and return the NaN values.

If you configure an uncached Lookup transformation to look up data from a Google BigQuery source and the source contains a column of DateTime or Timestamp data type, the mapping fails for certain values with the following error:

If you configure an uncached Lookup transformation to look up data from a Google BigQuery source, ensure that the lookup table name does not begin with a number.

If you configure an uncached Lookup transformation to look up data from a Google BigQuery source, ensure that the lookup condition does not contain a field of the byte data type. Otherwise, the mapping fails with the following error:

If you configure an uncached Lookup transformation to look up data from a Google BigQuery source, ensure that the lookup condition does not contain a field of the numeric data type. Otherwise, the mapping fails with the following error:

When you configure an uncached lookup transformation to read BigNumeric data from a Google BigQuery source and use a Google BigQuery V2 connection in hybrid mode, ensure that all the following conditions are not true:

You have configured staging mode to read data from the lookup object.
You have configured CSV or JSON as the data format of the staging file.
You have configured an advanced lookup filter on the column of BigNumeric data type.

When you configure a mapping with the following scenarios, you need to enable the

-DENABLE_NULL_FLAG_FOR_UNCACHED_LOOKUP=true

property in the JVM options of the Secure Agent to fetch the correct data in the first row of the table:

Configured an uncached Lookup transformation to read from
Google BigQuery
.
The source table contains null data in the first row.

To enhance the lookup performance in a cached Lookup transformation, configure the following settings:

Set the
-DENABLE_SORTED_INPUT_FOR_LKP=true
property as a JVM option under the DTM type in the Secure Agent properties.
Set the
Read Mode
property to Staging in the lookup advanced properties.
Enable the
Sorted Input
and
Use EXPORT DATA Statement to stage
properties in the lookup advanced properties.