Database lookup
Description
Database Lookup searches a database table for matching records based on input key fields and adds the retrieved values as new fields in the data stream. Use this step when you need to enrich rows with reference data from a database — for example, looking up a customer name by ID, retrieving a product category by SKU, or fetching a configuration value by key. It supports multiple lookup keys, configurable failure behavior when no match is found, and caching to improve performance for repeated lookups.
Configurations
| Field Name | Description |
|---|---|
| Step name | Specify the name of the step as it appears in the workflow workspace. This name has to be unique in a single workflow. |
| Connection | Specify the Database connection for the lookup table. |
| Lookup schema | Specify the Database schema containing the lookup table. |
| Lookup Table | Specify the name of the database table used for the lookup. |
| Enable cache? | Enables caching of database lookups. This means that once a key (or group of key) has been looked up, the looked up values are stored, and returned again the next time this key (or group of key) is being looked up (without incurring the cost of a database call). |
| Cache size in rows | Specify the size of the cache (number of rows), 0 means cache everything. |
| Load all data from table | Pre-loads the cache with all the data present in the lookup table. This may improve performance by avoiding database calls. However, if you have a large table, you risk running out of memory. |
| Keys to look up table | Specify the keys and conditions to perform the database lookup. |
| Values to return table | Specify the fields from the lookup table to add to the output stream. - "New name" allows you to use a different name if the database column name is inappropriate. - "Default" is the value returned (instead of null) if the lookup fails. - "Type" is the type of the output field. Note: Enabling error handling on the Database Lookup step will redirect all failed lookup rows to the error handling step, instead of adding them to the main output stream with null/default values. |
| Do not pass the row if the lookup fails | Enable checkbox to avoid passing a row when lookup fails. Within the SQL syntax, enabling this would be an INNER JOIN, otherwise it would be an OUTER JOIN. |
| Fail on multiple results? | Enable checkbox to force the step to fail if the lookup returns multiple results. |
| Order by | If the lookup query returns multiple results, the ORDER BY clause helps you to select the record to take. For example, ORDER BY would allow you to pick the customer with the highest sales volume in a specified state. |
| Get Fields | Click to return a list of available fields from the input stream(s) of the step. |
| Get lookup fields | Click to return a list of available fields from the lookup table that can be added to the step's output stream. |