External Data Ingestion & Transfer Specifications

Why this matters

When clinical data arrives from outside systems (central labs, devices, imaging, ePRO), most failures are structural: wrong file name, missing or extra columns, incorrect types, wrong delimiter or encoding. If you skip this structure-first gate and jump to content counts (rows per subject or visit) or “logic checks,” you will:

• Load bad data and spend days chasing false discrepancies.
• Open the wrong queries (e.g., asking sites to fix what is actually a vendor cut‑timing issue: the record fell after the file’s data cut‑off).
• Lose auditability if you overwrite or “clean” a received file instead of keeping it as received and asking the vendor to correct and re‑send.

A simple discipline prevents this: validate structure → validate logic → reconcile, while keeping an immutable as‑received archive for provenance.

Core concepts & definitions

• Electronic Data Capture (EDC): The database where sites enter Case Report Form (CRF) data.

• Vendor transfer / external data: Files or API payloads delivered by partners (e.g., central lab, ECG vendor, ePRO app).

• Transfer specification (“the spec”): The single source of truth for how files must look and arrive, often called a data transfer specification (DTS); many companies put it in, or attach it to, a data transfer agreement (DTA). Data management and the vendor agree it before the first transfer, prove it with a test transfer, and change it only under change control. It defines:

  • File naming (pattern, date stamp, version), compression/encryption, delivery method/cadence.
  • Layout (delimiter, header row, column order), column data types, lengths, formats (e.g., ISO 8601 dates with timezone), codelists, required/nullable fields.
  • Keys and uniqueness rules (e.g., SubjectID+SampleID+AnalyteCode).
  • Cumulative (all data to date) vs incremental (new or changed records only) deliveries, and versioning of the vendor data dictionary.
  • Blinding: how data that could reveal treatment (e.g., drug concentrations) are withheld or masked.

• Conformance check: Automated verification that a received file matches the spec before any load.

• Staging → promotion: Land to a quarantine/staging area, run checks, then promote only validated data to a curated layer used by downstream consumers.

• As‑received archive: Immutable, read‑only storage of the exact inbound file with checksum and metadata (sender, timestamp).

• Reconciliation: Matching vendor rows to EDC rows using defined match keys and tolerances (e.g., ±24‑hour window on collection time; rounding to specified decimal places). What is compared is what both sides hold: usually participant, visit, collection date and time, and sample (accession) number; central lab results are normally not entered in the CRF.

• Owner routing: Rules for who gets the query:

  • Presence mismatch: Record in CRF but missing in vendor file → query vendor first (often cut/capture issue).
  • Presence mismatch: Record in vendor file but missing in CRF → query site (data entry gap).
  • Value mismatch when both present → usually site, after confirming units/rounding per spec.

The process, step‑by‑step

1. Land & fingerprint

   • Goal: Capture exactly what arrived.
   • Do: Receive via secure channel (e.g., SFTP, a secure file transfer protocol). Compute checksum (e.g., SHA‑256), record sender, received timestamp, file size, and any envelope metadata.
   • Store: Write file to as‑received archive (append‑only / WORM, write once read many, if available). Do not alter name, delimiter, encoding, or line endings.

2. Conformance checks (structure before content)

   • File‑level: Name pattern, date in name (if required), extension, compression/encryption, decodable archive.
   • Encoding & layout: Encoding (e.g., UTF‑8), delimiter/quote, header present, column count and order.
   • Schema: Column names, data types, max lengths, nullability, codelist membership, date/time formats with timezone.
   • Keys: Primary key uniqueness within the file; no forbidden duplicates.
   • Completeness: The record count equals the vendor’s control total (manifest or trailer record), so a truncated file is caught.
   • Outcome: Block the load on any structural failure: the whole file is rejected and stays archived as received. Produce a clear error report for vendor/ops; the vendor re‑sends a corrected file. Nobody edits a received file to make it pass.

3. Load to staging (typed, transparent)

   • Do: Parse into a typed staging table. Apply necessary type coercions (e.g., string → date) and log all coercions and rejects with row numbers and reasons.
   • Keep: A stable batch/load ID linking back to the as‑received file.

4. Logical/clinical checks (after structure passes)

   • Examples: Plausibility of dates (no future collection), values inside the spec’s plausibility ranges (outside: a query, not a rejected file), reference‑range flags present, required cross‑field logic (e.g., units required when result present), kit/sample linkage, allowed visit windows.
   • Integration checks: Referential integrity to master lists (site codes, subject IDs), unit compatibility, time zone normalization rules.
   • Outcome: A failed check becomes a query to its source (the vendor, or the site for what the site recorded); nobody corrects values in staging or curated.

