Keys, Grain & Joins

Why this matters

Most reconciliation errors trace back to wrong grain or wrong join. If you don’t first decide the unit of a row (the grain) and the fields that uniquely identify it (the keys), then joins can multiply rows unexpectedly, inflate counts, and hide real gaps. Decide the unit of comparison first; everything else follows.

Core concepts & definitions

Grain (level of detail / LOD). What one row represents. Examples you’ll see in clinical data:

Subject (one row per subject)
Subject‑visit (one row per subject per scheduled visit)
Subject‑visit‑test (e.g., vitals or labs at a visit for a specific test)
Specimen (one row per collected tube)
Adverse event episode (one row per AE with dates)

Keys. The minimal set of fields that uniquely identify a row at the declared grain. Often composite.

Natural keys come from the business (e.g., subject_id, visit, test_code).
Surrogate keys are system‑assigned (e.g., vs_id, ae_id).
Keys should be stable, not nullable, and cleaned for casing, spaces, and leading zeros.
If uniqueness fails at the declared grain, either the grain is wrong or the data needs de‑duplication. De‑duplicate in your program by a documented rule (for example, latest record by update time), list the rows you drop, and query unexplained duplicates to their owner: the site corrects EDC (a system‑caused duplicate is removed only by an approved data‑correction script), and a vendor re‑delivers its file. Never edit or delete source data yourself.

Cardinality. How many rows on one side match a row on the other:

1:1 (demography ↔ randomization record)
1:M (demography ↔ vitals)
M:M (subject ↔ procedures via a bridge)

Join types (intent first, syntax second).

INNER JOIN = keep matches that exist in both sides (the intersection).
LEFT JOIN = keep all left rows; right side fills null when missing (left completeness).
FULL OUTER JOIN = keep all rows from both; best for presence gap analysis.
SEMI / ANTI joins (often written with EXISTS / NOT EXISTS) return each left row once if it has / doesn’t have a match—useful for filters without multiplying rows.

Sets vs joins.

INTERSECT on key columns gives the distinct keys present in both sources.
Joins preserve multiplicity; they can duplicate rows if there are duplicates on either side.

Rule of thumb: If your goal is “Who is in both lists?”, think set. If your goal is “Bring columns together, row‑by‑row”, think join, but verify cardinality first.

Join‑safe workflow

Declare grain Write it at the top of your SQL: -- Grain: subject_id + visit + test_code (vitals)

Validate keys

Check uniqueness:

-- assert no duplicate keys at vitals grain
SELECT subject_id, visit, test_code, COUNT(*) AS n
FROM vitals
GROUP BY 1,2,3
HAVING COUNT(*) > 1;

Standardize join fields (trim, upper, zero‑pad) before joining.

Choose join to match intent
- Presence gaps → FULL OUTER JOIN with side flags.
- Intersection → INNER JOIN (or INTERSECT on keys).
- Left completeness → LEFT JOIN.
Align grains before join
- If one table is more granular, aggregate or pick latest (e.g., most recent vitals per visit) so both sides represent the same thing per row.
Verify counts & sample
- Compare expected vs resulting rows after each step.
- Investigate mismatches using samples of duplicated keys.

Common patterns & guardrails

One‑to‑many joins inherit the child grain. Joining Demography (subject) to Vitals (subject‑visit‑test) yields subject‑visit‑test rows. Aggregate after the join (or aggregate the child first) if you need subject‑level results; with a unique parent key, both give the same result.
Bridge tables for many‑to‑many. Don’t join subject directly to procedure attributes if a subject can have multiple procedures and each procedure maps to multiple categories. Use a bridge (e.g., subject_procedure) then aggregate or distinct the result to your target grain.
Latest‑record pattern. When a system holds histories, select the “current” record first:
```
SELECT *
FROM (
  SELECT d.*, 
         ROW_NUMBER() OVER (PARTITION BY subject_id ORDER BY updated_at DESC) AS rn
  FROM demography_history d
) x
WHERE rn = 1;
```
Add a tie‑breaker to ORDER BY (for example, a record ID) so rows with equal updated_at cannot swap between runs.

