Migrating a Live Star from V7 to V8

V8's selective trigger cannot just be added — it has to replace V7's always-rebuild triggers, on a database that already has data. Two triggers firing on the same table, one rebuilding everything and one rebuilding selectively, is a recipe for double work and inconsistent facts. So V8 ships a migration: drop the old, install the new, verify the switch. After V7's two-pass fix-script disaster, I built this one with an actual verification function — and it still has a landmine. This post is 03-migration-from-v7.sql, what it does right, and the one thing it can get catastrophically wrong.

Step one: remove the old regime

The migration begins by tearing out V7's trigger regime, cascading so dependent objects go with it:

DROP TRIGGER IF EXISTS TR_SalesDim_SCD2_V4 ON SalesDim;
DROP TRIGGER IF EXISTS TR_Sales_To_SalesDim_V4 ON Sales;
DROP TRIGGER IF EXISTS TR_EmployeeSales_AnalyticalUpdate ON EmployeeSales;
DROP TRIGGER IF EXISTS TR_Sales_AnalyticalUpdate ON Sales;

DROP FUNCTION IF EXISTS ProcessSalesDimSCD2() CASCADE;
DROP FUNCTION IF EXISTS ProcessSalesToSalesDim() CASCADE;
DROP FUNCTION IF EXISTS ProcessEmployeeSalesAnalyticalUpdate() CASCADE;
DROP FUNCTION IF EXISTS ProcessSalesAnalyticalUpdate() CASCADE;

This has to happen first and completely. If any old always-rebuild trigger survives, it will keep firing alongside the new selective one, and every dimension change will both rebuild-everything and selectively-recalculate — the worst of both worlds, and inconsistent to boot. The IF EXISTS guards make the drops idempotent (safe to re-run), and CASCADE ensures no orphaned dependency blocks the removal. Removing the old regime cleanly is the foundation the rest of the migration stands on.

Step two: install the new regime

With the old triggers gone, the migration installs V8's. TR_IntelligentDimensionChange — the selective trigger — goes on SalesDim. And a new insert trigger, ProcessSalesToSalesDimV8 behind TR_Sales_To_SalesDim_V8, replaces the old dimension explosion. This is where the schema visibly grew: the V8 insert path loops dimensions 1 through 12, not the old six or seven, because V8 added five new dimensions — ShippingCost, TaxRate, PaymentMethod, SalesRepId, CampaignId — to support its richer measure set. Each dimension row it writes gets stamped with its AffectedMeasures, precomputing the dependency lookup at write time.

There is also CreateSalesFactsV8 and a statement-level ProcessSalesDimSCD2V8 that works over the NEW/OLD transition sets. The migration swaps the entire fact-derivation machinery, not just the one trigger.

Step three: verify, for once

Here is the part I am proud of, because it is the direct lesson from V7's fix-script mess. That mess happened because there was no way to check whether a schema change had actually worked — you shipped it and waited for the next thing to break. V8's migration ends with a function that checks itself:

SELECT * FROM VerifyV8Migration();

VerifyV8Migration returns a set of PASS/FAIL rows for the things that must be true after migration: “Old Triggers Disabled,” “New Triggers Enabled,” “Dependency Mapping” (is MeasureDimensionDependency populated?), “V8 Functions” (do the new functions exist?). Instead of hoping the migration worked, you run one query and read a checklist. If any row says FAIL, you know exactly what to fix before the first real dimension change exercises the broken path. It is the integration-test-shaped safety net V7 desperately needed, finally built — a small function, but the single most important lesson of V7 turned into code.

The migration also logs a sentinel row to mark that it ran — a MeasureRecalculationLog entry with SalesReferenceId = 0, TriggeredByDimTypeId = 0, the full measure array, reason 'V8 Migration from V7', and BatchId = 'V8_MIGRATION'. Like V7's test insert and the MANUAL_SETUP seeds, it is a sentinel that lives in a real table forever — the same self-littering habit, though here at least it is in a log table where a marker row is defensible rather than in Sales.

The landmine: dimension ids that lie

Now the thing that can go catastrophically wrong, and it is subtle enough that I want it stated loudly. V8 reuses dimension type ids that mean something else in a fixed V7 database.

Recall that V7's fix scripts registered new dimension types: DimTypeLookup id 8 = QuoteDate, id 9 = DeliveryDate. V8's schema uses the same ids for different things: id 8 = ShippingCost, id 9 = TaxRate (with 10 = PaymentMethod, 11 = SalesRepId, 12 = CampaignId). Same surrogate keys, completely different meanings.

If you migrate a V7 database that went through those fix scripts into V8 without reconciling the dimension ids, every existing SalesDim row with DimTypeId = 8 still means QuoteDate — but V8's code, its dependency map, and its measures all now believe id 8 means ShippingCost. The data says one thing; the code assumes another. Measures that depend on ShippingCost would happily consume old QuoteDate values. There is no error, no constraint violation — just silently wrong numbers, because a surrogate key is only meaningful relative to the version that assigned it.

This is the renumbering hazard that first appeared when V2 shifted dimension ids, at its most dangerous. It is why VerifyV8Migration checks the dependency mapping but cannot catch this particular problem — the mapping is internally consistent; it is the pre-existing data that carries the old meaning. A truly safe V7-to-V8 migration would have to remap or quarantine any dimension rows with the reused ids, and the shipped migration does not. I flag it as the sharpest edge in the whole upgrade: surrogate ids are stable only within a version, and migrating across a boundary that reused them is a data-integrity trap with no automatic guardrail.

What good migration looks like

Set the landmine aside and this migration is how I wish V7's refactor had gone: drop the old regime cleanly and idempotently, install the new one, and verify with a function that returns a pass/fail checklist instead of leaving you to discover breakage by touching production. The contrast between V7's two-pass, _Corrected-trigger chaos and V8's single verified migration is the clearest before-and-after in the project of what building a safety net buys you.

The landmine is the reminder that a safety net only catches what you thought to check. VerifyV8Migration verifies the things I anticipated; the reused-dimension-id trap is the thing I did not encode a check for, and so it waits for anyone who migrates real V7-fixed data. Honest migration tooling is not “it can't go wrong” — it is “here are the checks, and here, explicitly, is the one they don't cover.”

One V8 honesty remains. The row-level trigger is genuinely selective — but the batch path that also exists is much less so than its name claims. When selective isn't is the closing audit of V8.