Derivations & Versioned Algorithms

Why this matters

Regulators, auditors, clinical teams, and statisticians must be able to see exactly how a computed value was produced. If you can’t point each derived value to a named, versioned algorithm (with its inputs and parameters), you cannot reliably reproduce results, validate changes, or defend analyses. Clear versioning and provenance also prevent silent drift—where the same field means something slightly different from month to month without anyone noticing.

Core concepts & definitions

• Derived variable — A value computed from one or more source elements (for example, “Age at Consent,” “Time to Event,” “BMI”).

• Algorithm — The defined steps that transform inputs into an output, including parameter choices (such as rounding, imputation rules, or unit handling).

• Versioning — An identifier that ties an algorithm to a specific release (for example, BMI_v1.1). Use a predictable scheme so teams know what changed.

• Execution locus — Where the derivation runs:

  • EDC (Electronic Data Capture) calculated field (an edit check only tests a value; it does not derive one)
  • ETL (Extract–Transform–Load) or data pipeline job
  • Analysis script within biostatistics or reporting

• DVP (Data Validation Plan) — The study document that defines checks, derivations, test cases, and review procedures, and links to the evidence that they work. It contains or references the edit check specification of the earlier chapters. Derivations made only for analysis are specified in the statistical analysis plan (SAP) and the analysis dataset specifications; the derivations catalog (below) points to them.

• Provenance fields — Metadata stored with outputs (algorithm name and version, code or spec reference, input sources and batch, run date, executor). Together they record the value’s lineage: where it came from and how it was made.

The process, step‑by‑step

1. List derivations from endpoints and CRFs Start from the endpoint map (the list that links each protocol endpoint to the data that support it) and the case report form (CRF) fields. For each planned derived value, capture name, business purpose, and the exact inputs (dataset/variable names and units).

2. Define the algorithm clearly Record: algorithm name and version, a named owner (the role accountable for the logic and its changes), parameter choices (rounding, imputation, unit conversions), data quality rules (valid ranges, outlier handling), and how missing or partial dates are treated. Avoid ambiguous phrases such as “standard rounding”—state it precisely (for example, “round half up to one decimal place”). An imputed value is used only inside the derivation and is flagged as imputed; the value the site entered is never overwritten.

3. Choose the execution locus (and justify it)

   • EDC when sites need immediate feedback or the value drives on‑screen logic.
   • ETL when logic spans multiple sources, needs scheduled recomputation, or feeds many downstream consumers.
   • Analysis when the logic is analysis‑specific or exploratory and would clutter operational pipelines.

4. Create test cases and acceptance criteria Include normal and edge cases. For each case, show inputs, expected output, and the parameter settings used. Store the evidence (screenshots, logs, or test data) with the DVP.

5. Govern changes Any change to logic, inputs, parameters, or execution locus requires: impact assessment, approver sign‑off, version bump, and release notes describing the rationale and downstream effects. Test the new version before release, re‑run it on the existing data, and compare old and new values so that every difference is explained. Earlier outputs stay retrievable under their own version.

6. Document lineage and store provenance With each derived record, store the algorithm name/version, input file or batch identifiers, execution locus, timestamp, and (if available) a code or spec reference. This makes re‑runs and investigations straightforward. For a regulatory submission, the method for each derived variable is also described in the Define‑XML file, the CDISC metadata that accompanies the datasets, so a reviewer can trace and reproduce it.

Naming tip: Use a consistent pattern such as <SHORTNAME>_v<major>.<minor>.

• Bump minor when you change parameters or rounding that alter values.
• Bump major when you change the definition, inputs, or meaning.

What good looks like

• A derivations catalog listing: name, purpose, inputs (with units), algorithm steps, parameters, owner, version, execution locus, and change history.
• Traceability from any output value back to: input records, algorithm version, and the release note that introduced it.
• Test evidence linked in the DVP: clear cases, expected outputs, and results from a dry run.
• Release notes that tell a story: what changed, why it changed, risk assessment, validation performed, and who approved it.

Worked examples

The examples below are self‑contained. Each includes the context, inputs, algorithm, parameters, test cases, and sample outputs—no external references needed.

Example 1 — BMI (Body Mass Index)

Purpose: Standardize body habitus across participants for screening and analysis.

Inputs (canonical units):

• weight_kg (kilograms)
• height_m (meters)

Unit policy:

• If weight is captured in pounds, convert to kilograms using a validated conversion before applying the algorithm.
• If height is captured in centimeters, convert to meters.

Algorithm (named and versioned):

• Name: BMI

• Version: v1.1

• Steps:

  1. Confirm both inputs are present and within plausible ranges (for example, adult height and weight ranges defined in the DVP).
  2. Multiply the height in meters by itself.
  3. Divide the weight in kilograms by the result from step 2.
  4. Round half up to one decimal place (23.75 becomes 23.8).

Execution locus: ETL job (feeds listings, medical review dashboard, and analysis extracts).

Parameters: Rounding half up to one decimal; out‑of‑range values are flagged, not auto‑corrected.

Test cases (selected):

Provenance fields stored with output: algorithm=BMI, version=v1.1, exec_locus=ETL, source_batch=VS_2024_11_15, run_id=etl-3245, run_ts=2024-11-16T02:10.

Example 2 — Time to Event (TTE) in days

