Author: davide.gaudesi

None of these problems is necessarily solved by closing queries.

This is the distinction I want to explore in this article: data cleaning is an essential part of data quality management, but a clean-looking database is not sufficient evidence that the data are trustworthy or fit for their intended purpose.

The discussion is grounded in clinical research, where decisions can affect both participant safety and the interpretation of study results. Many of the methods are also useful for registries, observational studies, real-world data and other healthcare datasets—although the regulatory obligations and operational responsibilities are not identical across these contexts.

My goal is practical. We will move from data quality principles to two complementary ways of detecting problems—query-based cleaning and exploratory data review—and then to the most important question of all:
what should happen after a problem has been detected?

1. Start with a better definition of data quality

Dimension	Working question	Clinical example
Accuracy	Does the recorded value reflect what was actually observed or documented?	A blood pressure value is accurately transcribed from the source record, although this alone does not establish the accuracy of the original measurement.
Completeness	Are the necessary data available, with missingness understood?	A primary endpoint is absent for some participants.
Consistency	Do values agree across fields, visits and relevant systems?	relevant systems? The recorded treatment start date conflicts with an administration record.
Validity	Does the value comply with the applicable format, domain and rules?	A visit date is a valid date; a coded response uses the permitted values.
Timeliness	Are data available and reviewed when they are needed?	Safety-relevant results reach the responsible team in time.
Traceability	Can we reconstruct where the value came from and what happened to it?	A correction has a justified, attributable and reviewable history.
Fitness for purpose	Are the data appropriate for the decision or analysis they must support?	An endpoint has the required definition, measurement method and timing.

A complete dataset is not necessarily accurate;
a valid value is not necessarily plausible;
a consistent dataset is not necessarily clinically meaningful.

The idea of fitness for purpose is particularly important. ICH E6(R3) explicitly uses it in describing trial quality and asks for processes proportionate to participant risk and to the importance of the information collected [1].
This does not mean that some data can be ignored. It means that quality efforts should be designed around the decisions that the data are expected to support.

The right opening question is not “How many checks can we implement?” but “What could go wrong with these data, how would we detect it, and what would be the consequence?”

2. Data quality begins before the first query

There is a limit to what cleaning can repair.

If the protocol does not define an endpoint clearly, if an eCRF collects insufficient context, or if external data arrive without usable metadata, additional queries may only expose the underlying design problem.
They cannot reliably reconstruct information that was never captured.

For that reason, data quality management should begin before the first participant is enrolled—or, for secondary-use projects, before data are ingested and transformed.

A sound preparation phase includes:

This is closely aligned with the Quality by Design and proportionate, risk-based principles in ICH E6(R3) [1] and with the direction of the Society for Clinical Data Management‘s Good Clinical Data Management Practice resources [3].

The practical implication is straightforward: a well-designed data collection process prevents avoidable errors; cleaning addresses the problems that remain and helps reveal weaknesses in the process itself.

3. Query-based cleaning: essential, but not sufficient

The most recognisable form of clinical data cleaning is the edit check and its associated query.

An edit check applies a defined rule to a value or relationship between values.
Some checks are implemented directly in an EDC system and may run during data entry.
Others run periodically or on exported datasets.
Checks can be automatic, semi-automatic or part of a documented manual review.

They are especially useful when the expected condition can be stated clearly.

The five groups of checks I would start with

1. Missingness and completeness

Mandatory or expected data are absent.

A form or visit is missing when the study schedule indicates that it should exist.

A missing value is present without an appropriate reason, where such a reason is required.

Example: a primary endpoint assessment is missing, and the database contains no explanation of whether the assessment was not performed, not available, or not applicable.

2. Range and format checks

Values fall outside a defined admissible domain.

Dates, codes or numeric fields have an invalid format.

The recorded value conflicts with the variable’s specified units or precision.

Example: an age field contains a negative value.
This can be detected deterministically. An unusually high but clinically possible laboratory result needs a different response.

3. Logical and cross-field consistency

Two or more values contradict a documented rule.

A conditional form is inconsistent with the answer that should trigger it.

A treatment record conflicts with a related disposition or exposure field.

Example: a field states that no treatment was administered, while a corresponding administration record reports a dose.
The discrepancy requires review; the check alone does not establish which record is wrong.

4. Temporal consistency

Events appear in an unexpected chronological order.

Visit dates fall outside defined windows.

Dates conflict across forms or source systems.

Example: an investigational treatment is recorded before the documented consent date.
This may indicate a date-entry problem, but it may also reveal a genuine process deviation. Correcting a date without verification would risk concealing the latter.

