Validating the Migration, Line by Line
How 100+ SQL validation queries and full source-to-target reconciliation brought a legacy CRM’s data safely into a modern Azure Data Lakehouse — with no unresolved ETL defects at Phase 1 UAT.
Quality engineering for an enterprise data migration.
The client is a global professional association supporting finance and treasury professionals through certifications, education, networking, and large-scale conferences. Its largest annual event attracts thousands of attendees and exhibitors, and the organisation maintains extensive registration, exhibitor, booth, and personal data in NetForum, a widely used association-management CRM platform. The client's legacy reporting environment relied directly on NetForum — functional, but limited by slow reporting performance, complex reporting logic, difficult scalability, legacy database dependencies, and limited analytical capabilities. To modernise reporting, the organisation initiated a migration into Azure Data Lakehouse, restructuring source data into Bronze, Silver, and Gold layers while preserving business rule integrity. The team's role was to validate that migrated data accurately represented the source system, that derived calculations were correct, that business rules held, and that the resulting Gold layer was ready to support downstream Power BI and the client's internal reporting dashboards.
Understanding the business rules is as important as validating the data.
SQL VALIDATION QUERIES EXECUTED
VALIDATION PHASES COMPLETED
DERIVED BUSINESS RULES VALIDATED
COMPLETE — UAT-READY
Modernising reporting without losing the business rules buried inside it.
The legacy environment's limitations were clear — slow reporting, complex logic, difficult scalability — but the migration itself introduced its own risk: years of derived business logic, undocumented edge cases, and a client reporting layer that didn't always agree with the new data layer.
Legacy Schema Complexity
The legacy CRM's schema had grown across years of historical event structures, with limited documentation and heavily derived business logic embedded in reporting rather than the source system itself.
Nine Interlocking Derived Fields
Fee category, registration status, registration type, loyalty, retention, weeks out, weeks early, event type, and member type all had to be recalculated and validated independently — each with its own edge cases and exceptions.
Registrant & Exhibitor Complexity
Coverage spanned full registrants, exhibitor conference registrations, exhibitor floor staff, and complimentary and internal registrations — each requiring distinct validation logic across booth inventory, personnel allocation, and fee treatment.
Reporting vs. ETL Discrepancies
The client's internal reporting dashboards sometimes classified records differently than the new Gold layer did — requiring careful investigation to determine whether a discrepancy was a genuine ETL defect or a difference in reporting-layer logic.
Scale of Reconciliation Required
Validating source-to-target accuracy across registrant, exhibitor, and historical event data required well over a hundred distinct SQL validation queries — with no tolerance for silent data loss during transformation.
Four phases, one reconciled Gold layer.
Testing was structured into four validation phases — source, transformation, business rule, and reporting — each building on the last to confirm the migrated data was both accurate and business-ready.
Source-to-Target Validation
Compared source system records directly against the Bronze layer, then traced data through Silver and Gold transformations to confirm nothing was lost or altered unexpectedly in migration.
Registrant Validation
Verified registration status (active/cancelled), paid-versus-comp fee logic, registration type classifications, loyalty tiers, retention calculations, and geography fields — with particular attention to exhibitor, complimentary, and internal registrations.
SQL Validation at Scale
Executed 100+ SQL validation queries covering registrant counts, fee category logic, duplicate analysis, booth reconciliation, inventory validation, historical registrations, and source-to-target comparisons.
UAT Support & Documentation
Supported user acceptance testing with clarified findings, thorough documentation, and close collaboration with developers and business analysts — delivering a UAT-ready Gold layer at the close of Phase 1.
Complete source-to-target validation delivered across registrant, exhibitor, and historical event data — confirming the migrated Bronze, Silver, and Gold layers accurately represented the legacy source system.
All nine derived business rules independently validated — fee category, registration status, registration type, loyalty, retention, weeks out, weeks early, event type, and member type — preserving business logic integrity through the migration.
100+ SQL validation queries executed, covering registrant counts, duplicate analysis, booth reconciliation, inventory validation, and historical registration derivations.
A reporting-versus-ETL discrepancy investigated to full resolution — confirming the Gold layer correctly followed source data and no ETL defect existed, protecting the team from chasing a false alarm.
Exhibitor and booth data fully validated — booth types, categories, personnel allocation, and inventory states all confirmed accurate ahead of UAT.
Phase 1 delivered UAT-ready — with complete documentation and defect investigation history supporting a smooth handoff to user acceptance testing.
The stack.
The migration required more than confirming rows matched — it required understanding years of derived business logic well enough to know when a discrepancy was a genuine defect and when it was simply a difference in reporting-layer logic. That distinction, backed by rigorous SQL validation and close collaboration with developers and business stakeholders, delivered a Gold layer the client could trust — and a Phase 1 handoff ready for UAT without unresolved questions.