Overview Phase 1 — Assessment Phase 2 — Schema Conversion Phase 3 — Data Migration Phase 4 — Validation Phase 5 — Performance Phase 6 — Documentation
Phase 04 — Validation & Integrity Testing

Proving the Migration Is Correct

Row counts only prove I moved the right quantity of data. This phase proves the actual values, the constraints, and real query behavior all migrated correctly too — across three separate layers of validation.

Aggregate Checksums Constraint Testing Query Diffing 3-Layer Validation
Overview

What I Did in Phase 4

I ran three layers of validation: aggregate checksums beyond row counts, constraint enforcement testing with intentionally bad inserts, and representative query diffing against the live source. Every check passed.

100%
Row Count Match
4 / 4
Constraint Tests Passed
3
Queries Diffed
100%
Checksum Match
Layer 1

Aggregate Checksums

For every table I compared sums, distinct counts, min/max values, and null counts between PostgreSQL and SQL Server — a lightweight but effective way to catch silent data corruption a row count alone would miss.

TableCheckResult
cms.providersdistinct NPIs, states, sum(ruca), null countsExact match
cms.provider_servicesmin/max id, sum(tot_benes), sum(tot_srvcs), sum(avg_sbmtd_chrg)Exact match
cms.user_state_accessdistinct usernames, statesExact match
cms.staging_rawrow count, distinct NPIsExact match
Layer 2

Constraint Enforcement Testing

Matching data is only half the story — I needed to prove SQL Server enforces the same rules PostgreSQL did. I ran four intentionally-bad inserts, each wrapped in TRY/CATCH.

Test 01
Duplicate Primary Key
Insert a provider with an NPI that already exists — correctly rejected with a PRIMARY KEY constraint violation.
Test 02
Invalid Foreign Key
Insert a service row referencing a non-existent NPI — correctly rejected with a FOREIGN KEY constraint conflict.
Test 03
NULL in NOT NULL Column
Insert a provider with a NULL organization name — correctly rejected as the column doesn't allow nulls.
Test 04
Duplicate Composite Key
Insert a duplicate (username, state_abbr) pair — correctly rejected with a PRIMARY KEY constraint violation.

All four were rejected with the expected, specific error message, and row counts on every affected table were confirmed unchanged afterward.

Layer 3

Representative Query Diffing

I ran three realistic, application-style queries against both systems: top 10 states by provider count, top 10 billing codes by service volume, and a full record lookup for the single busiest provider. All three matched exactly — same values, same order, same result.

One difference worth noting, not a defect: an AVG() result showed more decimal places in PostgreSQL than SQL Server. Since the underlying SUM() values matched exactly in the aggregate checks, this is purely a display-precision difference between engines' AVG() implementations, not data corruption.

Screenshot — Migration Row Count Validation
Row count validation showing PostgreSQL and SQL Server tables matching exactly, with a clean zero-row audit log
Final validation — every table matched, and the audit log confirmed clean at zero rows
Phase 5 — Performance Tuning