Inventory Schemas, ETL Jobs, Reports, Stored Procedures, Dependencies, SLAs, and Hidden Business Logic

Learning outcomes

1. Problem frame: the table says 860 USD, finance says 820 USD

AtlasMart's legacy EDW has accumulated years of SQL, scheduled jobs, report filters, procedure code, service identities, and operational promises. A modernization team discovers a compact sales_line table and proposes to copy it into a new platform first, then “fix reports later.” The raw table has eleven paid-looking rows and 860 USD of line amount. Finance's certified report has only ten paid lines and 820 USD. Neither side is necessarily wrong: the report applies a hidden rule that excludes internal test orders.

The first migration question is therefore not “How do we copy this table?” It is “Where is business meaning encoded, who depends on it, and what evidence will prove that the new system behaves the same?”

2. Inventory state surfaces, not just tables

A migration inventory is a structured list of data objects, executable transformations, interfaces, consumers, owners, security boundaries, schedules, and service expectations that must either move, be replaced, or be retired. Schema DDL is only one slice. ETL jobs encode sequencing and retry behavior. Stored procedures and views can hide business filters. Reports can add last-mile logic. SLAs/SLOs encode consumer expectations. Credentials and roles determine who can bypass governed views.

3. Hidden business logic is executable metadata

The lab's legacy physical table contains one synthetic TEST900 row. The governed view applies status='paid' AND is_test_order=0. The second predicate is easy to miss because the column exists physically but its business significance lives in procedure/report code, not in the table name.

CREATE VIEW legacy_finance_report ASSELECT *FROM sales_lineWHERE status = 'paid'  AND is_test_order = 0;-- Raw physical stateSELECT COUNT(*), SUM(line_amount_usd) FROM sales_line;-- 11 rows, 860 USD-- Governed business stateSELECT COUNT(*), SUM(line_amount_usd) FROM legacy_finance_report;-- 10 rows, 820 USD

4. Dependency discovery must reach people and products

A dependency graph is directed: a source feeds a job; the job writes a table; a procedure derives a daily surface; reports query it; named consumers depend on those reports. Stopping lineage at legacy.finance_daily underestimates blast radius. Migration readiness needs the transitive path to every report, metric, export, service, and human owner that can observe a semantic change.

erp.orders  -> job_extract_erp_orders  -> legacy.sales_line  -> sp_refresh_finance_sales  -> legacy.finance_daily      -> rpt_finance_daily -> finance_controller      -> rpt_exec_revenue  -> executive_dashboard

5. SLA inventory is part of semantics

Data can be numerically correct and still fail migration acceptance if it arrives after the decision window. AtlasMart records “certified finance report ready by 08:30 UTC” as an owned contract. That contract needs its source-ready assumption, completeness rule, timezone, exception policy, and consumer. A platform that returns 820 USD at noon is not equivalent for an 08:30 operational process.

6. Controlled failure: migrate tables first

Diagnosis: row-count parity proves only that a physical set was copied. It does not prove which rows contribute to governed revenue, how UTC days are derived, whether report filters survived, or whether consumer access changed. The naïve modern report therefore reproduces the physical 860 USD rather than the governed 820 USD.

Repair: inventory executable logic before migration, convert each hidden rule into an explicit versioned contract/test, then reconcile old and new consumer-visible results. Row counts remain useful as one test, but they are subordinate to grain, metric, history, and access semantics.

legacy physical: (11, 9, 13, 860, 515, 345)legacy governed: (10, 8, 12, 820, 495, 325)naive modern:   (11, 9, 13, 860, 515, 345)corrected new:  (10, 8, 12, 820, 495, 325)history sha256: 240cc2c5a2ae0fdb58be216e6a560f3975f36cb7903ff2e340778e756508bb8a

7. Local lab: build and query the migration inventory

import json, sqlite3from pathlib import Pathroot = Path("atlasmart_ch29_lab")root.mkdir(exist_ok=True)conn = sqlite3.connect(root / "legacy.db")conn.executescript("""CREATE TABLE sales_line(  order_id TEXT, line_no INTEGER, line_amount_usd INTEGER,  status TEXT, is_test_order INTEGER,  PRIMARY KEY(order_id,line_no));INSERT INTO sales_line VALUES ('O1007',1,50,'paid',0);INSERT INTO sales_line VALUES ('TEST900',1,40,'paid',1);CREATE VIEW legacy_finance_report ASSELECT * FROM sales_lineWHERE status='paid' AND is_test_order=0;""")print(conn.execute("SELECT SUM(line_amount_usd) FROM sales_line").fetchone()[0])print(conn.execute("SELECT SUM(line_amount_usd) FROM legacy_finance_report").fetchone()[0])inventory = {  "stored_procedures": ["sp_refresh_finance_sales"],  "reports": ["rpt_finance_daily", "rpt_exec_revenue"],  "sla": "certified finance report ready by 08:30 UTC",  "hidden_logic": ["status='paid'", "is_test_order=0", "UTC day"]}(root / "migration_inventory.json").write_text(json.dumps(inventory, indent=2))# Expected: 90 then 50 in this tiny focused fixture.

8. Verification checklist

• Every migrated table has a declared business grain and owner.
• Stored procedure/view/report SQL has been scanned for filters, timezone logic, currency/unit rules, deduplication, and special members.
• Lineage reaches named reports/consumers.
• SLAs/SLOs and security surfaces are inventory items, not afterthoughts.
• At least one consumer-visible golden result exists for each critical metric.

9. Production judgment and bridge

The inventory phase is complete only when the migration team can answer what must remain equivalent, who will notice a difference, and how that difference will be measured. Missing documentation is itself a migration risk signal. Chapter 29 next moves from discovery to the strategy choice: rehost, replatform, or refactor—and explains why copying technical debt onto newer infrastructure is not modernization.

Knowledge check

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.