Lookup node

Transform

What it does

The Lookup node reads matching records from a second connection and adds their fields to each row. The match uses a key built from the row, such as customerId. A row with no match is kept unchanged, dropped, or sent to error, as you choose.

Keys are read in batches: repeated keys are read once, so ten thousand orders across fifty customers read fifty keys. With a Generic REST API connection, each distinct key is one request to the API.

When to use

• Rows from one system need a few fields from another, such as an account tier from a database or an account name from Salesforce.
• Rows carry ids only, and an API returns the full record for an id. See REST API lookups.
• The reference table is large and only a few of its records are needed.
• To match every row of two full streams, use a Join. To read a small reference table once and look it up many times, use a Source and a Cache.

Configure

Pick the lookup connection in the node's inspector, then press Configure on the node. For a database or Salesforce connection, Configure opens Configure lookup action, where you choose the object. For a Generic REST API connection, Configure opens the inspector at the Request settings: see REST API lookups.

The inspector groups the settings as Connection, Object (or Request and Response for a REST API connection), Key, Policies and Reads.

1. 1
   Pick the lookup connection

   The connection to read from, which can differ from the one that produced the rows.
2. 2
   Choose the object and key column

   The table, view or object, and the column in it that holds the key. For a REST API connection, set the request and response instead.
3. 3
   Write the key expression

   Built once per row. A field such as row.customerId is the usual case; UPPER(accountCode) works when the reference system stores codes in upper case.
4. 4
   Decide what happens when nothing matches

   Emit row keeps the row unchanged, Drop row removes it, and Route to error sends it to the error port.

The settings in detail:

• Lookup connection: SQL Server, PostgreSQL, MySQL, Oracle, Snowflake, Salesforce or Generic REST API. It can differ from the connection that produced the rows.
• Object: the table, view or object to read. Procedures, functions and scripts are not available.
• Key expression: built per row. A field such as row.customerId, or an expression such as UPPER(accountCode).
• Key column: the column in the object that the key is matched against, chosen from the object's columns.
• Merge policy:
  • Spread (lookup wins) (default): the matched fields are added, and replace row fields of the same name.
  • Preserve row: the row's own fields are kept where names clash.
• Failure policy, for a row with no match or a failed read:
  • Emit row (default): the row continues unchanged.
  • Drop row: the row is removed.
  • Route to error: the row goes to the error port.
• Batch size: how many distinct keys are read per query. The default is 200.

REST API lookups

With a Generic REST API connection, the node sends one request per distinct key and adds fields from the response to the row. There is no object or key column: in the node's inspector, Request and Response take the place of Object and Key column, and Configure on the node opens the inspector with the Path field selected.

The connection supplies the base URL, the authentication, the default headers and query parameters, the timeout, the retries and the rate limit.

Request

• Request method: GET or POST. GET is the default; POST is for APIs that search with a request body.
• Path: added to the connection's base URL. {key} is replaced by the row's key, such as /{key} or /customers/{key}.
• Query parameters: name and value pairs. A value can contain {key}, such as email:{key}.
• Body: for POST only. JSON, with {key} where the key goes, such as {"email": "{key}"}.

Response

• Records path: where the record sits in the response, such as data[0] or results. Blank uses the whole response. When the path holds a list, the first item is used.
• Name and values list: a field holding a list of {"name": ..., "values": [...]} items. Each item becomes a column named after name. A single value becomes the column's value; several values are joined with , .
• Fields to add: comma separated, such as email, full_name. A dotted name such as address.city reads a nested field. Blank adds every field.
• Column prefix: added to the start of each new column name, such as lead_.

What counts as no match

• A 404 response, an empty response, an empty list, or a records path that is not in the response. The failure policy decides what happens to the row.
• Any other error status, a response that is not JSON, a timeout or a refused request is a failed read for that key only. Under Route to error, the row goes to error with the reason.

How requests are made

