This chapter strings every piece from Chapter 2 together into a single, realistic linkage pipeline: an incoming batch of records needs to be matched against a reference database. It’s the same shape of problem examples_data_linkage.R in the repo demonstrates, written out here as a narrative.
The batch arrives the way batches actually arrive — names in mixed case with punctuation, dates as strings in a local format, a load timestamp per row, a junk record that should never have been sent, and the same person submitted twice. Getting from that to a defensible set of links is eight steps, and most of the functions in this package show up in one of them.
4.1 The data
incoming<-data.frame( id =1:7, name =c(" JOHN O'BRIEN ", "MARY-JANE SMITH", "ROBERT BROWN","Kyle John Haynes", "Hans Müller", "UNKNOWN 00000000","JOHN OBRIEN"), dob =c("15/03/1985", "22/07/1990", "01/12/1975", "05/01/1988","09/09/1971", "01/01/1900", "15/03/1985"), received =c("2024-06-18 09:15:00", "2024-06-18 09:17:42","2024-06-18 11:02:10", "2024-06-18 11:45:00","2024-06-18 12:00:00", "2024-06-18 12:30:00","2024-06-18 14:20:00"), stringsAsFactors =FALSE)reference<-data.frame( ref_id =101:106, name =c("JON OBRIEN", "MARYANN SMITH", "ROB BROWN", "JOHN BRIEN","Haynes John Kyle", "Hans Mueller"), dob =as.Date(c("1985-03-14", "1990-07-20", "1975-12-02","1985-03-15", "1988-01-05", "1971-09-09")), stringsAsFactors =FALSE)incoming
id name dob received
1 1 JOHN O'BRIEN 15/03/1985 2024-06-18 09:15:00
2 2 MARY-JANE SMITH 22/07/1990 2024-06-18 09:17:42
3 3 ROBERT BROWN 01/12/1975 2024-06-18 11:02:10
4 4 Kyle John Haynes 05/01/1988 2024-06-18 11:45:00
5 5 Hans Müller 09/09/1971 2024-06-18 12:00:00
6 6 UNKNOWN 00000000 01/01/1900 2024-06-18 12:30:00
7 7 JOHN OBRIEN 15/03/1985 2024-06-18 14:20:00
reference
ref_id name dob
1 101 JON OBRIEN 1985-03-14
2 102 MARYANN SMITH 1990-07-20
3 103 ROB BROWN 1975-12-02
4 104 JOHN BRIEN 1985-03-15
5 105 Haynes John Kyle 1988-01-05
6 106 Hans Mueller 1971-09-09
Seven incoming records, six reference records, deliberately messy: inconsistent casing, stray whitespace, punctuation, typos, a near-duplicate in the reference file ("JOHN BRIEN" vs. "JON OBRIEN"), a fully reordered multi-token name ("Kyle John Haynes" vs. "Haynes John Kyle"), a German name spelled two ways ("Hans Müller" vs. "Hans Mueller"), a placeholder record ("UNKNOWN 00000000"), and one person submitted twice in the same batch (ids 1 and 7).
4.2 Step 1: profile before you match
The cheapest way to improve a linkage run is to not feed it rubbish. fcount() counts non-overlapping matches per element, which makes column-level quality rules one vectorised call each: how many digits are in a name field (a name with digits is almost always a placeholder or a concatenated identifier), and how many tokens a name has.
id name n_digits n_tokens
1 1 JOHN O'BRIEN 0 2
2 2 MARY-JANE SMITH 0 2
3 3 ROBERT BROWN 0 2
4 4 Kyle John Haynes 0 3
5 5 Hans Müller 0 2
6 6 UNKNOWN 00000000 8 2
7 7 JOHN OBRIEN 0 2
Record 6 is the placeholder. fcount() is the counting counterpart to fgrepl() — same PCRE2 and prepared-fixed-string engines, same parallel dispatch — and replaces the vapply(gregexpr(...), length, ...) idiom, which allocates a match-position vector per row just to take its length and needs two explicit branches to reproduce fcount()’s NA-and-zero handling:
The batch carries dates as DD/MM/YYYY strings and a load timestamp per row. fas.Date() and fas.POSIXct() parse one fixed, locale-free shape each — no format guessing, no timezone-database lookup.
The two functions differ in strictness, and it matters when you’re deciding which one is allowed to silently accept bad input. fas.Date() validates field ranges only (month 1-12, day 1-31), so an impossible calendar date rolls over; fas.POSIXct() validates the full calendar, including month lengths and leap years, and returns NA:
fas.Date("29/02/2023", "dmy")# rolls over -- 2023 is not a leap year
[1] "2023-03-01"
fas.POSIXct("2023-02-29 08:00:00")# NA
[1] NA
So fas.POSIXct() doubles as a validity check on the timestamp column, and an NA there is a legitimate reason to quarantine a row.
4.4 Step 3: normalise and quarantine
Strip punctuation and whitespace inconsistencies before comparing, so the comparison step measures genuine name similarity rather than formatting noise — then set aside the rows the profile step flagged.
id name_clean dob_date received_ts
1 1 JOHN O BRIEN 1985-03-15 2024-06-18 09:15:00
2 2 MARY JANE SMITH 1990-07-22 2024-06-18 09:17:42
3 3 ROBERT BROWN 1975-12-01 2024-06-18 11:02:10
4 4 Kyle John Haynes 1988-01-05 2024-06-18 11:45:00
5 5 Hans Müller 1971-09-09 2024-06-18 12:00:00
7 7 JOHN OBRIEN 1985-03-15 2024-06-18 14:20:00
(jaro_winkler_tokens() does its own internal punctuation stripping and case-folding — see Chapter 5 — but normalising up front keeps this example explicit, and matters for jaro_winkler()/jaro_winkler_matrix() and the q-gram metrics, which don’t.)
4.5 Step 4: collapse duplicates inside the batch
Ids 1 and 7 are the same person, sent twice. Deduplicating before matching keeps a single incoming record from generating two competing links to the same reference row. The parsed timestamp decides which copy survives — last write wins.
id name_clean dob_date received_ts
2 2 MARY JANE SMITH 1990-07-22 2024-06-18 09:17:42
3 3 ROBERT BROWN 1975-12-01 2024-06-18 11:02:10
4 4 Kyle John Haynes 1988-01-05 2024-06-18 11:45:00
5 5 Hans Müller 1971-09-09 2024-06-18 12:00:00
7 7 JOHN OBRIEN 1985-03-15 2024-06-18 14:20:00
Record 1 (09:15) is dropped in favour of record 7 (14:20). Note that this step only catches duplicates that normalise to the same string — genuinely fuzzy within-batch duplicates need the same scoring machinery as the linkage itself, run with batch on both sides.
4.6 Step 5: block on more than one phonetic key
Comparing every incoming record against every reference record is \(O(n \times m)\) — fine at this toy scale, prohibitive at millions of rows. Blocking groups records by a cheap, coarse key first and only compares records within the same block.
The classic key is soundex() on a surname, but a single key inherits that scheme’s blind spots. Two of the additions here exist precisely to cover different ones:
Read that table in the direction of how much each key merges. Classic soundex() does catch "Müller"/"Mueller" here — but only as a side effect of discarding vowels entirely, which is also why it drops "Miller" in with them and collapses "Meier"/"Meyer" to a single M600. That bluntness is the thing you’re trying to get away from at scale: coarse keys make big blocks, and big blocks are what blocking exists to avoid.
refined_soundex() is the precise end. It encodes vowels rather than discarding them and doesn’t truncate, so blocks get smaller — but the umlaut isn’t an ASCII vowel it can encode, so it splits "Müller" (M8709) from "Mueller" (M80709) and on its own would never make that pair a candidate. cologne() folds umlauts by design and gives both 657, while staying coarse enough to pull "Miller" in too.
Neither key is right on its own, and that’s the point: running both and unioning the candidate sets recovers the specific pair the precise key misses without adopting the coarse key’s block sizes everywhere else. Chapter 3 quantifies the selectivity difference across a wider surname list.
Because names arrive in inconsistent token order, block on the phonetic code of every token rather than a positional “surname” — a record is a candidate if it shares any token code, within the same birth year.
id key token scheme
1 2 M8090:1990 MARY refined_soundex
2 2 J4080:1990 JANE refined_soundex
3 2 S38060:1990 SMITH refined_soundex
4 3 R901096:1975 ROBERT refined_soundex
5 3 B1908:1975 BROWN refined_soundex
6 4 K3070:1988 Kyle refined_soundex
7 4 J408:1988 John refined_soundex
8 4 H0803:1988 Haynes refined_soundex
The whole exploded token vector is encoded in one vectorised call per scheme, so the per-token cost is a parallel C++ pass rather than an R-level loop over records.
hits<-merge(inc_keys, ref_keys, by =c("key", "scheme"))pairs<-unique(hits[, c("id", "ref_id")])pairs<-pairs[order(pairs$id, pairs$ref_id), ]nrow(pairs)
[1] 6
nrow(batch)*nrow(reference)# the full cross product this replaces
Six candidate pairs instead of thirty. At this scale that saving is cosmetic; the point is the ratio, which is what survives to five million rows — and that every true match is still in the set.
Keeping the token alongside the key makes the blocking step auditable — you can ask why any given pair is a candidate, which matters when you’re tuning keys and need to know which one is carrying the recall:
scheme token ref_token key
5 cologne Hans Hans 068:1971
9 cologne Müller Mueller 657:1971
13 refined_soundex Hans Hans H083:1971
Note what this says, and what it doesn’t. The "Hans Müller" pair is reachable under both schemes, because the given name is identical and refined_soundex() blocks it happily. Cologne’s contribution is the second row: it is the only one of the two that also links the surnames ("Müller"/"Mueller" → 657). On this batch that’s redundant — but given names disagree across systems constantly ("Hans"/"Johann"/"H"), and when they do, that surname key is the only thing keeping the pair alive.
4.7 Step 6: score the candidates
Score name similarity only among surviving candidates, with two deliberately different metrics. jaro_winkler_tokens() compares names token by token and is insensitive to token order by construction. cosine_similarity() compares q-gram frequency profiles across the whole string, so it is order-sensitive and weights repeated q-grams — an independent signal rather than a restatement of the first.
id name_a ref_id name_b jw cos dob_gap
10 2 MARY JANE SMITH 102 MARYANN SMITH 0.9525641 0.6943651 2
7 3 ROBERT BROWN 103 ROB BROWN 0.9416667 0.7893522 1
4 4 Kyle John Haynes 105 Haynes John Kyle 1.0000000 0.8666667 0
5 5 Hans Müller 106 Hans Mueller 0.9142857 0.7272727 0
2 7 JOHN OBRIEN 104 JOHN BRIEN 0.9800000 0.8432740 0
1 7 JOHN OBRIEN 101 JON OBRIEN 0.9733333 0.8432740 1
The two scores disagree in informative ways. "Kyle John Haynes" vs. "Haynes John Kyle" is a perfect 1.000 under token comparison — every token has an exact partner — but 0.867 under cosine, which sees the reordering as changed bigrams across token boundaries. That gap is the information: a high jw with a lower cos says “same tokens, different arrangement”, which is a different kind of evidence from “same arrangement, one typo”.
4.8 Step 7: decide, and admit ambiguity
Apply a cutoff to decide which candidate pairs are accepted as links. 0.85 is a common starting point for Jaro-Winkler on names; the right value depends on your data and how costly false positives/negatives are relative to each other — tune it against a labelled sample if you have one. The date gap is a second field-level filter, tolerating a small typo in the day or month without accepting an unrelated birth date.
id name_a ref_id name_b jw cos dob_gap ambiguous
10 2 MARY JANE SMITH 102 MARYANN SMITH 0.9525641 0.6943651 2 FALSE
7 3 ROBERT BROWN 103 ROB BROWN 0.9416667 0.7893522 1 FALSE
4 4 Kyle John Haynes 105 Haynes John Kyle 1.0000000 0.8666667 0 FALSE
5 5 Hans Müller 106 Hans Mueller 0.9142857 0.7272727 0 FALSE
2 7 JOHN OBRIEN 104 JOHN BRIEN 0.9800000 0.8432740 0 TRUE
1 7 JOHN OBRIEN 101 JON OBRIEN 0.9733333 0.8432740 1 TRUE
Every incoming record links to its obvious match, including the fully reordered "Kyle John Haynes" → "Haynes John Kyle" pair — the one case in this dataset where plain jaro_winkler() would have scored well below threshold and jaro_winkler_tokens() is doing real work — and the "Hans Müller" → "Hans Mueller" pair, which clears the threshold at 0.914 despite the umlaut, because token comparison is scoring the characters rather than a phonetic key.
Record 7 is the honest case. It has two surviving candidates, 0.980 and 0.973, and identical cosine scores of 0.843:
id name_a ref_id name_b jw cos margin
10 2 MARY JANE SMITH 102 MARYANN SMITH 0.9525641 0.6943651 NA
7 3 ROBERT BROWN 103 ROB BROWN 0.9416667 0.7893522 NA
4 4 Kyle John Haynes 105 Haynes John Kyle 1.0000000 0.8666667 NA
5 5 Hans Müller 106 Hans Mueller 0.9142857 0.7272727 NA
2 7 JOHN OBRIEN 104 JOHN BRIEN 0.9800000 0.8432740 0.006666667
A margin of 0.007 between the top two candidates is not a decision, it’s a coin toss with extra steps. The second metric doesn’t break the tie here — it agrees — and that agreement is itself the finding: "JOHN OBRIEN" is genuinely close to both "JOHN BRIEN" and "JON OBRIEN", and no threshold on this evidence can tell you which. The right output is a review queue, not a link. Routing thin-margin pairs to clerical review, rather than letting !duplicated() silently pick the first row, is the difference between a linkage you can defend and one you can’t.
4.9 Step 8: stamp the output
Downstream loads need to know which batch they’re looking at. format_datetime() writes the batch watermark without a timezone-database lookup, in UTC or at one fixed offset for a local-time report:
watermark<-max(batch$received_ts)format_datetime(watermark, "rfc3339")# UTC, for the audit log
[1] "2024-06-18T14:20:00Z"
format_datetime(watermark, "iso_offset", offset ="+10:00")# local time, for humans
"iso_offset" applies one fixed numeric offset to every value — it is not a timezone. It has no daylight-saving rules, so a batch spanning a DST boundary needs base::format() with a real timezone; for stamping a single load with a known offset, this is the cheap and correct tool.
4.10 Why the fast versions matter here
Steps 1 and 2 are the ones that quietly dominate a real batch load: they touch every row of every column, before any of the interesting matching work starts. A single timed run on this machine, over 300,000 rows:
Both are single runs on one machine and will vary with cores and input shape — Chapter 6 is the chapter with the reproducible harness, and it scales the comparison step of this exact pipeline shape up to 3 million rows.