How I Proved a 12-Million-Record Data Migration Was Actually Correct

When my team assessed the migrated data against the original, only 86% of records matched on the most crucial field. By the final run, an impressive 99.9994% of 12.3 million records matched, and every record that didn't match could be explained. The discrepancy between these two figures wasn't due to data migration but thorough verification.

This verification aspect is often overlooked yet essential in the migration process. With over two decades of experience in building and leading engineering teams, particularly in digital health insurance marketplaces, I understand that records are rarely simple. They can represent complex decisions, such as a person's choice to be contacted.

During our migration from a legacy Oracle system to microservices backed by PostgreSQL and DocumentDB, we couldn't simply rely on rows being correct. We needed a method to prove the data's accuracy and document our findings. Here's what I discovered, applicable to your migration projects. A green checkmark on a migration can be misleading.

Most migration discussions focus on movement and tools like dual writes and replication. However, correctness is challenging because the team creating the comparison report often shares the same understanding of the data as the one who wrote the migration. This common understanding can lead to the same mistakes being made. A comparison report can falsely indicate correctness.

To avoid this, it's crucial to agree on what 'correct' means before testing. Correctness involves three key questions: completeness, faithfulness, and repeatability. Ensuring completeness means every record that should exist in the new system does. Faithfulness requires each value to have the same meaning on both sides, with matching text not being sufficient.

Repeatability ensures the migration yields the same result each time it runs. A successful verification process begins early, with testing on a small data subset. As data grows, tests become slower, and failures harder to trace. Careful selection of fields for comparison is also vital. During our migration, the timestamp field in user consent history was critical, as it determines the latest consent choice.

Start by loading the source and migrated data into a single staging environment, like a warehouse (e.g., Snowflake). All tests then run against this unified dataset, eliminating arguments about whose copy is correct. Resist the temptation to compare individual records; first, sort keys into four categories: match, mismatch, missing, and newer.

Analyzing the sizes of these categories reveals the problem's nature before focusing on individual records. Tools like Google Cloud's Data Validation Tool or data-diff can assist in this process, offering summaries and detailed insights. Utilizing an AI coding agent can significantly speed up this cycle, from working out matching queries in the sandbox to identifying offending records—what once took hours now takes minutes.

Maintain a clear distinction between the plan, business rules, and final verdict, keeping this structured summary human-centric. Always compare against the true source of data, not a duplicate, to ensure reliability. The system of record, where the application writes data, is the only trustworthy source. Two separate categories should label failures: one for messages never arriving and another for incorrect values written by the migration.

Blending these categories complicates problem-solving. Before fixing mismatches, clearly define what each value signifies on both sides, not just their appearance. Trace at least one real record through its history and the code that generated it. One thoroughly understood example is worth more than a thousand unexamined records.

Ultimately, decisions about which side is correct should be based on this evidence, not assumptions that the source data is inherently right due to its earlier origin. Pay special attention to high-stakes fields like consent, financial data, and access control, ensuring strict adherence to correctness.