External Data Reconciliation for Clinical Data Managers: From Vendor Transfer to Database Lock Readiness

Introduction

External data reconciliation is a CDM control process - not just a matching exercise.

Clinical trials routinely rely on data generated outside the EDC: central lab results, ECG vendor outputs, ePRO diaries, IRT randomisation data, imaging reads, PK samples, and safety reports. Before any of this data can be used in listings, analyses, or regulatory submissions, it must be reconciled against expected study records to confirm it is complete, consistent, traceable, and protocol-aligned.

Vendor data becomes valuable only when it can be verified, reconciled, and used confidently.

What Is External Data Reconciliation?

A controlled process of comparing vendor/external data against expected study records to confirm the data is:

• Complete - All expected data is received and accounted for
• Consistent - Data values align across systems (EDC, vendor file, CTMS)
• Traceable - Every data point has a clear, documented source
• Protocol-aligned - Data matches study rules, visit schedules, and data definitions
• Analysis-ready - Data is accurate, reconciled, and fit for downstream use

The Data Transfer Specification (DTS) is the governing document for external data reconciliation. It defines matching rules, file format requirements, transfer frequency, expected fields, subject ID format, controlled terminology, and discrepancy resolution process for each vendor. All reconciliation is performed against the DTS. A finding not explained by the DTS is a gap in the DTS - not a judgement call for the CDM.

Common External Data Sources

Data Source	What Is Reconciled
Central Lab	Hematology, chemistry, biomarker results against EDC lab forms
ECG Vendor	Intervals, interpretations, and collection times against EDC ECG forms
eCOA / ePRO	Diary entries, questionnaire responses, PRO scale scores against EDC records
IRT / Randomisation	Randomisation assignments, kit assignments, and dosing support data
Imaging Vendor	MRI, CT, and reader assessment data against imaging status in EDC
Wearables / Devices	Heart rate, activity, sleep, and sensor data against device records
PK / PD Lab	PK sample data, PD biomarkers, and collection times against EDC PK forms
Safety / PV	AE/SAE consistency between EDC safety data and pharmacovigilance outputs

Why Reconciliation Matters

Six categories of data quality risk from poor reconciliation:

1. Missing Endpoint or Safety Data - External data that never made it into the reconciled dataset is missing from analysis without anyone knowing
2. Incorrect Visit or Timepoint Mapping - Lab results linked to the wrong visit, PK samples at the wrong timepoint - among the most difficult errors to detect after lock
3. Unresolved Protocol Deviation Questions - Discrepancies may reflect a deviation (e.g., sample outside the protocol window) that is undocumented if not reconciled
4. Delayed Database Lock - Outstanding reconciliation issues block the data review cycle; reconciliation left until the lock window is the leading cause of external-data lock delays
5. Audit Trail and Traceability Gaps - Regulatory inspectors expect documented reconciliation: what was compared, what was found, what was done, and who approved it
6. Incorrect Downstream Analysis Inputs - Unreconciled data produces incorrect summaries and tables requiring costly post-lock corrections

Reconciliation protects data integrity before external data reaches analysis.

Key Matching Fields

Field	Purpose
Study / Site ID	Confirms correct study and site context
Subject ID	Confirms correct participant across systems
Visit / Visit Number	Confirms visit mapping between vendor and EDC
Timepoint	Confirms protocol-required collection point
Collection Date / Time	Supports timing and protocol window checks
Test / Assessment Name	Confirms the correct data type is being matched
Accession / Sample ID	Supports lab and sample-level traceability
Status / Not Done Reason	Explains missing or incomplete data

Never guess at a match. Use predefined matching rules documented in the DTS and document every exception. Subject ID format consistency is one of the most frequently overlooked reconciliation failure points - resolve format differences at DTS design and confirm in UAT.

Common Reconciliation Issues

