Two Triggers Run the Whole Warehouse

The tables from part 2 do nothing on their own. What makes KC Star V1 a self-populating warehouse — no ETL job, no application-layer mapper — is two triggers. One explodes a sale into dimension rows; the other versions a dimension when it changes and rebuilds the facts. Between them they encode every rule the warehouse enforces, which means they also encode every gap I left. This post is both halves of that.

Trigger one: a sale becomes seven rows

TR_Sales_To_SalesDim fires AFTER INSERT ON Sales FOR EACH ROW and calls ProcessSalesToSalesDim(). Its whole job is to take the wide Sales row and scatter it across SalesDim as seven typed rows:

CREATE OR REPLACE FUNCTION ProcessSalesToSalesDim()
RETURNS TRIGGER AS $$
DECLARE
    v_hash BYTEA;
BEGIN
    v_hash := digest(CONCAT(NEW.ProductId, '|', NEW.CustomerId), 'sha256');

    INSERT INTO SalesDim (SalesReferenceId, DimTypeId, DimValue, EffectiveDate, VersionHash)
    VALUES
        (NEW.Id, 1, NEW.ProductCount::TEXT, NEW.CreatedDate, v_hash),
        (NEW.Id, 2, NEW.ProductPrice::TEXT, NEW.CreatedDate, v_hash),
        (NEW.Id, 3, NEW.Discount::TEXT,     NEW.CreatedDate, v_hash),
        (NEW.Id, 4, NEW.SaleDate::TEXT,     NEW.CreatedDate, v_hash),
        (NEW.Id, 5, NEW.SaleType,           NEW.CreatedDate, v_hash),
        (NEW.Id, 6, NEW.ProductId::TEXT,    NEW.CreatedDate, v_hash),
        (NEW.Id, 7, NEW.CustomerId::TEXT,   NEW.CreatedDate, v_hash);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Insert one row into Sales and seven rows appear in SalesDim, each tagged with its DimTypeId, each stamped with the same version hash. This is the inversion happening in real time.

And here is the first thing to notice: this trigger writes dimensions but never creates a single fact. There is no call to CreateSalesFacts anywhere in the insert path. So immediately after an insert, the sale has a full set of current dimension rows and no measure rows at all. Query CurrentSalesState for a freshly inserted sale and every measure column comes back NULL — not because the measures are the uncomputed aggregate ones from part 2, but because no fact rows exist yet.

Trigger two: change, versioned

Facts get made on the update path. TR_SalesDim_SCD2 fires AFTER UPDATE ON SalesDim and calls ProcessSalesDimSCD2(), which is where the slowly-changing-dimension machinery lives:

CREATE OR REPLACE FUNCTION ProcessSalesDimSCD2()
RETURNS TRIGGER AS $$
DECLARE
    v_new_hash BYTEA;
BEGIN
    IF OLD.DimValue = NEW.DimValue THEN
        RETURN NULL;  -- nothing actually changed
    END IF;

    -- expire the old dimension row
    UPDATE SalesDim
    SET IsCurrent = FALSE, ExpiryDate = CURRENT_TIMESTAMP
    WHERE DimSurrogateKey = OLD.DimSurrogateKey;

    -- insert the new current value
    INSERT INTO SalesDim (SalesReferenceId, DimTypeId, DimValue, EffectiveDate)
    VALUES (OLD.SalesReferenceId, OLD.DimTypeId, NEW.DimValue, CURRENT_TIMESTAMP);

    -- recompute the sale's fingerprint over all current dimensions
    v_new_hash := CalculateVersionHash(OLD.SalesReferenceId);
    UPDATE SalesDim SET VersionHash = v_new_hash
    WHERE SalesReferenceId = OLD.SalesReferenceId AND IsCurrent = TRUE;

    -- upsert the version cache; if the hash changed, rebuild facts
    -- ...expire current facts, then CALL CreateSalesFacts(...)

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Three things in here are worth dwelling on.

First, RETURN NULL cancels the physical UPDATE. The function does not let PostgreSQL apply the update you asked for. It expires the targeted row and inserts a new row with the new value instead — that is what SCD2 means. Returning NULL from an AFTER trigger on a row-level update tells Postgres to skip nothing (the row is already updated by the time AFTER fires), but the pattern here is written defensively around exactly that lifecycle, expiring OLD.DimSurrogateKey and inserting fresh. The mental model is: you never mutate history, you supersede it.

Second, the guard IF OLD.DimValue = NEW.DimValue THEN RETURN NULL is what keeps this from looping forever. The function itself issues UPDATE SalesDim statements to stamp the new hash, and every one of those would re-fire the trigger. The value-equality check is the circuit breaker. This class of bug — an SCD2 trigger that re-triggers on its own bookkeeping updates — haunted later versions badly enough that the V6 README still opens with a note about fixing “SCD2 infinite loop issues.” V1 is where I first learned to fear it.

Third, only here does CreateSalesFacts get called. After the hash is recomputed, if it differs from the cached one, the function expires the current facts and rebuilds them. Which is the whole reason a fresh sale has no measures: nothing has changed yet, so the fact-building path has never run.

The setup-order trap

There is one more V1 scar, and it is a good one because it is purely operational. The setup-all script runs the files in numeric order: it creates the traditional tables (file 03, which also inserts three sample sales) before it creates the insert trigger (file 06). So the seed sales are inserted at a moment when TR_Sales_To_SalesDim does not exist yet. They land in Sales and produce no dimension rows at all. The warehouse ships empty of exactly the data the seed script pretends to load.

I could have reordered the files. I left the observation in the changelog instead, because it is the kind of thing that only bites once and teaches a durable lesson: in a trigger-driven warehouse, the order you install objects is part of the schema. Data loaded before its trigger is data the warehouse never sees.

What the two triggers are really for

Step back and the design is clean even with the gaps. One trigger turns writes to the operational table into dimension rows. One trigger turns changes to dimension rows into new versions and rebuilt facts. There is no third component — no scheduler, no application service, no message queue. The consistency rules live in the database, next to the data they govern, and they run inside the same transaction as the write that triggered them.

That is also the ceiling V1 hits. Everything is synchronous and per-row: every insert pays for its own dimension explosion, every dimension change pays for a full fact rebuild, inside the writing transaction. It is exactly the right model for correctness and exactly the wrong model for twenty thousand sales a minute — which is the problem V7 exists to solve. But that is a long way off. Next, the fingerprint that decides when facts need rebuilding at all.