Skip to content
AITroveRead. Build. Understand.
Reading list
Make this comfortable

Identifier normalization and exact linkage without false merges

Last updated: 5 Oct 20265 min read
tutorial
IntermediateBy AITrove Editorial

Exact joins are reliable only when identifier meaning and normalization rules are documented for both sources.

Start with namespace and issuer

An account ID from a retail platform and an account ID from a support platform may share the value 047 while naming different people. A join key needs the issuing system or tenant as well as the local ID. Preserve raw ID, source, canonical ID and normalization version. Join-grain checks should fail when a supposedly one-to-one account link multiplies case rows.

Normalize only proven equivalences

Trimming accidental outer whitespace may be safe for a source that documents whitespace as padding. Case folding may be safe for a source that guarantees case-insensitive IDs. Removing leading zeros is unsafe when CUS-047 and CUS-47 can be distinct. Email addresses need particular care: provider-specific punctuation or tags cannot be stripped as a universal rule. Keep the raw field so a future correction can reconstruct the link.

Separate identity from contact

A shared household phone number or shipping address does not establish one customer. A person can also change email while preserving account identity. Use a stable issued ID when available, then keep contact fields as evidence rather than replacing identity with them. Product event identity may use a session or actor ID; record linkage must declare when that actor is believed to be the same entity across systems.

Measure exact-link gaps

After an exact join, count left-only, right-only, one-to-one and one-to-many keys. Inspect nulls and reused IDs by source and time. If 2,047 order accounts and 1,910 support accounts are present, the overlap size alone says little without a clear eligible window and namespace. A missing match can mean no support case, an unobserved account mapping or a key-format error; do not classify all unmatched rows as faults.

Test adversarial fixtures

Include CUS-047 and CUS-47, mixed-case IDs from a documented case-insensitive source, and the same local number in two tenants. Assert only the intended equivalences. If source rules differ, canonicalization must take the source contract as an argument, not apply one global string-cleaning function.

Implementation

python
def canonical_account_key(source, tenant, raw_account_id):
    if raw_account_id is None or not str(raw_account_id).strip():
        raise ValueError("account ID required")
    account_id = str(raw_account_id).strip()
    if source == "retail_v2":
        account_id = account_id.casefold()  # Issuer documents case-insensitive IDs.
    elif source != "support_v1":
        raise ValueError("unknown account namespace")
    return source, tenant, account_id

assert canonical_account_key("retail_v2", "north", " CUS-047 ") == ("retail_v2", "north", "cus-047")
assert canonical_account_key("support_v1", "north", "CUS-047") != canonical_account_key("support_v1", "north", "CUS-47")

Performance and operating cost

Canonicalizing N records costs O(N) time and O(N) key storage. Hash joins on canonical keys have O(N + M) expected time for two sources, but collision audits and one-to-many checks must be retained before any downstream aggregation.

Common Mistakes

  • Do not remove leading zeros without an issuer rule that makes them insignificant.
  • Do not treat contact fields as stable person identity.
  • Do not join local IDs from different namespaces without the source and tenant.

Read next

ai-data
data-science
Storage details