Your spec is a slowly changing dimension

What happens to your stability history if you tighten a specification? In case your system evaluates results against “the current limit”, every historical measurement near the old boundary may silently change its out-of-specification status the moment the new limit lands. Nothing crashes but your trend reports may suddenly paint a very different picture.

Evaluate based on the measurement date

AssayVault treats specifications as versioned facts with effective dates. Method-level, molecule-level and effective specifications form a coherent history and every analytical result is assessed against the limit in force when the sample was measured. In such a setting, a revision creates a new version instead of reaching back and re-flagging the past.

Traffic law has always worked this way and illustrates the scenario: a speeding ticket from 2023 is judged against the limit posted on that road that day. Lowering the speed limit in 2026 does not retroactively turn previous drives at the old limit into violations. Nobody would accept the alternative on the road.

What data warehousing calls this

The problem has both a name and a well-worn solution with an approach Ralph Kimball codified in the 1990s. In dimensional modelling, still the default shape of an analytical warehouse, measured numbers live in a fact table (here, one row per analytical result), everything that describes them lives in dimensions: the molecule, the method, the storage condition, the specification. A specification should be considered as a slowly changing dimension: it changes over time which raises the question of how to handle the change.

Kimball’s original catalogue had three responses, two of which matter here. Type 1 overwrites the old value and the dimension always shows today’s truth. However, the previous version is gone which is fine for correcting a typo in a molecule name but an issue for an acceptance limit. Type 2 chooses a different approach and writes a new row for each version. Each version carries its validity window which means a result can be joined to the description that applied when it was recorded. AssayVault’s specifications employ Type 2 which is the underlying reason an old result keeps its old verdict.

None of this was originally intended for pharmaceutical data. It comes from retail analytics where “which sales region did this order belong to at the time” has the same analytical shape. It fits a regulated evaluation chain because both settings care about the same underlying principle: statements that stay true after the world changes.

Honesty about derived values

The discipline extends to the inputs. Where a measurement date has to be derived rather than read from the export, the row says so explicitly (measurement_date_source = 'derived'). An evaluation is only as defensible as its weakest timestamp which means the provenance of that timestamp is data instead of a footnote.

The rule: anything a result is judged against (limits, constants, rule versions) is a dimension with history instead of a mutable lookup. The same applies one level up, to the guidelines themselves: The stability rulebook is being rewritten looks at what happens when the rule base changes.

The payoff shows up by avoiding uncomfortable conversations. “Why was this batch not flagged in 2024?” has a precise answer: because the 2024 limit said so, and here is that limit, with its effective window, in the same warehouse as the result.