Twenty Queries Against a Dual Star

Every KC Star version ships a 10-usage-examples.sql file, and the count of examples is a decent proxy for how much a version can do. V1 had ten. V2 had fourteen. V3 has twenty — the largest set in the early versions — and the jump is not padding. Splitting facts across two tables opened a whole class of questions that a single table made awkward. This post is a tour of the ones worth knowing, and what each reveals about a dual-star warehouse.

Two views, two starting points

The dual-table design means two presentation views, and most queries start from one or the other. CurrentSalesState pivots the current base facts — one row per sale, its base measures as columns. CurrentAggregationState surfaces the aggregation table — one row per (SaleType, measure), with its FactCount and validity window. Base questions go to the first view; rollup questions go to the second. The clean separation is the point: you never have to filter out aggregate rows from a base-fact query, or vice versa, because they live in different places.

The join that stitches them back together

The most instructive query is the one that reunites what the schema separated — a sale next to the rollup for its sale type:

SELECT bf.SaleType, bf.TotalSale,
       af.FactValue AS SaleTypeTotal, af.FactCount
FROM CurrentSalesState bf
JOIN CurrentAggregationState af ON bf.SaleType = af.SaleType
WHERE af.MeasureName = 'SaleTypeTotal';

Each sale, its own TotalSale, and the SaleTypeTotal its sale type contributes to — joined on the SaleType column that V2 promoted out of the rows precisely so this join would be a column match rather than a self-join. The FactCount rides along, so you see not just the group total but how many sales are in the group. This is the payoff of the dual design: base and aggregate are separately stored but trivially recombined, on a shared key, whenever you actually want them together.

Watching a rollup evolve

Because SalesAggregationFact carries its own SCD2 history, you can ask questions about how an aggregate changed, not just what it is now. This one counts the versions of every rollup:

SELECT SaleType, MeasureName,
       COUNT(*)       AS VersionCount,
       MIN(ValidFrom) AS FirstVersion,
       MAX(ValidTo)   AS LastVersion
FROM SalesAggregationFact
GROUP BY SaleType, MeasureName
ORDER BY SaleType, MeasureName;

A rollup with a high VersionCount has been rebuilt many times — every base-fact change in its sale type triggered a fresh version (a direct consequence of the rebuild-everything approach). This is aggregate evolution: the same number, tracked through every state it passed through. In V2 you could not ask this cleanly, because aggregate history was tangled with base-fact history in one table and half of it never expired anyway. The dedicated table makes aggregate history a first-class thing you can query.

The distribution question

The examples file labels this one “performance analysis.” I read it as asking about the shape of the data behind the aggregates:

SELECT FactCount, COUNT(*) AS AggregationCount, AVG(FactValue) AS AvgValue
FROM SalesAggregationFact
WHERE IsCurrent = TRUE
GROUP BY FactCount
ORDER BY FactCount;

Grouping aggregates by their own FactCount tells you how well-supported your rollups are. A cluster of aggregates with tiny FactCount values means thin partitions — small-sample averages you should not lean on. It is a meta-query: the warehouse describing the statistical reliability of its own summaries. Only possible because FactCount was stored as a durable property of each aggregate.

Before and after a change

Several of the twenty examples come in before/after pairs — snapshot the aggregation state, update a dimension, snapshot again — to demonstrate that the rollups actually track base-fact changes. The pattern is simple:

-- before
SELECT SaleType, FactValue FROM CurrentAggregationState WHERE MeasureName = 'SaleTypeTotal';

-- change one sale
UPDATE SalesDim SET DimValue = '150.00'
WHERE SalesReferenceId = 4 AND DimTypeId = 3 AND IsCurrent = TRUE;

-- after: the affected SaleType's total has moved; its FactCount is unchanged
SELECT SaleType, FactValue, FactCount FROM CurrentAggregationState WHERE MeasureName = 'SaleTypeTotal';

What makes this convincing is the FactCount: after editing an existing sale's discount, the SaleTypeTotal changes but the FactCount does not — because you changed a value, not the number of sales. If both moved, something would be wrong. The audit column doubles as a correctness check on the update itself.

What twenty queries actually demonstrate

Step back from the individual queries and the set makes one argument: a warehouse where aggregates have their own versioned table can answer questions about aggregates as objects — their history, their sample size, their evolution — not just report their current values. V1 and V2 could tell you a total. V3 can tell you a total, how many sales are behind it, how many times it has been recomputed, and what it was last week. The extra ten examples are all variations on “now that aggregates are first-class, here is what first-class buys.”

That is the end of the aggregation arc. Across V2 and V3, KC Star learned to compute every measure and to compute them correctly. What it still assumed, blithely, was that the numbers going in were trustworthy. V4 is the version that stops assuming — where the warehouse grows a conscience and starts scoring the quality of its own data.