Audit helper columns (traceability). Carry source_table, join_reason, and a key hash:

SELECT MD5(CONCAT_WS('|', COALESCE(subject_id,''), COALESCE(visit,''), COALESCE(test_code,''))) AS key_hash, ...

Null‑safe joins. Avoid joining on nullable fields: nulls never match, so missing‑key rows silently drop out; list them and query their owner. A shared placeholder such as COALESCE(field, '<MISSING>') on both sides pairs every missing‑key row with every other one (false matches, row blow‑up); use it only for an optional key part (for example, an unscheduled‑visit number) whose blank means the same on both sides.
FULL OUTER not supported? Emulate it: take the LEFT JOIN, then UNION ALL the right‑side rows with no match (an anti‑join, written with NOT EXISTS). A LEFT JOIN UNION ALL a RIGHT JOIN would list every matched row twice.

Worked examples (CV trial)

All examples are self‑contained: the CTEs (WITH blocks) hold the sample data and run as‑is in PostgreSQL. In follow‑up queries, paste the join above in place of /* previous join */. We’ll use a hypothetical cardiovascular (CV) trial with simplified data.

1) Vitals (child) ↔ Demography (parent) — grain inheritance

Exam tie‑in: Q033 (the grain a one‑to‑many join inherits).

WITH demography AS (
  SELECT * FROM (VALUES
    ('SUBJ001','M',55,'Site01'),
    ('SUBJ002','F',63,'Site01'),
    ('SUBJ003','M',71,'Site02')
  ) AS t(subject_id, sex, age, site_id)
),
vitals AS (
  -- Grain: subject_id + visit + test_code
  SELECT * FROM (VALUES
    ('SUBJ001','V1','HR',  72),
    ('SUBJ001','V1','SBP',120),
    ('SUBJ001','V2','HR',  70),
    ('SUBJ002','V1','HR',  80)
  ) AS t(subject_id, visit, test_code, result_value)
)
SELECT v.subject_id, v.visit, v.test_code, v.result_value,
       d.sex, d.age, d.site_id
FROM vitals v
JOIN demography d
  ON d.subject_id = v.subject_id;

What to notice

Result grain = vitals grain (subject_id + visit + test_code).
Demography columns repeat for each vital. That’s expected and safe.

If you need one row per subject, aggregate after the join:

SELECT subject_id,
       AVG(CASE WHEN test_code='HR'  THEN result_value END) AS avg_hr,
       MAX(CASE WHEN test_code='SBP' THEN result_value END) AS max_sbp
FROM (/* previous join */) j
GROUP BY subject_id;

Result: SUBJ001 avg_hr 71, max_sbp 120; SUBJ002 avg_hr 80, max_sbp null. SUBJ003 is missing: the inner join dropped them (no vitals). To keep every subject, start from demography, LEFT JOIN vitals and take subject_id from demography.

2) Intersection of subject lists (EDC vs PV) — who’s in both?

Exam tie‑in: Q014 (intersection vs left completeness).

Both lists hold subjects with at least one serious adverse event (SAE), from EDC and from the pharmacovigilance (PV) safety database. Comparing all enrolled subjects would flag everyone who never had an SAE.

WITH edc_subjects AS (
  SELECT * FROM (VALUES
    ('SUBJ001'), ('SUBJ002'), ('SUBJ004')
  ) AS t(subject_id)
),
pv_subjects AS (
  SELECT * FROM (VALUES
    ('SUBJ001'), ('SUBJ003'), ('SUBJ004')
  ) AS t(subject_id)
)
-- Option A: INNER JOIN
SELECT e.subject_id
FROM edc_subjects e
JOIN pv_subjects  p
  ON p.subject_id = e.subject_id;

-- Option B: INTERSECT (distinct keys only)
-- SELECT subject_id FROM edc_subjects
-- INTERSECT
-- SELECT subject_id FROM pv_subjects;