5. Cross-source reconciliation

Related information differs between the EDC system and another authoritative process or dataset.

Data transfers omit records or change their meaning.

External laboratory, safety, coding, or device data do not align as expected.

Example: an adverse event appears in one safety process but is not reconciled with the relevant clinical dataset according to the study’s procedures.

These categories overlap. What matters is not their taxonomy but whether each check has a clear rationale, reliable logic and an appropriate action when triggered.

Not every edit check should generate a query

An excessive number of low-value queries can consume site resources and hide more consequential issues.

Before turning a check into a query, ask:

Is the rule grounded in the protocol, data definition or clinical rationale?

Is the variable sufficiently important to justify the intervention?

Can the issue be resolved by consulting an existing authoritative source or a documented transformation rule?

Does the check produce many false positives in legitimate clinical situations?

Would an informational alert, central review or process change be more useful than another site query?

Thresholds should be tested against realistic cases and reviewed during study conduct. EMA’s guidance calls for protocol-relevant edit checks and controlled, documented implementation and changes [2].

A good query asks for clarification—not for a preferred answer

Compare these two messages.

Weak: “Weight is incorrect. Please correct it.”

Better: “The weight recorded at Visit 3 is 142 kg, compared with 72 kg at Visit 2. Please verify the Visit 3 value and unit against the source record, and confirm or correct the entry as appropriate.”

The better query identifies the discrepancy, makes the request actionable, and leaves the clinical interpretation and source verification to the responsible party. It does not presume an error solely from an unusual result.

Clarity also matters when a query is resolved. “Confirmed as correct” can be a legitimate outcome. Resolution is not synonymous with modification.

4. Query management is a process, not a counter

An effective query process has several stages:

For each issue, the record should make it possible to understand the affected participant and variable, the check that triggered the query, the question, the response, any changes and the resolution.
Metrics help manage this process, but they need interpretation.

Useful indicator	What it can reveal	What it cannot prove by itself
Open queries by age and criticality	Bottlenecks affecting important data	That older queries are necessarily more serious
Reopened-query rate	Weak responses or ineffective review	That a site is inherently poor quality
Repeated discrepancy types	Training, form design or process weaknesses	That every repeated finding has the same root cause
Query volume per expected data or visits	Workload patterns across sites and time	Directly comparable data quality without adjusting for context
Time to resolution	Process responsiveness	The correctness of a response
Post-query correction rate	Proportion of resolved queries resulting in a documented data modification, indicating how frequently query resolution involves changing recorded data.	That queries without corrections were unnecessary

I would be particularly cautious about ranking sites on raw query counts. A centre with more complex participants, earlier data entry or more active review may generate more queries without having worse underlying data. Denominators, study phase, data complexity and query criticality matter.

More importantly, a rising concentration of the same error should prompt a question about the process, not just another round of queries. Is the completion guidance ambiguous? Is a unit missing from the interface? Did a transfer specification change? Is additional training needed?

That is where cleaning begins to contribute to data governance.

5. Exploratory data review: looking beyond predefined rules

Query-based cleaning is powerful when we know what to look for. But some of the most revealing data problems are not violations of a predefined rule.

Exploratory data review asks a different question:

“Do the observed data behave as we would reasonably expect, given the population, the protocol and the collection process?”

This does not require sophisticated machine learning. Descriptive statistics, simple visualisations and carefully selected comparisons often provide valuable signals.

These approaches are complementary rather than mutually exclusive: exploratory findings may inform new programmed checks, while rule-based findings may trigger broader exploratory investigations.

A. Examine distributions, not only individual values

For quantitative variables, start with the number of observations, missingness, minimum and maximum, median, quartiles and distribution shape. When appropriate, inspect histograms, density plots and boxplots.

Look for:

Illustrative case: mixed laboratory units

Suppose serum creatinine is collected from several laboratories. Most values are close to 1.0, but a subset is around 90. The data appear to contain a cluster of extreme observations—until you discover that some records use mg/dL and others use µmol/L. For serum creatinine, 1 mg/dL corresponds to approximately 88.4 µmol/L. The two clusters may therefore represent comparable concentrations expressed in different units rather than biologically distinct populations.

The problem is not that the larger values are necessarily biologically extreme. The problem is that the measurement context was lost or not standardised. A conversion may be possible, but only after the original units, specifications and provenance have been verified. Raw values and units should remain reconstructable.

This illustrates why semantic and technical context matter as much as numeric validity.

B. Look across participants, sites and time

Global summaries can conceal local problems.

