Backfilling the Facts the Pipeline Skipped

Every long-lived database accumulates repair scripts — the small, embarrassed files that fix what the main code got wrong in production. KC Star has a handful, and two of them, fix_missing_data.sql and fix_analytical_data.sql, tell the same instructive story about V6: the dimensions got loaded, the facts did not, and someone had to backfill the missing half by hand. This post is that war story, because a fix script is the most honest documentation a system produces — it records exactly what actually broke.

The half-run pipeline

Recall the two-trigger design that runs the warehouse: dimensions get created one way, and facts get generated from those dimensions by CreateSalesFacts (and, in the org star, CreateAnalyticalFacts). The pipeline has two halves — populate dimensions, then derive facts — and they are supposed to run together.

Sometimes they didn't. During V6 setup, dimensions were seeded manually — inserted directly, tagged with a telltale BatchId = 'MANUAL_SETUP' — but the fact-generation half never fired for them. The result is a warehouse in a broken-but-plausible state: SalesDim and AnalyticalDim full of current dimension rows, and SalesFact / AnalyticalFact missing the rows those dimensions should have produced. Every query that reads dimensions looks fine. Every query that reads measures comes back empty for the affected references. The data is half there, and the half that is missing is the half you actually analyse.

The BatchId = 'MANUAL_SETUP' tag is the fingerprint of the problem. It marks exactly the rows that were hand-loaded outside the normal trigger path — which is precisely the set that skipped fact generation. The fix scripts key off it.

The backfill, as a DO block

fix_missing_data.sql repairs the sales star. Its header says, plainly, “Fix missing data for V6.” The mechanism is a DO block that finds every hand-seeded dimension set with no facts and generates the facts it should have had:

DO $$
DECLARE
    rec RECORD;
    version_hash BYTEA;
BEGIN
    FOR rec IN
        SELECT DISTINCT SalesReferenceId
        FROM SalesDim
        WHERE IsCurrent = TRUE AND BatchId = 'MANUAL_SETUP'
    LOOP
        SELECT VersionHash INTO version_hash
        FROM SalesDim
        WHERE SalesReferenceId = rec.SalesReferenceId AND IsCurrent = TRUE
        LIMIT 1;

        CALL CreateSalesFacts(rec.SalesReferenceId, version_hash, now(), 'MANUAL_SETUP');
    END LOOP;

    RAISE NOTICE 'Processed % sales with manual setup dimensions', ...;
END $$;

It loops the distinct MANUAL_SETUP references, recovers each one's version hash from its dimension rows, and calls the same CreateSalesFacts the trigger would have called — retroactively running the pipeline's missing second half. The RAISE NOTICE at the end reports how many it repaired, which is the small dignity a fix script affords you: a count of the damage undone.

fix_analytical_data.sql does the identical thing one hierarchy up, for the analytical star. Its header: “Fix missing analytical data for V6.” It loops AnalyticalDim rows where BatchId = 'MANUAL_SETUP' and HierarchyLevel = 'Employee', and calls CreateAnalyticalFacts for each. Same shape, same cause, same repair — dimensions seeded, facts skipped, backfill by loop.

Why the pipeline half-ran

The root cause is the setup-order fragility that has haunted KC Star since V1, in a new dress. When you seed dimensions by hand — because you are bootstrapping a demo database, or migrating data in, or setting up a test fixture — you bypass the trigger that would normally fire CreateSalesFacts. The dimensions land; the facts never get their trigger. Manual data loading and trigger-driven derivation do not compose unless you remember to run the derivation yourself.

That is the durable lesson, and it generalises far past KC Star: in a trigger-driven system, any data that arrives outside the trigger path is data the derivations never see. Bulk loads, manual seeds, migrations — all of them sidestep the very automation that makes the warehouse consistent. The MANUAL_SETUP batch id is the honest confession of exactly that: those rows came in the side door, and the side door has no trigger on it.

Fix scripts as documentation

I keep these scripts in the repo deliberately, the same way I keep the misleading V6 changelog — because a repair script is a primary source. The main code tells you what the system is supposed to do. A fix script tells you what it actually did to real data, badly enough that someone had to intervene. You cannot get that from reading the happy path.

fix_missing_data.sql and fix_analytical_data.sql document, more reliably than any changelog, that V6's pipeline could be half-run, that manual seeding was the trigger, and that the recovery is a backfill loop keyed on a batch id. If I ever wondered “can this warehouse end up with dimensions but no facts?” the answer is written into the repository as an executable yes.

The hand-off to V7

There is a straight line from these fix scripts to the next version. The whole reason dimensions could be seeded without facts is that fact generation was welded to a per-row trigger — sidestep the trigger, lose the facts. And the reason the per-row trigger model was under pressure at all is that it does not scale: firing CreateSalesFacts synchronously on every write is fine for a demo and fatal at volume.

V7 confronts exactly that. It turns the triggers off and moves fact generation into an explicit, batched, monitored pipeline — one where “populate dimensions” and “derive facts” become named batch operations you can run, observe and, crucially, re-run on data that came in the side door. The V6 fix scripts are the manual, one-off version of what V7 makes into a first-class system. The V7 series starts there: turning off the triggers at twenty thousand sales a minute.