Giving Aggregates a Table of Their Own

The V2 series ended on a bug I could point at: per-SaleType aggregates never expired, because base facts and aggregates shared one SalesFact table and were told apart by a nullable column that the expiry clause got wrong. V3 is the fix, and it is a structural one. Aggregates get a dedicated table — SalesAggregationFact — with its own SCD2 windows, its own indexes, and its own audit column. This post is the shape of that split, and the split is as much about what SalesFact loses as what the new table gains.

The design in one sentence

V3's changelog calls it a dual-table design, “the best of both worlds,” and for once the marketing is accurate: base measures stay in SalesFact, aggregate measures move to SalesAggregationFact, and each table holds exactly one population. Everything that made V2's single table error-prone came from two populations cohabiting. Separate them and the errors have nowhere to live.

V3 inherits the whole V2 machinery unchanged: SaleType is still a column, the six dimension types, the two triggers, the SHA-256 hashing. What changes is where aggregates go and, consequently, what SalesFact is allowed to be.

What SalesFact loses

The tidiest way to understand V3 is to list what got removed from SalesFact:

• BaseFactId is gone. In V2 this column back-referenced an aggregate to a contributing base fact — plumbing that only existed because aggregates lived in the same table and needed a way to point at their inputs. With aggregates elsewhere, the pointer is unnecessary.
• SaleType becomes NOT NULL. In V2, SaleType was nullable on SalesFact precisely so that overall-aggregate rows (which belong to no sale type) could sit in the table. No aggregates in SalesFact means no NULL SaleType, so the column can be tightened. That NOT NULL is a small but real integrity win — every base fact now provably has a sale type.
• Aggregate rows are gone entirely. SalesFact holds only base measures 1–4, one row per (sale, measure), every row with a real SalesReferenceId. One population, no exceptions.

Removing columns and tightening constraints is the opposite of how schemas usually evolve, and it is the sign the split was the right call. SalesFact got simpler because it stopped doing a second job.

What SalesAggregationFact gains

The new table is where the aggregates live, and it is built to hold them properly:

CREATE TABLE SalesAggregationFact (
    AggregationFactId SERIAL PRIMARY KEY,
    SaleType VARCHAR(50) NULL,          -- NULL = an overall aggregate
    VersionHash BYTEA,                  -- hash of the contributing FactIds
    MeasureTypeId SMALLINT,
    FactValue DECIMAL(18,4),
    CalculationContext TEXT,
    ValidFrom TIMESTAMP,
    ValidTo TIMESTAMP DEFAULT '9999-12-31 23:59:59',
    IsCurrent BOOLEAN DEFAULT TRUE,
    CreatedDate TIMESTAMP,
    FactCount INT NOT NULL              -- how many facts this aggregate summarises
);

Three things earn their place here. SaleType NULL now means something unambiguous within this table: a NULL sale type is an overall aggregate, a non-NULL one is a per-SaleType aggregate — and because there are no base facts to confuse them with, the distinction is finally safe to use. The table has its own SCD2 window (ValidFrom/ValidTo/IsCurrent), so aggregate history versions independently of base-fact history. And FactCount is new — an audit column recording how many base facts fed each aggregate, which part 2 is entirely about.

The table also brings five indexes of its own — on SaleType, VersionHash, IsCurrent, ValidFrom and ValidTo — pushing the schema from V2's eight indexes to ten. Aggregates now get first-class query support instead of sharing SalesFact's.

The expiry that can't be written wrong

Here is the immediate payoff, and it is the whole reason for the split. In V2, expiring old aggregates meant WHERE SaleType IS NULL AND IsCurrent = TRUE, which caught only the overall aggregates and leaked the per-SaleType ones. In V3, expiring old aggregates is:

UPDATE SalesAggregationFact
SET IsCurrent = FALSE
WHERE IsCurrent = TRUE;

No nullable-column predicate, because every row in this table is an aggregate by construction. There is no base fact to accidentally sweep, and no per-SaleType aggregate to accidentally miss. The condition V2 needed but stated wrong can no longer be stated wrong, because the schema now encodes the distinction the WHERE clause used to carry. That is the deep move: push the thing you kept getting wrong out of your query logic and into your table structure. Part 3 walks through the rewritten function in full.

Two views now, not one

Because aggregates have their own home, they get their own presentation. CurrentSalesState reverts to base measures only — the four aggregate columns V2 had bolted on come back out, since they no longer live in SalesFact. A new view, CurrentAggregationState, surfaces the aggregation table: SaleType, MeasureName, FactValue, FactCount, and the validity window. Two views for two tables, each with a single clear job, and part 4 shows how they join back together when you want a sale next to its rollups.

Where the series goes

1. Giving aggregates a table of their own — this post.
2. FactCount and the audit trail of an average — the new column that records how many facts went into every rollup.
3. Expire everything, rebuild everything — the rewritten CreateAggregatedFacts, its clean full-table expiry, and the cost of rebuild-it-all.
4. Twenty queries against a dual star — the largest usage-example set in the early versions, and what a two-table warehouse lets you ask.

V3 is the end of the “make the aggregates correct” arc that started in V2. After it, V4 stops improving the math and starts asking whether the data feeding it can even be trusted.