Facts That Show Their Work

There is a habit I picked up in school and never dropped: show your work. A final answer with no working is a number you either believe or don't — you cannot check it. Most data warehouses store final answers. KC Star V4 stores the working too. This post is about CalculationContext growing up from a text blob into queryable JSONB, and the statistical columns that let an aggregate carry its own shape — two changes that turn a stored fact from an assertion into a defensible claim.

From TEXT to JSONB

CalculationContext existed from V1, where it was a TEXT column holding a rough string of the inputs behind a fact. In V4 it becomes JSONB:

-- V1: a text blob you could read but not query
CalculationContext TEXT
-- V4: structured, indexable, queryable
CalculationContext JSONB

That is a small DDL change with a large consequence. As text, the context was documentation — you could eyeball it, but you could not ask questions of it. As JSONB, it is data. Every fact now carries a structured record of how it was computed: the input values, the formula applied, the intermediate quantities. And because PostgreSQL indexes and queries JSONB natively, that record is not just readable but interrogable.

The point is best felt with a base measure. TotalSale for a sale is not a mystery number; it is ProductCount × ProductPrice − Discount, and the JSONB context can say exactly that:

{
  "formula": "ProductCount * ProductPrice - Discount",
  "inputs": { "ProductCount": 2, "ProductPrice": 550.00, "Discount": 40.00 },
  "result": 1060.00
}

Now the fact is self-describing. If someone disputes the 1060.00, you do not go archaeology-hunting through logs — you read the fact's own account of itself and check the arithmetic. In a warehouse whose whole premise is that data changes over time, a fact that carries the inputs it was computed from is a fact that stays defensible even after those inputs have since changed.

Aggregates carry their shape

Base facts explain themselves through their inputs. Aggregates need something more, because “the average of these” is not a satisfying account of a rollup — you want to know the distribution, not just the central value. So V4 adds statistical columns to SalesAggregationFact:

• MinValue and MaxValue — the range of the base values that fed the aggregate.
• StdDev — how spread out they were.
• TimeGranularity and AggregationLevel — at what temporal and hierarchical resolution the rollup was computed.
• FactValueRaw — the value before any rounding or unit adjustment, preserved alongside the presented value.

Combined with the FactCount V3 already added, an aggregate now carries its own five-number summary: how many values, their min, max, spread, and the level they were rolled up at. An average of 842.50 with FactCount = 37, MinValue = 12.00, MaxValue = 9800.00, StdDev = 1450.00 is a completely different object from the bare 842.50 — the second one is obviously dominated by a couple of huge outliers, and you can see that without touching the base data. The aggregate shows its work by carrying its shape.

Querying the working

The reason JSONB matters over text is that you can now ask questions across the calculation contexts, not just read them one at a time. You can find every fact computed with a particular formula, or every aggregate whose spread exceeds its mean, or filter facts by an input value buried in the context. The working becomes a queryable surface:

SELECT FactId, FactValue, CalculationContext->>'formula' AS formula
FROM SalesFact
WHERE IsCurrent = TRUE
  AND (CalculationContext->'inputs'->>'Discount')::numeric > 100;

Facts whose discount input exceeded 100, with their formula pulled out of the JSON. That query is impossible against a TEXT blob and trivial against JSONB. The context stopped being a comment and became a column you can slice.

Why this is the heart of governance

The three governance threads V4 added are really one idea seen from three angles. Quality scoring says how much to trust a value. Lineage says where it came from. Calculation context says how it was computed. Trust, provenance, derivation — the three questions an auditor asks about any number. V4 makes a fact able to answer all three about itself.

I think the calculation-context change is the most quietly important of the three, because it is the one that makes the other two verifiable. A quality score of 0.95 is more meaningful when you can read the inputs it was scoring. A lineage edge saying “derived from fact 4001” is more useful when fact 4001 carries the formula that consumed it. The JSONB context is the connective tissue that turns three separate governance features into one coherent story: this warehouse can account for every number it holds.

The cost, honestly

Storing the working is not free. Every fact now carries a JSONB document, which is bytes on disk and bytes in every read that touches the column. For a small operational warehouse that is a fine trade — the storage is cheap and the auditability is worth real money the first time a number is disputed. At V7's scale of twenty thousand sales a minute, writing a JSON document per fact is a cost you would want to measure and possibly make optional. V4 does not worry about that yet; it is a governance release for medium-sized data, and at that size, showing your work costs almost nothing and saves entire afternoons.

That closes the governance features themselves. What remains is somewhere to run all this — and V4 is the version whose docker stack stands up the whole warehouse, through exactly this version, with one command.