Useful comparisons include:

A site-level chart may reveal a problem that is almost invisible in the pooled dataset. But a between-site difference is a signal to investigate, not evidence of misconduct or poor practice. Differences in case mix, clinical setting, devices, local workflows or random variation may explain part or all of the pattern.

C. Examine longitudinal behaviour

In repeated-measures studies, each participant’s trajectory adds context.

Consider a variable that changes little between visits for most participants. Identical values may be genuine. Yet a sudden cluster of participants with precisely the same value at every visit can also warrant review of copy-forward behaviour, default values or collection procedures.

Longitudinal plots help reveal:

Clinical expertise is essential. A physiologically unusual trajectory cannot automatically be treated as a data-entry error.

D. Investigate missingness as a pattern

Counting missing cells is a starting point, not a complete analysis.

Ask where and when information is missing, whether missingness is related to particular centres, visits, participant groups or clinical events, and whether the collection process offers an explanation.

Missingness can reflect a legitimate clinical circumstance, a protocol-defined condition, a technical failure, a missed assessment or an undocumented operational issue. These mechanisms have different implications for data quality and statistical analysis.

The cleaning team should document and investigate operational causes. The statistical implications of missing data—including handling strategies and sensitivity analyses—must be addressed through the appropriate analysis planning and statistical expertise, not improvised as a cleaning correction.

E. Review metadata and the audit trail when warranted

Sometimes the pattern of how data were entered or changed is as important as the values themselves.

Depending on the system and approved review procedures, metadata may help identify unexpected entry times, concentrated batches of changes, unusual query histories, source-to-system delays or patterns potentially linked to training or technical issues.

EMA explicitly describes risk-based audit-trail review, including the possibility of detecting abnormal data patterns, unexpected timestamps, processing issues and system malfunction [2]. Such reviews must remain proportionate, properly governed and respectful of roles, confidentiality and access restrictions.

Exploration does not automatically establish the cause. It produces hypotheses that should be tested against documented evidence.

6. An anomaly is not an error—and an error is not always a simple correction

This may be the most important practical principle in the entire guide.

Imagine a laboratory result outside the expected clinical range. Several explanations are possible:

The appropriate response depends on which explanation is supported.

Finding after review	Appropriate general approach
Source confirms the unusual value	Retain it; document the review if needed. Consider clinical or safety escalation under the applicable procedures.
Source demonstrates a transcription error	Arrange an authorised, documented correction in the appropriate system.
Raw data are correct, but units were mismatched during transformation	Correct the controlled transformation, preserve raw data and provenance, and assess all affected records.
The evidence is insufficient to determine the correct value	Preserve the uncertainty; follow documented unresolved-data and analysis procedures. Do not invent a replacement.
The issue affects many records	Investigate root cause, scope, potential downstream impact and preventive actions.

The key is to separate detection, assessment and correction. These are different activities, and they may belong to different authorised roles.

Who can correct what?

In clinical trials, a data manager should not simply overwrite an investigator’s original entry because a check suggests a different value. ICH E6(R3) emphasises the need for clearly defined responsibilities and appropriate controls over data modifications. Any sponsor-initiated changes to investigator- or participant-entered data should follow applicable documented arrangements, respect the responsibilities of the investigator and preserve data traceability, and support corrections requested by investigators or participants with appropriate source support [1]. EMA guidance likewise emphasises authorised changes and an attributable audit trail [2].

The eCRF is not automatically the source record. The source may be a medical record, instrument, diary or another system; in defined direct-data-capture arrangements, the eCRF or other acquisition tool may itself hold source data. Source location and responsibility must therefore be identified rather than assumed [2, 3].

For derived or standardised analysis datasets, controlled transformations may legitimately create a corrected representation of raw inputs. But this is not permission to silently change the underlying observation. Retain the original value, transformation logic, version and traceability so that the result can be reconstructed.

7. Make corrections attributable, justified and reproducible

A correction is only as reliable as the evidence and process behind it.

For an individual value, a sound correction workflow should answer:

The audit trail is part of the evidence that data have remained interpretable and trustworthy throughout their lifecycle. EMA’s 2023 guideline sets out detailed expectations for traceable electronic changes, including the previous and current values, who made the change, when and—where applicable—why [2].

One caveat: not every quality problem should be “fixed” in the source system. A genuine protocol deviation, for example, may need to be documented and managed as a deviation. Altering a date to restore expected chronology would be the opposite of data integrity.

Worked example: from an exploratory signal to a controlled correction

