Keys, Identifiers & De‑duplication

Why this matters

Bad keys quietly create bad science. If you pick the wrong key (or skip de‑duplication), you will:

• Multiply rows when joining tables, inflating counts and rates.
• Mask errors by “averaging away” duplicated values.
• Expose identifiers (e.g., Medical Record Number—MRN, Social Security Number—SSN) unnecessarily, adding privacy and audit risk.

Clinical data lives under inspection. Getting keys, grain, and de‑dup right prevents rework, deviations, and painful lock delays.

Core concepts & definitions

• Grain The “level” at which each row is unique (e.g., subject, subject+visit, subject+visit+parameter, plus a sequence number for repeat readings). A one‑to‑many join inherits the child table’s grain; a many‑to‑many join multiplies rows.

• Natural key A real‑world identifier already present (e.g., site + subject number, a lab accession number; in hospital records, the MRN). Natural keys can change, be reused, or have formatting quirks (leading zeros, dashes).

• Surrogate key A system‑assigned, meaningless identifier (e.g., vital_id, or ar_id in the examples). Good surrogates are stable and never reused.

• Composite key Uniqueness defined by more than one column (e.g., subject_id + visit + param).

• Participant identifier Subject numbers may repeat across sites, so the participant key is site + subject number or a study‑wide ID built from them (like subject_id here); subject number alone merges different people. Someone with several numbers (screening, randomization, re‑screening, site transfer) needs a mapping table to one stable ID.

• Direct identifier Data that point straight to a person (name, MRN, SSN or other national ID). Sponsor datasets should hold none: a study code identifies each participant, and the list linking codes to people stays at the site.

• De‑duplication (de‑dup) The act of selecting a single winner among records that represent the same real‑world fact, with transparent rules and reason codes. An exact duplicate repeats every value; a near duplicate shares the key but differs in a value. Either is a finding for its source. De‑dup only chooses what the curated view shows: nothing is deleted.

• Lineage Keeping the as‑received record set, documenting all selections/merges, and being able to show why a row was picked and which file and load it came from.

The process, step‑by‑step

1. Declare the grain and key Write it down for each dataset (e.g., Vitals grain = subject+visit+param; key = (subject_id, visit, param)).

2. Profile uniqueness

   • Count total rows vs. distinct key values.
   • List duplicate keys and their counts; do not join until resolved.

3. Define de‑dup rules (in order of precedence) Set them in advance (data management plan, transfer specification), not case by case. Common patterns:

   • Authoritative source (e.g., the lab’s direct transfer beats a forwarded copy).
   • Latest timestamp (e.g., most recent correction/transfer).
   • Adjudication status (prefer adjudicated over preliminary).
   • Completeness/validity (prefer rows with required fields present).
   • Explicit priority list (e.g., source_priority: corrected file > original file).

   These rules choose among versions of the same record; two sources that disagree (site entry vs lab file) are a reconciliation discrepancy, not duplicates.

4. Implement a de‑dup view Use a window function to number candidates per key, keep row 1 (row_number() with a unique last sort column, so ties cannot give two winners), and carry a reason_code and chosen_by_rule.

5. Record lineage Keep an immutable “as_received” table. Store the de‑dup selection table separately. Include: chosen record id, rule triggered, timestamp, and operator/system.

6. Join only de‑duped sets Join curated, key‑unique datasets. The join result’s grain will be the more detailed (child) side—ensure that is what you want.

7. Protect identifiers Export the minimum necessary. Study datasets use study IDs, not MRN/SSN. When linkage is required, the data holder uses tokenization (random, stable mappings) and keeps the mapping table in a restricted vault. Always log who received what, when, and why.

What good looks like

• A Key Catalog (one row per dataset):

  • Dataset name, grain, natural key fields, surrogate key (if any), uniqueness check status, de‑dup rule summary, reason codes, and data owner.

• A dedicated de‑dup view/table with:

  • winner_flag, winner_rank, reason_code, chosen_by_rule, as_received_id (ar_id in the examples), and source_priority.

• Immutable raw + curated layers:

  • Raw is append‑only. Curated is reproducible from raw + rules.

