How to Fix Bad Historian Data Without Overwriting the Original
A failed transmitter pegs a tag at full scale for eleven hours. Correcting the record with an audit trail that survives review, raw sample intact.
A Type K thermocouple opens up on a burnout-upscale input card. The card does exactly what it is supposed to do and drives the channel to full scale, so the historian records 850 °C for the eleven hours it takes for maintenance to get to the reactor. The historian did nothing wrong. That was the number on the wire.
Then quality pulls the batch report and wants to know why the vessel spent half a shift above its release limit.
Nobody argues about whether to correct that interval. The argument, every time, is about what happens to the original value once you do.
Corrections never happen in place
This is the one rule I would not bend. The corrected value goes into a new layer — a new version, a substitute flag, a correction record joined at query time — and the raw sample stays exactly where it was, byte for byte.
21 CFR Part 11.10(e) puts it better than I can: audit trails must be computer-generated and time-stamped, and "record changes shall not obscure previously recorded information." That clause is aimed at pharma, but the design rule is right for a cement plant too. Same idea appears as the Original in ALCOA+, and as the requirement in EU GMP Annex 11 that any change to data be traceable to the person who made it, with a reason.
I have never regretted keeping the raw layer. I have regretted the other thing — a site where an engineer "cleaned up" a bad transmitter run directly in the archive, and eighteen months later nobody could prove to a customer what had actually been recorded during the disputed lot. The correction was almost certainly right. It was also undefendable.
Check what your historian already gives you before you build a correction table
Half the sites I see hand-roll a correction table in SQL because nobody read the manual.
If the data comes across OPC UA, Part 11 (Historical Access) already covers this. HistoryUpdate with UpdateDataDetails takes an Insert, Replace or Update action, and ReadModifiedDetails reads back the superseded values with ModificationInfo attached — update type, user name, modification time. The "what did it say before?" question is answered by the server, not by a spreadsheet someone keeps on a share drive.
Most major historians have their own version. PI archive events carry Substituted and Questionable flags plus annotations, so a corrected value can be visibly marked as corrected in every client that bothers to read the flag — the catch being clients that do not. Check yours before you design anything.
Build the custom correction table only if the platform genuinely lacks the feature, and if you do, understand what you have signed up for: every report that reads the raw tag must now join the correction table. The day somebody writes a new report and forgets the join is the day the process quietly stopped working, and nothing will tell you.
Classify before you edit
Not every correction carries the same risk, and lumping them all under "manual edit" makes the audit log unreadable. Store a category with the change:
| Correction type | Example | What actually goes wrong |
|---|---|---|
| Failed instrument | Transmitter at 21.5 mA for hours, historian sees full scale | Quality release blocked, or worse, granted on a substituted number |
| Missing interval | Historian connector down, gap in the archive | Replacement is estimated but reported as measured |
| Wrong context | Batch ID corrected after the run | Values move between lots; MES never hears about it |
| Counter repair | Totalizer reset on PLC restart | Shift totals double-count or lose a whole run |
| Late manual entry | Offline lab result entered next morning | Sample time and entry time get conflated |
"Show me every communication-outage backfill from Q2" is a query. "Show me every manual edit" is a list of 4,000 rows nobody will read.
Measured and estimated are not the same value
This is where I get fussy. If the transmitter was sitting at NAMUR NE 43 fault current — anything at or above 21.0 mA, or at or below 3.6 mA — then there was no measurement during that window. There is nothing to correct, only a hole to describe.
Store the provenance alongside the value: measured replacement from a validated instrument, lab result with both sample time and analysis time, interpolation between known-good samples, a figure back-calculated from mass balance or totalizer delta, or an operator's paper log. Reports that go to a customer or a regulator need to be able to separate the first two from the last three.
Given the choice, I would rather ship a report with a visible gap than a smooth curve that was drawn by an interpolation routine. A gap starts a conversation. A plausible line ends one, wrongly.
Boundaries are where corrections actually break
The middle of the interval is almost always fine. The damage is at the edges.
Store the correction interval in UTC, always. If your site observes DST, 01:30 local happens twice in the autumn, and a correction entered in local time will either miss an hour or repair it twice. I have watched a team spend an afternoon on a report that was off by exactly 3,600 seconds.
Then settle, in writing, whether the interval is inclusive at both ends, which timestamp the record carries — event time, entry time, approval time, or historian server time, and they are four different numbers — and how the archive interpolates across the corrected span. A stepped tag and an interpolated tag will produce different report totals from the same correction.
Counters deserve their own paragraph. When a totalizer resets to zero on a PLC restart, the delta for that period goes negative, and most report logic clamps a negative delta to zero and silently eats the production. Correcting the visible bad sample does not fix that. You have to repair the delta across the rollover, then re-run every calculated tag downstream of it.
Who signs
Requester finds the interval and proposes a value. Reviewer checks the evidence — work order, calibration certificate, network incident ticket, alarm log, lab sample ID. Approver accepts it for reporting use.
On a three-person site, one person is all three. That is fine and I would not pretend otherwise. What is not fine is a shared engineer login, because at that point the audit trail records a role, not a person, and it cannot be defended by anyone.
Reason codes should be a fixed enum, not free text. Keep the comment field too, but the enum is what makes the log searchable in two years when the person who wrote the comment has left.
The value changing is not the end of the job
A correction is done when the reports agree with it. Check the trend across the corrected interval and an hour either side, the shift and batch reports, any MES reconciliation that consumes historian totals, and every calculated tag that reads the corrected signal.
Watch the MES boundary in particular. If a correction moves a quality value from lot A to lot B, the historian edit does not propagate — the ISA-95 level 3 record still shows the old association, and the two systems now disagree about the same physical material. Somebody has to close that by hand.
For anything that touches a release decision, save the before-and-after report output. The audit trail should show that the number changed. The saved reports show what the change did.
Next thing to check on your own system: pull twelve months of correction records and group them by reason code. If instrument failure dominates, your correction process is working and your maintenance schedule is not. If communication outage dominates, stop refining the approval workflow and go fix the connector. And if the log comes back empty, that is not a clean plant — it means nobody ever turned the audit trail on.