Why this matters
Clinical data lives in many systems: EDC (Electronic Data Capture), PV (Pharmacovigilance safety database), IVRS/IWRS (interactive response systems for randomization; also called IXRS or IRT), ePRO (patient diaries), central labs, imaging, and more. When these disagree, locks slip and inspections get tense. The good news: almost every mismatch fits one of four repeatable patterns. If you can name the pattern, you can pick the right keys, the right join, the right owner, and the right fix—fast.
Core concepts & definitions
Grain (level of detail) The unit you compare: subject, subject‑visit, subject‑visit‑test, specimen, dose event, etc. Getting grain wrong creates “phantom” mismatches.
Keys (how you identify a row) Often composite: e.g.,
Study + Subject + Visit + Timepoint + Lab Test Code. Keys must reflect the grain (child grain when joins are one‑to‑many).Normalization (make values comparable first) Align units (e.g., g/dL vs g/L), time zones (site local vs UTC), code lists (local test names → standard codes), and rounding rules before comparing.
Tolerance (how close counts as a match) A margin agreed in advance and written in the spec (e.g., collection times within ±30 minutes; values equal after rounding). Too tight, or an exact match on a date in the key, creates false mismatches; too loose hides real ones.
The four reconciliation patterns
- Presence — Exists in A but not in B.
- Identity — Both exist, but keys don’t actually point to the same thing (wrong subject ID, swapped specimen, wrong test code).
- Timing — Same item, but timestamps are outside allowed windows/tolerances.
- Value — Same item, same time, but values differ after normalization.
Source of truth (SoT) For each domain and field, define which system wins by default (e.g., lab result value from central lab, visit and collection dates from the site’s records as entered in EDC/CRF, treatment assignment from IVRS/IWRS).
SLA (Service‑Level Agreement) The turnaround for each owner to respond/resolve (e.g., vendor re‑deliver in 3 business days, site query response in 5 business days).
The process, step‑by‑step
Declare the grain Write it down (e.g., “Lab reconciliation grain = Subject + Specimen ID + Analyte + Collection DateTime”).
Choose keys that match the grain Include all columns needed to uniquely identify a row at that grain. De‑duplicate each source before joining; an unexpected duplicate is a discrepancy for the source’s owner, not a row to drop silently.
Check structural conformance Validate file format, required columns, allowed values, and code translations. Only then proceed to counts/logic/reconciliation.
Normalize Convert units, map codes, standardize time zones and rounding.
Select the join in staging Use a full comparison (conceptually a FULL OUTER join) to reveal presence gaps (an INNER join keeps only matched rows and hides them). Then split in order: one‑sided rows are presence gaps, unless a near‑match on the other side (the same SAE under another onset date) shows an identity or timing problem; matched rows are checked for timing, then value.
Classify & route Apply a decision table to send each discrepancy type to the correct owner with the correct SLA.
Fix → re‑test → log Each fix is made by the data’s owner (the site in EDC, the vendor by re‑delivery, PV in the safety case); data management (DM) raises the query and never edits either side. Track each discrepancy to closure in a reconciliation log; rerun the checks after corrections or vendor re‑deliveries; trend counts over time.
What good looks like
- A one‑page Reconciliation Spec that states grain, keys, tolerances, SoT, and routing rules.
- A decision table mapping discrepancy → owner → action → SLA.
- Automated runs with counts per category (presence/identity/timing/value), plus backlog and aging.
- Version‑controlled scripts/config, reproducible outputs, and an auditable reconciliation log with provenance.
- A simple dashboard (e.g., by site/vendor) to target interventions.
Example decision table
| Discrepancy type | Typical cause | Owner | Action | SLA |
|---|---|---|---|---|
| EDC present, vendor missing | Cut timing; file omission | Vendor | Re‑deliver file; confirm extract window | 3 biz days |
| Vendor present, EDC missing | Missed or late entry; filed under another visit or subject | Site | Complete the CRF entry or explain (the vendor holds a record, so “Not Done” cannot be right) | 5 biz days |
| Identity mismatch | Wrong Subject/Specimen mapping; test code map | Vendor / Site | Test code or duplicate in the file: vendor re‑delivers. Subject, visit or specimen ID: site checks its records first | 3–5 biz days |
| Timing out of window | Late visit; time‑zone conversion error | Site (date) / DM (own conversion) | Site confirms the date (a real late visit is a protocol deviation); DM fixes its conversion; analyzability per the statistical analysis plan (SAP) | 5 biz days |
| Value mismatch | Transcription error; unit mismatch | Site (CRF) / Vendor (units) | Correct CRF or confirm unit conversion | 5 biz days |
| Randomization vs EDC | Baseline date entered wrongly; Baseline done after randomization | Site (DM checks IVRS/IWRS logs) | Site corrects the Baseline date or a protocol deviation is documented | 3–5 biz days |
| SAE in PV not in EDC | Site reported the SAE to PV but did not record it in EDC | Site | Enter SAE details in EDC; cross‑ref case numbers | 5 biz days |
| Serious AE in EDC not in PV | SAE report not sent, or held in PV under another term or date | PV checks → Site (DM alerts PV at once) | PV checks for a matching case; if none, the site reports the SAE to PV immediately | Immediate |
| Central lab missing, CRF present | Sample not received; specimen mislabeled | Lab vendor → Site | Once the cut has passed, lab confirms sample receipt; if not received, site clarifies (not drawn, lost, re‑collect) | 3 + 5 biz days |
Worked examples
1) Pharmacovigilance (PV) vs EDC — Serious Adverse Events (SAEs)
Scenario. PV lists 3 SAEs for Subject 1001; EDC has only 1 SAE form.
Grain & keys. Subject + SAE Start Date + Event Term (plus PV Case ID for traceability); the start date is matched within the written tolerance and the term after coding, so the same SAE recorded a day apart is not counted as two gaps.
Normalization. Ensure dates are local site time; compare terms at the MedDRA (Medical Dictionary for Regulatory Activities) Preferred Term level, not as verbatim text.
Join & classify. Full comparison shows presence gaps (PV yes / EDC no) for 2 events.
Actions. DM queries the Site to enter the SAE forms in EDC from its source records; PV provides the case IDs so each query points to the right event.
Close. After entry, verify identity by cross‑referencing PV Case ID on the CRF; mark reconciled in the log. The reverse gap (a serious AE in EDC with no PV case) is urgent: DM alerts PV at once; if PV holds no matching case under another term or date, the site reports the SAE to PV directly.
2) Central Lab vs EDC — Chemistry Panel
Scenario. EDC has a Chemistry page marked “Collected” for Visit 3; no corresponding lab record arrived.
Grain & keys. Subject + Specimen ID + Collection DateTime + Analyte. The CRF has one row per sample, the lab file one per analyte: check presence at specimen grain, values at analyte grain.
Normalization. Time‑zone standardization (keep site local time as collected; derive UTC); unit table (e.g., mmol/L vs mg/dL).
Join & classify. Presence gap (EDC yes / Lab no).
Actions. Check the transfer cut first: a result finalized after the cut is due in the next delivery. If overdue, the lab vendor confirms by Specimen ID whether the sample was received.
- If not received: Site clarifies whether sample was actually drawn; if not, update CRF to Not Collected (with reason).
- If received: Vendor re‑delivers; DM checks identity (Specimen ID) and value after unit conversion.
Close. Record outcome and any unit conversions used; trend repeated sites for targeted training.
3) Randomization (IVRS/IWRS) vs EDC — Baseline & Treatment Dates
Scenario. Subject 1023 shows randomization on 15‑Jan in IVRS; EDC Baseline visit is 16‑Jan.
Grain & keys. Subject + Randomization Number. In a blinded study, only unblinded staff see the treatment arm.
Normalization. Verify IVRS timestamp and site time zone; ensure EDC Baseline is the intended anchor visit.
Join & classify. Timing discrepancy (sequence should be: Baseline ≤ Randomization).
Actions. Query Site to confirm visit dates; either correct Baseline date or document protocol deviation. DM validates against IVRS audit trail.
Close. Update the deviation log if a deviation is confirmed; ensure the randomization number and kit assignment remain consistent.
4) ePRO (Patient Diary) vs EDC — Dose Taken Flags
Scenario. ePRO shows Evening Dose “Taken” on 10‑Mar; EDC Study Drug Dosing page lacks that entry.
Grain & keys. Subject + Calendar Date + Dose Window (AM/PM).
Normalization. Align calendars and cut windows (define what counts as “Evening” locally).
Join & classify. Presence (ePRO yes / EDC no).
Actions. Site reviews with participant; if dose truly taken, update EDC per protocol (some programs don’t duplicate ePRO‑captured medication—respect SoT). The diary entry is the participant’s own record: neither the site nor DM edits it; a correction, if ever needed, goes through the diary vendor’s documented data‑change process at the investigator’s request.
Close. Document SoT decision so teams stop opening unnecessary queries.
5) Imaging Vendor vs EDC — Assessment Window
Scenario. EDC says Tumor Assessment due Week 12 (±7 days); imaging occurred on Day 94, 2 days after the window closed (randomization = Day 1, so Week 12 = Day 85; window Days 78–92).
Grain & keys. Subject + Visit (Week 12) + Assessment Type; the assessment date is what you compare, so it stays out of the key.
Normalization. Compute visit windows from randomization; keep window rules in the spec.
Join & classify. Timing out of window.
Actions. Site confirms the scan date; a truly late scan is a protocol deviation, not a data error. Whether it is “late but evaluable” or “not analyzable” follows the protocol and the statistical analysis plan (SAP), not DM or the site; ensure downstream analysis flags match.
Close. Keep an exceptions list so the analysis team gets the same answers later.
Pitfalls & misconceptions
- Wrong grain. Joining lab results at visit grain instead of specimen grain inflates mismatches.
- Joining before de‑duping. Duplicates create false many‑to‑many mismatches.
- Comparing before normalizing. Units/time zones/rounding must be aligned first.
- Zero vs null. “0” (a real value) ≠ blank (missing). Treat separately.
- Assuming projection changes counts. Selecting fewer columns changes width, not rows.
- Window confusion. Use explicit, written tolerances (e.g., “±7 calendar days, local site time”).
- Wrong owner. A CRF result missing from the lab transfer (after its cut) goes to the lab first, not the site; a lab result with no CRF entry goes to the site.
Practice in the wild (checklist)
- Write the grain and list the keys for this reconciliation.
- De‑dup each source to that grain before any join.
- Normalize units, time zones, and codes.
- Run a full comparison and split into presence/identity/timing/value buckets.
- Apply your decision table (owner → action → SLA).
- Re‑test after fixes; update the reconciliation log with outcomes and evidence.
- Trend counts and aging; escalate persistent patterns (by site/vendor).
- Confirm SoT for each field to avoid circular edits.
Key points to remember
- Reconciliation follows four patterns in order: presence → identity → timing → value; classify results accordingly.
- Declare the grain and pick composite keys that uniquely identify a row at that grain; child joins inherit the child grain.
- Normalize first, compare second—align units, time zones, code lists, and rounding; write down tolerance windows (e.g., ±7 days, local site time).
- Verify structural conformance (file format, required columns, code translations) before doing counts, logic, or value comparisons.
- Choose the right join for the question: use a full comparison (conceptual FULL OUTER join) to surface gaps; use an INNER JOIN when you need the intersection (records common to both sources).
- Route discrepancies with a simple decision table (type → owner → action → SLA), and log every case for auditability and trending.
- Practical rule: for a central lab presence gap (CRF present, lab missing, cut passed), confirm sample receipt with the lab before querying the site.