Making a versioned datastore queryable without losing its history

Context

A platform I lead an engineering team on stores versioned records: not just the current state of something, but every state it has held and every correction since. That history is the point of the system. Anything downstream that loses it is worse than useless, because it looks authoritative and is quietly wrong.

This study is the analytics workstream, which I am accountable for. The client is current, so it is not named, and neither is the platform.

The problem

Versioned stores answer two questions at once: what was true about a record at a point in time, and what did we believe about it as of another point in time. Keeping those separate is what makes the data defensible later.

Queries against the primary store were fast along the paths that had been indexed and impractical everywhere else. People wanted to query on any property without first asking someone to add an index for it.

That means an analytical replica on a columnar backend. A columnar backend has no cheap UPDATE. The versioning model closes an interval every time a new version of a record arrives, which is an UPDATE by another name. Those two facts had to be reconciled without losing the semantics that make the history trustworthy.

What I did

• Modelled the replica as append-only record and closure tables. A new version is an insert. Nothing on the write path updates a row.
• Derived interval closure at read time with an anti-join and a window function instead of maintaining it inline. The close of one interval is the open of the next, and the query works that out rather than the writer.
• Preserved both time dimensions end to end, held record linkage, and kept stable identity across the migration, so a reference in the source is the same reference in the replica. Nested structures are serialised rather than flattened.
• Phased the delivery: historical backfill first, then continuous streamed replication with catch-up after gaps.
• Delivered as merged pull requests with automated test coverage, inside the client's ecosystem and on the client's tooling.

What it cost

• Deferred materialisation. Deriving closure at query time spends at read what it saves at write. The trade is deliberate and it is not free.
• An asynchronous replica is behind its source by definition. Freshness became an explicit opt-in, so callers choose the trade-off knowingly rather than discovering it during a reconciliation.

Outcome

The replica answers queries on any property without an index request standing in the way, and the history survives the trip.

No figures here, and no product, platform or client names. They belong to the client, and none of them are what makes the approach worth reading about.