Match records across systems

Two systems often describe the same customer, product or order without sharing an ID. The CRM knows a customer as C-1042, the ERP knows the same company as account 20417, and the names are spelled slightly differently. Before you can report across both systems or move data from one to the other, you need a table that says which record in one is which record in the other.

This recipe builds that table for customers. It normalizes the fields both systems share, matches exactly where it can, falls back to a fuzzy name match where it cannot, and records how each match was made so a person can review the uncertain ones. The same shape works for products (SKU, then description) and orders (order number, then amount and date).

The pipeline

The pipeline has 2 sources: crm_customers (customer_id, name, email, postal_code) and erp_customers (customer_no as an integer, company_name, email, zip). Each stage below is one stage in the builder and reads earlier stages by name.

SELECT
  customer_id AS crm_id,
  name AS crm_name,
  regexp_replace(lower(trim(name)), '[^a-z0-9]', '', 'g') AS name_key,
  lower(trim(email)) AS email_key,
  regexp_replace(upper(trim(postal_code)), '[^A-Z0-9]', '', 'g') AS postal_key
FROM crm_customers

SELECT
  CAST(customer_no AS VARCHAR) AS erp_id,
  company_name AS erp_name,
  regexp_replace(lower(trim(company_name)), '[^a-z0-9]', '', 'g') AS name_key,
  lower(trim(email)) AS email_key,
  regexp_replace(upper(trim(CAST(zip AS VARCHAR))), '[^A-Z0-9]', '', 'g') AS postal_key
FROM erp_customers

SELECT c.crm_id, c.crm_name, e.erp_id, e.erp_name,
       'exact_email' AS match_method, 1 AS method_rank, 0 AS distance
FROM crm c
JOIN erp e ON c.email_key = e.email_key
WHERE c.email_key <> ''
UNION ALL
SELECT c.crm_id, c.crm_name, e.erp_id, e.erp_name,
       'exact_name', 2, 0
FROM crm c
JOIN erp e ON c.name_key = e.name_key AND c.postal_key = e.postal_key
WHERE c.name_key <> ''

SELECT c.crm_id, c.crm_name, e.erp_id, e.erp_name,
       'fuzzy_name' AS match_method, 3 AS method_rank,
       CAST(levenshtein(c.name_key, e.name_key) AS BIGINT) AS distance
FROM crm c
LEFT JOIN exact_matches x ON x.crm_id = c.crm_id
JOIN erp e ON e.postal_key = c.postal_key
WHERE x.crm_id IS NULL
  AND c.name_key <> '' AND e.name_key <> ''
  AND CAST(levenshtein(c.name_key, e.name_key) AS DOUBLE)
      / greatest(character_length(c.name_key), character_length(e.name_key)) <= 0.2

SELECT crm_id, crm_name, erp_id, erp_name, match_method, method_rank, distance
FROM exact_matches
UNION ALL
SELECT crm_id, crm_name, erp_id, erp_name, match_method, method_rank, distance
FROM fuzzy_matches

SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY crm_id
    ORDER BY method_rank, distance, erp_id
  ) AS rn,
  COUNT(*) OVER (PARTITION BY crm_id, method_rank) AS candidate_count
FROM candidates

SELECT
  crm_id, crm_name, erp_id, erp_name, match_method, distance, candidate_count,
  COUNT(*) OVER (PARTITION BY erp_id) AS erp_claimed_by,
  match_method = 'fuzzy_name'
    OR candidate_count > 1
    OR COUNT(*) OVER (PARTITION BY erp_id) > 1 AS needs_review
FROM ranked
WHERE rn = 1

A match needs review when it came from the fuzzy step, when the winning method found more than 1 candidate (2 ERP accounts that share an info@ address, for example), or when 2 CRM records picked the same ERP account. erp_claimed_by is counted after the rn = 1 filter, so it only sees the chosen matches.

Feed review decisions back in

Once someone has reviewed the flagged rows, keep their decisions in a small dataset, such as a File Set with the columns crm_id, erp_id. Add it as a third source, match_overrides, and add one more branch to candidates with rank 0 so a confirmed pair always wins:

UNION ALL
SELECT c.crm_id, c.crm_name, e.erp_id, e.erp_name, 'manual', 0, 0
FROM match_overrides o
JOIN crm c ON c.crm_id = o.crm_id
JOIN erp e ON e.erp_id = CAST(o.erp_id AS VARCHAR)

A CSV import reads a column of digits as a number, which is why o.erp_id is cast to match the text erp_id built in the erp stage. Apply the same cast to crm_id if your CRM IDs are numeric.

Write mode

Write the match table with Overwrite, full table. Both systems change between runs, and a CRM record can gain an exact match it did not have before, so rebuilding the whole table each run is simpler than updating it. See Write modes and partitioning.

Check the result

After a run, query the destination in Query. This shows how the matches were made:

SELECT match_method, needs_review, COUNT(*) AS matches
FROM customer_matches
GROUP BY match_method, needs_review
ORDER BY match_method, needs_review

This lists the rows to review, the least certain first:

SELECT crm_id, crm_name, erp_id, erp_name, match_method, distance, candidate_count, erp_claimed_by
FROM customer_matches
WHERE needs_review
ORDER BY distance DESC, crm_id
LIMIT 100

And this finds CRM records that matched nothing:

SELECT c.customer_id, c.name
FROM crm_customers c
LEFT JOIN customer_matches m ON m.crm_id = c.customer_id
WHERE m.crm_id IS NULL
LIMIT 100

Pitfalls

levenshtein is the only string-similarity function available. DataFusion has no similarity, soundex or trigram functions, so build the fuzzy step on edit distance and good normalization.

Comparing every record on one side with every record on the other grows with the product of both table sizes and can run out of time on large tables. Always join the fuzzy step on a blocking key that both sides share, such as postal code, country, or the first letter of the name with left(c.name_key, 1).

Short names match too easily. With a fixed threshold of 2 edits, abc matches xbz. A threshold relative to the length, as above, avoids most of that. Tune it on your own data by previewing fuzzy_matches and reading the borderline pairs.

The pattern [^a-z0-9] also strips letters such as ä, é and ß, which can merge names that differ only in those letters. If your names use them, map them first, for example with replace(translate(lower(name), 'äöüé', 'aoue'), 'ß', 'ss'), and apply the same mapping on both sides.

Integer IDs lose leading zeros. If one system stores 000123 as text and the other stores 123 as a number, strip the zeros on the text side with ltrim(id, '0') before you compare.

Empty strings are equal to each other. Without the <> '' conditions, every customer with no email matches every other customer with no email.

Related