Two Fix Scripts and What They Confess

The partition mess was V7 being built fast. This is V7 being refactored fast, and it is the sharpest scar in the whole project. Reorganising V6's monolithic SQL into V7's tidy subfolders broke the schema — badly enough that it took not one but two passes of fix scripts to put it back together, and the second pass found things the first pass missed. V7_FIXES.sql and V7_FINAL_FIXES.sql are those passes, and like all fix scripts they are the most honest documentation V7 has. This post reads their confessions.

The refactor that broke everything

V7 reorganised the SQL into subfolders — 01-core, 02-batch, 04-features, and so on — a genuine improvement in navigability over V6's flatter layout. But splitting a working monolith into ordered files is exactly the setup-order hazard that has stalked KC Star since V1, at maximum scale. Columns got added in one file and referenced in another that ran first. Triggers got redefined without the old ones being dropped. INSERTs assumed columns that a reordered file had not created yet. The refactor was structurally cleaner and behaviourally broken.

V7_FIXES.sql opens with the plain admission: “This script addresses all identified issues in the V7 implementation.” It did not — which is why there is a second one.

Pass one: patching Sales

The first fix script cleans up after the refactor in sections, and the section headers are the confession:

Duplicate triggers and functions. The refactor left orphaned and doubled objects, so pass one drops them: DROP TRIGGER IF EXISTS tr_sales_to_salesdim ... tr_sales_to_salesdim_v7, DROP FUNCTION IF EXISTS processsalestosalesdim(). Section header: “CLEAN UP DUPLICATE TRIGGERS AND FUNCTIONS.” When you split files, you can easily end up creating the same trigger twice under slightly different names, and two SCD2 triggers on one table is how you get the infinite-loop stories the versions kept warning about.

Missing columns on Sales. The batch and governance columns never got added by the reorganised core files:

ALTER TABLE Sales ADD COLUMN IF NOT EXISTS BatchId VARCHAR(50);
ALTER TABLE Sales ADD COLUMN IF NOT EXISTS DataQualityScore DECIMAL(3,2) DEFAULT 1.0;
ALTER TABLE Sales ADD COLUMN IF NOT EXISTS DataLineage VARCHAR(500) DEFAULT 'System Generated';
ALTER TABLE Sales ADD COLUMN IF NOT EXISTS BatchProcessed BOOLEAN DEFAULT FALSE;

The batch pipeline cannot work without BatchProcessed, and the refactor dropped the column on the floor. Pass one bolts it back on.

A trigger named _Corrected. The rebuilt trigger is created as TR_Sales_To_SalesDim_V7_Corrected. That literal suffix — _Corrected — is my favourite confession in the whole codebase. It is a name that admits, permanently, that the previous one was wrong. Nobody names a trigger _Corrected in a clean build; you name it that when you are patching production and the un-suffixed name is already taken by the broken version you cannot safely drop.

Pass one also fixes CreateAggregatedFacts to handle the missing BatchId column, rebuilds several views, and migrates existing data: UPDATE Sales SET BatchId = 'INITIAL_' || Id, BatchProcessed = TRUE WHERE BatchId IS NULL. It ends by dumping information_schema.triggers and .columns to prove the fix — the SQL equivalent of showing your work because you no longer trust the code to be self-evidently correct.

Pass two: the parts pass one missed

V7_FINAL_FIXES.sql is the tell that pass one was optimistic. Its header: “This script completes all remaining fixes.” Pass one patched Sales; pass two discovered that SalesDim and DimTypeLookup were still broken:

ALTER TABLE SalesDim ADD COLUMN IF NOT EXISTS DataQualityScore DECIMAL(3,2);
ALTER TABLE SalesDim ADD COLUMN IF NOT EXISTS DataLineage VARCHAR(500);
ALTER TABLE SalesDim ADD COLUMN IF NOT EXISTS BatchId VARCHAR(50);
ALTER TABLE DimTypeLookup ADD COLUMN IF NOT EXISTS Description VARCHAR(255);

That last line is a small tragedy of ordering. An earlier INSERT INTO DimTypeLookup (DimTypeId, DimTypeName, DataType, Description) had assumed a Description column that did not exist yet — so the insert would fail until pass two adds the column it presupposed. Pass one fixed the tables it happened to look at; pass two fixed the tables pass one forgot. That is precisely how schema repair goes when there is no integration test: you fix what you can see fails, ship it, watch the next thing fail, and fix that.

Pass two also rewrites ProcessSalesDimSCD2 with the new quality/lineage/batch parameters, and — notably — rewrites CalculateVersionHash to fold DataQualityScore into the hash: ... || '|DQ:' || COALESCE(v_DataQualityScore::TEXT, '1.0'). The fingerprint grew again, exactly as it did at every governance boundary — quality now changes a sale's identity.

The self-test in the migration

The strangest thing pass two does is test itself by mutating production. Embedded in the migration is a live insert:

INSERT INTO Sales (SaleType, ProductCount, ProductPrice, Discount, SaleDate,
                   ProductId, CustomerId, BatchId)
VALUES ('Online', 3, 1500.00, 150.00, '2023-05-07', 101, 201, 'TEST_BATCH_001');
-- 'Testing V7 functionality with new sale...'  'Verify the trigger worked.'

The migration inserts a test sale, then counts SalesDim, SalesFact and CurrentSalesState rows to confirm the repaired trigger fired. It works as a smoke test — but it leaves a TEST_BATCH_001 demo row sitting in the production Sales table forever. A migration that pollutes the data it is migrating is a bad pattern, and I did it because I wanted immediate proof the fix took. The right move is a transaction that rolls back the probe; the shipped move is a permanent test artifact. It joins the MANUAL_SETUP seeds and the V8 migration's sentinel rows as self-inflicted litter in otherwise-real tables.

What the scars teach

Two fix scripts for one refactor is not a story about carelessness — it is a story about the absence of an integration test. Every one of these breaks would have been caught instantly by a test that stood up the schema from scratch and ran one sale through it end to end. There wasn't one. So the “test” was: reorganise the files, run the setup, see what explodes, write a fix script, run it, see what explodes next, write another. Two passes because manual repair is iterative when you cannot see the whole failure at once.

The durable lesson, and the one I carried into V8's migration which ships an actual VerifyV8Migration function: a schema change you cannot verify automatically is a schema change you will fix in as many passes as it takes to notice all the breaks. The _Corrected trigger, the two rounds of ADD COLUMN IF NOT EXISTS, the Description column an insert assumed before it existed — all of it is the shape of refactoring SQL without a safety net. I keep the scripts because they are the most convincing argument in the project for building that net before the next big refactor.

That argument lands in the final V7 post, the operational one: running the warehouse, where the maintenance and performance guidance turns V7 from a pile of subsystems into something you can actually operate — and hands off to V8.