Reconcile governed AtlasMart metrics to trusted atomic controls and stable golden fixtures while using prior-period expectations as anomaly signals rather than moving truth.
Metric/Reconciliation Tests Against Trusted Source Totals and Prior Period Expectations
Build an identity and access model for AtlasMart that separates humans from services, eliminates shared credentials, enforces environment boundaries, and proves least privilege with allow/deny evidence.
Learning outcomes
Reconcile warehouse facts to independent trusted controls at the same semantic scope.
Distinguish golden datasets from moving production baselines.
Test governed metric SQL/results and detect dashboard-specific drift.
Use prior-period expectations as anomaly evidence without treating them as truth.
Design regression tests that survive legitimate business growth and planned semantic versions.
Chapter 24 begins from the governed AtlasMart state produced by Chapters 01–23: 10 current paid lines, 8 orders, 12 units, 820 USD gross revenue, 495 USD cost, and 325 USD gross profit. The current fact grain remains one accepted current paid order line; Chapter 20 metric definitions remain authoritative; Chapter 21 certified marts remain dependent on conformed assets; Chapter 22 security controls remain in force; and Chapter 23 ownership/lineage metadata supplies the change context. A test may block, quarantine, alert, or document a failure, but it must not silently change business semantics to make the suite green.
Runtime:
Python 3.13.5 + SQLite 3.46.1.
Storage: in-memory SQLite.
Time zone: UTC. Currency: USD.
Source/warehouse grain: one paid order line
identified by (order_id, line_id), with a separate
immutable delivery/event ID.
Security: synthetic customer IDs only.
Cost: free/local, no managed service.
Non-guarantee: passing these fixtures proves
the encoded contracts for this dataset/runtime; it does not
prove unknown business rules, external source truth, or
vendor-specific behavior not represented by the fixture.
1. The realistic problem: every table is “valid,” but one dashboard reports 665 USD
The certified warehouse reports 820 USD gross revenue. A
marketing dashboard independently filters out channel
sales and reports 665 USD while still labeling the
KPI “Revenue.” No schema constraint is violated; every row is
valid. The failure is semantic regression and duplicated
business logic.
2. Reconciliation requires a trusted control with matching scope
| Control | Expected | Scope |
|---|---|---|
| Current paid line count | 10 | One current accepted paid order line |
| Distinct paid orders | 8 | Current accepted paid orders |
| Units | 12 | Additive paid-line quantity |
| Gross revenue | 820.00 USD | Governed paid-line amount, all approved channels |
| Cost | 495.00 USD | Governed accepted line cost |
| Gross profit | 325.00 USD | Revenue minus cost at same accepted scope |
A source total for all order statuses cannot directly reconcile to a paid-only warehouse fact without an explicit bridge/filter. The numbers must share grain, population, units/currency, time window, and correction state.
3. Golden fixture versus moving production baseline
A golden dataset is a small immutable/versioned
fixture with deliberately known outputs. AtlasMart’s current
Chapter 24 golden fixture expects
10 current paid lines, 8 orders, 12 units, 820 USD gross
revenue, 495 USD cost, and 325 USD gross profit. A production baseline is observed real data and will change.
Writing
assert today_revenue == yesterday_revenue creates a
flaky business test. Instead, use production expectations as
anomaly signals with governed thresholds/seasonality/owner
response, while exact golden tests protect deterministic
semantics.
4. Executed governed metric and drift result
governed gross_revenue_usd.v1 = 820.0local dashboard query excluding channel='sales' = 665.0semantic difference = 155.0Result: dashboard_drift_detected = PASSRepair: consume governed metric contract; do not relabel a narrower population as the same metric.This intentionally reuses Chapter 21’s conformance failure so the regression suite protects a known semantic boundary rather than inventing a new metric definition.
5. Daily golden regression output
2026-09-18 365.002026-09-19 150.002026-09-20 175.002026-09-21 130.00TOTAL 820.00The four day-level results reconcile to the atomic total. A regression test can compare the complete ordered result set or a canonical hash. Stable ordering and canonical formatting matter when checksums are used.
6. Reconciliation SQL should expose both sides
WITH warehouse AS ( SELECT COUNT(*) AS line_count, COUNT(DISTINCT order_id) AS order_count, SUM(quantity) AS units, ROUND(SUM(line_amount_usd),2) AS revenue, ROUND(SUM(cost_amount_usd),2) AS cost FROM fact_sales), expected AS ( SELECT 10 AS line_count, 8 AS order_count, 12 AS units, 820.00 AS revenue, 495.00 AS cost)SELECT w.*, e.*FROM warehouse w CROSS JOIN expected e;In production the trusted control may come from an independently captured source manifest, ledger, finance control, or signed batch summary. Avoid “reconciling” a model to another model derived through the same buggy transformation path.
7. Prior-period expectations: useful, but not truth
Suppose revenue rises 80% versus the prior comparable period. That may be a duplicate-load incident, a promotion, a new store, a correction, or genuine growth. An expectation test should produce evidence and route ownership. It should not silently rewrite data toward the expected value. Exact hard thresholds are justified only by explicit business constraints, not because a monitoring library needs a number.
8. Regression tests for metric versions
| Change | Testing approach |
|---|---|
| Bug fix with unchanged intended semantics | Old and new implementation must match golden expected outputs after fixture correction |
| Intentional semantic change |
Create v2, keep v1 during
migration, publish comparative results
|
| Source correction/restatement | Update source evidence + authorized correction ledger; update golden fixture/version explicitly |
| Dashboard-local filter | Fail conformance if it claims the governed metric name without matching contract |
9. Checksums are evidence, not explanations
A checksum can prove two canonicalized result sets differ; it cannot explain which row or rule caused the difference. Keep row-level diff/reconciliation queries alongside hashes. Likewise, engine-dependent floating-point formatting can make naive hashes unstable. This lab rounds monetary controls to two decimals and uses deterministic ordering; production financial systems may require fixed-precision decimal types rather than SQLite REAL.
10. Production judgment and bridge
Correctness: exact golden results protect the encoded semantics. Freshness/history: reconciliation must state the as-of/correction state. Idempotency: reruns should reproduce the same accepted controls for the same evidence. Observability: alert on control deltas with enough context to diagnose them. Performance: compare scans only on equivalent queries; testing every full table at every commit may be too slow. Security: source controls and result diffs can expose sensitive aggregates. Versioning: do not mutate metric meaning in place. Lesson 5 turns these tests into CI and production actions with severity and waiver governance.
11. Verification checklist
- Confirm 10/8/12/820/495/325 controls exactly.
- Confirm daily revenue rows sum to 820 USD.
- Confirm governed revenue is 820 while the deliberately drifted dashboard is 665.
- Confirm a golden dataset is immutable/versioned, not copied from “current production.”
- Confirm anomaly expectations route investigation rather than force data to match expectations.
Knowledge check
Acceptance questions
- Why is reconciling two models from the same transform weak evidence?
- Why should prior-period tests usually not be exact equality assertions?
- What must happen when metric semantics intentionally change?
- What can a checksum prove—and not prove?
Review the answers
1. They can share the same defect and agree incorrectly.
2. Real business activity changes; expectations are anomaly signals unless equality is a true invariant.
3. Version the metric, test both contracts, document migration/deprecation, and update consumers deliberately.
4. It can prove canonical outputs differ/match; it does not identify the business cause.
Authoritative references
- Python — unittestStandard-library support for deterministic fixtures, assertions, setup/teardown, and automated test suites.
- SQLite — CREATE TABLE / constraintsAuthoritative syntax and semantics for NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and table constraints used in the local lab.
- SQLite — Foreign Key SupportExplains foreign-key behavior and the need to enable enforcement explicitly in SQLite connections.
- SQLite — TransactionsUsed to demonstrate that target writes, delivery ledger, and watermark advancement must commit atomically for safe restart.
- SQLite — UPSERTReference for conflict-aware idempotent target application in the CDC fixture.
- Python — hashlibUsed to fingerprint deterministic test evidence rather than compare unstable timestamps or environment-specific logs.
12. Lab cleanup/reset
Close the in-memory database and rerun to reproduce the exact golden outputs. Any intentional golden-fixture change should be reviewed/versioned rather than overwritten during test execution.