TOOL NOTES

Normalize on write or on read: where the math belongs

Store raw and derive visibly, or write clean rows once. The layered pattern keeps the evidence and the convenience at the same time.

Every odds pipeline hits the same fork: do you normalize the data when it arrives, or when someone reads it? Normalize on write and every reader gets clean, uniform rows. Normalize on read and the store stays faithful to the source. Both are defensible; the wrong choice is the accidental one, made by whichever file you happened to edit first.

The case for normalizing on write

Writing the normalized form means the expensive decisions happen once. Keys get resolved, formats get unified, margins get removed, and every downstream consumer reads the same clean shape. A dashboard, a model and a report all see identical numbers because the differences were settled at the door.

The cost is rigidity. The write path bakes in the removal method, the key choices, the rounding. If you later decide the power method suits longshots better than proportional, every row you wrote under the old choice needs recomputing, and the raw inputs may be gone.

The case for normalizing on read

Keeping the store raw keeps the evidence. Prices as served, timestamps as sent, and every derivation is a function over the source that you can re-run when your method changes. The normalization guide stresses keys that keep meaning intact, checked 2026-11-04, and raw storage preserves every bit of it by default.

The cost lands on the readers. Each consumer re-implements the same conversions, and two consumers with slightly different versions of the same math produce slightly different answers to the same question. That drift is quiet and poisonous: two dashboards disagreeing by a cent, and nobody knows which is right.

The shape that holds both

The pattern that survives is a layered one. Store the raw row, always, with its keys and timestamps intact. Then write the normalized form as a second, derived record: clearly marked as derived, carrying the method and version that produced it, as the sanity discipline of logging inputs and outputs suggests.

Derived data is a cache of a decision. When the method changes, you recompute the derived layer from the raw one and nothing is lost. When readers disagree, the raw row is the referee.

A rule for choosing

Small projects with one consumer can normalize on write and stay honest, because one reader means one interpretation. The day a second consumer arrives with its own math, the single-interpretation assumption breaks.

If you are building anything that will be re-analyzed, re-modeled or re-priced later, keep the raw. The historical data guide makes the same point about archives: sampling and revision semantics decide whether history can be re-read at all, checked 2026-11-04. Your own store is no different.

Write raw, derive visibly, version the derivation. The store stays the witness, and every answer it gives stays reproducible.