Stable Record IDs and Join Keys Across Dataset Deliveries

This page is general information, not legal advice. Confirm requirements with counsel for your jurisdiction and use case.

Why record IDs decide whether a recurring purchase stays usable

Record IDs are the only mechanism that lets you apply an update, honor a deletion or keep an evaluation split clean once a second delivery arrives. Without them, every refresh becomes a fuzzy-matching project, and the cost compounds with each drop.

Four downstream jobs depend on the same column:

• Upserts and corrections. An incremental file that changes ticket 4,812's resolution code must say which row it replaces. See incremental deliveries vs full refreshes for how the delivery mode changes this.
• Deletions. A supplier withdrawal or data-subject request arrives as a list of IDs; if the IDs drifted, you cannot prove the records left your training corpus. The mechanics live in propagating deletions and corrections through recurring deliveries.
• Deduplication and splits. Duplicates that cross the train and eval boundary inflate scores and increase memorization [6]. Splitting by a stable entity key (all records for one account on one side) prevents leakage that splitting by row cannot.
• Lineage. Tracing which model saw which record, as in dataset-to-model traceability, requires IDs that mean the same thing in month 1 and month 12.

Which identifiers break, and how

Most ID failures come from keys that were stable only inside one export run. The common failure modes are predictable enough to rule out in the data contract before the first file lands.

The Jira issue key is a good example of quieter breakage: project keys can be renamed, so PROJ-123 becomes NEW-123 and a naive join silently drops history. Ask the supplier to derive IDs from the immutable internal identifier, not the display key.

How to build pseudonymous join keys with keyed hashing

Use HMAC with SHA-256 over a normalized source identifier, with a secret key held only by the supplier or its preparation team. HMAC combines a standard hash with a secret key; keep it on SHA-256 rather than MD5-era constructions, whose security considerations the IETF has revisited [1]. Because the output is deterministic for a given key, the same customer gets the same join key in every delivery, yet a buyer cannot recompute or reverse it from a guessed email.

A workable construction looks like this:

join_key = base32( HMAC-SHA256( K_entity, namespace || ":" || normalize(source_id) ) )[0:26]

Each piece matters:

• Namespace. Prefix with the system and entity type (zendesk.user, stripe.customer) so a numeric user ID and a numeric order ID with the same value never collide.
• Normalization. Trim whitespace, fix case for case-insensitive IDs, and strip leading zeros only if the source treats them as insignificant. Document the rule in the data dictionary, because a change in normalization changes every key.
• Truncation length. A 26-character base32 prefix keeps 130 bits, which keeps accidental collisions negligible at operational scales; do not truncate to 8 or 10 characters to save space.
• One key per licensing relationship. If the supplier reuses the same secret for two buyers, those buyers could join their copies. A per-buyer key prevents cross-buyer linkage.

Where the entity identifier is not personal and the supplier is comfortable with it, a name-based UUIDv5 (namespace plus name, hashed with SHA-1) gives a deterministic, standard-format ID. It is unkeyed, so it carries the same dictionary-attack exposure as a plain hash and should not wrap emails, phone numbers or account numbers. Time-ordered UUIDv7 suits row IDs that the supplier persists in a mapping table, not IDs that must be recomputed.

Who holds the key, and what happens when it rotates

The secret key is the re-identification material, so its custody determines how the data is treated. The EDPB's guidance treats pseudonymised data as personal data, with the separately held additional information being what keeps it pseudonymous rather than directly identifying [2]. The UK ICO likewise stresses keeping that additional information separate and secured [3].

Practical custody rules to write into the agreement and the data contract:

• The supplier holds K; the buyer never receives it. The buyer receives only outputs. If you need to send back a deletion list, you send join keys and the supplier maps them.
• The supplier keeps a mapping table (source ID to join key) inside its own boundary, so deletion requests that arrive by email address can be translated into keys.
• Rotation is a breaking change. Rotating K re-keys every entity. If rotation is ever needed (suspected key exposure), the supplier should ship an old-to-new crosswalk once, under the same access controls as the data, and bump the dataset version.
• Key storage. Keep the key in a managed KMS or HSM with access logging; an HMAC key pasted into a notebook or config file is an easy way to leak it.

For health data, check the HIPAA path before choosing a construction. Safe Harbor permits a re-identification code only if it is not derived from information about the individual and the mechanism is not disclosed [4]. A keyed hash of a medical record number is derived from such information, so teams relying on Safe Harbor typically use a random surrogate held in a supplier-side mapping table, or document the HMAC approach under an Expert Determination. Confirm the approach with your privacy counsel.

Spec artifact: an ID block for your data contract

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

