Transfer Specs & Structural Conformance

Why this matters

Most reconciliation headaches start long before you compare numbers. They start with unclear transfer specifications—documents that leave room for guesswork about file names, field formats, time zones, or code lists. When each partner “interprets” the spec, you get drifting column types, unexpected units, mixed date formats, and late rework.

A simple discipline avoids this: structure first. Before you look at record counts or medical meaning, you check whether the file exactly matches the agreed structure. If it does not, you do not load it. This fail‑closed approach keeps bad data out of your pipeline and focuses everyone on fixing the right thing at the right time.

An effective transfer spec—parts & patterns

What is a transfer spec? It is the contract (the data transfer specification, DTS) for data movement from a vendor (lab, device, electronic clinical outcome assessment or eCOA, coding, safety, etc.) to the sponsor. It defines how files are delivered and what is inside them—down to field order, allowed values, and units. Below are the sections your spec should include, with plain‑language guidance and examples you can copy.

1) File operations (“file ops”)

Spell out logistics so deliveries are predictable and auditable.

• Delivery method & location: secure file transfer location, credentials process, and allowed IP addresses.
• Naming pattern: include study, feed, date, and version. Example: STUDYID_LAB_CHEM_YYYYMMDD_vN.csv (case exactly as shown, UTC date).
• Encoding & delimiter: UTF‑8; comma‑separated (or pipe). State one and stick to it, along with the header row, the quoting rule for values that contain the delimiter, and the line ending.
• Compression & encryption: e.g., zip with password sent by a separate channel.
• Delivery cadence & cut time: daily. Each delivery (a cut) holds records up to a stated data cut‑off (for example, 00:00 UTC) and arrives by a deadline (for example, 17:00 UTC the same day); define the time zone explicitly.
• Transfer type: cumulative (each file holds all data to date and replaces the previous one) or incremental (only records new or changed since the last cut).
• Time zone policy: timestamps carried in UTC with offset in a separate field if needed.
• Manifest & checksum: each delivery comes with a manifest (control file) listing every file with its record count and checksum (for example, SHA‑256), so the receiver can prove that the file arrived complete and unaltered.
• Retry rules: if delivery fails, vendor retries within 2 hours; notify contacts.
• Contacts & escalation: named roles and email addresses for both sides.

2) Schema (tables and fields)

Define the exact structure the file must follow.

• Table name and one line describing its purpose.

• Column order and data types for each column.

• Nullability: which fields can be blank and which cannot.

• Formats:

  • Dates: YYYY-MM-DD
  • Timestamps: YYYY-MM-DDThh:mm:ssZ (UTC “Z” suffix)
  • Booleans: Y/N (choose one representation)

• Primary key or composite key: which fields together uniquely identify a row.

• Uniqueness rules across files: e.g., (SubjectID, VisitID, TestCode, CollectedDateTime) must be unique within each file and, for incremental transfers, across all deliveries (a key already delivered may come again only as a declared change, such as a RecordAction of UPSERT).

Tip: Include a compact data dictionary table in the spec. For each field list: name, description (plain words), type, length if applicable, null allowed (Y/N), allowed values or reference to a code list, and example.

3) Controlled terminology

Most feeds carry coded values. Avoid chaos by agreeing the vocabulary.

• Sponsor code lists: definitive sets for sex, disposition, sample type, etc.
• Vendor → Sponsor mapping tables: if vendors use their own codes, provide a versioned mapping that translates each vendor code to the sponsor code. Store the mapping as a table under version control and reference the mapping version in every delivery. A published version is never edited: each change is a new version with an owner’s approval and an effective date, and a change to an existing code’s mapping is assessed for data already loaded.
• Unknown/other handling: define exactly what vendors send when a value is unknown, and how that maps.

4) Units

Pick a canonical unit per measurement (for example, potassium in mmol/L). Allow a short whitelist of inbound units per test. If a file arrives with a unit outside that list, it fails the gate. If you support conversion, document the conversion method in the spec (conversion itself is covered in the Unit Normalization & Time Zones chapter).

5) Timestamps

State whether timestamps must be in UTC. If local time is necessary, carry both the local timestamp and the UTC timestamp, and include the offset (for example, -07:00). Be explicit about daylight saving changes: “Offsets must reflect the local time at collection.”

6) Corrections and supersessions

Real data changes. Define how to correct it safely.