• Identifier policy:

  • MRN/SSN stay with the data holder (the site) and out of study datasets; any other disclosure needs explicit approval with controls (access list, purpose, retention, and destruction plan). Delivery logs maintained.

Worked examples

All examples are self‑contained with small sample data and SQL you can run in a scratch database (never a study database) that supports standard window functions.

Example 1 — Vitals with duplicate rows

Goal: Make vitals_curated unique by (subject_id, visit, param) before joining to other data.

Sample data

-- as_received_vitals (raw)
create table as_received_vitals (
  ar_id int primary key,
  subject_id varchar(20),
  visit varchar(20),
  param varchar(10),   -- SBP, DBP, HR
  value_num decimal(6,2),
  position varchar(10), -- Sitting/Standing
  source_file varchar(50),
  recorded_at timestamp
);

insert into as_received_vitals values
(1,'S001','Week12','SBP',132,'Sitting','site_upload_1.csv','2025-06-01 09:10'),
(2,'S001','Week12','SBP',128,null,'site_upload_1.csv','2025-06-01 09:12'), -- missing position
(3,'S001','Week12','DBP',82,'Sitting','site_upload_1.csv','2025-06-01 09:10'),
(4,'S001','Week12','SBP',130,'Sitting','site_upload_2_fix.csv','2025-06-02 14:05'); -- correction

Uniqueness check:

select subject_id, visit, param, count(*) as n_rows
from as_received_vitals
group by subject_id, visit, param
having count(*) > 1;

It returns S001, Week12, SBP with 3 rows: the table has 4 rows but 2 distinct keys.

De‑dup rule: (1) prefer the latest recorded_at; (2) break ties by completeness (non‑null position); (3) otherwise take the earliest ar_id.

-- Rank candidates per composite key (subject_id, visit, param)
create view vitals_curated as
with ranked as (
  select v.*,
         row_number() over (
           partition by subject_id, visit, param
           order by recorded_at desc,
                    (case when position is not null then 0 else 1 end),
                    ar_id
         ) as winner_rank,
         count(*) over (partition by subject_id, visit, param) as n_candidates,
         count(*) over (partition by subject_id, visit, param, recorded_at) as n_same_time
  from as_received_vitals v
)
select ar_id, subject_id, visit, param, value_num, position,
       source_file, recorded_at,
       case when n_candidates = 1 then 'only_record'
            when n_same_time = 1  then 'latest_recorded_at'
            else 'tie_break' end as reason_code
from ranked
where winner_rank = 1;

Result

For S001, Week12, SBP the winner is ar_id=4 (the correction), making the vitals table key‑unique: 2 rows. The three different SBP values are near duplicates: raise a data query to the site (repeat readings need a sequence number in the key).

Example 2 — Central lab sends two transfers for the same period

Goal: Keep the most recent transfer per lab result key and retain lineage.

Sample data

-- as_received_lab
create table as_received_lab (
  ar_id int primary key,
  subject_id varchar(20),
  specimen_id varchar(30),
  analyte varchar(20),       -- e.g., ALT
  result_num decimal(10,3),
  unit varchar(10),
  transfer_batch int,        -- vendor batch #
  file_received_at timestamp -- when we received the file
);

insert into as_received_lab values
(10,'S010','A-1001','ALT',45.2,'U/L', 7,'2025-05-21 08:00'),
(11,'S010','A-1001','ALT',44.9,'U/L', 8,'2025-05-22 11:30'); -- corrected

Key: (subject_id, specimen_id, analyte).

Rule: Highest transfer_batch (or latest file_received_at) wins.

create view lab_curated as
with ranked as (
  select r.*,
         row_number() over (
           partition by subject_id, specimen_id, analyte
           order by transfer_batch desc, file_received_at desc, ar_id desc
         ) as winner_rank
  from as_received_lab r
)
select *
from ranked
where winner_rank = 1;

-- lineage (mapping curated winners back to raw)
create view lab_lineage as
select l.ar_id   as winning_ar_id,
       r.ar_id   as source_ar_id,
       r.transfer_batch as source_batch
from lab_curated l
join as_received_lab r
  on r.subject_id = l.subject_id
 and r.specimen_id = l.specimen_id
 and r.analyte = l.analyte;

Result