• The key is encoded before it goes into the path, a query value or the body, so it cannot change the address of the request.
• Every request goes to the base URL's host, under the base URL's path. A path that leads anywhere else is refused.
• Requests to private network addresses are refused.
• Redirects are not followed. A redirect response is a failed read for that key.
• Up to four requests run at the same time. The connection's rate limit and retry settings apply, and a Retry-After reply from the API is honoured.
• Responses larger than 1 MB are refused.
• A Lookup node sends at most 10,000 requests in one run. Each further key is a failed read.
• A Lookup node holds at most 64 MB of matched data at a time. Each further key is a failed read.
• An OAuth2 connection reuses its token for up to 10 minutes in a run.
• Error messages never include the connection's credentials. An error from the API is cut to 120 characters.

Example: a lead's answers from an id. A row carries leadgen_id. The lookup connection has the base URL https://graph.facebook.com/v26.0. Key expression row.leadgen_id, path /{key}, name and values list field_data, fields to add email, full_name, prefix lead_. A response of:

{ "id": "1234", "field_data": [ { "name": "email", "values": ["ada@example.com"] }, { "name": "full_name", "values": ["Ada Lovelace"] } ] }

adds lead_email and lead_full_name to the row. The full recipe is in Meta Lead Ads with a generic webhook.

Ports

Port	Direction	Kind	Notes
in	in	data	Rows to enrich.
out	out	data	Enriched rows, and unmatched rows under Emit row.
error	out	control	Unmatched rows under Route to error, with the reason.

How matching works

• For a database, key values are sent as query parameters, never written into the query text.
• When a key matches several records, the first is used.
• Text case follows the database: SQL Server, MySQL, Oracle and Snowflake ignore case by default, so abc matches ABC. PostgreSQL does not.
• Results are not kept between runs.

Examples

Add an account tier to orders.

Start

Orders

Add account tier

Map columns

Load enriched

Lookup connection: the customer database. Object accounts, key expression row.customerId, key column customer_id, merge Spread, failure Emit row. A row that arrives as:

orderId	customerId
10	C1

leaves as:

orderId	customerId	tier
10	C1	Gold

A row whose customerId matches nothing leaves unchanged.

Catch unknown customers. Set the failure policy to Route to error and connect error to a Notification node.

Troubleshooting

Symptom	Cause	What to do
The run fails with a missing lookup connection	No lookup connection is set and the failure policy is Route to error.	Pick a lookup connection.
No fields are added	The key values differ in case or spaces, or the key column is wrong.	Use TRIM(...) or UPPER(...) in the key expression, and check the key column.
Rows match on SQL Server but not on PostgreSQL	PostgreSQL compares text with case.	Normalise case in the key expression and in the stored data.
Fewer rows than expected	The failure policy is Drop row.	Use Emit row or Route to error.
A row field was replaced	Merge policy Spread lets lookup fields win.	Use Preserve row.
The node reads Not configured	A setting is missing: the lookup connection, the key expression, and the object and key column, or for a REST API connection the request path.	Open the node and fix each issue it lists.
The issue Lookup: put {key} in the path or a query parameter.	Every row would send the same request.	Add {key} where the API expects the id, such as /{key}.
A REST lookup adds no fields	The records path or field names do not match the response.	Call the API once with the same key and compare the response with the records path and fields.
A REST lookup fails with "outside the connection base URL"	The path names another host, or climbs above the base URL's path with ...	Make the path relative to the base URL, such as /{key}.
A REST lookup fails with HTTP 401 or 403	The connection's credentials are wrong or lack a permission.	Check the connection's authentication, and the permissions the API requires.
A REST lookup fails with "a redirect"	The API answered with a 3xx response.	Set the base URL or path to the address the API redirects to.
A REST lookup fails with "larger than 1024 KB"	The response is over 1 MB.	Ask the API for fewer fields, for example with a query parameter.
A REST lookup fails with "reached its limit of 10000 requests"	The run asked for more than 10,000 distinct keys.	Split the input across runs, or remove duplicate keys upstream.
A REST lookup fails with "more than 64 MB of response data"	The matched records are too large to hold.	List only the fields you need in Fields to add.

Related nodes

Join

Match two full streams on a key

Cache

Look up a small reference set many times in one run

Compute

Derive fields from what is already on the row