The Analytical Star and Its Update Log

I called V5 “three stars and an org chart,” and the previous post explained why the count is three, not two: alongside the sales star and the org star, the root build ships an analytical star. This post closes the V5 series by looking at that third star directly — its polymorphic keying, the real-time triggers that feed it, and the AnalyticalUpdateLog with its PENDING-to-COMPLETED status that looks exactly like a work queue and is not one.

One star for every level

The sales star describes sales. The org star describes the org. The analytical star describes anything — it is a generic rollup store, and it pays for that generality with an unusual key. Instead of a SalesReferenceId that means “a sale,” AnalyticalDim and AnalyticalFact are keyed by a polymorphic ReferenceId plus a HierarchyLevel string:

CREATE TABLE AnalyticalFact (
    AnalyticalFactId SERIAL PRIMARY KEY,
    ReferenceId INT,               -- means different things at different levels
    HierarchyLevel VARCHAR(50),    -- 'Employee', 'Office', 'Region', 'Organization'
    MeasureTypeId SMALLINT,
    FactValue DECIMAL(18,4),
    VersionHash BYTEA,
    ValidFrom TIMESTAMP,
    ValidTo TIMESTAMP DEFAULT '9999-12-31 23:59:59',
    IsCurrent BOOLEAN DEFAULT TRUE,
    BatchId VARCHAR(50)
);

ReferenceId is deliberately untyped-in-meaning: at HierarchyLevel = 'Employee' it is an employee id; at 'Office' it is an office id; at 'Region' a region id. The pair (ReferenceId, HierarchyLevel) identifies what is being measured, and the same table holds rollups for every level of the org hierarchy at once. It is the inversion taken one step further — not just measures as rows, but entities as rows, distinguished by a level string rather than by which table they live in.

This is powerful and slightly dangerous in the same way the whole row-based design is. Powerful, because one analytical star can summarise the entire hierarchy without a table per level. Dangerous, because the type discipline is gone — a ReferenceId is just an integer, and nothing at the schema level stops you comparing an employee id at one level with an office id at another. The HierarchyLevel string is the only thing keeping the meanings apart, and strings drift.

Fed in real time

Where the folder build keeps its stars current with SCD2 triggers and batch procedures, the analytical star is fed by real-time triggers that fire on every change to the source tables:

CREATE TRIGGER TR_Sales_AnalyticalUpdate
AFTER INSERT OR UPDATE OR DELETE ON Sales
FOR EACH ROW EXECUTE FUNCTION ProcessSalesAnalyticalUpdate();

CREATE TRIGGER TR_EmployeeSales_AnalyticalUpdate
AFTER INSERT OR UPDATE OR DELETE ON EmployeeSales
FOR EACH ROW EXECUTE FUNCTION ProcessEmployeeSalesAnalyticalUpdate();

A sale changes, and the analytical star updates immediately — no CALL, no batch window. This is the push model in its purest form: the warehouse's analytical layer is always live with the operational data, at the cost of paying propagation on every write. It is also, in retrospect, the prototype for V7's batch architecture — V7 exists precisely because this real-time model does not survive contact with twenty thousand writes a minute. The analytical star is where I first built the eager version and first felt its ceiling.

The queue that isn't a queue

The most interesting object is AnalyticalUpdateLog, and specifically its ProcessingStatus column:

CREATE TABLE AnalyticalUpdateLog (
    UpdateLogId SERIAL PRIMARY KEY,
    ReferenceId INT,
    HierarchyLevel VARCHAR(50),
    ProcessingStatus VARCHAR(20) DEFAULT 'PENDING',  -- PENDING → PROCESSING → COMPLETED / FAILED
    UpdateTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    SourceSystem VARCHAR(50)
);

That status lifecycle — PENDING → PROCESSING → COMPLETED, with a FAILED branch — is the unmistakable shape of a work queue. You read it and expect a background worker: something that picks up PENDING rows, flips them to PROCESSING, does the work asynchronously, and marks them COMPLETED. Every instinct says “there is a consumer somewhere.”

There isn't. The processing is synchronous, inside the trigger. When TR_Sales_AnalyticalUpdate fires, it does the propagation right there in the same transaction, and the log row moves through its statuses within that single synchronous call. PENDING is barely a real state — the row is COMPLETED before the transaction commits. The queue-shaped schema describes an asynchronous system that was never built; the actual system is eager and in-line.

I find this the most honest artifact in V5, because it is a design intention fossilised in a column. The status lifecycle is where the code was heading — toward an async queue with a real worker, decoupled from the write path. The synchronous implementation is where the code actually was. The gap between them is the roadmap: AnalyticalUpdateLog is V5 sketching the asynchronous batch processor that V7 would eventually build for real. The PENDING status is a promise, exactly like V1's four NULL measures were a promise.

Provenance baked into defaults

One last detail I am fond of. The analytical layer stamps its origin into column defaults. Where the core sales data defaults SourceSystem to 'SYSTEM', the org layer uses 'ORG_SYSTEM', the cross-database layer 'CROSS_SYSTEM', and the analytical layer 'ANALYTICAL_SYSTEM'. A row's provenance is not something you have to reconstruct — it is baked into the default the moment the row is born. Given three stars writing into overlapping territory, being able to ask “which layer produced this?” by reading a column is exactly the kind of governance affordance that V4 taught the project to value.

Closing V5, opening V6

The analytical star completes the picture V5 was painting: a warehouse that can measure not just sales, but the entire organisation that produces them, at every level, live. Three stars, an org chart, a bridge across an imaginary database boundary, and a queue-shaped log sketching the async future.

What none of it can do is decide who gets to see which numbers. An org hierarchy is exactly the structure a permission system wants to key off — a regional manager should see their region, an employee only themselves — and V5 built the hierarchy without the gate. V6 is that gate: role-based access control and permission-aware calculations, layered onto the hierarchy V5 just finished building.