Verifying Record Linkage Across Systems in Multi-Source Business Datasets

Why linkage errors are a separate failure mode

A multi-system dataset can pass every field-level check and still be wrong, because each record is valid but attached to the wrong parent. Field validation confirms that a ticket.status value is in its allowed set; it says nothing about whether the telephony call joined to that ticket was actually about it. Completeness checks, covered in checking completeness of case and ticket records, count what is missing, while linkage checks test what is connected.

The damage is specific to how joined data is used. An agent trained on cross-system workflow records learns that a CRM update follows a ticket event; if 4% of those pairs are mislinked, it learns spurious action sequences. An evaluation set built from linked outcomes, such as "refund issued" in ERP joined to a support thread, scores a model against the wrong ground truth, which compounds the label problems discussed in verifying outcome labels in operational records.

How joins in CRM, ITSM, telephony and ERP data are actually built

Every join in a business dataset rests on one of three mechanisms, and the buyer should know which one produced each edge. The mechanism determines the likely error pattern.

• Native foreign keys. A ServiceNow case or a Zendesk ticket can carry the customer account or organization ID; a Salesforce Case has an AccountId lookup; an ERP sales order references the customer master number. These are the most reliable joins, but they break when records are merged, migrated or re-keyed.
• Integration keys. Middleware writes an external identifier into the target system. Salesforce upsert, for example, matches on an External ID field to decide whether to insert or update, and it refuses to act when a key matches more than one record [4]. Sync jobs that bypass that protection can leave duplicates that later look like valid parents.
• Inferred matches. Calls joined to tickets by caller phone number and a time window, or emails joined to cases by subject line, are probabilistic entity resolution. They need blocking rules (which candidate pairs are even compared), match rules and a threshold, and blocking alone can silently exclude true matches [5].

Identifier discipline is a recognized data quality requirement, not a convenience. ISO 8000-115 sets syntactic, semantic and resolution requirements for identifiers exchanged as master data, meaning an identifier must be well formed and must resolve to the thing it names [1][2]. A joined dataset whose keys cannot be resolved back to a documented source field fails that test even if every row looks plausible.

The five linkage metrics to request or compute

Ask for these metrics per join edge, not one blended number, because a strong account-to-ticket join can hide a weak call-to-ticket join. Compute them yourself on the delivered sample as well; most need only SQL over the keys. They complement the general metrics in training data quality metrics.

1. Linkage rate. Share of child records (for example calls) with at least one linked parent (ticket). Low rates are often legitimate, since many calls never open a ticket, so compare against the expected rate the supplier states in its process description.
2. Orphan rate. Share of records whose foreign key points to a parent that is absent from the delivery. Orphans usually come from extraction windows that cut parents but not children, from deleted or archived records, or from scope filters applied to one table only.
3. Fan-out anomalies. Parents with implausibly many children (one ticket linked to 300 calls) or children linked to several parents when the relationship should be one-to-one. Heavy fan-out is the signature of a placeholder or default key such as a shared "unknown customer" account.
4. Time-order violations. Linked pairs whose timestamps contradict the business process: a resolution note before ticket creation, an invoice before the order, a call that ends after the ticket it supposedly opened was closed by days. Normalize time zones first; UTC versus local storage creates false violations at exactly the offset hours.
5. Hand-verified precision. A reviewer reads a random sample of linked pairs, including the content, and marks each correct, wrong or undeterminable. This is the only metric that measures whether links are true rather than merely present.

Worked example: a linkage QA summary for one delivery

A one-page linkage summary per edge makes the review fast and repeatable across deliveries. The table below shows the shape such a report can take, with thresholds a buyer might set for an agent-training use.

Illustrative example: invented to show structure; it does not describe an available dataset.

The call-to-ticket row illustrates the common pattern: an inferred join with a respectable-looking linkage rate but roughly 11% wrong links on inspection. A useful remedy is to require the supplier's match score and rule identifier as columns, so you can train only on high-confidence links and keep the rest for analysis.

