Enterprise Data Migration: Legacy SQL Server to Modern PostgreSQL with Zero Data Loss
Every order, user, and calculation migrated intact. Zero data loss. Zero regressions.
Years of critical business data locked in a vendor-controlled SQL Server
The legacy Marketing Manager platform stored years of critical business data in a SQL Server database: 156 user accounts, 4,098 orders, 496 breakeven calculations with nested cost structures, 540 operations questionnaires with delivery location preferences, and 5 options positions. This data represented the complete operational history of the brokerage and its client relationships. Any data loss or corruption during migration would be unacceptable.
Additional complexity arose from the organizational dynamics: the previous IT vendor controlled the production database, and access had to be negotiated through the client’s non-technical management. A secure .bak database backup was ultimately obtained via Bitwarden Send with a time-limited download link.
An 11-phase ETL pipeline with a dry-run validation pass before any writes
We built a comprehensive 11-phase ETL migration pipeline that transformed the legacy flat data structures into the new normalized PostgreSQL schema:
The pipeline began with a dry-run pass that validated all queries and row counts before writing any data. Migration proceeded in strict dependency order: reference data first (7 crop types, 15 sale types, 83 buyers), then users (49 managers/admins + 107 clients), followed by client records with manager assignment preservation, then downstream entities (orders, breakeven calculations, questionnaires, options).
Breakeven calculations required the most complex transformation: the legacy system stored costs in a flat structure, which our pipeline normalized into 7,708 individual cost line items with proper categorization (equipment, inputs, land, labor, insurance, other). Questionnaire migration preserved 92 preferred delivery locations and 17 refused locations with their client relationships intact.
Post-migration verification compared row counts between legacy SQL Server and new PostgreSQL, with every delta traced to non-client user records (manager accounts without corresponding client records in the new system). A full regression test suite (584 tests) confirmed the migration introduced no functional regressions. All migrated users received temporary placeholder passwords with password reset email capability for secure credential establishment.
Dry-Run Validation: A full dry-run pass validated all queries and row counts against the source before writing a single row to the target. This caught schema mismatches and data type issues before they could cause data loss.
Dependency-Ordered Phases: 11 migration phases executed in strict dependency order — reference data → users → client records → downstream entities. Manager assignment preservation maintained throughout.
Breakeven Normalization: The most complex transformation — 443 flat-structured legacy calculations normalized into 7,708 individual cost line items with proper categorization (equipment, inputs, land, labor, insurance, other).
Delivery Location Preservation: 92 preferred delivery locations and 17 refused locations migrated with all client relationships intact — a critical detail for brokerage workflow continuity.
Post-Migration Verification: Row counts compared between legacy SQL Server and new PostgreSQL. Every delta traced and accounted for. 584 regression tests confirmed zero functional regressions. All migrated users received temporary placeholder passwords with secure password reset flows.
How we built it
Dry-Run Validation: A full dry-run pass validated all queries and row counts against the source before writing a single row to the target. This caught schema mismatches and data type issues before they could cause data loss.
Dependency-Ordered Phases: 11 migration phases executed in strict dependency order — reference data → users → client records → downstream entities. Manager assignment preservation maintained throughout.
Breakeven Normalization: The most complex transformation — 443 flat-structured legacy calculations normalized into 7,708 individual cost line items with proper categorization (equipment, inputs, land, labor, insurance, other).
Delivery Location Preservation: 92 preferred delivery locations and 17 refused locations migrated with all client relationships intact — a critical detail for brokerage workflow continuity.
Post-Migration Verification: Row counts compared between legacy SQL Server and new PostgreSQL. Every delta traced and accounted for. 584 regression tests confirmed zero functional regressions. All migrated users received temporary placeholder passwords with secure password reset flows.
Complete operational history migrated with zero data loss and zero regressions
Every order, user, breakeven calculation, questionnaire, and delivery location preference made it across intact — with a full regression test suite proving the system worked correctly after migration.
ETL Engineering, Data Migration, SQL Server, PostgreSQL, Schema Normalization, Data Integrity Verification
Agricultural Technology