5. Promote to curated

   • Do: Move validated rows into curated tables intended for analysis/recon/reporting.
   • Versioning: Tag with transfer date, vendor dictionary/version, and load ID. Keep change notes if a rerun replaces or supersedes rows.

6. Reconcile with EDC

   • Match keys: Define deterministic keys on what both sides hold (e.g., SubjectID + Visit + SampleID, then compare CollectionDateTime within the tolerance).

   • Tolerances: Specify windowing (e.g., ±24 h), rounding/precision, and unit normalization rules. Convert a copy to compare; the received value and unit stay as sent.

   • Outputs: Classify discrepancies:

     • Missing in vendor / present in CRF
     • Present in vendor / missing in CRF
     • Value mismatch (after units/rounding)
     • Timing/cut misalignment

   • Route: Open to vendor or site per owner routing rules. Each side corrects its own record (site: the CRF; vendor: its data, re‑sent); data management edits neither.

7. Document & retain

   • Evidence: Conformance report, logic validation results, reconcile outputs, query dispositions.
   • Traceability: From curated row → staging row → as‑received file (via batch/load IDs).
   • Retention: Keep per study retention policy; never delete as‑received archives.

What good looks like

• A machine‑checkable spec (e.g., CSV schema or JSON schema) that tooling validates automatically.
• An automated conformance gate that blocks structural failures and produces actionable error messages.
• An immutable as‑received archive with checksums and metadata for every delivery.
• Deterministic promotion: given a file and spec version, the curated result is reproducible.
• Reconciliation dashboards that separate presence vs value mismatches and route to the right owner.

Worked examples

These examples are self‑contained. All acronyms are expanded on first use.

Example 1 — Central lab chemistry panel

Context. A central lab sends daily CSV files for a cardiovascular (CV) trial. Sites enter Case Report Form (CRF) data into the Electronic Data Capture (EDC) system; for a lab sample the CRF usually holds collection date and time and sample number, not results.

Spec snippet (simplified).

File name: LAB_<YYYYMMDD>_v<NN>.csv
Encoding: UTF-8, delimiter=",", header row present
Primary key: SubjectID + SampleID + AnalyteCode

Columns:
- SubjectID (string, len ≤ 20, required)
- SampleID (string, len ≤ 30, required)
- CollectionDateTime (ISO 8601 with timezone, required)
- AnalyteCode (string; codelist: K, NA, CREA, GLU; required)
- ResultValue (decimal, precision 6,3; required if Status="FINAL")
- Units (string; codelist per analyte; required with ResultValue)
- Status (string; allowed: PRELIM, FINAL)
- ReferenceRangeLow (decimal, optional)
- ReferenceRangeHigh (decimal, optional)

A) Conformance failure. The file arrives as LAB_20240431.csv (missing version; invalid date).

• Action: Conformance gate rejects at file‑name rule; notify vendor with error: “Expected LAB_<YYYYMMDD>_v<NN>.csv; got LAB_20240431.csv.” The vendor re‑sends under a valid name; nobody renames the received file.
• Why: If naming is wrong, you cannot safely correlate to transfer date or version.

B) Logical check failure (after passing structure). ResultValue="5.8" with AnalyteCode="K" (potassium) but Units="mg/dL" (should be mmol/L). mg/dL is a valid unit code (for glucose), so column checks pass; the per‑analyte rule is a cross‑field check.

• Action: Flag as unit mismatch. Do not change values in staging or curated; open a vendor query: the vendor corrects and re‑sends.

C) Reconciliation scenarios.

• CRF records a sample as collected; lab file has no rows for it.

  • Owner: Vendor first. Likely a cut/timing issue or a sample not processed yet; if the lab never received it, query the site.

• Lab file has results; CRF has no collection record for the sample.

  • Owner: Site (data entry lag).

• Where the CRF also holds a result (e.g., a screening value entered from the lab report), the two differ by 0.1.

  • Action: Apply spec rounding; if equal after rounding, mark as match; otherwise open site query after confirming units.

Example 2 — ePRO daily symptom diary

Context. An electronic Patient‑Reported Outcome (ePRO) vendor sends weekly CSV files.

Spec snippet (simplified).

File name: EPRO_<SiteID>_<WeekOfYYYYMMDD>_v<NN>.csv
Primary key: SubjectID + DiaryDate
Columns:
- SubjectID (string, required)
- SiteID (string, required)
- DiaryDate (YYYY-MM-DD, required)
- CompletedAt (ISO 8601 with timezone, required if Completed="Y")
- Completed (Y/N, required)
- ScoreFatigue (integer 0–10, required if Completed="Y")

