Promoting SaleType from Row to Column

The whole thesis of KC Star, from V1 onward, is that dimensions are rows. So there is something almost heretical about the first real move V2 makes: it takes one dimension, SaleType, and turns it back into a column. This post is about why that promotion was necessary, exactly what it changed in the schema, and the honest tension of un-inverting a single attribute in a design whose selling point is inversion.

Why SaleType, and why a column

The four new measures V2 computes are two overall aggregates and two per-SaleType aggregates. SaleTypeTotal is “the sum of TotalSale across all sales of this type”; SaleTypeAverage is the average. Both partition by sale type. Sale type is the grouping key.

In V1, SaleType was DimTypeId 5 — a key-value row in SalesDim like every other dimension. To group by it while it lives there, every aggregation would have to join SalesDim back to itself: once to get the base measure's sale, again to fish out that sale's SaleType row, then group on the recovered value. Every aggregate query carries a self-join whose only purpose is to un-bury the partition key.

Promoting SaleType to a real column on both SalesDim and SalesFact removes that self-join. The partition key is right there, in a column, on the row you are already reading. Aggregation becomes a plain GROUP BY SaleType. That is the entire motivation: the partition key of your aggregates wants to be a column, even in a row-based schema.

The renumbering

Removing SaleType from the dimension rows was not free. It shifted the dimension type ids. V1 seeded seven:

1 ProductCount  2 ProductPrice  3 Discount  4 Date
5 SaleType  6 ProductId  7 CustomerId

V2 seeds six, with SaleType gone and the tail renumbered:

1 ProductCount  2 ProductPrice  3 Discount  4 Date
5 ProductId  6 CustomerId

ProductId moved from 5-adjacent to 5, CustomerId from 7 to 6. The insert trigger that explodes a sale into dimension rows now writes six rows, not seven, and drops the SaleType row entirely — instead it stamps NEW.SaleType into the new column on each of the six rows it does write.

This is a genuine schema-version boundary, and it is exactly the kind that a versioned-in-the-open project makes visible. If you loaded V1 data and ran V2 code against it, DimTypeId 5 would mean two different things. The version folders keep these apart on purpose — each version is its own database with its own seed — but the renumbering is a standing reminder that surrogate ids are only stable within a version. It is a theme that returns with teeth in V8, where dimension ids 8 and 9 mean QuoteDate/DeliveryDate in one lineage and ShippingCost/TaxRate in another.

What it did to the hash

The version hash had to change too, and this is the part I find most telling. In V1, CalculateVersionHash aggregated the dimension rows — and since SaleType was a row, it was automatically in the hash. Promote it to a column and it silently falls out of the fingerprint, because the fingerprint only sees rows. A sale could change its SaleType and the hash would not move, so the facts would not rebuild. That is a correctness bug waiting to happen.

So V2 explicitly folds the column back in:

-- V1: hash over dimension rows only
STRING_AGG(DimTypeId || ':' || DimValue, '|' ORDER BY DimTypeId)
-- V2: append the promoted column
... || '|SaleType:' || v_SaleType

The insert-time quick hash gets the same treatment — it now concatenates ProductId | CustomerId | SaleType instead of just the first two. The lesson generalises cleanly: whatever your change-detection fingerprint hashes over defines what “changed” means, and promoting an attribute out of the hashed structure quietly narrows it. Every later version that added a column had to remember to feed it to the hash, and this is the version where I learned to.

The honest tension

Promoting SaleType works. The aggregates compute without self-joins, the hash stays honest, the view surfaces real numbers. But it is worth naming what was traded, because a design that quietly abandons its own principle is worse than one that admits to bending it.

The whole pitch of the inverted schema is add a dimension with an INSERT, no DDL. SaleType as a column breaks that for exactly one attribute: change how sale types work and you are altering a table again, not inserting a lookup row. V2 is a hybrid — mostly rows, one column — and hybrids owe you an explanation for the seam.

Mine is that SaleType is not really a dimension in the same sense as the others. It is the partition every aggregate is organised around — closer to a grouping axis than a sliceable attribute. Promoting the partition key while keeping the sliceable attributes as rows is a defensible line to draw. But it is a line, and drawing it is the moment KC Star stopped being purely inverted and became pragmatically inverted. Every subsequent version lived on the pragmatic side.

With SaleType accessible, the aggregates can finally be computed — and the way CreateAggregatedFacts does it, GROUP BY masquerading as window functions, is where we go next.