Batch data review

Why it matters

Before a batch of drug substance can be released, someone has to confirm that every required test was done and every result meets its specification. A single missed out-of-specification (OOS) result can put an unsafe batch on the market; a sloppy investigation that "tests into compliance" can lead to regulatory action. Batch data increasingly lives in databases (LIMS and manufacturing execution systems), so being able to query it, not just read printouts, makes review faster and more reliable.

Specifications, OOS and OOT

• A specification is the list of tests, methods and acceptance criteria a batch must meet, for example pH 5.8 to 6.4 or endotoxin not more than 0.5 EU/mg. Some limits are two-sided; others have only a lower limit (purity at least 95.0 percent) or only an upper limit (endotoxin).
• An out-of-specification (OOS) result falls outside an acceptance criterion.
• An out-of-trend (OOT) result is inside specification but unusual compared with previous batches, for example a sudden step change. It is a warning worth investigating, just like a Westgard warning in clinical QC.

Boundary values matter: if the limit is "not more than 0.5" then 0.5 passes and 0.51 fails. Specifications also state how results are rounded before comparison, so follow the written rule.

What a batch data review checks

1. Completeness: every required test has a result, from a valid run, with the right method version.
2. Compliance: each result meets its specification.
3. Trends: results compared with previous batches (control charts).
4. Related records: the executed batch record is complete, deviations are closed or assessed, environmental monitoring is in control, and equipment was qualified and calibrated.
5. Data integrity: audit trails show no unexplained changes, deletions or repeated injections.

Only after QA is satisfied is the batch released, and a certificate of analysis (CoA) issued.

Querying batch data in SQL

Suppose a LIMS has results(batch_id, test, value, analyst) and specs(test, lower_limit, upper_limit, units), where a missing limit is stored as NULL. Joining them puts every result next to its limits:

SELECT r.batch_id, r.test, r.value, s.lower_limit, s.upper_limit
FROM results AS r
JOIN specs AS s ON s.test = r.test;

To keep only OOS results, add a condition that the value is below the lower limit or above the upper limit. In SQL, any comparison with NULL is "unknown" rather than true, so a test with no upper limit simply never fails the upper check, which is exactly what we want here. (If you write it the other way round, as "keep rows that are within limits", NULL limits would wrongly exclude rows, so be deliberate.)

Worked example. pH limits 5.8 to 6.4. Batch B-202 has pH 6.6: 6.6 > 6.4, so it is OOS. Batch B-204 has pH exactly 6.4: not greater than 6.4, so it passes.

What happens after an OOS

In the US, FDA's guidance Investigating Out-of-Specification (OOS) Test Results for Pharmaceutical Production sets expectations: https://www.fda.gov/regulatory-information/search-fda-guidance-documents/investigating-out-specification-oos-test-results-pharmaceutical-production-level-2-revision (Checked on 2026-10-23.) In outline:

1. Phase I, laboratory investigation: the analyst and supervisor promptly check for an obvious, documented laboratory error (wrong dilution, instrument failure, calculation error), preserving the original samples and data. Only a clearly identified lab error can invalidate the result.
2. Phase II, full-scale investigation: if no lab error is found, the investigation extends to manufacturing: batch record, deviations, raw materials, equipment and other batches that might be affected.
3. Retesting and resampling follow predefined rules. Testing again and again until a passing result appears ("testing into compliance") is not acceptable, and averaging cannot be used to hide an individual OOS value.
4. The conclusion, root cause and CAPA are documented, and QA decides the batch's fate.

Common mistakes

• Reviewing only the tests that look interesting, not checking completeness.
• Treating a value exactly at the limit as a failure (or a value just past it as a pass) without reading the specification wording.
• Mishandling NULL limits in queries, which silently drops or wrongly flags rows.
• Discarding an OOS result as "probably a lab error" without evidence.
• Ignoring OOT results because they are technically in specification.

Quick recap

• Specifications define tests and acceptance criteria; OOS is outside them, OOT is unusual but inside.
• Review covers completeness, compliance, trends, related records and data integrity before QA release.
• In SQL, join results to specs and filter value < lower OR value > upper; know how NULL behaves.
• OOS investigations: Phase I lab check, Phase II full-scale, controlled retesting, documented conclusion; never test into compliance.

Try it

Open the Do tab and write the WHERE clause that flags out-of-specification results.