One Version, Two Implementations

I have been careful, through the V5 series, to say “the folder build” and “the root build” as if that distinction were normal. It is time to stop hedging and confront it directly. V5 does not have one implementation. It has two, under the same version number, and they do not contain the same objects. The version folder tells a batch-and-SCD2 story; the root DatabaseV5.sql tells a real-time story. This post is what actually diverged, why I let it, and what letting variants drift taught me — some of it the hard way.

The two builds

Every KC Star version exists in a few forms, but V5 is where they diverge most sharply.

The folder build (V5/01-…10-…sql, wired by 09-setup-all.sql) is the one the earlier posts described. It carries the sales star, the org star, and the cross-database bridge, all kept current by SCD2 triggers — TR_OrgDim_SCD2_V5, TR_EmployeeSales_To_OrgDim_V5, TR_Personnel_To_OrgDim_V5. Rollups come from CreateOrganizationalRollups and the views. By the counts I trust, it lands around 20 tables, 9 functions, 8 procedures, 7 views, 45 indexes.

The root build (DatabaseV5.sql) is a different animal. It is not the folder concatenated — it is an additive overlay that assumes a V4 database already exists. It contains no Sales, Product, Customer, SalesDim or SalesFact at all; it reaches into the pre-existing V4 tables for those. What it adds on top is a third star — AnalyticalDim, AnalyticalFact, AnalyticalUpdateLog — driven by real-time triggers (TR_Sales_AnalyticalUpdate, TR_EmployeeSales_AnalyticalUpdate) that fire on insert, update and delete, plus views the folder never has: OrgHierarchy, CrossDatabaseSales, MultiLevelRollup, CurrentAnalyticalState.

Same version. One does batch SCD2; the other does real-time propagation. One is self-contained; the other is a layer on V4. They are two answers to “what is V5,” and I shipped both.

Same rollup, computed two ways

The clearest way to feel the divergence is that the same rollup exists in both builds, computed by completely different mechanisms.

In the folder, an organizational rollup is a pull: you CALL CreateOrganizationalRollups(1), it walks the hierarchy and materialises SCD2 facts into OrgFact, and you read them back later. The computation is batch — it happens when you ask for it.

In the root, the same rollup is a push: a sale changes, TR_Sales_AnalyticalUpdate fires immediately, and ProcessSalesAnalyticalUpdate propagates the change into AnalyticalFact in real time, logging the propagation in AnalyticalUpdateLog. The computation is event-driven — it happens when the data changes, whether you asked or not.

Both produce a multi-level rollup. One is lazy and versioned; one is eager and live. Neither is wrong. They are different points on the batch-versus-real-time axis, and V5 accidentally became a natural experiment in running both.

Why I let it drift

The honest history is that I did not decide to ship two implementations — I let the folder and the root explore different ideas and never forced them to reconcile. The folder was where I built the careful, SCD2-consistent version; the root was where I sketched the real-time analytical layer that later hardened into V7's batch architecture. They served different purposes at the time, so I let them diverge.

I do not fully regret it, and that surprised me. Letting variants drift bought a real thing: two working explorations of the same problem, side by side, that I could compare directly. The push-versus-pull question is genuinely hard, and having both implementations in front of me — same data model, opposite update strategy — taught me more than picking one on a whiteboard would have. The root's real-time triggers showed me the latency benefits and the fan-out costs; the folder's batch procedures showed me the consistency benefits and the staleness costs. V7 chose batch, and it chose it knowing exactly what real-time gave up, because V5 had run the experiment.

What it cost

I will not pretend the drift was free, because it was not, and the cost is instructive. The most concrete casualty is that the CHANGELOG count tables match neither build. The V5 changelog reports table and function counts that correspond to some blend of the two — 23 tables, 12 functions — that you cannot find in either the folder or the root. Documentation that describes a schema that does not exist is worse than no documentation, because it invites confident wrong assumptions.

There is a subtler cost too. EmployeeSales.CommissionEarned in the folder is referenced as CommissionAmount in some usage scripts and the changelog — column-name drift that only happens when two builds evolve without a single source of truth. Every place the folder and root disagree is a place a reader can be misled, and I created a lot of those places.

The rule I took away, and applied to every later version, is the one I stated back in the V4 lineage post: the folder split files are ground truth. They are what 09-setup-all.sql actually installs and what the tests exercise. The root file is a legitimate alternate build worth reading, but when they disagree, the folder wins, and the CHANGELOG is aspiration, not documentation. That rule is not a nice-to-have — it is the only thing that keeps a project with drifting variants navigable.

The lesson, generalised

Letting variants drift is a real technique with real costs. It is good for exploration — two live implementations beat one whiteboard argument, and I would do the parallel-exploration part again. It is bad for anyone who arrives later, because without a declared source of truth, drift is indistinguishable from inconsistency, and documentation quietly describes a third thing that is neither.

If I were doing it deliberately now, I would keep the two builds but be loud about it: a README at the top saying “these are two implementations of V5, here is how they differ, here is which one the tests run.” The drift was fine; the silence about it was the mistake. V6's changelog is where that silence gets genuinely expensive — a changelog describing a security system that was never built.

Before that, one more piece of V5 to account for: the third star itself. The analytical star and its update log is the real-time layer up close, and the queue that only looks like a queue.