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 ends. If you have that project and its server is running, use it. If not, this makes it:
spool init companies
cd companiesspool devLeave that running. In a second terminal, in the companies directory:
spool load samples/crm-1.csv
spool match
spool load samples/crm-2.csv
spool matchThree 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:
-- 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)-- 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-- 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.countryname_key is a feature: 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:
no_matchif any no_match rule holds,- otherwise
matchif any match rule holds, - 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:
- A new record that matches members of exactly one identity, with no
no_matchagainst it, joins that identity. - New records that match only each other form a new identity.
- A new record that matches members of two or more identities is left without an identity, and a review item is recorded.
- Identities that exist are never merged or split by a run.
Load three rows that try them:
id,name,country
c-10,Acorn Tooling,GB
c-11,The Halden Group,GB
c-12,Kestrel Analytics,GBspool load crm-3.csv
spool matchloaded 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 createdspool show recordssource 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-e7300637659aWhat 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 splits it.crm:c-11, The Halden Group, got an identity of its own. Its name begins withthe, so the block rule never put it besidehalden 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:
spool explain crm:c-8 crm:c-12`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_matchThe 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.
spool show runs --limit 2run 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:56ZThe 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":
-- 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:
spool match --rematchrun 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 themThe 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 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 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 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 has the rest.