Having recently concluded a migration from a heavily customized Salesforce org to a more streamlined HubSpot Enterprise instance, I must emphasize that the most critical phase began after the technical cutover. The validation of migrated data is often relegated to spot-checking a few records, which is a profound operational risk. Our team instituted a methodical, query-based validation protocol that I believe should be a standard part of any migration playbook.
We operated on a core principle: every data point must be validated in its new context, not just for presence but for relational integrity and business rule adherence. We maintained a parallel run of the old system for 30 days, but the true source of truth was a set of validation queries run against the new system's database (HubSpot's PostgreSQL data warehouse in this case) and compared against exports from the source. The key was structuring these queries to test specific, high-value assertions.
Our workflow was broken into sequential layers, each dependent on the previous layer passing a threshold (we used 99.5% accuracy for non-critical fields, 100% for core identifiers and financial data).
* **Layer 1: Volume & Uniqueness Checks.**
Simple counts to ensure no record loss or duplication during batch operations. This included total records per object, counts by core status (e.g., `Contact.Status`), and distinct counts of primary keys.
* **Layer 2: Critical Field Integrity & Transformation Validation.**
Here, SQL proved indispensable for validating complex data mappings. For example, we had concatenated three custom fields in Salesforce into a single structured field in HubSpot. The validation query checked the transformation logic explicitly.
`SELECT COUNT(*) FROM contacts WHERE new_structured_field != (old_field_one || ' | ' || old_field_two || ' | ' || old_field_three);`
Similar queries verified that date shifts (for timezone normalization) were applied correctly, and that picklist value mappings were accurate.
* **Layer 3: Relational Integrity Tests.**
This is where many migrations develop silent errors. We wrote queries to identify orphaned records—for instance, companies without any associated contacts, or deals linked to a non-existent pipeline stage. A crucial test validated our automated lead-to-contact merging logic by ensuring the count of unique email addresses in the new system matched the expected deduplicated count from the source.
* **Layer 4: Business Logic and Historical Accuracy.**
The final layer involved complex joins to ensure reports would run correctly post-migration. We sampled key reports—such as "Win Rate by Sales Rep for Closed Deals in FY23"—and wrote a single SQL query that replicated the report logic, comparing the result set totals against the same report run in the old system. Discrepancies here often uncovered subtle issues in date logic, currency conversion, or stage mapping.
The primary advantage of this SQL-centric approach is its auditability and repeatability. Each query is a documented test case. The most significant lesson was the necessity of building these validation scripts *during* the data mapping phase, not after. This allowed us to identify ambiguous transformation rules early. What I wish I had known earlier is to allocate at least 40% of the total migration timeline to this validation and reconciliation process; it uncovered more issues than the actual data transfer itself.
That's exactly the right approach. The parallel run is a good safety net, but those targeted SQL queries are what give you real confidence. We learned the hard way that you can't just check row counts, you have to check the *meaning* of the data in the new schema.
One thing I'd add: we built a simple dashboard for the business team to run a subset of these validation queries themselves during UAT. It turned them from passive reviewers into active participants, and they caught a few edge cases our queries missed because they understood the business context better.
~Isla