When you configure a Lookup transformation in a mapping, you can cache the lookup data during the runtime session.
When you select Lookup Caching Enabled, Data Integration queries the lookup source once and caches the values for use during the session, which can improve performance. You can specify the directory to store the cached lookup. You can configure dynamic and persistent lookup caches.
For information about lookup caching, see the chapter "Lookup transformations" in Transformations in the Data Integration documentation.
Dynamic lookup cache has the following restrictions:
•When you configure dynamic lookup cache, set the On Multiple Matches property to Report Error. 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. Ensure that all the lookup fields are mapped.
•A lookup condition in dynamic lookups can use only an equal operator.
•SQL ELT optimization is not applicable.
•You cannot parameterize a Lookup transformation enabled to use dynamic lookup cache.
Using ORDER BY and WHERE clause
You can use the ORDER BY clause in a query in a connected and unconnected cached lookup if you configure the -DENABLE_SORTED_INPUT_FOR_LKP=true property in the JVM options of the Secure Agent and enable the Sorted Input option in the advanced lookup properties.
Consider the following guidelines when you use the ORDER BY clause:
•When you configure a lookup condition to return all rows or any row, include the lookup fields in the ORDER BY statement.
•When you configure a lookup condition to return first or last row, additionally include the fields mapped in the target in the ORDER BY statement.
When you use an uncached connected lookup to read from and write to Snowflake and the data has null values, configure the -DENABLE_NULL_FLAG_FOR_UNCACHED_LOOKUP=true property in the JVM options of the Secure Agent to include the WHERE clause in the SQL statement.
Log uncached lookup queries
When you enable caching, Data Integration queries the lookup source once and caches the values for use during the session. When you disable caching, each time a row passes into the transformation, a SELECT statement gets the lookup values.
Data Integration logs the uncached lookup queries for a connected and unconnected lookup in the session log. Enable the Lookup Caching Enabled property in the mapping, and then enable the verbose mode in the Snowflake Data Cloud mapping task.
The following image shows the selected verbose mode in the mapping task:
When you run the mapping, Data Integration logs the uncached lookup queries in the session logs.
