Decommissioning a Source System With No Trustworthy Lineage
Wayfinder Logistics will retire its 22-year-old order management system in six weeks. The system feeds 900 downstream objects: 300 warehouse tables, 120 transformation jobs, three mart schemas, 47 Tableau workbooks, and 12 file interfaces. Lineage exists only in spreadsheets maintained by two engineers who are leaving. Sixty warehouse tables have no documented owner. Business users say only five reports matter, but usage logs show 180 unique users across 34 workbooks in the last 90 days. Two workbooks feed a quarterly regulatory filing; one feeds carrier settlement. The replacement system has a different order-line model: one order can now have multiple fulfillment splits, and cancellations are event-based rather than a status flag. Cutover is fixed by the vendor contract and cannot move.
1. Describe your impact analysis approach. Which evidence would you trust, and which would you challenge?
Read the worked answer
Combine evidence, because no single source is trustworthy here. Usage logs identify 34 workbooks with active users, but usage is not criticality. Use tiered triage: tier 1 is regulatory filings, carrier settlement, and any report with contractual or financial statement impact; tier 2 is operational reports with named owners and daily use; tier 3 is low-use or ownerless. Reconstruct lineage from query logs, SQL parsing, and job schedules, then interview the two departing engineers and record what they know before they leave. Do not accept the claim that only five reports matter without testing it: 180 users over 90 days may include automated service accounts, so separate human and machine access. For the 60 ownerless tables, assign provisional owners by domain and require explicit disposition: migrate, retire, or archive. Build a line-by-line impact register with object, downstream consumers, criticality, disposition, and validation method. Timebox the analysis and start with the replacement system's known model changes.
2. The replacement changes the grain and the meaning of order lines. How would you map and validate the migration?
Read the worked answer
The replacement changes the grain and the meaning, so this is a semantic migration, not a table swap. Old order-line records likely represent one row per order line with a status flag; new fulfillment splits mean one order line can produce multiple shipments, and cancellations are events that may occur after fulfillment. Define the new canonical grain first, probably order-line-fulfillment, and document how to reconstruct the old view for legacy reports. Cancellation logic must use the latest event as of the report timestamp, not a status column, or historical restatements will break. Build conformed dimensions and keep effective dating. For each downstream object, choose rebuild, re-point, or retire; do not mechanically re-point 300 tables, because that preserves old assumptions. Reconcile counts and amounts between old and new for overlapping periods, and investigate every difference above a defined tolerance. The judgment is that a faithful migration may change report logic, and business owners must approve semantic changes, not just IT.
3. Design the cutover validation and risk-acceptance process when the date cannot move.
Read the worked answer
Run a parallel period covering at least one full order-to-cash cycle and a regulatory reporting date if possible. Validate with control totals at multiple levels: order count, line count, fulfilled quantity, cancelled quantity, revenue, and settlement amount, plus hash comparisons on key extracts. Include negative tests for cancellation events and split shipments. Obtain written sign-off from owners of tier-1 reports and explicit risk acceptance from the business for anything not migrated. Prepare a rollback or read-only fallback, and freeze noncritical report changes during cutover. Staff a war room for the first two weeks with named contacts for source, warehouse, and business. Retire legacy objects only after two clean closes, not on the cutover date. The main risk is that the fixed contract date compresses validation; if so, reduce scope explicitly by deferring tier-3 reports with documented risk acceptance rather than skipping reconciliation. The judgment is that a visible deferral is safer than a silent broken regulatory feed.