---
title: "Matching"
description: "Block rules pick the pairs worth comparing, match and no_match rules judge them, and a match places new records without moving the identities that exist."
url: https://spool.alexpatow.com/docs/concepts/matching
docs_index: https://spool.alexpatow.com/llms.txt
---

# Matching

Block rules pick the pairs worth comparing, match and no_match rules judge them, and a match places new records without moving the identities that exist.

Matching answers one question about two records: are they the same thing? In Spool the answer is a **judgement**: `match` or `no_match`. A third, `unsure`, can come only from a strategy written in code. A judgement is kept, and it changes nothing until a run turns judgements into identities.

This page covers the three kinds of rule, what a match does with their judgements, what that costs, and what to do when a rule gets better.

## Before you start

The examples use the sample project with both of its files of rows loaded and matched, which is where [Getting started](https://spool.alexpatow.com/docs/getting-started.md) ends. If you have that project and its server is running, use it. If not, this makes it:

```sh
spool init companies
cd companies
```

```sh
spool dev
```

Leave that running. In a second terminal, in the `companies` directory:

```sh
spool load samples/crm-1.csv
spool match
spool load samples/crm-2.csv
spool match
```

## Three kinds of rule

A rule is a SQL file that holds one condition over two rows of the feature table, `l` and `r`. The file's name is the rule's name, and the directory it is in says what kind it is.

| Directory | What a rule there says |
| --- | --- |
| `rules/block/` | This pair is worth comparing. Pairs that no block rule finds are never compared. |
| `rules/match/` | This pair is the same thing. |
| `rules/no_match/` | This pair is not the same thing. It wins over every match rule. |

The sample project has one of each:

```sql title="entities/company/rules/block/name_prefix.sql"
-- A block rule says which pairs are worth comparing: here, two names that
-- begin with the same four characters. `l` and `r` are two rows of the
-- feature table.
left(l.name_key, 4) = left(r.name_key, 4)
```

```sql title="entities/company/rules/match/similar_name.sql"
-- A match rule says a pair is the same thing: here, two names that are
-- close. The similarity is 1 for equal names and 0 for names with nothing
-- in common.
jaro_winkler_similarity(l.name_key, r.name_key) >= 0.92
```

```sql title="entities/company/rules/no_match/different_country.sql"
-- A no_match rule says a pair is not the same thing, and wins over any
-- match rule. This one holds only when both rows have a country.
l.country <> r.country
```

`name_key` is a [feature](https://spool.alexpatow.com/docs/concepts/entity-types-and-features.md): the name in lower case, with the space around it removed.

Block rules exist because comparing every record with every other does not scale. A block rule should be an equality, so that the database can find the pairs with a join. An equality over an expression is fine, as here. Write one file per equality: a block rule that says `OR` compares everything with everything.

For each pair the block rules find, the judgement is:

1. `no_match` if any no_match rule holds,
2. otherwise `match` if any match rule holds,
3. otherwise no judgement at all.

## Empty values

A comparison with an empty feature is neither true nor false, as in SQL, so a rule that is only that comparison does not hold. Two records without a country are not kept apart by `l.country <> r.country`.

In a rule with `AND`, `OR` or `NOT`, say what an empty value should mean with `coalesce`: `coalesce(l.country = r.country, false)` holds only when both have a country and they are equal, and `NOT coalesce(l.country <> r.country, false)` holds unless both have a country and they differ.

## What a match does

`spool match` starts a run. A run has a scope: the records of one entity type that are new since the last run of that type that succeeded, plus any that an earlier run left for review. It compares new records with each other and with the ones that are placed. It does not compare two records that are both placed.

It then turns the judgements into identities. The rules for that are fixed, and there is no setting for them:

1. A new record that matches members of exactly one identity, with no `no_match` against it, joins that identity.
2. New records that match only each other form a new identity.
3. A new record that matches members of two or more identities is left without an identity, and a [review item](https://spool.alexpatow.com/docs/concepts/review.md) is recorded.
4. Identities that exist are never merged or split by a run.

Load three rows that try them:

```csv title="crm-3.csv"
id,name,country
c-10,Acorn Tooling,GB
c-11,The Halden Group,GB
c-12,Kestrel Analytics,GB
```

```sh
spool load crm-3.csv
spool match
```

```text title="Output"
loaded  3 rows into source `crm`, type `company`: 3 new
next    `spool match` places them in identities
run         8, type `company`
examined    3 records, 5 candidate pairs
judgements  3 match, 1 no_match
placed      3 records, 2 identities created
```

```sh
spool show records
```

```text title="Output"
source and key  name                 identity
crm:c-1         Acorn Tools          01a1efc6-41cd-775d-8758-e7300637659a
crm:c-10        Acorn Tooling        01a1efc6-41cd-775d-8758-e7300637659a
crm:c-11        The Halden Group     01a1f0a0-8d53-77b4-bc9c-978de1e67f11
crm:c-12        Kestrel Analytics    01a1f0a0-8d53-77b4-bc9c-978e6d03a9bf
crm:c-2         ACORN TOOLS          01a1efc6-41cd-775d-8758-e7300637659a
crm:c-3         Acorn Tools Ltd      01a1efc6-41cd-775d-8758-e7300637659a
crm:c-4         Larkfield Logistics  01a1efc6-41cd-775d-8758-e733aa086f7d
crm:c-5         Halden Group         01a1efc6-41cd-775d-8758-e7329447fd9a
crm:c-6         Halden Group         01a1efc6-41cd-775d-8758-e731c61371cb
crm:c-7         Larkfeild Logistics  01a1efc6-41cd-775d-8758-e733aa086f7d
crm:c-8         Kestrel Analytics    01a1efc6-42c0-7776-b736-83fde0cb4498
crm:c-9         Acorn Tool           01a1efc6-41cd-775d-8758-e7300637659a
```

What happened to each new row:

- **`crm:c-10`, Acorn Tooling, joined Acorn Tools.** The names are close enough for the match rule. If Acorn Tooling is another company, this is a wrong match, and a looser rule makes more of them. [Identities](https://spool.alexpatow.com/docs/concepts/identities.md#split) splits it.
- **`crm:c-11`, The Halden Group, got an identity of its own.** Its name begins with `the `, so the block rule never put it beside `halden group`. That is a miss, and the last section of this page fixes it.
- **`crm:c-12`, Kestrel Analytics in GB, got an identity of its own.** The one that exists is in the US, and the no_match rule held.

`spool explain` says which rules decided a pair:

```sh
spool explain crm:c-8 crm:c-12
```

```text title="Output"
`crm:c-12` belongs to identity 01a1f0a0-8d53-77b4-bc9c-978e6d03a9bf and `crm:c-8` to identity 01a1efc6-42c0-7776-b736-83fde0cb4498.
Run 8 (strategy `rules`) judged them `no_match` (no_match rule `different_country` held).
`crm:c-12` was placed at log position 3 by `engine` (reconcile run 9, "reconcile new records").
`crm:c-8` was placed at log position 2 by `engine` (reconcile run 6, "reconcile new records").
By the rules as they are now, block rule `name_prefix` makes them a candidate pair and no_match rule `different_country` holds, so they would be judged `no_match`.

left   56ee88f4-49f2-e913-e596-70c5288afd7f  crm:c-12  identity 01a1f0a0-8d53-77b4-bc9c-978e6d03a9bf
right  7da2b0d2-3601-e533-efa5-e1068cce5344  crm:c-8   identity 01a1efc6-42c0-7776-b736-83fde0cb4498

features
            left               right
  country   GB                 US
  name      Kestrel Analytics  Kestrel Analytics
  name_key  kestrel analytics  kestrel analytics

rules as they are now
  block     name_prefix        holds
  no_match  different_country  holds
  match     similar_name       holds

runs that compared them
  run 8  rules  succeeded  block: name_prefix  no_match
```

The run itself is in `spool show runs`. A match makes two runs: a match run, which judges pairs, and a reconcile run, which turns the judgements into identities and commits them as one [changeset](https://spool.alexpatow.com/docs/concepts/identities.md#the-log).

```sh
spool show runs --limit 2
```

```text title="Output"
run  kind       status     about           started by                                 started
9    reconcile  succeeded                  user:01a1f0a0-8c93-757a-852f-1cc4a87967ed  2026-11-18T21:46:56Z
8    match      succeeded  company, rules  user:01a1f0a0-8c93-757a-852f-1cc4a87967ed  2026-11-18T21:46:56Z
```

## The cost of IDs that stay

Rule 4 is the reason an ID can be stored somewhere else and trusted. It also makes the result depend on the order rows arrive in. Two records that arrive together and match become one identity. The same two, each placed alone before a rule matched them, stay apart until someone merges them.

So a better rule does not repair old data by itself. The next section shows what it does instead.

## Rules that get better

`crm:c-11` should have joined Halden Group. Make the transform drop a leading "the":

```sql title="transforms/name_key.sql"
-- Lower case, without the space around it or a leading "the".
lambda name: regexp_replace(lower(trim(name)), '^the ', '')
```

`spool dev` computes the features again. No identity changes, because both records are placed. To see what the better rule says about records that are placed, match again with `--rematch`, which compares every record of the type:

```sh
spool match --rematch
```

```text title="Output"
run         10, type `company`
examined    0 records without an identity and every record placed before, 15 candidate pairs
judgements  11 match, 3 no_match
placed      0 records, 0 identities created
suggested   1 merge, 0 splits: `spool show runs 10` lists them
```

The run placed nothing and moved nothing. It reports a **suggested merge**: two identities with matches between their members. A person or an agent who agrees merges them, which [Identities](https://spool.alexpatow.com/docs/concepts/identities.md#merge) shows. A rematch reports suggested splits the same way, when a no_match rule holds between two members of one identity.

A better matcher run over old data produces a list of suggestions, not a change to IDs that other systems have stored.

The suggestions are part of the run's report and are not kept as review items. `spool show runs <run>` lists them for any run, and `GET /v1/runs/<run>` has them as JSON. Nothing sorts or pages them, so a rule change that suggests thousands of merges gives a report of thousands of lines.

## Other ways to judge

SQL rules are the built-in strategy. A project can also hold strategies written in Python or TypeScript under `entities/<type>/strategies/`, which the server runs as child processes with `uv` or `bun`. They read candidate pairs and write judgements, which may be `unsure`, and the same four rules turn them into identities. The repository's [`benchmarks/resolution/people`](https://github.com/alexpatow/spool/tree/main/benchmarks/resolution/people) project has a strategy that runs Splink.

A caller can also open a run, fetch the candidates over the API and send judgements of its own. [The HTTP API](https://spool.alexpatow.com/docs/api.md#runs) lists the calls.

## What matching does not do

- The four rules that turn judgements into identities cannot be changed.
- No SQL rule says `unsure`. A pair is matched, kept apart, or not judged.
- A rule sees the features of two records and not their sources. It cannot say that one source never holds the same thing twice.
- Rules compare records of one entity type.

[Limits](https://spool.alexpatow.com/docs/limits.md) has the rest.