Output contains SUBJ001 and SUBJ004 only. Use this when you want only common subjects.

3) Presence gaps between two systems — who’s missing where?

Exam tie‑in: Q014 (FULL OUTER for gaps vs INNER for overlap).

WITH edc_subjects AS (
  SELECT * FROM (VALUES ('SUBJ001'),('SUBJ002'),('SUBJ004')) AS t(subject_id)
),
pv_subjects AS (
  SELECT * FROM (VALUES ('SUBJ001'),('SUBJ003'),('SUBJ004')) AS t(subject_id)
)
SELECT 
  COALESCE(e.subject_id, p.subject_id)    AS subject_id,
  CASE WHEN e.subject_id IS NOT NULL THEN 1 ELSE 0 END AS in_edc,
  CASE WHEN p.subject_id IS NOT NULL THEN 1 ELSE 0 END AS in_pv
FROM edc_subjects e
FULL OUTER JOIN pv_subjects p
  ON p.subject_id = e.subject_id
ORDER BY subject_id;

Output: SUBJ001 and SUBJ004 in both, SUBJ002 in EDC only, SUBJ003 in PV only.

Use it to drive reconciliation:

in_edc=1 & in_pv=0: SAE in EDC, no PV case → PV follows up with the site, which sends the SAE report to PV if it was never reported (sites report each SAE to PV directly).
in_edc=0 & in_pv=1: PV case, no SAE in EDC → DM queries the site, which enters the SAE in EDC.

4) Aligning grains before comparing values — PV case vs AE episodes

You can’t compare PV case‑level severity to EDC AE‑episode severity without alignment. (Simplified: here the PV side is one summary row per subject, but a PV case is an event report and SAE reconciliation matches serious events one by one; in practice keep only serious AEs on the EDC side and match on subject + event term + onset date.)

WITH pv_case AS (
  -- one row per subject (case-level)
  SELECT * FROM (VALUES
    ('SUBJ001','Severe'),
    ('SUBJ002','Moderate')
  ) AS t(subject_id, worst_severity_overall)
),
edc_ae AS (
  -- multiple AEs per subject (episode-level)
  SELECT * FROM (VALUES
    ('SUBJ001', '2024-02-01','Moderate'),
    ('SUBJ001', '2024-02-15','Severe'),
    ('SUBJ002', '2024-03-01','Mild')
  ) AS t(subject_id, ae_start_dt, ae_severity)
),
ae_rollup AS (
  -- Align to subject-level grain first
  SELECT subject_id,
         MAX(ae_severity) AS worst_severity_overall
  FROM edc_ae
  GROUP BY subject_id
)
SELECT a.subject_id,
       a.worst_severity_overall AS edc_worst,
       p.worst_severity_overall AS pv_worst
FROM ae_rollup a
FULL OUTER JOIN pv_case p
  ON p.subject_id = a.subject_id;

Lesson: Align both sides to the same grain (subject‑level) before joining.

Result: SUBJ001 matches (Severe); SUBJ002 differs (Mild in EDC, Moderate in PV) and goes to reconciliation.

Caution: MAX on text works here only because Mild, Moderate and Severe happen to sort alphabetically; a value such as Life‑threatening sorts before Mild, so rank severity (Mild = 1, Moderate = 2, Severe = 3) before taking the maximum.

5) Many‑to‑many blow‑up — detect and fix

Suppose each subject has multiple procedures and each procedure maps to multiple categories.

WITH subject_procedure AS (
  SELECT * FROM (VALUES
    ('SUBJ001','PROC_A'),
    ('SUBJ001','PROC_B'),
    ('SUBJ002','PROC_A')
  ) AS t(subject_id, procedure_code)
),
procedure_category AS (
  SELECT * FROM (VALUES
    ('PROC_A','CAT_X'),
    ('PROC_A','CAT_Y'),
    ('PROC_B','CAT_Y')
  ) AS t(procedure_code, category)
)
-- Naïve join (grain explodes to subject x procedure x category)
SELECT sp.subject_id, sp.procedure_code, pc.category
FROM subject_procedure sp
JOIN procedure_category pc
  ON pc.procedure_code = sp.procedure_code;

