The Expiry Clause That Missed

Every version boundary in KC Star has a reason, and the honest ones are bugs. V3 exists because of a specific defect in V2 that I can point at line by line. This post is that defect: an expiry clause in CreateAggregatedFacts that looks correct, passes a casual test, and quietly lets half the aggregate rows accumulate forever. It is the clearest example in the whole project of why I versioned in the open — the fix is only legible because the break is still on record.

The setup: two populations, one table

Recall from part 3 that V2 stores two kinds of fact in one SalesFact table:

• Base facts — one per sale, with a real SalesReferenceId and the sale's SaleType.
• Aggregate facts — per-SaleType rollups (measures 5, 6) and overall rollups (measures 7, 8), all with SalesReferenceId = NULL.

And recall the elegant-seeming distinction: aggregates are the rows where SalesReferenceId IS NULL. That NULL is how CreateAggregatedFacts knows which rows are its own. It is also, it turns out, not enough information.

The clause that missed

Each time facts rebuild, old aggregates have to be expired before new ones are inserted — otherwise stale rollups linger as IsCurrent = TRUE next to their replacements. CreateAggregatedFacts does this with an expiry statement that reads perfectly reasonable:

-- expire the previous aggregates before inserting the new ones
UPDATE SalesFact
SET IsCurrent = FALSE, ValidTo = CURRENT_TIMESTAMP
WHERE SaleType IS NULL
  AND IsCurrent = TRUE;

Look at the predicate: WHERE SaleType IS NULL AND IsCurrent = TRUE. The intent was “expire the aggregate rows.” But which aggregate rows have SaleType IS NULL? Only the overall aggregates — OverallTotal and OverallAverage, measures 7 and 8, which belong to no sale type. The per-SaleType aggregates — SaleTypeTotal and SaleTypeAverage, measures 5 and 6 — carry a non-null SaleType (that is the entire point of them). They do not match SaleType IS NULL. They are never expired.

So every fact rebuild inserts a fresh set of per-SaleType aggregates without retiring the previous set. SaleTypeTotal for 'Online' accumulates: version after version, all marked IsCurrent = TRUE, all valid, all wrong the moment there is more than one. Query the current per-SaleType total and you get duplicates. The overall aggregates, meanwhile, expire correctly, because they genuinely have SaleType IS NULL. Half the aggregates are fine and half leak, which is exactly the kind of bug that survives a demo — if your test only checks OverallTotal, everything looks perfect.

Why the NULL wasn't enough

The root cause is a modelling error, not a typo. I used two different properties to mean "this is an aggregate." When inserting, an aggregate is identified by SalesReferenceId IS NULL. When expiring, the clause reached for SaleType IS NULL instead — conflating “is an aggregate” with “is an overall aggregate.” The two populations in one table are distinguishable by SalesReferenceId, but the per-SaleType aggregates are not distinguishable from base facts by SaleType alone, because base facts have a SaleType too.

To expire aggregates correctly in the single table, the clause would need to say WHERE SalesReferenceId IS NULL AND IsCurrent = TRUE — the same predicate the insert side uses to define an aggregate. That one-word fix would have worked. But finding it meant first noticing the leak, and the leak hides behind the half that works.

This is the deeper lesson of the single-table design: when one table holds two populations distinguished only by a nullable column, every statement that touches the table has to get the distinction exactly right, every time, and there is no schema-level guard when it doesn't. The database cannot help you, because as far as it is concerned these are all just rows in SalesFact. The correctness lives entirely in your WHERE clauses, and WHERE clauses drift.

Why this is V3's reason to exist

I could have shipped the one-word fix and moved on. Instead it convinced me the single-table arrangement was the wrong shape. If base facts and aggregates keep getting confused because they live together and look alike, the durable fix is to stop making them live together.

V3 moves the aggregates into a dedicated SalesAggregationFact table. Once aggregates have their own table, “expire the old aggregates” becomes UPDATE SalesAggregationFact SET IsCurrent = FALSE WHERE IsCurrent = TRUE — no nullable-column predicate to get wrong, because every row in that table is an aggregate by construction. The distinction moves from a value you have to remember to check into the schema itself. The V3 rewrite makes exactly that trade, and the leaky clause vanishes because the condition it needed can no longer be stated wrong.

That is the hand-off. V2 computed all eight measures and proved the inverted schema could be analytical — and in doing so, it demonstrated that cramming aggregates into the fact table is a false economy. The V3 series gives them a table of their own, and the first thing that table buys back is a clean expiry.