Let us return to the hypothetical creatinine dataset. An initial exploratory plot reveals two distinct clusters. Records from most centres are distributed around 1.0, while records from one laboratory feed are concentrated around 90.

What should the data team do?

First, verify the signal. Check the variable definition, number of affected records, site or laboratory origin, collection dates and any recent changes in the transfer process. Do not label all higher values as errors.

Second, investigate provenance. Compare the raw laboratory files, their unit fields and the agreed import specification. Suppose the source files correctly contain µmol/L, while the target dataset labels every imported value as mg/dL.

Third, establish the scope and impact. Determine when the mapping problem began, which records were affected, and whether the incorrectly labelled values have already been used in safety review, eligibility assessment, interim outputs or derived analyses. Involve the relevant clinical, data and statistical stakeholders.

Fourth, correct the right layer. A verified mapping defect calls for a controlled change to the transformation or import process, with appropriate validation and authorisation—not manual substitution of laboratory results in the clinical source. Preserve original values and units and record the rule used for any standardisation.

Fifth, verify and prevent recurrence. Reprocess or otherwise remediate affected records under the approved procedure; compare counts, values, units and outputs; document the change and impact; and strengthen automated checks so the same defect is detected earlier.

Notice how the initial histogram did not provide the answer. It provided an investigation lead. Source evidence established the cause; governance determined the permitted correction; and quality control established whether the problem was actually resolved.

8. From isolated corrections to process improvement

Cleaning should not end with the individual record.

Suppose a site repeatedly enters laboratory values in the wrong unit. Querying and correcting each observation may be necessary, but it is also inefficient and leaves the underlying risk in place.

A stronger response investigates the cause:

Depending on the finding, corrective and preventive measures might include modifying the eCRF configuration through change control, clarifying instructions, validating a transfer mapping or providing targeted training.

We then need to verify whether the issue actually declines and whether historical data require a retrospective review.

This is an important transition: data cleaning moves from repairing records to improving the system that produces them. It becomes a source of evidence for better design, governance and risk management.

9. When is a dataset ready for analysis?

“Zero open queries” is a tempting definition of readiness. It is also incomplete.

Readiness must consider the study’s critical data, planned analyses, outstanding discrepancies, external data and documented acceptance criteria. A query can be closed without a value changing; some residual issues may be documented and appropriately handled; other unresolved issues may prevent an analysis or database lock because of their importance.

Before concluding that the data are ready, ask:

These considerations reflect the lifecycle approach in ICH E6(R3), including the need to establish appropriate data management steps before analysis [1]. The exact database-lock or interim-analysis criteria should follow the applicable study plan; the list above is a practical aid, not a substitute for an approved procedure.

10. A practical data cleaning playbook

The following playbook provides a structured operational framework for clinical data cleaning and quality review. Its activities, frequency and level of detail should be adapted to the study objectives, data criticality, source systems, identified risks and approved procedures.

Step	What to do	Main question	Expected evidence/output
1. Define	Review protocol, data specifications, critical variables, sources and collection schedule.	What needs to be trustworthy, and for which decision?	Critical-data map; definitions; planned controls.
2. Prevent	Configure field constraints, labels, units, completion guidance and relevant edit checks.	Which avoidable problems can be stopped at entry?	Tested and documented collection controls.
3. Check completeness	Review expected forms, visits, variables and missing-data reasons.	What is absent, and why?	Missingness listings and investigated discrepancies.
4. Run rule-based checks	Apply format, range, chronology, logic and cross-form checks.	Which records violate a justified rule?	Validated check outputs and triaged findings.
5. Reconcile sources	Compare required external datasets, mappings, record counts and related information.	Did anything change or disappear between systems?	Transfer/reconciliation results and issue log.
6. Explore distributions	Summarise quantitative and categorical variables; inspect outliers and unexpected patterns.	What looks unusual beyond the predefined rules?	Descriptive outputs, visualisations and review notes.
7. Compare groups and time	Review site-level, longitudinal and process-related patterns.	Is the problem concentrated or systematic?	Stratified analyses and investigation hypotheses.
8. Investigate	Verify relevant source information, clinical context, units, metadata and process history.	Is this a true observation, an error or an unresolved ambiguity?	Documented assessment and appropriate query/escalation.
9. Resolve and verify	Obtain authorised corrections where justified; preserve provenance and reassess affected data.	Was the right action taken, and can it be reconstructed?	Resolution, audit trail, QC and impact assessment.
10. Learn and finalise	Review recurring causes, residual risks and data readiness.	What should change in the process, and are the data fit for use?	Process improvements; final quality review; documented decisions.

A three-question checkpoint for every suspicious value

