> Documentation index: https://docs.beetl.io/llms.txt

# Match records across systems

> Linking the same customer or order between two sources.

Source: https://docs.beetl.io/pipelines/match-across-systems/
Last updated: 2026-10-03

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](https://docs.beetl.io/pipelines/builder) and reads earlier stages by name.

    Stage `crm` builds comparable keys for the CRM side. Lowercase, trim, and strip everything except
    letters and digits from the name, so `ACME Tools GmbH.` and `Acme-Tools GmbH` both become
    `acmetoolsgmbh`.

    ```sql
    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
    ```

    Stage `erp` does the same for the ERP side. `trim` accepts only strings, so cast the integer
    columns first.

    ```sql
    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
    ```

    Stage `exact_matches` joins on the normalized keys. Each branch carries a method name and a rank,
    where a lower rank means stronger evidence.

    ```sql
    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 <> ''
    ```

    Stage `fuzzy_matches` compares names with `levenshtein`, which counts the single-character edits
    between 2 strings. It runs only for CRM records without an exact match and only against ERP records
    in the same postal code. The threshold allows 1 edit per 5 characters of the longer name.

    ```sql
    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
    ```

    Stage `candidates` puts both kinds of match in one list.

    ```sql
    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
    ```

    Stage `ranked` orders the candidates for each CRM record: strongest method first, then smallest
    distance, then `erp_id` as a tiebreak so the choice is the same on every run. `candidate_count`
    counts the candidates found by the same method, so a pair matched on both email and name does not
    count twice.

    ```sql
    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
    ```

    Stage `customer_matches` keeps the best candidate per CRM record and flags the ones a person should
    look at. It is the last stage, so it feeds the destination.

    ```sql
    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](https://docs.beetl.io/sources/file-sets) 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:

```sql
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](https://docs.beetl.io/pipelines/write-modes-and-partitioning).

## Check the result

After a run, query the destination in [Query](https://docs.beetl.io/query). This shows how the matches were made:

```sql
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:

```sql
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:

```sql
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

- [Deduplicate records](https://docs.beetl.io/pipelines/deduplicate-records): One row per key before you match.

- [DataFusion SQL](https://docs.beetl.io/query/datafusion-sql): What the SQL dialect supports.

- [Builder tour](https://docs.beetl.io/pipelines/builder): Sources, stages and preview in the editor.