Deterministic matching links two records because they share an exact, unique identifier — the same hashed email, the same CRM contact ID, the same authenticated account. The claim is binary and verifiable: either the identifiers match exactly or they don't. Probabilistic matching links two records because a model estimates they likely represent the same entity based on a combination of weaker, non-unique signals (name, address, device fingerprint, IP, browsing pattern) — the claim is always a confidence score, never a certainty, even when that score is 99%.

Why marketing data forces you to use both

Pure deterministic matching is precise but has limited reach — it only connects records that happen to share an exact identifier, which excludes a large share of real customer activity (anonymous browsing, guest checkout, cross-device journeys with no login). Pure probabilistic matching has much broader reach but accumulates error — false merges (treating two different people as one) and false splits (treating the same person as two) both degrade downstream decisions. Production identity systems use deterministic matches as a trusted backbone and apply probabilistic matching only to extend coverage into gaps deterministic matching can't reach, with clearly lower confidence thresholds for any action taken on probabilistic-only links.

match_confidence_scoring.sql

sql

-- A simplified blocking + scoring pattern for probabilistic record matching
WITH candidate_pairs AS (
  SELECT a.record_id AS id_a, b.record_id AS id_b,
    -- Exact match components (deterministic signals, weighted heavily)
    (a.email_hash = b.email_hash) AS email_exact,
    (a.phone_normalized = b.phone_normalized) AS phone_exact,
    -- Fuzzy match components (probabilistic signals, weighted lightly)
    (a.last_name = b.last_name) AS last_name_exact,
    ST_DISTANCE(a.geo_point, b.geo_point) < 500 AS geo_proximity
  FROM customer_records a
  JOIN customer_records b
    ON a.zip_code = b.zip_code  -- "blocking" key limits comparisons to plausible pairs
    AND a.record_id != b.record_id
)
SELECT *,
  (CASE WHEN email_exact THEN 50 ELSE 0 END) +
  (CASE WHEN phone_exact THEN 30 ELSE 0 END) +
  (CASE WHEN last_name_exact THEN 10 ELSE 0 END) +
  (CASE WHEN geo_proximity THEN 5 ELSE 0 END) AS match_score
FROM candidate_pairs
WHERE match_score > 40  -- threshold decided by acceptable false-positive rate

Blocking: making probabilistic matching computationally tractable

Comparing every record against every other record is quadratic and infeasible past a few hundred thousand rows. 'Blocking' first groups records by a cheap, high-recall key (zip code, first three letters of last name, phone area code) and only runs expensive pairwise comparison within each block — trading a small amount of recall (two genuinely matching records that happen to land in different blocks get missed) for a massive reduction in comparison volume, which is what makes probabilistic matching practical at marketing-data scale.

The right threshold depends entirely on what happens if you're wrong

A false-positive merge that causes a minor personalization glitch (showing a slightly wrong product recommendation) is cheap to tolerate. A false-positive merge that causes a GDPR data-subject access request to return another person's purchase history, or that suppresses a legitimate prospect from outreach because they were wrongly merged with someone who unsubscribed, is expensive and sometimes legally consequential. Set match thresholds based on the downstream action's cost of being wrong, not a single global 'confidence' number applied uniformly everywhere.

Measuring match quality honestly

Precision (of the matches you made, how many were correct) and recall (of the true matches that existed, how many did you find) trade off against each other as you move the confidence threshold, and marketing teams need to pick a point on that curve deliberately rather than defaulting to whatever a vendor ships. The only reliable way to measure this is against a labeled ground-truth sample — a manually reviewed set of record pairs with known correct answers — re-evaluated periodically, since match quality silently degrades as underlying data sources or signal availability (e.g. fingerprinting restrictions) change over time.

What's Next?

Matching methodology is the mechanics of identity resolution — the organizational question of who owns the resulting golden record, and how conflicting values from different source systems get reconciled, is the next layer up.

I build these systems professionally.

Whether it's a RAG pipeline, analytics migration, or AI workflow — let's talk.

Work With Me →

Need production AI or MarTech expertise? I build custom LLM workflows, server-side GTM tagging, and analytics infrastructures professionally.

Need custom AI or MarTech setup? Let's build together.

Let's talk →