Why a Snapshot Needs Validation
An interim dataset is not made review-ready merely by exporting files on a specified date. Records may still be changing in operational systems, different sources may update at different times, and a plausible-looking extract can contain omissions, duplicates, or inconsistent versions. A controlled snapshot makes clear which information was captured, when it was captured, and what checks were performed.
The earlier material on setting the interim data cut establishes the intended cutoff for information to be included. The snapshot process implements that cutoff and checks the resulting data; it does not change the cutoff because a later extract happens to be easier to obtain. A cutoff is a rule about eligible information, while an extraction timestamp records when a source was queried. Those times can differ, and both must be documented.
Validation is a bounded operational assurance, not proof that every source record is error-free. It should state what was checked, the results, unresolved exceptions, and any limitations relevant to use. A committee can only assess the information it receives; the validation record helps establish what that information represents.
Freeze the Data Without Losing Its History
Before extraction, specify the source systems and data domains in scope, the applicable cutoff rule, the population or sites included, the extraction method, and the person or role authorized to perform it. Also specify how late-arriving records and corrections will be handled. These decisions prevent the snapshot from being defined retrospectively by whatever happened to be available.
Capture the extract using a controlled process and preserve it as a read-only, versioned artifact. Record, at minimum, the source and environment, query or export version, extraction date and time with time zone, cutoff applied, record counts, file names, and responsible role. Where available, retain a cryptographic checksum for each file. A checksum can show that a file has not changed since it was hashed; it does not show that the original query selected the right records or that the source data are accurate.
If multiple systems are extracted at different times, document that fact rather than describing them as simultaneous. State which sources were current through the cutoff and which were current only through a later or earlier system update. Preserve the original extract even if a corrected snapshot is later produced. Give the corrected version its own identifier and explain how it supersedes, or supplements, the earlier version.
List the source systems, data domains, cutoff rule, included population, and authorized extraction method.
Extract the specified information; record timestamps, versions, file names, and counts; preserve the original files read-only.
Compare counts and record identifiers with expected schedules and relevant source systems; classify discrepancies rather than silently changing data.
Retain the audit trail, describe unresolved issues and their scope, and record whether the snapshot is suitable for the stated review purpose.
Reconciliation: Use the Right Denominator
A reconciliation begins with a defined expectation. For forms or visits, distinguish items on an initial schedule from items actually due by the cutoff. Let \(E\) be the number of expected-due items, \(R\) the number received and identifiable in the snapshot, and \(M\) the number expected but missing. If each expected item is classified exactly once as received or missing, then \(E=R+M\). The completion proportion is \(R/E\), provided \(E>0\). Items not yet due are outside \(E\); they are not missing.
This arithmetic is useful only when the categories are mutually exclusive and the unit being counted is consistent. For example, a form count cannot be reconciled directly to a patient count unless the relationship between forms and patients is defined. Investigate exceptions by identifier and status, not just by comparing two aggregate totals.
Worked Example: Reconciling Forms Due at the Cutoff
In a hypothetical trial, the initial schedule lists 160 visit forms as candidate records for the review period. The frozen extract contains 156 submitted forms. Reconciliation shows that two of the four absent forms were not yet due by the cutoff; the other two were due, and their status remains unresolved. What is the due-form completion proportion, and what should be reported?
First exclude the forms that were not yet due. The number expected by the cutoff is \(E=160-2=158\). The 156 submitted forms are the received due forms, so \(R=156\), and the number of due forms still missing is \(M=158-156=2\). The check is \(R+M=156+2=158=E\).
The snapshot therefore contains 156 of 158 forms due by the cutoff, a completion proportion of about 98.73%, with two due forms unresolved. The two not-yet-due forms should be reported separately as not due, not counted as missing. The percentage alone does not establish that the snapshot is fit: the unresolved forms could be important even though the overall proportion is high.
Compare Source Systems at the Record Level
Different systems may store related but nonidentical records. A transfer log, for example, can show that an item was sent without proving that it was entered into the destination system; a report in one system may also have no corresponding record in another. Reconciliation should therefore define a matching key or matching rules, compare the relevant record identifiers, and classify matches, source-only records, and possible duplicates. A discrepancy is a question to investigate, not automatic evidence that one system is correct.
Worked Example: Reconciling Two Event Sources
In a hypothetical reconciliation, the clinical data extract contains 42 event records and the safety system contains 44 raw records. Inspection finds one repeated safety-system record with the same event identifier, so there are 43 unique safety-system records. Using the prespecified matching rules, 40 records match across systems; two clinical-data records have no safety-system match, and three unique safety-system records have no clinical-data match. Check the accounting and describe the next action.
The clinical-data count reconciles as \(40+2=42\). The unique safety-system count reconciles as \(40+3=43\). The combined set of distinct records is \(40+2+3=45\), not 42 or 43, because the 40 matched records appear in both sources but count once in the union. The safety-system raw count is also accounted for: 43 unique records plus one repeated row equals 44 raw rows.
The reconciliation identifies five unmatched unique records and one repeated raw row. Investigators should check identifiers, transfer timing, and documented source procedures to determine whether the unmatched records are valid source-specific items, delayed entries, or errors. They should not delete the repeated row or force a match solely to make totals agree; any disposition needs a documented basis.
Corrections Must Be Traceable
A correction can arise during validation, but it should never make the original snapshot disappear. Record the affected file or record identifier, the value before and after the change, the reason, the supporting evidence, who authorized and made the change, and when it occurred. Preserve the original value and the relationship between the original and corrected versions. If a correction changes a source system, retain its source-system audit trail as well as the snapshot-level record of the change.
Decide whether a discrepancy is a confirmed error, an expected timing difference, a valid difference in source definitions, or unresolved. Do not convert “not found” into “not applicable” without evidence. Similarly, do not use an undocumented manual edit to make a file agree with a reconciliation total. If correction requires a new extraction, identify it as a new snapshot version and repeat the relevant checks.
Worked Example: Recording a Post-Freeze Correction
In a hypothetical frozen extract, participant P104 has a visit date recorded as 12 May. A source-system audit trail and contemporaneous visit documentation support 13 May. The correction is verified two days after the extract was frozen. How should the data be handled?
The original snapshot remains unchanged and is retained as version 1. The correction log identifies participant P104 and the visit-date field, records “12 May” as the frozen value and “13 May” as the supported corrected value, cites the source evidence, and records the reviewer, authorizer, and correction date. The log also states whether the source system itself was corrected and gives its audit reference.
If the committee review requires a corrected value, the responsible team can create a new, versioned snapshot or a clearly controlled correction record linked to version 1. The new artifact must identify the change and be checked for the affected counts and downstream consistency. It would be misleading to silently replace the date in version 1 and continue to call that file the original frozen extract.
Assess Fitness for Committee Review
Fitness is judged against the intended review and the prespecified scope, not against a vague goal of “clean data.” Useful checks include whether the extract used the intended cutoff; whether expected records and system counts reconcile or have explained exceptions; whether key identifiers and required fields are present; whether duplicate and unmatched records are classified; whether corrections are traceable; and whether access and version controls were maintained. The resulting assessment should describe both the evidence and the limits of the checks.
A snapshot can be suitable with disclosed limitations when unresolved items are bounded and their potential relevance is understood. Conversely, a high completeness percentage does not compensate for a missing source, an incorrect cutoff, or unresolved records that could materially affect interpretation. The validation record should distinguish “not detected by these checks” from “shown not to exist.”
Common warning signs include unexplained differences in record counts, extraction timestamps that fall before relevant source updates, a cutoff implemented inconsistently across systems, unexpected duplicate identifiers, missing audit history, and corrections made outside controlled procedures. When a check fails, document the finding, investigate its scope and cause, decide whether a new extract or escalation is needed, and state what remains uncertain. Do not certify fitness merely because the review meeting is scheduled.
A live, continuously changing query may be useful for operational monitoring, but it cannot provide the same stable reference point as a preserved snapshot: rerunning it later can return different data. A frozen extract supports reproducibility but can become outdated and does not itself resolve source errors. The practical approach is to use the frozen, versioned artifact as the review reference and to document later information or corrected versions separately, rather than allowing live changes to blur what the committee reviewed.
Check Your Understanding
Answer each question, then compare your reasoning with the solutions below.
- A schedule lists 90 candidate forms. Five are not due by the cutoff, 82 due forms are present, and the remaining due forms are unresolved. Find the expected-due count, missing count, and completion proportion.
- Why does a file checksum not establish that an interim extract contains the correct records?
- A source extract is frozen on Monday. A supported correction is confirmed on Tuesday. What should happen to the original snapshot?
- Two systems have different record totals. Give two reasons that the difference should be investigated rather than corrected by forcing the totals to match.
Solutions. 1. The expected-due count is \(90-5=85\). Missing due forms are \(85-82=3\), and the completion proportion is \(82/85=0.9647\), or about 96.47%. The five not-yet-due forms are excluded from the denominator. 2. A checksum can detect a change to a file after hashing, but it does not verify the extraction query, cutoff, source completeness, or correctness of source values. 3. Preserve Monday’s snapshot unchanged, log the evidence and correction with its authorization and timing, and create a clearly identified corrected version or linked correction record if needed for review. 4. The systems may have different update timing or record definitions, and records may be unmatched, duplicated, or valid in only one source. Record-level investigation is needed before assigning a cause or making a correction.