Purpose: Measure time from randomization to the first qualifying event for an endpoint.

Inputs:

• rand_date (date of randomization)
• event_date (date of qualifying event, if any)
• censor_date (last known date of no event, when no event occurred)
• censor_reason (for example, study end, lost to follow‑up)

Algorithm (named and versioned):

• Name: TTE_days

• Version: v1.2

• Steps:

  1. If an event occurred, count the number of calendar days from rand_date to event_date, including the randomization day.
  2. If no event occurred, count days from rand_date to censor_date, including the randomization day, and mark the record as censored with censor_reason.
  3. If the event date precedes randomization, flag as data error and do not derive.
  4. Partial dates follow the imputation rules pre‑specified in the statistical analysis plan (SAP), which also defines the qualifying event and the censoring rules. The collected dates stay as entered.

Execution locus: Analysis pipeline (statistical programs), with mirrored logic in ETL for monitoring. The analysis value is the authoritative one; the ETL copy is reconciled against it.

Parameters: Inclusive counting (event or censoring date minus randomization date, plus 1); partial‑date imputation rules as per the SAP.

Test cases (selected):

Provenance fields: algorithm=TTE_days, version=v1.2, exec_locus=Analysis, code_ref=tte_program_3.4, run_ts=2025-03-02T06:00, run_id=ana-8821.

Example 3 — Age at Informed Consent (in years)

Purpose: Support the age eligibility check and age‑stratified analyses.

Inputs:

• dob (date of birth; may be partial in some countries)
• consent_date (date informed consent was signed)

Algorithm (named and versioned):

• Name: AGE_CONSENT_YRS

• Version: v2.0

• Steps:

  1. If dob is complete (year, month, day), compute the number of full years between dob and consent_date. A participant’s age increases on their birthday.
  2. If dob is partial, apply the study’s imputation rule (for example, first day of the month when day is missing, or January 1 when only the year is present). Mark the result as imputed. The earliest possible birth date gives the oldest possible age, so near an age limit the derived age cannot confirm eligibility: the investigator confirms it against source.
  3. If consent_date precedes the birth date (complete or imputed), or the resulting age is implausible, flag for data review and do not derive.

Execution locus: EDC (real‑time eligibility checks), which holds the authoritative value; ETL re‑derives it independently as a cross‑check.

Parameters: Imputation policy documented in the DVP; no rounding beyond full years.

Test cases (selected):

Provenance fields: algorithm=AGE_CONSENT_YRS, version=v2.0, exec_locus=EDC, field_id=AGE01, dob_imputed=N, run_ts=2025-06-15T10:42, test_evidence=DVP_v1.4_section5.

Pitfalls & misconceptions

• “Common formulas don’t need documentation.” They do. Auditors expect the same rigor for familiar calculations.
• “User ID and a reason for change are enough.” Those are audit‑trail items for entered data. A derived value also needs the algorithm version, parameters used, and input sources.
• Silent changes. Adjusting rounding, imputation, or units without a version bump breaks traceability.
• Unstated unit conversions. Always specify and validate conversions before deriving.
• Mixing execution loci. If you compute the same field in EDC and ETL, maintain one source of truth and reconcile routinely.
• Missing edge cases. Partial dates, outliers, negative intervals, and missing inputs must be handled explicitly. A flagged input goes as a query to its data owner (the site, or the vendor for vendor‑loaded data); the owner confirms or corrects it and the derivation runs again. Nobody edits a derived value by hand.

Practice in the wild (checklist)

• Build a derivations catalog with: name, purpose, inputs (with units), algorithm steps, parameters, owner, version, and execution locus.
• Add test cases (normal and edge) with expected outputs to the DVP; store evidence of passing results.
• Store provenance fields with every derived output (algorithm name/version, input sources/batches, execution timestamp, code/spec reference).
• Maintain release notes for every change, including impact assessment and approvals.
• Reconcile duplicate implementations (EDC vs ETL vs Analysis) and declare a single authoritative source.

Key points to remember

• Every derived value must reference a named, versioned algorithm with clear inputs, parameters, owner, and execution locus (EDC, ETL, or Analysis).
• Be explicit about units, rounding, imputation, and inclusive/exclusive counting; never use ambiguous phrases like “standard rounding.”
• Capture inputs by dataset/variable name and units; validate and document any unit conversions before applying the algorithm.
• Choose and justify the execution locus; declare a single source of truth and routinely reconcile any duplicate implementations.
• Store provenance with each output (minimum set): algorithm name & version, parameter settings, inputs (dataset/variable + units), execution locus, source batch/run IDs, timestamp, and code/spec reference.
• Apply clear versioning rules: bump minor for parameter tweaks (e.g., rounding or imputation) and major for definition/input changes; record the change in the DVP and release notes and perform re‑validation as needed.
• Maintain a derivations catalog and link it to the DVP with test cases (normal and edge) and saved evidence of passing results.
• Govern all changes with impact assessment, approvals, version bump, and release notes that explain rationale, risks, and downstream effects.
• Ensure traceability end‑to‑end: from any output back to specific input records, algorithm version, and the release that introduced it—so results remain reproducible and defensible.
• Handle edge cases deliberately (partial dates, outliers, missing inputs, negative intervals); flag rather than silently auto‑correct.