Staging → Curated Lineage

Why this matters

In clinical data work, you will be asked: “Which exact file and algorithm produced this value?” If your pipeline cannot answer that in minutes—with evidence—you risk audit findings, rework, and delays to database lock or submission.

Plain‑language goal: Build a predictable path for every external file to travel from as‑received (your untouched original) → staging (validated and normalized) → curated (analysis‑ready), leaving a breadcrumb trail (lineage) at every step.

Key terms (simple definitions):

• Lineage: The “story” of a value—where it came from (file, row) and how it was transformed (algorithm & version).
• As‑received: Your read‑only archive of the exact files vendors/sites delivered.
• Staging: A safe workspace that receives only files that passed the structural gate, where values are normalized without losing the originals.
• Curated: Harmonized, analysis‑ready tables used by downstream consumers (e.g., SDTM mapping, stats).
• Promotion: The controlled action of moving validated data from staging into curated, with a manifest. The earlier load from as‑received into staging is logged too (load_id, gate outcome, spec and mapping versions), a record some teams call a “promotion log”.
• Supersession: Marking older curated rows as replaced when corrected data arrive or a re‑run under a new algorithm version replaces them—without deleting history.

Three tiers & their contracts

Think of the tiers as rooms with different rules (“contracts”). Moving between rooms must be explicit and logged.

1) As‑received (immutable originals)

Purpose: Preserve provenance and enable reprocessing at any time.

What you store (examples):

• File bytes exactly as delivered (e.g., the ZIP or CSV).
• Metadata: sender_org, received_at, file_name, file_checksum, transfer_channel (SFTP/portal/email), optional delivery_notes.
• A unique source_file_tag you assign on receipt (e.g., lab_2024-06-15_run3_v1).

Allowed: Read, checksum, copy to cold storage. Not allowed: Edits to the file or renaming that loses the original context.

Tip: A checksum (e.g., SHA‑256) taken on receipt detects any later change when recomputed; matching the checksum in the vendor’s delivery manifest (control file) shows the file arrived complete and unaltered. Store it with the as‑received record.

2) Staging (validate & normalize safely)

Purpose: Hold data from files that passed the structural gate and prepare values for harmonization, without destroying the raw values.

Typical actions:

• Load only files that passed the structural conformance gate (columns present, datatypes, date formats), which runs on the as‑received file before load.
• Normalize units/time zones (keep both raw and normalized).
• Enforce keys; de‑duplicate only by a documented rule, list the rows dropped, and query unexplained duplicates with the vendor.
• Log validation errors and warnings.

Minimum columns to add in staging:

• source_file_tag (from as‑received)
• load_id (unique per load/pipeline run; UUIDs work well)
• source_row_hash (stable hash of the raw row to detect duplicates)
• Raw vs normalized forms: e.g., bp_systolic_raw, bp_systolic_unit_raw, bp_systolic (normalized), bp_unit (standardized).

Contract: Only structurally valid files land here. Invalid files are quarantined (kept in the as‑received archive with an issue log), never loaded or promoted; the vendor re‑delivers a corrected file.

3) Curated (analysis‑ready with lineage)

Purpose: Serve consistent, harmonized datasets to downstream consumers.

What you add:

• Row‑level lineage: source_file_tag, load_id, source_row_hash, optionally source_system_pk.
• Algorithm trace: algorithm_name, algorithm_version (for any derivation/cleaning logic).
• Promotion trace: promoted_at, promoted_by, and a manifest_id linking each batch back to its promotion record.

Contract: No direct edits in curated. Any change is via new promotion (new load_id, new manifest_id).

Promotion & version manifests

A promotion is a gate. When you promote from staging → curated, you must leave a paper trail.

Your promotion manifest should capture:

• Who/when: promoted_at, promoted_by (the person who approved and started it, under a personal account, even when the job runs under a service account).
• What went in: input_source_file_tags[], input_row_count_staging, algorithm_name/version used, configuration flags, and the transfer spec and mapping table versions applied.
• What came out: output_row_count_curated, curated_tables[] (names and row counts), sample checksums/hashes.
• Quality signals: number of warnings/errors resolved, QC signatures (names & timestamps).
• References: links to validation reports and run logs.
• ID: A unique manifest_id written back onto the curated rows created by this promotion.

