Advanced ETL Processor's the “Is In List” validation function checks whether a value exists in a database or file. Although effective in many situations, it can become inefficient when working with large datasets.
When “Is In List” Becomes Inefficient
- The lookup list is too large to fit into memory
- Loading the entire lookup table takes too long
- You need to validate only a few records, but the lookup contains millions
In these scenarios, a better option is to use the “Is Value In Database” and “If Value In Database” transformation functions.
How These Functions Work
At runtime, the placeholder <value> is replaced with the actual value being processed. If the database query returns one or more rows, the result is success; otherwise it returns failure.
-- Conceptual example
SELECT *
FROM your_table
WHERE your_column = <value>This method ensures faster processing and minimal memory usage, particularly when dealing with large datasets.