Checks you can run on a sample delivery

Most linkage defects surface with a handful of queries over the keys and timestamps. Run them on the sample before you commit to a full delivery, and again on each refresh, because re-extraction can change keys (see stable record IDs and join keys across deliveries).

Illustrative example: invented to show structure; it does not describe an available dataset.

-- Orphan rate: calls pointing to tickets not in the delivery
SELECT COUNT(*) FILTER (WHERE t.ticket_id IS NULL)::float / COUNT(*) AS orphan_rate
FROM calls c LEFT JOIN tickets t ON c.ticket_id = t.ticket_id
WHERE c.ticket_id IS NOT NULL;

-- Fan-out: tickets with unusually many linked calls
SELECT ticket_id, COUNT(*) AS n_calls
FROM calls WHERE ticket_id IS NOT NULL
GROUP BY ticket_id HAVING COUNT(*) > 20 ORDER BY n_calls DESC;

-- Time-order: calls that start after the linked ticket was closed
SELECT c.call_id, c.started_at_utc, t.closed_at_utc
FROM calls c JOIN tickets t ON c.ticket_id = t.ticket_id
WHERE c.started_at_utc > t.closed_at_utc + INTERVAL '1 day';

Then draw the hand-check sample. Stratify it by join edge and by match mechanism, oversample inferred links and fan-out parents, and size it using the approach in how many records to check for a dataset's error rate. For a recurring supply, fold the same checks into an acceptance plan as described in acceptance sampling for dataset deliveries.

Evaluating fuzzy matching and entity resolution claims

When a supplier resolved entities probabilistically, judge the method at the level of resolved entities, not only by pairwise precision and recall. Research on entity resolution evaluation argues that pairwise metrics computed on small labeled samples can mislead, and that cluster-level, entity-centric evaluation against benchmark data gives a truer picture [3]. In practice, one wrong merge of two large customer clusters can contaminate thousands of downstream joins while barely moving a pairwise score.

Ask four questions about any inferred join:

• What blocked the candidates? Phone number, normalized email domain, account name tokens? Records never compared can never be matched [5].
• What was the rule or model, and its threshold? Request the rule version and per-link score.
• How were merges audited? Look for a labeled holdout of matched and non-matched pairs and the resulting error rates.
• Can you reverse it? Delivery should keep the original source keys next to the resolved entity ID, so you can re-link with your own rules.

A system ID beats any matcher when it exists. If a ticketing platform stored the CRM contact ID, prefer that link to a name-and-email match even when the matcher reports high confidence, and treat disagreements between the two as a defect list worth reviewing.

What to put in the request and data dictionary

Linkage quality is cheapest to secure in the request, before extraction choices are locked in. Specify the entity graph you need (account, contact, ticket, call, order, invoice), the cardinality you expect on each edge, and that every join key must be documented in the data dictionary for the delivery with its source field, its mechanism (native, integration or inferred) and, for inferred links, a score and rule identifier.

Also request that the extraction window be applied consistently across tables so parents are not cut while children are kept, and that merged or deleted records are flagged rather than silently dropped. Packaging linked records from multiple systems covers file layout; for the broader quality framework, start at the data quality hub.

SourceX sources operational datasets from US companies on request, including support and sales histories, engineering records and finance workflows, and manages the licensing process. Nothing is held in stock and a request does not guarantee a match, so describing the systems and joins you need through the buyer request page is the useful first step. Related owner content: enterprise workflow datasets and agent trajectories.

Sourcing linked multi-system records for training or evaluation

SourceX looks for US businesses that hold the data you describe, assesses data and licensing permissions, and agrees allowed uses in a license before anything is delivered. Each dataset is rights-reviewed, personal details are removed or replaced before delivery, and diligence materials are prepared per dataset, so your linkage checks can run on prepared records. Describe the systems, entities and joins you need at sourcex.si/buyers.