Skip to main content

Background

A common task is matching a list of organizations, such as a file of a few hundred company or fund names, to the accounts in a CRM or database. The names rarely match exactly, and most differences aren’t spelling mistakes:
  • acronyms: ADIA vs Abu Dhabi Investment Authority (ADIA)
  • abbreviations and state codes: NJ Division of Investment vs New Jersey Division of Investment
  • extra words: Temasek vs Temasek Holdings Pte Ltd
  • a second organization in parentheses: Northwind College (Contoso Advisors)
Comparing every row with every row (a Cross Join) gets slow as the sources grow, and picking the best match by hand is error-prone. This recipe does it in three stages:
  1. Find candidates. Generate matching keys for each name with NAME_MATCH_KEYS and join the two sources on them. Only names that share a word or acronym are compared.
  2. Score. Score each candidate pair with FUZZY_MATCH and the company method.
  3. Decide. Keep the best account for each row, and label it MATCH, REVIEW or NO MATCH.
In the steps below, the first source has id and name fields and the second has account_id and account_name. Rename to suit your data.

Steps

1. Generate keys on both sides

Add a Select after each source. Keep the fields you need and add a key field: Each row now appears once per key. NAME_MATCH_KEYS returns null for a blank or placeholder name such as N/A; the COALESCE gives such rows on the first source a key that matches nothing, so they still reach the output as NO MATCH. Accounts with blank names on the second source are dropped, which is what you want.

2. Join on the keys

Add a Join with the first source on the left, type Left join, matching match_key to account_key. Rows with no candidate keep a null account_id, so they still reach the output as NO MATCH.

3. Remove duplicate pairs

Two names can share more than one key. Add a Select that keeps only id, name, account_id and account_name, then a Distinct.

4. Score each pair

Add a Select that keeps those four fields and adds:

5. Rank the candidates

Add a Window partitioned by id and ordered by match_score descending, with: Then a second Window partitioned by account_id and ordered by match_score descending, with row_number as account_rank and lead of match_score (offset 1, default 0) as account_runner_up. This tells you whether another row is an even better fit for the same account.

6. Keep the best candidate and label it

Add a Filter that keeps row_rank == 1, then a Select that adds:
as match_status. Send the result to a destination, or use a Filter on match_status to write matches and rows for review to separate outputs.

Tuning

  • Thresholds. 80 (match), 60 (review) and the 8-point lead over the runner-up are starting points. Check a sample of REVIEW rows and adjust.
  • Ties. If two accounts score the same for one row, the lead over the runner-up is 0, so the row goes to review rather than being matched to either.
  • Known aliases. Some names can’t be worked out from their words, such as nicknames or acronyms used for different organizations. Keep a small file of alias,full_name pairs and, before step 1, use a Join or Lookup to replace a known alias with its full name.
  • Large second sources. The join only pairs names that share a distinctive word or acronym, which is usually far fewer comparisons than a Cross Join. Short acronym keys and words that are common in your data (for example health or energy in a list of hospitals) can still bring in many candidates; check the row count after the Distinct in step 3 on a real run.
Last modified on November 17, 2026