Only the corrected record (batch 8) lives in lab_curated, and lab_lineage shows both the winner and the superseded raw row. The changed value (45.2 → 44.9) is a near duplicate: have the vendor confirm that batch 8 is a correction.

Example 3 — Joining after de‑dup to avoid row multiplication

Scenario: Join curated vitals to demographics.

-- demographics (one row per subject)
create table dm (
  subject_id varchar(20) primary key,
  sex varchar(1),
  age integer
);

insert into dm values ('S001','F',58), ('S010','M',63);

-- Correct: join curated vitals (unique per subject+visit+param)
select v.subject_id, v.visit, v.param, v.value_num, d.sex, d.age
from vitals_curated v
left join dm d on d.subject_id = v.subject_id;

Why not join raw?

The curated join returns 2 rows; the raw join returns 4, because all three SBP rows pass through, and a later join to a table with 2 rows for that key (e.g., meds with a duplicate) would turn the three into 6 (3×2). De‑dup first.

Example 4 — Protecting identifiers while enabling linkage

Need: Keep a site’s lab and pharmacy records, both keyed by MRN, linkable to each other across files without the MRN leaving the site (for enrolled participants the site sends the study ID instead).

Approach:

• The site (the data holder) generates a token per MRN in a secure mapping table that only its authorized staff can open:

  • mrn_token_map(mrn plaintext, mrn_token random_uuid, created_at, created_by)

• Share mrn_token externally, never the plaintext MRN.

• Keep an export log: recipient, fields shared, count of rows, purpose, approval id, and destruction date.

Pitfalls & misconceptions

• Joining before de‑dup This multiplies duplicates across the result set and silently corrupts counts and rates.

• Deleting or hiding duplicates Never delete as‑received rows or hide duplicates with select distinct; report them to their source.

• Assuming parts of a composite key are unique visit is not unique; param is not unique; only the combination is.

• Treating formatting differences as distinct keys 00123 vs 123, A-1001 vs A1001—decide a canonical form (trim, upper, remove dashes) and keep the original.

• Case sensitivity & collation drift Joins can fail or duplicate if one system is case‑sensitive and the other isn’t. Normalize casing explicitly.

• Missing values inside keys A null in any key part means the composite key is incomplete; keep the row out of the curated view and send it back to its source (site or vendor); never fill the gap by hand.

• Identifier sprawl Exporting MRN/SSN “just in case” is not acceptable. Use study IDs or tokens; log every disclosure.

Practice in the wild (checklist)

• For each dataset, fill a Key Catalog: dataset, grain, key columns, natural vs surrogate, uniqueness status, de‑dup rule, owner.
• Run duplicate reports: count(*) vs count(distinct key); list top duplicate keys and row counts.
• Build a de‑dup view using window functions; include winner_flag, winner_rank, reason_code.
• Keep raw immutable; rebuild curated from raw + rules (no manual edits).
• Normalize key formatting (trim, uppercase, strip punctuation) but preserve original columns.
• Join only curated, key‑unique tables.
• Minimize identifier exports; when needed, use tokens; maintain an export log with approvals and retention.

Key points to remember

• Define the grain and key explicitly for every dataset (e.g., vitals are unique by (subject, visit, param)), and make your joins and checks align to that grain.
• Verify uniqueness and de‑dup before any join. Require the join key to be unique on at least one side; otherwise rows will multiply.
• Design joins to respect grain. In a one‑to‑many join the result inherits the child table’s grain—confirm it matches the level you intend to analyze.
• Use deterministic de‑dup rules with clear precedence (authority → recency → adjudication → completeness → stable tie‑break) and carry evidence of why the winner was chosen (e.g., reason_code). Duplicates are findings for their source; never delete or edit as‑received rows by hand.
• Maintain lineage and reproducibility. Keep immutable raw data; rebuild curated from raw + rules; keep a lineage map from winners to all candidates with timestamps and operators.
• Minimize identifier exposure. Study datasets carry study IDs, no direct identifiers; when linkage is needed, use tokens; export the minimum necessary with approvals, purpose, retention, and delivery logs. MRN/SSN stay with the data holder (the site) and out of study datasets; any other disclosure needs a documented need, approval and controls—otherwise do not export.