How to Downsample Historian Data Without Losing the Trip That Mattered

Six weeks after startup a customer asks what the reactor pressure did during batch 4471. You pull the trend and it's a smooth line — because the purge job that ran overnight replaced the raw 1-second samples with hourly averages, and the 90-second dip that tripped the interlock averaged straight out of existence. The number is technically still in the database. The event isn't.

Retention and downsampling usually get configured late, when the disk estimate is already tight and someone wants a knob to turn. That's the wrong moment to decide what a value has to still answer after a week, a month, a year, and an audit cycle. It's an engineering decision wearing a storage setting's clothes.

Start from the question each user actually asks, because they don't all need the same resolution:

User	Typical question	Data shape needed
Operator	What happened during the last upset?	Raw or near-raw trend around the event
Process engineer	Did the loop oscillate this month?	Enough resolution to see cycling
Maintenance	How many starts did the pump make?	Event count or counter with good quality
Energy team	What was daily usage by area?	Validated rollup with source coverage
Quality or compliance	What value was recorded for this batch?	Original record or approved reconstruction rule

Write the retention plan so it says four things per tag class: what is kept raw, what is downsampled, what is deleted, and who signed off on losing the detail. That last column is the one that saves you in a meeting.

Classify tags before you touch a retention rule

One rule for every tag is how you either blow the disk budget or throw away the data you needed. A motor run bit, a critical reactor temperature, a lab result, and a screen-only diagnostic flag have nothing in common once a month has passed. A split that holds up in the field:

• Critical process values: temperatures, pressures, flows, levels, recipe actuals, utility conditions.
• Equipment state and alarms: run state, fault state, mode, permissive summary, alarm events.
• Counters and totalizers: energy, water, air, batch quantity, runtime hours.
• High-volume diagnostics: communication quality, scan time, heartbeat, internal driver counters.
• Temporary commissioning tags: tags created to tune, test, or troubleshoot a startup.

Record raw retention and long-term retention separately for each group. What I usually land on: raw critical process values for 18 months, then a 1-minute aggregate (or validated event-based records) for 7 years; equipment state and alarm events kept long, because they're cheap and they're what you reconstruct incidents from; high-volume diagnostics 30 to 90 days unless there's an open support case. Commissioning tags get a delete date on the day you create them — otherwise they live forever and nobody remembers what TT_TEST_3 was for.

Downsampling is not compression — it changes the answer

Pick the wrong aggregate and you don't lose precision, you lose the event. The method has to match what the tag is:

Method	Good for	Risk
Average	Slow analog process values	Hides short spikes and trips
Min/max pair	Envelope review	More storage than a single average
Last value	Step-like state tags	Misleading for analog values
Time-weighted average	Process exposure calculations	Requires valid quality and duration
Event count	Starts, trips, alarms	Needs clear edge rules
Delta total	Utility consumption	Must handle rollover and reset

For any analog you'd ever pull up during an upset review, store min and max alongside the average for each interval. A single hourly average will make a steam-header dip vanish; min/max keeps the excursion visible even after the raw is gone. For state tags, percent-of-interval-on beats last-value-in-the-hour — last value tells you the pump was running at 10:59:59 and nothing about the other 3599 seconds.

If your historian speaks OPC UA Historical Access (Part 11), these aren't just vendor labels — the aggregate functions are defined in OPC UA Part 13: TimeAverage, Minimum, Maximum, Count, Interpolative, and the rest. A client that asks for TimeAverage over a stretch with bad-quality samples is supposed to get a quality flag back with the result, not a clean-looking number. Which is the whole next point.

A retained value without its quality lies

A 10-minute average built from two valid samples looks exactly as authoritative as one built from 600 — unless you kept the quality with it. Long-term rows should carry at minimum:

• Timestamp and time basis.
• Value or aggregate values.
• Engineering unit.
• Source tag name and version if names can change.
• Quality or validity percentage.
• Calculation method.
• Time zone or UTC convention.
• Source sample count when relevant.

This is the difference that surfaces months later when a report is challenged. A daily energy total from 99.8%-valid data and one computed straight through a four-hour comms outage are not the same number, and only the metadata tells them apart.

Legal records and trends-I-like-to-look-at are different animals

Some data you keep because engineers want to trend it. Some you keep because a contract, a customer, a regulator, or your own quality system says you must. If you're under 21 CFR Part 11 or an equivalent GxP regime, the second group carries rules the first doesn't — audit trail, no silent edits, defined retention — and downsampling or recalculating those records is not a decision you make alone. Decide, per record type:

• The authoritative source: historian, MES, batch system, or LIMS.
• Whether raw samples must be preserved.
• Whether recalculation is allowed after late data arrives.
• Who can edit or delete records.
• How backups are restored and verified.
• How clock changes, daylight-saving time, and NTP faults are handled.

If the historian isn't the system of record, don't let a trend screenshot become the only evidence. Link the tag to the batch, lot, or report record that actually owns the decision.

Test the purge on real tags before it runs for real

A purge or archive rule is a one-way door. Before it runs in production, prove it on data you can still check:

1. Pick one fast analog tag, one slow analog tag, one state tag, one counter, and one diagnostic tag.
2. Export a period before downsampling and after downsampling.
3. Compare peak, minimum, total, state duration, and event count.
4. Check that trend displays clearly show when data is aggregated.
5. Confirm reports do not mix raw and downsampled data without labeling.
6. Restore archived data to a test system and run the same query.
7. Confirm backup retention is longer than the historian purge delay.

And give the purge job a dry-run report — tag count, row count, oldest and newest timestamp, estimated space recovered — before it deletes a single row. A purge that can't tell you what it's about to remove has no business running unattended.

The failures you'll actually see

Clean trend, no trip. The downsample stored averages only. Add min/max or event markers for anything used in upset review.

Daily totals move after the report went out. Late data or backfill recalculated a closed period. Define a freeze point and a separate correction process — don't let backfill silently rewrite history.

The archive exists but a restore takes a day. Nobody ever tested the restore. Measure restore time at commissioning, not during the audit that needs it.

A tag rename breaks two-year-old reports. Long-term data was tied to the display name. Keep a stable tag id or an alias history.

Disk fills with retention configured correctly. Exception deadbands too tight, a duplicate collector, or calculated tags generating more rows than the estimate assumed. Sort your top talkers by row count, not tag count — one chattery analog can outweigh a thousand well-behaved ones.

Review all of this a month after startup. Real operation is the first time you learn which tags are noisy, which data you actually reach for, and which commissioning tags can go. The plan you wrote before startup was a guess; the one you fix in month two is the real one.