FactCount and the Audit Trail of an Average

When V3 gave aggregates their own table, it added one column that was not in V2 at all: FactCount. It records how many base facts went into each aggregate. That sounds like a footnote, and in storage terms it is one INT. But it changes what an aggregate is — from a number you have to trust into a number you can audit — and this post is why that small column carries more weight than its size suggests.

An average without a denominator

Consider SaleTypeAverage for 'Online'. It is a single stored value: say, 842.50. Is it right? You cannot tell from the value. An average of 842.50 could be one sale of exactly that amount, or ten thousand sales that happen to average it, or — if V2's expiry bug were still in play — a corrupted rollup over a duplicated input set. The number alone carries no evidence of how it was produced.

FactCount is the denominator, made durable:

CREATE TABLE SalesAggregationFact (
    ...
    FactValue DECIMAL(18,4),
    FactCount INT NOT NULL,   -- number of base facts aggregated
    ...
);

Now SaleTypeAverage = 842.50, FactCount = 37 is a claim you can check. Thirty-seven online sales averaged 842.50. If you expected a couple of hundred online sales, that 37 is a red flag before you ever look at the value. The aggregate stopped being an assertion and became an assertion with its supporting count attached.

How it gets populated

FactCount falls out of the same GROUP BY that computes the aggregate — it is just the COUNT(*) you were already one keystroke away from. In the rewritten CreateAggregatedFacts:

WITH BaseFacts AS (
    SELECT sf.FactId, sf.FactValue AS TotalSale, sf.SaleType
    FROM SalesFact sf
    WHERE sf.MeasureTypeId = 1 AND sf.IsCurrent = TRUE
)
SELECT SaleType,
       SUM(TotalSale) AS SaleTypeTotal,
       AVG(TotalSale) AS SaleTypeAverage,
       COUNT(*)       AS FactCount      -- free, right here
FROM BaseFacts
GROUP BY SaleType;

Every aggregate the function writes — per-SaleType and overall — carries the count of base facts it summarised. Because the count is computed from the same BaseFacts set as the value, the two can never disagree: the FactCount on a row is definitionally the number of facts that produced that row's FactValue. It is not a separately maintained statistic that could drift; it is a byproduct of the computation, frozen alongside its result.

What it lets you query

The CurrentAggregationState view surfaces FactCount right next to the value, so the audit is one column away in normal use:

SELECT SaleType, MeasureName, FactValue, FactCount, ValidFrom, ValidTo
FROM CurrentAggregationState
ORDER BY SaleType, MeasureName;

But the count really earns its keep in a query the usage examples frame as “performance analysis” and I think of as distribution analysis:

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

This groups aggregates by how many facts fed them. It answers questions like: how many of my rollups are based on a handful of facts versus hundreds? A warehouse where most aggregates have FactCount in the single digits is telling you the partitions are thin — the averages are noisy, the totals are small-sample. That is a data-shape insight you cannot get from the values alone, and it is available only because the count was stored.

An audit trail across versions

Because SalesAggregationFact has its own SCD2 window, FactCount is versioned too. Every time an aggregate rebuilds, the new row records the count at that rebuild, and the old row keeps the count it had. So the history of an aggregate is also the history of its sample size:

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

Walk the versions of a single aggregate and you can watch its FactCount grow as sales accumulate — a rollup that went from FactCount = 5 to 50 to 500 over its lifetime, each step preserved. That is the “audit trail of an average” the title promises: not just the current number, but the whole sequence of numbers and the sample size behind each one.

Why I care about a denominator

The FactCount column is a small instance of a principle the whole project kept rediscovering: a computed value should carry evidence of its computation. V1 started this with CalculationContext, which stores the inputs to a fact. V3 extends it to aggregates with a count. V4 pushes it further with DataQualityScore and JSONB context, and by then the pattern is explicit — every derived number in KC Star should be able to answer “how did you get that?”

An aggregate that cannot tell you its sample size is a number you can only trust. An aggregate that carries FactCount is a number you can check. In a warehouse whose entire premise is that data changes and history matters, checkable beats trustable every time. Next, the function that produces these counts, and the deliberately blunt way it rebuilds everything: expire everything, rebuild everything.