• Re‑delivery rules: a corrected file must include the same name with an incremented version (v2, v3).
• Supersedes flag: a clear indicator that this file supersedes a previous delivery.
• Row‑level corrections: if allowed, include a “RecordAction” with values like UPSERT (insert or update) or DELETE. If deletes are allowed, say how the key is provided.

7) Change control

Specs evolve—but only by controlled release.

• Versioning: semantic versions: a minor step (1.0 → 1.1) for a change that existing files and programs still meet, such as a clarified description; a major step (1.1 → 2.0) for a breaking change, such as a changed data type, column order or code meaning.
• Effective date: the first cut to which the new version applies.
• Impact assessment: who assesses backward compatibility, testing steps, and how you communicate changes to every recipient.

Structural conformance gate (fail‑closed)

Goal: Block anything that does not match the spec before it reaches staging (the first database area where delivered data are loaded). Log exactly why it failed so the sender can correct it quickly.

Run these checks in order:

1. File presence & name The file is present, matches the naming pattern (study, feed, date, version), and its checksum matches the manifest (complete and unaltered). A late or missing delivery is logged and escalated to the vendor contacts.
2. Schema match Columns exist in the exact order, with the exact types and formats. No extra or missing columns, and required (non‑nullable) fields are not blank (blank key fields are caught at check 3).
3. Key rules Key fields are not blank; composite keys are unique within the file and across deliveries (where required).
4. Code/enum domain Each coded value is in the allowed set or present in the agreed mapping table version.
5. Unit whitelist Units for each test are within the permitted list for that test.
6. Row counts & basic sanity (only after structure passes) The row count matches the manifest. Optional sanity checks: counts are within ±X% of typical; date ranges are plausible for the study timeline.

Fail‑closed policy: if any required check fails (checks 1–5, or a row count that does not match the manifest), do not load the file into staging; an optional sanity‑check warning is reviewed with the vendor before the file is loaded. Create a ticket (include the error list and line numbers), store the as‑received file in the immutable archive with checksum, and notify the vendor contacts. Never edit the received file to make it pass: the vendor re‑delivers a corrected new version (made against an updated spec or mapping version when that is the agreed fix).

From spec to first file—step‑by‑step

1. Draft together Write the spec in a shared document. Use plain words and examples for every tricky field (dates, timestamps, units, codes).

2. Include example payloads Provide a tiny sample file that passes and another that fails, so vendors can test locally.

3. Dry‑run with two samples Ask for two sample files (different dates). Run your conformance gate and return a structured report. Repeat until both pass.

4. Sign‑off version 1.0 Store the spec and mapping tables in version control. Distribute the signed PDF and the machine‑readable artifacts (code lists, mapping tables, and a schema file).

5. Operationalize

   • Land each delivery in an as‑received archive with metadata and checksum.
   • Run the conformance gate automatically.
   • Only passing files move to staging.
   • Keep a promotion log that records file name, spec version, mapping version, and gate outcome.

Worked examples (CV trial)

Below are self‑contained scenarios from a cardiovascular study. Each shows the problem, how the gate catches it, and what fix restores compliance.

Example 1 — Date format drift in lab results

• Context: Central lab sends a chemistry file with a CollectedDate column.
• Spec rule: Dates must be YYYY-MM-DD.
• What arrived: 03/15/2026 (month/day/year).
• Gate result: Fail at “Schema match → Formats.” The error report lists the first 10 offending rows.
• Fix: Vendor corrects extraction to 2026-03-15 and re‑delivers as ..._v2.csv.
• Why it matters: Prevents mis‑ordering visits and misclassifying data by month.

Example 2 — Type change in device stream

• Context: Wearable device file carries heart rate as HR.

• Spec rule: HR is an integer beats per minute; blanks allowed only if the device did not record.

• What arrived: Values like 72.5 (decimal) after a firmware update.

• Gate result: Fail at “Schema match → Types.”

• Fix paths:

  1. Vendor rolls back to integers; or
  2. Sponsor issues spec v2.0 changing HR to decimal (a breaking type change, so a major version), with impact assessment and effective date.

• Why it matters: Mixed types break aggregates and alerts.

Example 3 — Unmapped codes for sample type