Why it helps: If a consumer reports a discrepancy, you can reproduce the exact curated batch by re‑running with the same files, same algorithm version, same configuration, and same spec and mapping versions recorded in the manifest.

Exports (to stats, safety, listings) should include in their metadata the manifest_id of every promotion whose rows they contain and the extract date and time, so any listing can be traced and rebuilt.

Corrections & supersession

Corrections and re‑deliveries are normal. Handle them without overwriting curated data.

Rules of engagement:

1. Every delivery is unique. Even if the vendor reuses a filename, assign a new source_file_tag and record its own received_at and checksum (computed from its bytes, so an identical re‑send has the same checksum).
2. Promote corrections as new data. Run them through staging; if valid, promote with a new manifest_id.
3. Supersede, don’t delete. In curated, mark affected older rows as is_superseded = true and record superseded_by_manifest_id. Only the promotion job sets these fields, and nothing else on the old rows changes; with the manifests’ promoted_at, the current view can be rebuilt as of any past date.
4. Lock proximity: If a correction lands near database lock, attach the promotion manifest and the diff report (each superseded value beside its replacement) to the lock package. After lock, a correction needs a documented, approved unlock first.
5. Re‑runs follow the same path. If the cleaning logic changes through change control (for example, lab_cleaning 1.6.3 → 1.7.0) and earlier data are to be re‑derived (a controlled backfill, justified and documented), re‑run the same as‑received files under the new version and promote with a new manifest_id, superseding the earlier rows. A re‑run with the same files, versions and configuration is only a check: it must reproduce the same values.

What counts as “the truth”? The current view filters curated rows to is_superseded = false. But the full table retains every historical row for auditability.

Worked examples (CV trial)

Scenario: Cardiovascular Trial CV‑201 receives weekly central lab files. One Week‑12 delivery was corrected and re‑sent.

A. As‑received

• First delivery arrives on 2024‑10‑07:

  • file_name: CV201_LAB_Wk12_20241007_v1.csv
  • checksum: b2…9f
  • You assign source_file_tag = lab_wk12_2024-10-07_v1.

• Correction arrives on 2024‑10‑10:

  • file_name: CV201_LAB_Wk12_20241007_v2.csv
  • checksum: 77…aa
  • You assign source_file_tag = lab_wk12_2024-10-10_corr.

Both files (bytes + metadata) are stored as‑received and are immutable.

B. Staging validation & normalization

For each file:

• Structural checks pass (all expected columns present).

• Normalize units (e.g., mg/dL → mmol/L where applicable), retaining raw values:

  • hdl_raw = 55, hdl_unit_raw = "mg/dL", hdl = 1.42, unit = "mmol/L" (55 ÷ 38.67 = 1.422… → 1.42; 38.67 is the HDL cholesterol factor).

• Add lineage scaffolding:

  • source_file_tag (as above)
  • load_id (e.g., a2c1…)
  • source_row_hash (stable hash of the raw row)

• Log: the 10/10 correction contains 12 updated rows (discrepant reference ranges fixed).

C. Promotion to curated (first delivery)

• Algorithm: lab_cleaning, version 1.6.3

• Promotion outcome:

  • manifest_id = man_2024-10-07_01
  • 2,384 rows inserted into curated.lab_results with lineage columns populated.
  • Exports to Stats include manifest_id in the export metadata.

D. Promotion to curated (correction)

• Run the 10/10 file through the gate and staging like any new delivery; validation passes.

• Promotion outcome:

  • manifest_id = man_2024-10-10_01
  • 2,384 rows inserted (the whole corrected file; 12 rows have corrected reference ranges).
  • During promotion, match rows by subject/visit/analyte and mark prior rows from man_2024-10-07_01 as is_superseded = true, with superseded_by_manifest_id = man_2024-10-10_01. All 2,384 earlier rows match and are superseded; an earlier row missing from the new file would be queried with the vendor, not silently kept or dropped.

E. Current truth vs history

• Current view (is_superseded = false) shows the 2,384 rows of the correction batch (12 corrected, 2,372 unchanged) and none from the first batch.

• History retains both batches (4,768 rows). An auditor can:

  • Trace any value to the specific file via source_file_tag.
  • See which algorithm version made the transformation.
  • Reproduce the dataset using the promotion manifest.