When a value attracts attention, pause before requesting or making a change:

If a cleaning process cannot answer these three questions, it risks producing tidy data without producing trustworthy data.

11. Common mistakes in Clinical Data Cleaning

12. The larger point: quality is a property of decisions and processes

Data cleaning is often described as a technical activity: execute rules, identify discrepancies, send queries, modify records, repeat.

That description misses much of the professional judgement involved. A reliable clinical dataset emerges from well-designed collection instruments, clinically informed checks, exploratory review, responsible query management, justified changes, traceable transformations and clear accountability.

It also depends on knowing when not to change a value.

Data quality is much more than a database-cleaning exercise: a continuous process of making data understandable, verifiable and appropriate for the decisions built upon them. And it connects directly to data governance: definitions, ownership, access, provenance, risk, controls and responsibilities determine whether quality can be maintained beyond an individual cleaning cycle.

The final objective is not a dataset that looks perfect on a dashboard.

It is a dataset whose strengths, limitations, transformations and remaining uncertainties can be explained—and whose use can be defended.

That is what makes clinical data trustworthy.

---

References and further reading

Editorial note: The examples in this article are illustrative, not accounts of identifiable studies or participants. The playbook is an educational framework and does not replace trial-specific plans, standard operating procedures, clinical review or applicable regulatory requirements

“The system is validated.”
In clinical research, that sentence sounds reassuring.
My instinct, however, is to ask a second question:

Validated for what?

The question matters because validation can easily become a label. Once a system has been described as “validated”, there is a temptation to treat that status as an intrinsic property of the technology.
But the same platform can support very different trials, configurations, workflows, interfaces, automated checks and calculations.
And those differences matter.

What ICH E6(R3) actually asks us to consider

Section 4.3.4 of ICH E6(R3) takes a risk-based approach to computerised systems validation.

The validation approach should consider the system’s intended use, the purpose and importance of the data or records it manages, and the potential impact of the system on participant rights, safety and the reliability of trial results.
Validation should then demonstrate that the system meets established requirements for completeness, accuracy and reliability, and that its performance is consistent with its intended purpose.
ICH goes further.

Standard functionality, protocol-specific configurations and customisations — including automated data-entry checks and calculations — should be validated appropriately. Interfaces between systems should also be defined and validated, while different degrees of validation may be appropriate depending on the type of system and how it is configured.

That distinction is important.

---

Imagine an EDC platform already used across dozens of clinical trials.
The underlying software may have extensive validation evidence.
But a new study can introduce a different database configuration, edit checks, derived variables, integrations with external systems or automated calculations.
The relevant question is therefore not simply:

“Is the EDC validated?”

It is also:
“What exactly has been validated for this intended use?”

Validation as evidence, not status

This is the distinction I find most useful.
Validated is not a context-free property of a system. It is a conclusion supported by evidence within a defined context of use and risk.
That changes the conversation.
Instead of looking for a validation label, we start looking at the chain of reasoning behind it:

What is the system expected to do?

Which data and processes are critical?

What could go wrong?

Which requirements address those risks?

What evidence demonstrates that those requirements have been met?

And what happens when the configuration or intended use changes?

A risk-based approach should help direct validation effort toward what matters most rather than applying the same level of control indiscriminately.

Where Clinical Data Management meets Data Governance

There is another implication that interests me.

A possible interpretation is that this way of thinking brings Clinical Data Management increasingly close to Data Governance.

Not because Data Governance should be added as another layer after data collection.

But because many of the questions surrounding validation are fundamentally governance questions:

Who is responsible for the system and its configuration?

Which requirements apply?

Who can access or change the data?

How are changes controlled?

How is traceability maintained?

Which evidence demonstrates that controls continue to work?

The technology is only one part of the system.

The rest consists of processes, responsibilities, controls, data flows and evidence surrounding it.

And those elements determine whether we can trust the data produced or managed through that technology.

The false comfort of “validated”

A validation package can provide evidence of control, but the word validated, when detached from its context, can create exactly the opposite: a false sense of security.

The more complex clinical data ecosystems become — with EDCs, ePRO, wearables, laboratories, EHR-derived data and multiple system integrations — the less useful a binary validated/not-validated mindset becomes.

What matters is whether the validation evidence remains appropriate for the specific purpose, configuration and risk we are asking the system to support.

So the next time someone tells you that a clinical system is validated, perhaps the most useful response is not reassurance.
It is curiosity.
Validated for what?
When you assess a clinical system, what do you challenge first:
the intended use, the configuration, or the validation evidence itself?