Keep metric meaning stable while implementations change.
Preserve/Version Metrics and Semantic Behavior Across Old/New Platforms
Preserve and version business metrics across old and new platforms without silent restatement.
Learning outcomes
Preserve metric meaning independently from SQL dialect or platform implementation.
Version genuinely breaking semantic changes instead of hiding them inside migration work.
Cross-check old/new metric outputs, drill paths, time windows, units, null/rounding behavior, and security context.
Use golden datasets and query-result diffs to detect semantic drift.
Plan consumer migration and deprecation for changed metric versions.
Chapter 29 begins from the governed AtlasMart state through
Chapter 28:
10 current paid lines, 8 orders, 12 units, 820 USD gross
revenue, 495 USD cost, and 325 USD gross profit, with source waterline 208. The sales fact
grain remains one paid order line;
gross_revenue_usd.v1 remains paid line amount in
USD under its governed filters and UTC time semantics. A
platform migration is not permission to redefine those
contracts.
The mandatory lab is free/local/synthetic and was executed with Python 3.13.5 and SQLite 3.46.1. SQLite supplies tables, views, transactions, UPSERT and query-plan evidence, but it does not implement stored procedures, cloud roles, CDC connectors, or managed-warehouse cost controls. Stored-procedure behavior and access roles are therefore represented as explicit metadata/policy fixtures, while SQL correctness and restart/idempotency behavior are executed locally. Time zone is UTC; no production credentials or personal data are used.
1. Problem frame: same column name, different metric
The modern target exposes a column named
line_amount_usd, so a dashboard author assumes
revenue can be computed with SUM(line_amount_usd).
That query returns 860 USD because it includes the synthetic
internal test row. The legacy report returns 820 USD because
gross_revenue_usd.v1 includes a governed exclusion.
Column names do not carry complete metric semantics.
2. A metric contract must survive SQL translation
The migration contract for a governed metric should record its name/version, source facts, grain, aggregation, filters, time basis, unit/currency, null/unknown policy, security context, owner, test fixture, and lineage. The old and new implementations may use different SQL syntax or physical layouts as long as the contract and golden outputs are preserved.
| Contract field | gross_revenue_usd.v1 |
|---|---|
| Base grain | one paid order line |
| Aggregation | SUM(line_amount_usd) |
| Required filter | status='paid' AND is_test_order=0 |
| Time semantics | order_ts bucketed in UTC |
| Unit | USD |
| Current golden result | 820 USD |
| Owner | finance semantic owner |
| Breaking changes | new metric version + consumer migration |
3. Preserve behavior at important boundaries
Semantic parity is broader than one top-line number. Test empty periods, zero-denominator ratios, unknown dimension members, backdated SCD rows, rounding, currency conversion dates, inclusive/exclusive time windows, and row-level security context where they apply. Two platforms can both return 820 USD overall and still disagree on customer segment or date allocation.
4. Controlled failure: improve the metric during infrastructure migration
The modernization team decides internal test revenue “should count now,” removes the exclusion in the new system, and treats the resulting 860 USD as a migration improvement without versioning the metric.
Diagnosis: infrastructure parity and business-definition change are now entangled. A 40 USD difference could be a migration bug or an intentional semantic change; consumers cannot tell. Historical dashboards silently restate.
Repair: first make v1 return 820
USD on both systems. If the business approves a new definition,
publish v2 with explicit effective date, owner,
documentation, lineage, golden tests, and consumer
migration/deprecation plan.
5. Golden queries and result diffs
-- Legacy implementationSELECT SUM(line_amount_usd) AS gross_revenue_usd_v1FROM legacy_finance_report;-- 820-- Modern implementation with explicit governed contractSELECT SUM(line_amount_usd) AS gross_revenue_usd_v1FROM modern_finance_report;-- 820-- Deliberately wrong modern shortcutSELECT SUM(line_amount_usd)FROM sales_lineWHERE status='paid';-- 860 (semantic drift)A golden query is not “the one true production query.” It is a deterministic reference case whose inputs and expected outputs are version-controlled. It protects against accidental changes and supports platform parity. Production monitoring still needs live reconciliation and observability because real data distributions evolve.
6. Tool independence and consumer migration
Do not preserve semantics only inside one dashboard file. The metric contract belongs in a governed semantic/catalog layer or equivalent versioned specification, and each BI tool should consume or cross-check that definition. If a new platform requires a different expression, compile or implement the same contract and retain source-to-metric lineage.
7. Local lab: fail the naïve metric, pass the governed one
legacy_v1 = 820modern_naive = 860modern_v1 = 820assert modern_naive != legacy_v1 # negative case must fail parityassert modern_v1 == legacy_v1 # migration acceptancemetric = { "name": "gross_revenue_usd", "version": 1, "filter": "status='paid' AND is_test_order=0", "time_zone": "UTC", "unit": "USD", "expected_fixture_result": 820, "owner": "finance semantic owner"}# Any intentional filter/unit/window change gets version=2 and a separate# consumer migration; do not overwrite version 1 in place.8. Security is part of semantic behavior
A metric evaluated under finance-wide access and the same metric evaluated under a row-restricted regional role may legitimately return different values. Migration parity therefore compares equivalent security contexts. The Chapter 29 acceptance fixture deliberately starts with a finance report reader who can bypass the governed view and query the raw target table; the gate fails until that direct access is denied.
9. Verification checklist
- Every critical metric has a versioned contract and golden fixture.
- Old/new results match at overall and business-relevant slice levels.
- Time zone, currency/unit, filters, null/unknown, and security context are explicit.
- Intentional semantic changes are published as new versions.
- Consumer ownership and deprecation dates exist before old semantics are retired.
All destructive actions target only a disposable
atlasmart_ch29_lab directory. Never point these
commands at a production warehouse. Reset with
python -c "import shutil; shutil.rmtree('atlasmart_ch29_lab',
ignore_errors=True)"
and rerun the fixture from a clean directory.
10. Production judgment and bridge
A migration is semantically successful when consumers receive the same governed meaning, not merely familiar column names. The next lesson turns all evidence into a single acceptance gate covering data, queries, performance, modeled cost, security, rollback, and legacy retirement readiness.
Knowledge check
Why can two SQL queries with the same selected column still represent different metrics?
Show answer
Because metric semantics include aggregation, filters, time windows, units/currency, history rules, and security context—not only the physical column name.
Should an intentional metric change be hidden inside platform cutover?
Show answer
No. Preserve the current version first; publish the changed meaning as a separately versioned metric with tests and consumer migration.
What is a golden dataset/query for?
Show answer
It provides deterministic inputs and expected outputs for regression/parity testing. It complements rather than replaces production observability.
Why include security context in metric parity?
Show answer
Row/column policies can change the visible fact set. Comparing outputs under different effective privileges can create false migration differences or conceal access regressions.
Authoritative references
- SQLite — Transactions for the local atomicity model used in the executable harness.
- SQLite — UPSERT for the local idempotent replay example.
- SQLite — EXPLAIN QUERY PLAN for the local plan evidence; its output format is explicitly not a stable application API.
- Kimball Group — DW/BI resources for dimensional modeling, business process/grain discipline, and lifecycle-oriented warehouse delivery.
- Big Data Academy — Data Warehousing and Dimensional Modeling curriculum for this course's stable AtlasMart contracts and chapter sequence.