Row multiplication is expected here. If your target is subject‑level, aggregate back:

SELECT subject_id,
       COUNT(DISTINCT procedure_code) AS n_procedures,
       COUNT(DISTINCT category)       AS n_categories
FROM (/* previous join */) j
GROUP BY subject_id;

Result: 5 rows from 3 subject‑procedure rows; SUBJ001 has 2 procedures and 2 categories. Without DISTINCT, COUNT(procedure_code) would give it 3 procedures.

Guardrail: Before a join, check for duplicate keys on both sides. If both are non‑unique, you have an M:M that requires aggregation or a bridge strategy.

Pitfalls & misconceptions

“Selecting fewer columns reduces rows.” Projection changes width, not row count.
Joining detailed tables without de‑duping causes cardinality explosions. Always assert key uniqueness at the intended grain.
Using LEFT when you meant intersection. A LEFT JOIN with WHERE right.id IS NOT NULL behaves like INNER JOIN, but if you filter differently later you may accidentally keep non‑matches.
Joining on dirty keys. Case, spaces, leading zeros, or trailing characters cause silent mismatches. Standardize both sides first.
Relying on surrogate IDs across systems. Surrogates often differ between systems; prefer natural keys (plus site) for cross‑system joins.
Ignoring nulls in join keys. Nulls don’t match; list them explicitly and query their owner to fix them at source.

Practice checklist

Write the grain and keys at the top of each SQL file.
Assert key uniqueness at that grain (if it fails, revisit the grain, de‑dup by a documented rule, or query the owner).
Standardize join fields (trim, upper, zero‑pad) before joining.
Pick join type to match intent (presence gaps vs intersection vs left completeness).
Align grains (aggregate or “latest” selection) before comparison.
After each join, verify row counts and sample duplicated keys.
Carry audit helper columns (source_table, join_reason, key_hash) for traceability.

Key points to remember

Declare the grain first. Write what one row represents (e.g., subject, subject‑visit, subject‑visit‑test) before you do anything else.
Choose minimal, stable keys at that grain. Prefer natural keys for cross‑system work; avoid nullable keys. Standardize casing, spacing, and zero‑padding before joins. If uniqueness fails, de‑duplicate or revisit the declared grain.
Pick the join to match your intent.
- Intersection of two lists → INNER JOIN (or INTERSECT on keys).
- Left completeness → LEFT JOIN (keep all left rows).
- Presence gaps across systems → FULL OUTER JOIN with side flags (or emulate with a LEFT JOIN plus UNION ALL of the unmatched right‑side rows).
Verify cardinality up front. Check for duplicate join keys on both sides. A 1:M join is expected; an M:M join signals potential blow‑ups—aggregate, DISTINCT, or use a bridge table.
Remember grain inheritance. A 1:M join adopts the child’s grain; aggregate back (e.g., COUNT DISTINCT, MAX, pivots) if your target is subject‑level.
Align grains before comparison. When datasets are at different levels (e.g., AE episodes vs PV case‑level), roll up or select the latest/current record so both sides represent the same unit per row.
Use set logic when you only need membership. INTERSECT (over keys) for overlap; EXISTS/NOT EXISTS (semi/anti) to filter without multiplying rows.
Treat nulls explicitly. Avoid joining on nullable fields; list null‑key rows separately, and use a shared COALESCE placeholder only for an optional key part whose blank means the same on both sides, never for a required one such as subject ID or visit.
Trust but verify. After each join, compare expected vs actual row counts and inspect samples of duplicated keys.
Carry traceability. Include helper columns like source_table, join_reason, and a computed key_hash to audit how rows were matched.

CDM Prep is an independent study resource and is not affiliated with or endorsed by SCDM. CCDM® is a registered mark of SCDM.

What you'll learn