• Context: Lab uses internal codes for SampleType.
• Spec rule: Sponsor codes are SERUM, PLASMA, URINE, with a vendor→sponsor mapping table v3.
• What arrived: New vendor code SRM_HAEM not in mapping v3.
• Gate result: Fail at “Code/enum domain.”
• Fix: The vendor confirms what SRM_HAEM means before anyone maps it (a guessed mapping could hide, for example, a hemolyzed sample, which falsely raises potassium). Once it is confirmed as serum, add SRM_HAEM → SERUM to mapping v4, publish mapping v4 with effective date, and require re‑delivery referencing v4.
• Why it matters: Without controlled mapping, analysis flags could misclassify specimens.

Example 4 — Unit outside whitelist

• Context: Potassium test K with canonical unit mmol/L.
• Spec rule: Allowed inbound units: mmol/L, mEq/L.
• What arrived: mg/dL.
• Gate result: Fail at “Unit whitelist.”
• Fix: Vendor corrects its export to send mmol/L and re‑delivers. If conversion is unavoidable, document conversion and add mg/dL to the whitelist with governance approval.
• Why it matters: Unit errors silently corrupt clinical meaning even if numbers “look right.”

Example 5 — Time zone mishandling around daylight change

• Context: Device sends LocalCollectionTime only.
• Spec rule: Both LocalCollectionTime and CollectionTimeUTC must be present; the offset must be the one in force at the moment of collection (on a daylight‑change day the date alone does not fix it).
• What arrived: LocalCollectionTime is filled; CollectionTimeUTC is blank.
• Gate result: Fail at “Schema match → Nullability.”
• Fix: Vendor fills the UTC field and the offset in force at collection (e.g., a site in Denver: 2026-11-01T09:15:00Z is local 2026-11-01T02:15:00-07:00, because clocks went back from -06:00 to -07:00 at 08:00 UTC that morning).
• Why it matters: Ensures correct sequence of events across sites in different regions.

Pitfalls & misconceptions

• “Check counts first.” Wrong. If the structure is wrong, counts waste time and can mislead.
• “We can map it later in the data load.” Hidden mappings in the pipeline create silent recoding. Keep mappings in the spec and version‑controlled.
• “An email is fine as a spec.” Emails drift, get lost, and are not versioned. Use a controlled document and machine‑readable artifacts.
• “Any date is fine; we can parse it.” Ambiguous dates (03/04/26) are a source of defects. Mandate a single format.
• “Local time only is enough.” Cross‑site or cross‑country studies require UTC to compare events reliably.

Practice checklist

• Spec includes file ops, schema, code lists, units, timestamps, corrections, change control.
• Vendor→Sponsor mapping tables exist, are versioned, and referenced by version in each file.
• Two sample files validated by the gate before first live cut.
• Fail‑closed structural conformance runs automatically on every delivery.
• As‑received files archived with checksums and promotion logs to preserve provenance.

Key points to remember

• Transfer spec = contract. Spell out delivery logistics (“file ops”), schema (column order, types, nulls, formats), controlled terminology, units, timestamps, corrections/supersessions, and change control—with plain‑language examples and machine‑readable artifacts.
• Gate every delivery, fail‑closed. Enforce structure before counts or content so non‑conforming files never reach staging.
• Gate order (in sequence): file presence/name/checksum → schema match (order, types, formats, nullability) → keys (non‑blank, unique at the declared grain) → code/enum domain (incl. referenced mapping version) → unit whitelist → then basic counts/sanity checks.
• Keys & grain. Declare the unique row (often a composite key) and treat duplicates within a file (or, for incremental transfers, a key repeated across deliveries without a declared change) as violations. Handle corrections via versioned re‑delivery/supersession and, if allowed, explicit row‑level actions.
• Controlled terminology. Use sponsor code lists and maintain vendor→sponsor mapping tables under version control; require each file to reference the mapping version used.
• Units. Define one canonical unit per test and a short whitelist of permitted inbound units; allow conversion only when documented and governed.
• Timestamps. Prefer UTC. If local time is required, carry both local and UTC with the explicit offset in force at the moment of the event (daylight changes included).
• Change control. Version specs (semantic versions) with an effective date and impact assessment; communicate changes and supply updated artifacts (schema, code lists, mappings).
• Provenance & audit. Archive the as‑received file with checksum and record the gate outcome and promotion log to preserve a trustworthy chain of custody.
• Operational discipline. Dry‑run with at least two sample files before go‑live; automate the gate on every cut; define contacts, retry rules, and escalations so issues are resolved quickly.