F. Example records (illustrative)

Curated row (after correction):

subject_id: 10023
visit: WEEK12
analyte: HDL
hdl_raw: 55
hdl_unit_raw: "mg/dL"
hdl: 1.42
unit: "mmol/L"
source_file_tag: "lab_wk12_2024-10-10_corr"
load_id: "a2c1e6…"
source_row_hash: "b0f9…"
algorithm_name: "lab_cleaning"
algorithm_version: "1.6.3"
promoted_at: "2024-10-10T22:14:35Z"
promoted_by: "dm.jlee"
manifest_id: "man_2024-10-10_01"
is_superseded: false

Superseded prior row (kept for history):

source_file_tag: "lab_wk12_2024-10-07_v1"
manifest_id: "man_2024-10-07_01"
is_superseded: true
superseded_by_manifest_id: "man_2024-10-10_01"

This example is fully self‑contained: it defines tags, shows how promotions work, and demonstrates supersession behavior without relying on external context.

Pitfalls & misconceptions

• Anti‑pattern: “Working copy only.” Editing a single “master CSV” erases provenance. Always keep the as‑received original.
• Anti‑pattern: Blending raw and normalized values in one column. Keep explicit *_raw and normalized fields with lineage.
• Anti‑pattern: Silent fixes in curated. All fixes must flow through staging and a new promotion (new manifest_id). A data fix comes from the data’s owner (vendor re‑delivery, or the site in EDC) and a logic fix from a new algorithm version under change control; no one edits values by hand in staging or curated.
• Misconception: Vendor audit trail is enough. You still need your own as‑received archive, staging validations, and promotion manifests.
• Pitfall: Re‑deliveries overwrite. Never UPDATE in place to “fix” data; supersede with a new promotion.
• Pitfall: Unversioned algorithms. Changing logic without bumping algorithm_version breaks reproducibility.
• Pitfall: Missing manifest in exports. If listings don’t include the manifest_id of every promotion they contain and the extract time, you’ve lost the ability to reproduce them precisely.

Practice checklist

• Write and publish clear tier contracts (what belongs/doesn’t belong in each tier).
• Generate and store checksums for all as‑received files.
• Tag every staged/curated row with source_file_tag, load_id, source_row_hash.
• Stamp derivations with algorithm_name and algorithm_version.
• Promote via a controlled job that writes a promotion manifest (manifest_id) and row counts.
• Implement supersession: set is_superseded = true and record superseded_by_manifest_id.
• Include the manifest_id of every contributing promotion, and the extract time, in every downstream export’s metadata.
• Quarantine invalid files; do not let them into staging/curated.
• Retain both raw and normalized fields; never drop originals.

Key points to remember

• Three‑tier flow & contracts: as‑received → staging → curated. As‑received is immutable; staging validates and normalizes without altering raw values; curated serves analysis‑ready data. Movement between tiers happens only through controlled, logged steps: the gate and load into staging, then promotion into curated.

• Reproducibility formula: immutable originals + versioned algorithms + promotion manifests. Every curated value must be traceable to the exact file and algorithm that produced it.

• Row‑level lineage (tag every row): In staging and curated: source_file_tag, load_id, source_row_hash. Additionally in curated: algorithm_name, algorithm_version, promoted_at, promoted_by, manifest_id.

• Raw and normalized side‑by‑side: Keep original (*_raw) and normalized fields; never overwrite originals.

• Each delivery is unique: Assign a new source_file_tag for every file received—even if filenames repeat.

• Promotion manifest essentials: Record who/when, inputs (file tags), algorithm version/config (with spec and mapping versions), row counts, QC/validation outcomes, and references to logs/reports. Write the resulting manifest_id onto the curated rows created by that promotion.

• Exports must be traceable: Ensure all listings/exports carry the manifest_id of every promotion they contain, plus the extract time, so they can be reproduced exactly and traced back to those promotions.

• Supersession, not overwrite: Promote corrections, and re‑runs under a new algorithm version, as new data. Mark prior rows as is_superseded = true and capture superseded_by_manifest_id. The current view filters to is_superseded = false, while full history remains intact.

• Near database lock: Include the promotion manifest and a concise diff report with the lock package to document what changed and why.