identifiers:
  record_id:
    column: record_id
    scope: one row per source event (ticket comment)
    derivation: HMAC-SHA256(K_record, "zendesk.comment:" || comment_id), base32, 26 chars
    stability: identical across re-exports and corrections of the same comment
    uniqueness: primary key within dataset; validated per delivery
  entity_keys:
    - column: ticket_key
      namespace: zendesk.ticket
    - column: requester_key
      namespace: zendesk.user
      note: same person across tickets maps to one key; no cross-system merge
    - column: account_key
      namespace: crm.account
      join: requester_key -> account_key via bridge table account_membership
  key_custody:
    holder: supplier preparation team
    buyer_receives_key: false
    per_buyer_key: true
    rotation: only on suspected exposure; crosswalk file shipped; major version bump
  change_semantics:
    op_column: _op            # insert | update | delete
    version_column: _record_version   # monotonic integer per record_id
    tombstones: retained for 2 deliveries   # illustrative value; agree per deal
  validation:
    - record_id not null and unique
    - every foreign key resolves or is listed in orphans.csv
    - no source-system ID patterns (regex list) in any key column

An example row in Parquet or JSONL would then carry record_id, ticket_key, requester_key, _op, _record_version and the payload fields. Format trade-offs for these columns are covered in Parquet vs JSONL for licensed training data.

Linking tables across multiple business systems

Join keys only link systems if the supplier resolves the entity before hashing; hashing does not perform entity resolution. A customer who is user 88213 in the help desk and contact 0035g00000ABC in the CRM gets two unrelated keys unless the supplier maps both to one internal entity first.

Ask suppliers to state, per key, whether it is system-scoped or resolved across systems, and if resolved, which match rule they used (exact email, verified account link, or probabilistic). Publish that as a bridge table rather than overwriting keys, so you can audit or discard the linkage. The packaging pattern is covered in packaging linked records from multiple business systems, and verification of match quality in record linkage quality for multi-system datasets.

Collision and re-identification risk you still own

Stable pseudonymous keys reduce exposure of direct identifiers but make longitudinal profiles possible, which raises re-identification risk. Research on process-mining event logs found that sequences of timestamps and activities attached to a pseudonymous case ID can make individuals highly unique even with names removed [5].

Controls that hold up in review:

• Key scope. Use separate keys for separate purposes. A requester key that persists across five years of tickets links far more behavior than a per-ticket key.
• Quasi-identifier review. Check timestamp precision, rare categories and free text attached to each key; coarsen timestamps where the use case allows.
• No reverse lookups in your stack. Do not build tables that join the delivered keys back to your own customer data, and record that rule in your access controls for licensed training data.
• Collision monitoring. With a full SHA-256 output collisions are not a practical concern, but validate uniqueness per delivery anyway; a duplicate key almost always signals a normalization bug, not a hash collision.

For the wider privacy framing, see the de-identified data for AI training guide and the glossary entry on pseudonymization.

Acceptance checks on every delivery

Treat ID stability as a tested property, not a promise. Run these before loading a delivery into training storage:

1. Primary key uniqueness: COUNT(*) = COUNT(DISTINCT record_id) per table.
2. Carry-over rate: the share of prior-delivery record_id values present again (for full refreshes) should match the supplier's stated deletions; a sudden drop to near zero means keys were regenerated.
3. Referential integrity: every ticket_key in comments exists in tickets, or appears in an orphans file with a reason.
4. Version monotonicity: _record_version never decreases for a record_id.
5. Leakage scan: regexes for source ID formats (Salesforce 15/18-character IDs, numeric help-desk IDs, email patterns) return nothing in key columns.
6. Split integrity: no entity key appears in both train and eval partitions.

Schema-level changes to ID columns, such as a new namespace or a widened key, should follow the process in schema evolution for recurring deliveries and the terms in your data contract. The delivery hub links the related format, manifest and transfer pages.

How SourceX approaches identifiers in licensed operational data

SourceX sources operational datasets, such as support and sales histories and engineering records, from US companies on request and manages the licensing and ongoing purchases. Before delivery, personal details such as names, emails, phone numbers and account numbers are removed or replaced, the method used is recorded, and a sample is checked; no method is perfect. Delivery runs through private, access-controlled workflows after an executed agreement and supplier approval. Buyers can bring an ID specification like the one above when they describe the data they need.

Sourcing linked operational records with stable keys

If your pipeline needs multi-table operational data that stays joinable across refreshes, describe the records, systems and identifier requirements rather than naming businesses. SourceX looks for US businesses that hold that data, reviews ownership and consents, and nothing is contracted until a supplier agrees; a request does not guarantee a match. Start a buyer request.