1. Vendor Record Exists, EDC Record Missing - Vendor delivered data for a subject/visit but no corresponding EDC record exists
2. EDC Shows Collected, Vendor Data Missing - EDC indicates assessment completed but no vendor data received
3. Subject ID / Site ID / Randomisation ID Mismatch - Identifiers do not match due to format inconsistency (leading zeros, hyphens) or incorrect assignment
4. Visit or Timepoint Mismatch - Vendor records the assessment against a different visit or timepoint than the EDC
5. Collection Date / Time Mismatch - May indicate a protocol deviation (window violation) or a data entry error
6. Duplicate Vendor Records - Vendor file contains more than one record for the same subject/visit/assessment
7. Wrong Test Name, Unit, or Controlled Terminology - Lab, ECG, and assessment data must align with CDISC controlled terminology used in the EDC and target SDTM dataset. Terminology mismatches must be resolved through DTS-documented mapping before data can be accepted - undocumented mapping is an undocumented transformation of source data
8. Updated Data Sent Without Update Flag - Corrected file sent without flagging changed records; CDM must re-reconcile the full file

Every issue must be reviewed, assigned, documented, and resolved. No discrepancy should be closed without a documented rationale.

CDM Action Pathway for Reconciliation Discrepancies

1. Confirm the reconciliation rule - Check the matching rule in the DTS for this data source
2. Check expected records in EDC or tracker - Confirm whether the record was expected, when, and current status
3. Review vendor file, transfer date, and version - Confirm which file version is in use; check for pending correction files
4. Classify issue type and impact - Missing record, ID mismatch, value mismatch, duplicate, or format error - and its severity
5. Assign owner - CDM, vendor, or site; every discrepancy must have a named responsible party
6. Raise query or vendor clarification - Document the date, contact, and expected response timeframe
7. Confirm correction and evidence - Document the correction, evidence, and resolution date
8. Reconcile again before database lock - Final reconciliation check across all data sources before the lock window opens

Every discrepancy needs an owner, action, due date, and documented resolution.

Reconciliation Tracker Example

Subject	Visit	Source	Issue	Severity	Owner	Status	Resolution
01-005	Day 15 / Pre-dose	Central Lab	Vendor record missing in EDC	Major	Vendor	Open	Awaiting corrected file
02-011	Baseline / Pre-dose	ECG Vendor	Timepoint mismatch	Moderate	Site	Answered	Clarification received
03-004	Day 29 / AM	ePRO	Missing assessment	Major	Site	Closed	Entered and verified

Track the discrepancy, the decision, the correction, and the evidence. The tracker is the audit trail for reconciliation.

Best Practices

1. Define reconciliation rules before the first transfer - Rules defined in the DTS set the standard; changes mid-study require formal DTS amendment
2. Confirm matching fields during UAT - Subject ID format, visit numbering, and timepoint codes must be validated in UAT before go-live
3. Align visit and timepoint mapping with the DTS - Discrepancies in DTS definitions are the root cause of systematic reconciliation failures
4. Use consistent Subject ID and status formats across all systems - Standardise and enforce format at study start
5. Review data early and at defined intervals - Begin at the first transfer, not at lock
6. Track open issues, aging, and repeated trends - Issues open >30-60 days and recurring patterns need escalation
7. Escalate critical or recurring issues promptly - Major discrepancies affecting endpoints or safety require a resolution plan, not just monitoring
8. Confirm final reconciliation before lock as a formal gate - Final reconciliation sign-off is a documented lock prerequisite, not an informal assumption. The lock readiness checklist must include sign-off for every data source in scope.

In blinded studies, reconciliation of IRT/randomisation data or any data that could reveal treatment assignment must be performed under blinding control procedures documented in the DMP. Unblinded reconciliation by a CDM who should remain blinded is a protocol deviation.

Final Takeaway

Five characteristics of a reconciled dataset ready for analysis and submission:

1. Complete - All expected data received and accounted for
2. Consistent - Values align across the EDC, vendor files, and external systems
3. Traceable - Every data point has a clear, documented source
4. Protocol-aligned - Data matches study rules, visit definitions, and protocol windows
5. Ready - Data is accurate, reconciled, and fit for downstream use

External data reconciliation is a CDM control process, not just a matching exercise.

This content is AI-assisted and expert-reviewed. All reconciliation principles are aligned with ICH GCP E6(R2), CDISC data standards (CDASHIG, SDTMIG), and clinical data management best practices. Content is intended for clinical research professionals for educational and professional development purposes.