← Writing

Stop storing status

· 3 min · data platform · modelling

Most systems I have worked on carry a column like current_stage. Something moves through a pipeline, and at each step a piece of code updates that column so screens can read it cheaply. It is the obvious design, and I have written it more than once.

The trouble is that it is a cache, and nobody treats it like one.

How it drifts

A status column is only correct if every path that advances the work also writes it. In practice, the paths multiply: the happy path, a bulk import, a correction made directly against the database, an integration that was added later by someone who did not know the column existed, a retry that runs the update twice.

Each of those is individually forgivable. Together they mean the column is an assertion about the world that nothing verifies. And because screens read it and not the underlying events, drift is invisible until someone notices that a board says one thing and the shop floor says another.

We had exactly that: a design's recorded stage and the steps actually logged against it disagreed, and there was no single writer to blame.

Stored: every writer must remember happy path bulk import manual fix a later integration forget to write it current_stage a cache with no owner reads Board drifts, silently Derived: one copy, nothing to sync happy path bulk import manual fix append event log the only copy latest step stage derived at read time Board Stored: every writer must remember happy path bulk import manual fix later integration two of them forget current_stage a cache with no owner Board drifts, silently Derived: one copy, nothing to sync every writer appends — no exceptions event log latest step = the stage derived at read time — cannot drift
The stored column is a second copy of something the log already knows, kept correct only by everyone remembering. Deriving it deletes the copy, and with it the reconciliation job.

The events already know

The thing worth noticing is that the answer was never missing. Every step taken against a piece of work was already being written to a log: who did what, to what, when. The stage was not extra information. It was a summary of that log, kept in a second place, allowed to disagree with the first.

So the fix was not to hunt down every writer and make it more careful. It was to stop storing the answer:

A record's stage is the latest step in its own event log.

Derived at read time, from the only data that is written by the act of doing the work. There is nothing to keep in sync, because there is only one copy. A correction made directly against the log immediately produces the right stage everywhere, with no reconciliation job.

What it costs

Two things, and both are worth paying.

The first is a join, and often a window function: the latest row per record rather than a column read. That is a real cost, and it is why the pattern gets rejected early in a project. It is also almost always cheaper than people assume once the log is partitioned sensibly, and enormously cheaper than the reconciliation script you write instead.

The second is that you must decide what "latest" means, precisely. Timestamps tie. Two steps land in the same second, or an import stamps a batch identically, and a query that orders only by time returns whichever row the engine felt like. That is not a rare edge case; it shows up the first week. The ordering needs a stable secondary key so it is a total order rather than a nearly-total one.

When a cache is still right

None of this argues against derived columns in general. If a figure is genuinely expensive to compute and slightly stale is acceptable, materialise it, but deliberately, with a name that says it is derived, and with a job that rebuilds it from the source.

What does not work is a column that looks authoritative, is written by hand from a dozen places, and has no owner. That is not a cache. It is a rumour with a schema.

Keep reading