A) Structural trap. Vendor adds a new column ScoreMood without a prior spec update.

• Action: Conformance gate blocks (unexpected column). Return a clear error that a spec change must be versioned and communicated.

B) Reconciliation. The diary is the participant’s own record (the source): nobody transcribes it into EDC or fills in a missed day. Reconcile participants and dates.

• ePRO has diaries for a SubjectID that EDC does not list.

  • Owner: Site confirms the ID (entry lag in EDC, or a typo at device set‑up, which the vendor corrects on the site’s documented request). Provide subject/date evidence.

• EDC shows a visit done; ePRO shows no completed diaries for the week before it.

  • Owner: Vendor first (device sync issue) after confirming timezones and cut period; a diary the participant never completed stays missing.

Example 3 — Device data (ECG vendor)

Context. An electrocardiogram (ECG) vendor delivers per‑visit files.

Spec snippet (simplified).

Primary key: SubjectID + Visit + AcquisitionDateTime
Columns:
- SubjectID (string, required)
- Visit (string; codelist: V1, V2, V3; required)
- AcquisitionDateTime (ISO 8601 with timezone, required)
- HeartRateBPM (integer 20–220, required)
- QTcMS (integer 200–700, required)
- ReadStatus (string; PRELIM/FINAL; required)

Issue. AcquisitionDateTime values are sent without timezone.

• Action: Conformance gate blocks due to format violation. Do not guess the timezone. Request corrected delivery per spec. A heart rate outside 20–220 would be different: a content finding to query with the vendor, not a rejected file.
• Reconciliation note: Time alignment drives matching; define a ± tolerance (e.g., ±2 h). Without timezones, you risk wrong matches.

Pitfalls & misconceptions

• Starting with record counts. Counts can “match” even when columns and types are wrong. Always validate structure first; the file’s control total is checked at the same gate, after the layout, and shows only that the file is complete.
• “Vendor is validated, so we can skip checks.” No—changes and regressions happen. Keep the conformance gate on every delivery.
• Overwriting the as‑received file. Never “fix in place.” Preserve the original; transform only in staging/curated.
• Silent coercions. Auto‑parsing dates/numbers in spreadsheets can corrupt IDs (leading zeros) or dates. Parse in controlled pipelines, with logs.
• Timezone and units drift. Missing offsets or unit swaps cause false mismatches. Require explicit formats and unit codelists.
• Unexpected columns / order. Even harmless‑looking additions can break parsers and downstream mappings. Treat schema changes as versioned releases.
• Duplicate keys. If the spec says the key must be unique, duplicates are a structural failure and should block load. A key repeated in a later file (cumulative transfer, corrected re‑send) follows the spec’s version rule; every received file is kept.
• BOM/encoding issues. A hidden byte order mark or wrong encoding breaks column recognition. Check encoding explicitly.

Practice in the wild (checklist)

• Automate a structural conformance checker from the written spec (file name, encoding, delimiter, header, column order, names, types, lengths, nullability, codelists, keys, control total).
• Archive as‑received files with checksums and metadata (sender, received time, size); use append‑only/WORM if possible.
• Stage → validate → promote with a batch/load ID linking each curated row back to the as‑received file.
• Log all coercions, rejects, and warnings with row numbers; produce human‑readable conformance reports.
• Define reconciliation match keys and tolerances, and set owner routing rules (vendor vs site) for each discrepancy class.
• Version the spec and vendor dictionary; require change notices for any schema change (new columns, codelist updates).
• Monitor feed health: delivery cadence, late/missing files, row deltas, duplicate batches.
• Retain evidence: conformance outputs, reconcile runs, and query dispositions for audit.

Key points to remember

• Validate structure first—file naming, encoding, column order, schema, keys, and the file’s control total—before content counts (by subject or visit), clinical logic, or reconciliation; block the load on any structural failure.
• Keep an immutable as‑received archive (exact file, checksum, and metadata) to ensure provenance and reproducibility.
• Reconcile on what both sides hold (participant, visit, collection date/time, sample number) using deterministic match keys with explicit tolerances (time windows, rounding, units), and classify issues as presence vs value mismatches.
• Route ownership correctly: CRF present but vendor transfer missing → engage vendor first (often a cut/capture timing issue); vendor present but CRF missing → engage site; after unit/rounding normalization, value mismatches typically route to site. Each side corrects its own record; data management edits neither.
• Maintain a machine‑checkable, versioned transfer specification and enforce it via the conformance gate; treat any schema change as a versioned release.