Why a Diagnostic Data Schema Needs a Clear Contract
A diagnostic routine can only compare values that have been identified and aligned consistently. One model output may label a measured concentration as DV, another as CONC; one may contain both population and individual predictions, while another provides them in separate files. Even if the numbers are valid, a routine cannot safely interpret a column merely because its name looks familiar. It needs a defined record grain, keys, value meanings, units, and rules for missing data.
A canonical diagnostic schema is that shared contract. It specifies how to represent the entities and values that diagnostic routines need, without requiring every model-fitting tool to produce them in exactly the same native format. The goal here is to define the target structure and its integrity rules. Converting each tool's particular output into that structure is a separate task.
The schema should preserve distinctions that affect interpretation: two observations from the same subject are not necessarily the same record; a population prediction is not an individual prediction; and a missing residual is not a residual equal to zero. It should also permit a diagnostic routine to join values by identifiers and declared relationships, not by row position.
A Practical Logical Schema
A useful design separates subject-level identity, occasion membership, response observations, covariate values, and model outputs. These entities can be implemented as database tables or as structured data frames. The important part is their meaning and linkage, not the storage technology.
| Entity | One row represents | Core fields and key |
|---|---|---|
| Subject | One subject within a study | study_id, subject_id; unique key is (study_id, subject_id) |
| Occasion | One occasion for one subject | study_id, subject_id, occasion_id; links to a subject |
| Observation | One recorded response at one observation event | observation_id; subject and occasion identifiers, time, endpoint, observed value, unit, and status |
| Covariate value | One value of one named covariate at its declared scope and effective time | Subject or occasion identifiers, covariate_name, value, unit, and applicable time or scope |
| Model output | One named prediction or residual associated with an observation | observation_id, model_id, output_kind, value, unit, and definition where needed |
The subject key includes the study identifier because a subject code such as “014” may be reused in another study. The occasion key includes the subject identifiers because occasion “2” is not globally unique. An observation needs its own stable identifier: subject, occasion, and time alone may not distinguish replicate samples, multiple endpoints, or multiple records at the same nominal time.
An observation record should identify the endpoint and the recorded response, with units attached or unambiguously specified by a field-level metadata contract. It should also preserve a status that distinguishes, for example, a measured value from a record at which the response was not available. Event time and nominal time should not be collapsed if they have different meanings in the source data. If diagnostic data must refer to dose or other event records, those should be represented as their own event entity and linked explicitly rather than disguised as response observations.
Predictions and residuals are model outputs tied to an observation, not replacements for the observation. A prediction requires a kind, such as population-level or individual-level, and an identifier for the model run or fit that produced it. A residual requires a residual kind or definition; its value is not self-describing. The schema should store the value and its unit and make the definition available as metadata. This avoids treating two differently defined residuals as interchangeable.
Covariates also require scope and timing. A baseline covariate may be subject-level; a time-varying covariate may apply only at or after a stated effective time, or to a particular occasion. The schema must say which interpretation applies. Copying a time-varying value onto every observation without retaining that timing can make the records appear more precise than the source data support.
Keys and Relationships: The Logic Behind the Design
A key is useful only if the uniqueness rule matches the entity's grain. If an observation table claims one row per subject and time, but the data allow two endpoints at one time, that claimed key is insufficient. A stable observation identifier can provide a simple reference, while validation also checks the meaningful natural fields for unexpected duplicates.
This notation states the intended dependency: once an observation identifier is known, its associated subject, occasion, time, endpoint, and observed value are determined. It does not mean the listed fields uniquely identify observations in every data set. That separate claim must be checked against the data and the defined grain.
Outputs should link to the observation they describe. If several prediction kinds or residual definitions exist for the same observation, their identity must include those distinctions as well as the model identifier. The relationship can be expressed as an integrity condition: every nonmissing output observation identifier refers to an existing observation.
This is a foreign-key condition. It prevents a prediction from being silently attached to a nonexistent observation. It does not prove that the prediction is scientifically appropriate for that observation; model and output metadata are still needed to judge that.
Worked Example: Choosing an Observation Key
Suppose hypothetical subject S07 has two concentration samples at 2 hours in occasion 1: one from the central laboratory and one from a repeat assay. A third record at 2 hours is a different endpoint, metabolite concentration.
A key made only from subject, occasion, and time would assign all three records the same identity. Adding endpoint distinguishes the metabolite record but not the two concentration assays. Adding assay source may distinguish the records if that field is reliably present, but identifiers based on source details can be fragile when source conventions change.
A robust schema assigns each observation a stable identifier, such as OBS-101, OBS-102, and OBS-103, while retaining subject, occasion, time, endpoint, and assay source as separate descriptive fields. The IDs identify the three records uniquely; the descriptive fields explain what each record represents. A validation rule can flag duplicate combinations of subject, occasion, time, and endpoint for review without assuming every such duplicate is invalid.
Worked Examples of the Contract in Use
Worked Example: Keeping Predictions Aligned to Observations
Consider two hypothetical observations, each measured in mg/L. The model output for observation OBS-201 is a population prediction of 10.8 mg/L and an individual prediction of 12.0 mg/L. The observed value is 12.4 mg/L. For this example, the declared raw residual convention is observed value minus prediction.
The output records should retain the same observation identifier and distinguish prediction kind. For the population prediction, the residual is \(12.4-10.8=1.6\) mg/L. For the individual prediction, it is \(12.4-12.0=0.4\) mg/L. These are different outputs for the same observation and therefore must not be identified solely by OBS-201.
A minimally sufficient output identity includes observation_id, model_id, and output_kind; the output_kind distinguishes the prediction or residual definition. Both prediction and residual records retain mg/L as the unit in this example. If a value is unavailable, it should be marked unavailable according to the schema rather than filled with zero, because zero would assert a numerical result that was not observed or computed.
Worked Example: Representing a Time-Varying Covariate
In a hypothetical study, subject S12 has a recorded body weight of 70 kg at hour 0 and a later weight of 66 kg effective from hour 48. An observation occurs at hour 24 and another at hour 72.
The covariate entity stores two values, each with its effective time: 70 kg from hour 0 and 66 kg from hour 48. Under a declared “latest value effective at observation time” rule, the hour-24 observation is associated with 70 kg because \(0 \leq 24 < 48\). The hour-72 observation is associated with 66 kg because \(48 \leq 72\).
The association is justified only because the rule and effective times are explicit. If the later measurement were merely recorded at hour 48 but not considered effective until a different time, the assignment could change. A schema should preserve source timing and the rule used to derive an observation-level covariate, rather than making the join appear automatic.
Worked Example: Handling a Missing Response
Suppose a hypothetical observation record identifies subject S03, occasion 2, hour 6, and endpoint concentration, but the sample was not quantifiable. The schema retains the observation record and its identifiers, records the response status as not quantifiable, and leaves the numeric observed-value field missing according to the declared missing-value convention.
A prediction may still exist for that scheduled observation, but a residual based on the missing measured value cannot be calculated. Therefore, the prediction can be retained with its observation link, while the residual is marked unavailable with an appropriate reason. Removing the whole observation would hide the fact that a scheduled sample existed; assigning a zero response would change the data meaning.
Validation, Diagnostics, and Common Failure Modes
Schema checks should test both structure and meaning. Structural checks establish that required fields exist, identifiers have the expected type, and keys are unique. Meaning checks establish that units are known, output kinds are allowed, timestamps use the declared convention, and relationships are valid. Passing a structural check does not establish that the model output itself is scientifically valid.
- Duplicate keys: count records sharing a key that is claimed to be unique. Investigate whether the grain is wrong or records were duplicated.
- Orphan outputs: verify every output links to an existing observation and every occasion links to an existing subject.
- Unstated units: reject or quarantine values whose units are absent or ambiguous; numeric values in different units cannot be compared safely.
- Unrecognized categories: check prediction kinds, endpoint names, and response statuses against documented allowed values.
- Unexpected row loss: compare counts before and after joins, and report unmatched records rather than silently dropping them.
- Invalid numeric values: distinguish missing values from nonfinite values and from explicit status codes; do not reinterpret one as another without a documented rule.
A common mistake is to join predictions to observations by row number because the two files happen to have the same number of rows. The reasoning is that row 15 in each file must describe the same event. That conclusion does not follow: sorting, filtering, or a failed record can change row order without changing the data's meaning. The correction is to join by stable observation identifiers, then check that each expected observation has the required output and that no unexpected records were lost or multiplied.
Another tempting choice is one very wide table with columns for every prediction kind, residual, and covariate. This can be convenient for a single fixed model output, but becomes brittle when new prediction kinds or covariates appear; it can also encourage ambiguous missing cells. A long-form output entity can accommodate multiple output kinds as records, while a wide table may still be useful as a temporary view for a specific analysis. The canonical contract should prioritize clear meanings and reliable relationships over a particular display layout.
Limitations and Design Decisions to Document
A schema cannot resolve ambiguity that was never recorded. If the source does not say whether a time is nominal or actual, whether a covariate is baseline or time-varying, or which residual definition was used, the schema should preserve that uncertainty rather than invent a value. Such records may need to be excluded from a particular diagnostic or reviewed before use.
Some choices are study-specific: endpoint vocabulary, time units, allowed statuses, and the scope of covariates. They belong in controlled metadata, not in undocumented assumptions inside a plotting routine. Likewise, the schema specifies what information must be available, but does not guarantee that every source can supply it. A transformation should report unrepresentable fields and records so users can decide whether the resulting diagnostics remain appropriate.
The key design principle is straightforward: preserve the identity and meaning of each record, and make every relationship explicit. Then diagnostic routines can consume different model outputs through a common interface without confusing subjects, occasions, measured responses, and model-derived values.
Check Your Understanding
Answer each question, then compare with the brief solutions below.
- Why is subject identifier alone generally not a suitable key for an observation?
- A prediction file and observation file have equal row counts. Why is joining by row order unsafe?
- For an observation with observed value 8.0 mg/L and prediction 7.5 mg/L, what is the raw residual under the convention observed minus prediction? What additional information should identify the residual record?
- How should a scheduled observation with an unavailable response be represented differently from an observed response of zero?
- What must be documented before assigning a time-varying covariate value to an observation?
Solutions. (1) One subject can have many observations across times, occasions, or endpoints, so the subject identifier does not distinguish them. (2) File ordering can change independently; join on stable identifiers and validate unmatched records. (3) The residual is \(8.0-7.5=0.5\) mg/L; its record should identify the observation, model, residual definition or kind, and unit. (4) Retain the observation and its status, leave the numeric value missing under the declared convention, and do not encode it as zero. (5) Preserve the covariate's scope, effective time, time convention, and the rule used to select its value for the observation.