Your data

Database lookup

Database lookup checks each incoming item against a table in your own database: PostgreSQL, BigQuery or Snowflake. One read-only query reads the table once per run (up to 5,000 rows), then every item is matched to it by name, phone, website or any field. Add the table’s columns to the items that match, keep only the ones you already have, or only the ones you do not have yet, without exporting a single file.

In the editor: Looks each incoming item up in a table of your own database (PostgreSQL, BigQuery or Snowflake): your customers, your sites. One query reads the table (up to 5,000 rows); the items are then matched to it. Add the table’s columns to the items that match, keep only the ones you already have, or only the ones you do not have yet. The lookup runs on the server.

Settings

Database

An account you connect

A PostgreSQL, BigQuery or Snowflake connection from the Connections page. Its sign-in is sealed and used only on the server.

Table to look in

Text, several lines

A SELECT with the columns to match on and to add. Up to 5,000 rows.

Needed before the flow can run.

Field of the incoming items

Text

Click one under Fields coming in.

Needed before the flow can run.

Column of the table

Text

The column that holds the same thing, as the query names it.

Needed before the flow can run.

They match when

Pick one

Capitals, accents and extra spaces are ignored. Phone numbers match however they are written.

  • They are the same The same text once capitals, accents and extra spaces are ignored.
  • One contains the other One value’s words appear in the other, ignoring punctuation and company endings (LLC, Co, Ltd...).
  • Same phone number The last 9 digits are the same, however the number is written.
  • Same website The same domain once https://, www. and any path are removed.
  • The item mentions it

Then

Pick one

What happens after the lookup. Only the first choice adds fields; the other two let items through or not.

  • Keep all, add the table’s columns Every item goes on with db_found; matched items also get the columns your query returned.
  • Keep only items in the table Only the items found go on, unchanged.
  • Keep only items not in the table Only the items not found go on, unchanged: the usual way to skip existing customers or dealers.

Name the added fields

Text

Each matched item gets db_found (Yes or No) and the table’s columns as db_<column>.

Needed before the flow can run.

When several rows match

Pick one

Only the first matching row’s columns are added.

  • Use the first match
  • One item for each matching row

What comes out

Each item that leaves the block carries these fields, to use in the blocks after it as {{field}}.

Then: Keep all, add the table’s columns

With Name the added fields left as db.

  • db_found: Yes when the item was found in the table, else No.
  • db_<column>: Each column your query returned for the matched row (up to 40), named after the column in lower case.
  • db_region: For example, a column called region.

How to use it

Telecom

Phone shops that are not dealers yet

A telecom operator keeps its dealers in PostgreSQL and wants every new phone shop that is not one of them.

One query reads the dealer table; matching by phone catches dealers whose shop name differs on the map.

Healthcare

Each clinic’s contract tier from the warehouse

An insurer wants every clinic a scan finds to carry its contract tier before the reps call.

Before: Reps looked each clinic up in an internal tool before calling.

With the flow: The email lists every clinic with db_found and db_tier, straight from the warehouse.

FMCG distribution

Route and credit limit on every shop

Branch on db_found sends existing customers and new shops to different teams in one flow.

Banking and payments

Merchants we already serve, with their manager

A bank wants its relationship managers to hear when a merchant they look after opens a new branch.

Keep only items in the table lets through just the new branches of existing merchants, each with the manager column from the bank’s table.

Real estate

Sites already in our pipeline

The land team sees at once whether a broker’s site is already in the pipeline, and at which stage.

Retail

Our own stores, enriched with today’s map data

A retailer checks every week that its store listings on the map match its master data.

Before: Store listings drifted: old phone numbers and closed hours on the map.

With the flow: Each store on the map is matched to master data by website; mismatched phones show up in the weekly email.

Good to know

  • Write one SELECT (or WITH ... SELECT) with the columns to match on and to add; a second statement or anything that changes data is refused: the editor flags it and the server never runs it. PostgreSQL runs it in a read-only transaction.
  • Up to 5,000 rows and 20 MB are read, within 20 seconds; past that the table is read in part and the step says so. BigQuery is capped at 1 GB scanned by default.
  • {{vars.name}} in the query is passed as a parameter, never pasted into the SQL.
  • The table is read on the server when the flow runs. The editor’s preview lets every item through until then.
  • If the query returns no column with the name you typed, or no item has the field, the step fails and lists the names it does have.
  • The database must be reachable on the public internet (with SSL where it supports it); private addresses are refused.
  • For a CSV or saved list in xMap, use Match with my table; to bring rows in as items, use Database query.
  • Free: lookups in your own database cost no credits.

Works well with

  • Database query: Reads rows from your own database with a SELECT query: PostgreSQL, BigQuery or Snowflake.
  • Match with my table: Looks each incoming item up in one of your tables or saved lists (My data), for example your customers.
  • Map business scan: Finds businesses on online maps live: name, category, rating, address, phone, website and whether it is open.
  • Only new: Lets through only the items this flow has not found before, so a daily flow sends only what is new.
  • Branch: Splits items in two.
  • CRM lookup: Looks each incoming item up in your CRM through its own search: is this company already a customer, is this email a contact?
  • HubSpot: Powerful CRM that offers tools for sales, customer service, and marketing automation.
  • Email: Sends one email per run with all the items, or one email per item.

Help articles

Try it

Build a flow with Database lookup on the home page, no account needed, or book a call. Try it live