Clinical data cleaning: a practical guide from first entry to database lock
Data cleaning is the set of checks, queries and reviews that turns entered data into data you can analyse. This guide lays out the process in order, shows which steps are automatic and which need a person, and gives the metrics that tell you when you are ready to lock.
- Automatic vs manual checks
- Query lifecycle and metrics
- Lock-readiness checklist
Free sandbox · No credit card · 21 CFR Part 11 aligned
Open queries
14
Forms with missing data
9
SDV complete
92%
Oldest open query
11 days
3 open
8 open
3 open
The process in five lines
- Prevent first: edit checks at entry stop most errors before they are saved, and cost the least to fix.
- Then review: checks cannot find everything. Review listings across forms for things only a person notices, such as an adverse event with no medication and a date before consent.
- Query, resolve, close: every discrepancy becomes a question to the site, with an answer, a change reason if data changes, and a record.
- Reconcile: safety, laboratory and other external data must agree with the clinical database.
- Lock on evidence: lock when open queries, missing data and verification meet the thresholds in your plan, not when the calendar says so.
Definition
What clinical data cleaning is, and why it is not one step
Clinical data cleaning, sometimes called data validation or data review, is the process of finding and resolving errors, inconsistencies and gaps in clinical trial data so that the final dataset is accurate, complete and traceable. It sits inside clinical data management and runs continuously from the first participant, not in a rush before lock. The aim is not perfect data, which does not exist, but data whose remaining errors are unlikely to change the conclusions and whose handling can be shown to an inspector.
It is also not only about numbers. A date of birth that makes a participant 140 years old is easy to catch. A visit that happened after the participant withdrew, a dose recorded for a day the participant was hospitalised, or two entries for the same adverse event are harder, and they usually need a person looking across forms. A good process has layers: automatic checks for the simple things, structured review for the rest, and a defined way to ask the site and record the answer.
The rules that drive cleaning live in the data management documents. Your data management plan says who does what, your data validation plan lists the checks, and your CRF completion guidelines tell sites how to avoid errors in the first place. This guide describes how those pieces work together in practice. If you are the person doing this job, see also software for clinical data managers.
The process
Seven stages of cleaning, in order
- 1
Plan the checks before data arrives
List each check in the data validation plan: the field, the rule, the message, and whether it is automatic or a manual review. Agree the priority of each.
- 2
Catch errors at entry with edit checks
Range high, range low and custom value rules raise an auto-query as soon as a site enters a value that breaks a rule. Calculated fields (BMI, QTc, eGFR) fill themselves in read-only, so a calculation error cannot be typed in. See edit checks.
- 3
Review data across forms
Run cross-form reviews on exported or on-screen data: dates in sequence, adverse events against medications, visits against the schedule, protocol deviations against the visit record.
- 4
Raise and resolve queries
Send each discrepancy to the site with a clear question. The site answers, corrects or confirms, and the change carries a reason. Close the query only when the data and answer agree. See query management.
- 5
Verify against source
Source data verification confirms entered values against source documents. Set which fields need it, risk-based, and track completion. See source data verification.
- 6
Reconcile with other data sources
Compare serious adverse events with the safety database, lab values with the central lab transfer, and device or diary data with its source. Mismatches become queries.
- 7
Confirm readiness and lock
Check the readiness criteria below, get sign-offs, then lock. See database lock and the lock and unlock workflow.
Types of checks
Which discrepancies are caught how
Matching the method to the kind of error keeps queries meaningful and avoids flooding sites.
| Type of discrepancy | Example | Best caught by |
|---|---|---|
| Out of range | Systolic blood pressure entered as 1200 | Range edit check at entry |
| Missing required data | Visit 3 weight blank | Required flag at entry; missing-data review |
| Wrong format or type | Text in a numeric field | Field type and validation at entry |
| Derived value mismatch | BMI entered by hand disagrees with height and weight | Calculated field (read-only) |
| Cross-field logic | Adverse event end date before start date | Custom value rule or listing review |
| Cross-form logic | Medication started for an event that is not on the AE form | Manual cross-form review |
| Visit and window | Assessment outside the protocol window | Visit window rules and deviation review |
| Duplicates | Same adverse event entered twice | Manual review of listings |
| External mismatch | SAE in safety database not on AE form | Reconciliation |
| Transcription error | Value differs from source document | Source data verification |
Capture runs the entry-time checks and raises auto-queries; cross-form and external reviews are done on exported data or on-screen review by your team.
Queries
Auto-queries at entry, and a query log you can read
Capture's edit checks raise a query automatically when a site enters a value that breaks a rule, with your message template, the entered value and the limit filled in, and a priority you set. The query appears in the action list, so the site sees it the same day, not at the next monitoring visit.
- Range high, range low and custom value rules on numbers, dates, choices and text.
- Low, normal or high priority per rule.
- A field-level audit trail records each change with old value, new value, user, timestamp and reason.
- By-site filtering of data and exports, so you can see which site generates most queries.
Break a rule on purpose and watch the query appear
Add an edit check in the free sandbox, enter a bad value and see the auto-query. Free, every feature, no credit card.
Measure it
Metrics that tell you whether cleaning is working
Cleaning is easier to manage when you can see it. A small set of numbers, checked weekly, will show where effort is needed. Choose thresholds in advance and write them in the plan.
Query metrics
Number of open queries, age of the oldest query, median time to answer, and queries per 100 data points by site and by form. A site with ten times the rate of the others usually needs retraining or clearer instructions, not more queries.
Completeness metrics
Expected versus entered visits, forms with missing required fields, and time from visit to entry. Late entry is the cause of most last-minute cleaning rushes.
Verification metrics
Percentage of fields marked for source data verification that have been verified, and the error rate found on verification. A high error rate suggests widening verification or fixing the form.
Database lock readiness
Checklist: are we ready to lock?
Use criteria from your plan. These are typical items; add study-specific ones.
All queries closed
No open or unanswered queries, or documented decisions on any accepted exceptions.
Missing data resolved
Every expected visit and required form is entered, or explicitly recorded as not done with a reason.
Source data verification complete
All required fields verified to the level the plan sets, with findings closed.
Safety reconciled
Serious adverse events match the safety database and the clinical database.
External data reconciled
Lab, ECG, wearable or other transfers agree with the clinical database.
Coding complete
Medical history, adverse events and medications coded to the dictionary named in the plan, and reviewed.
Protocol deviations classified
All deviations recorded and categorised. See protocol deviation tracking.
Sign-offs obtained
Principal investigators, data manager and statistician confirm in writing; users locked out of editing after lock.
Common failures
Where cleaning goes wrong
Everything left to the end
Months of unresolved queries and late entries pile up. Weekly review keeps lock to days, not weeks.
Checks nobody tested
A rule with the wrong operator floods sites with false queries. Test each check on valid and invalid data before go-live.
No reason for change
Edits without a documented reason fail inspection. Capture records the reason for change on every edit.
Read moreFree-text answers to queries
Answers like "see source" do not close the loop. Require the corrected value or a clear confirmation.
Ignoring form design
Many queries trace to ambiguous fields. Fix the form and the instructions.
Read moreCoding and reconciliation as an afterthought
They need their own owner, tools and timelines; plan them in the data management plan.
What is clinical data cleaning?
It is the process of finding and resolving errors, inconsistencies and gaps in trial data through edit checks, review, queries, reconciliation and verification, so the dataset can be analysed and defended.
What is the difference between an edit check and a query?
An edit check is a rule. A query is the question raised when data breaks a rule or a reviewer spots a problem. In Capture, an edit check raises an auto-query automatically.
Can Capture reconcile safety or lab data from other systems?
Capture holds your clinical data and exports it with a data dictionary for comparison. It does not offer import tooling for reconciliation, so the comparison is done by your team outside Capture.
Does Capture code adverse events with MedDRA?
No. Coding is done by your team with the licensed dictionary named in the data management plan.
When is the data clean enough to lock?
When the criteria in your plan are met: queries closed, missing data resolved, verification and reconciliation complete, and sign-offs obtained.
Is this a regulatory requirement?
Data quality and traceability expectations come from GCP and regulators, but the specific steps are yours to define. This is guidance, not legal advice.
Keep exploring
Related pages
Run a cleaning cycle free
Free sandbox with every feature. No credit card, and you pay only when you go live with real participants.