Warehouse migrations are getting faster at the front end. AI-assisted SQL conversion, automated schema translation and migration agents can now convert thousands of stored procedures and views in weeks rather than months. But the hardest part of a migration has always been the back end: proving to finance, regulators and business users that the new platform produces the same answers as the old one. Until they believe that, the legacy system stays on, and the migration’s benefits stay on paper.
This playbook focuses on that proof. It explains the levels of validation, how to plan tolerances and sign-off, how to run parallel operations efficiently, which tools help, and the common sources of mismatches. It applies whether you are moving from Teradata, Oracle, Netezza, SQL Server or an older cloud warehouse to Snowflake, Databricks, BigQuery, Microsoft Fabric or Amazon Redshift.
Why Validation Has Become the Critical Path
As conversion becomes more automated, validation increasingly determines the timeline. Three trends reinforce this.
- Volume of converted logic. Faster conversion means more code arrives for testing at once. Without automated validation, testing becomes a bottleneck.
- AI-generated code needs evidence. Converted code that looks correct can still behave differently on edge cases. Stakeholders rightly want data-level proof, not code review alone.
- Regulatory and audit expectations. Financial reporting, regulatory submissions and risk models depend on warehouse outputs. Auditors expect documented evidence that migrated reports are reliable.
A migration is finished when the business trusts the new numbers, not when the last script is converted. Plan validation as a product, with its own scope, tooling, owners and acceptance criteria.
The Levels of Validation
Effective validation works in layers, from cheap and broad to expensive and precise. Practitioner guidance, such as Datafold’s migration guide, describes a similar progression from baseline metrics to cross-database diffing to iterative validation during parallel runs.
Level 1: Schema validation
Confirm that tables, columns, data types, nullability and keys exist as expected in the target. Differences in type mapping, such as numeric precision, string lengths or timestamp types, are an early warning of later data mismatches.
Level 2: Aggregate (column-level) validation
Compare row counts and aggregates such as sums, averages, minimums, maximums, distinct counts and null counts per column, optionally grouped by key dimensions like date, region or product. This is fast and catches gross errors, but it can hide offsetting differences and edge cases such as nulls and outliers.
Level 3: Row-level diffing
Compare individual rows by primary key, either by hashing selected columns or by comparing values directly. Row-level diffing finds exactly which records differ and why, and is essential for critical tables. For large tables, partition the comparison by key ranges or time windows and run partitions in parallel.
Level 4: Business output validation
Compare the outputs people actually use: reports, dashboards, regulatory extracts, model inputs and key metrics. A table can match while a report built on it differs because of semantic layer or BI logic changes.
Level 5: Ongoing reconciliation during parallel runs
Repeat validations on each load cycle while both systems run, so that newly introduced differences are caught quickly and trends become visible.
Tools That Help
Validation at scale requires automation. Several categories of tools are available.
- Open source validators. Google’s Data Validation Tool, an open source Python command-line tool built on the Ibis framework, supports column, row, schema and custom query validations across sources including Teradata, Oracle, SQL Server, Snowflake, Redshift, BigQuery and PostgreSQL. It can partition large row validations to run in parallel and save validations as configuration files for reuse. Note that its PyPI listing labels it as not an officially supported Google product.
- Commercial diffing platforms. Tools such as Datafold provide cross-database diffing with summaries of missing and differing rows, schema differences and value-level differences, plus scheduled monitors and APIs to run many diffs. Datafold’s documentation recommends sampling, filtering and column selection for very large tables, and watermarking (comparing rows within a time window) for data that is still changing.
- Platform migration tooling. Cloud and platform vendors increasingly bundle validation with their migration services and conversion tools. Check what your target platform provides before building your own.
- Data quality and testing frameworks. dbt tests, Great Expectations, Soda and similar tools help encode expectations that remain useful after migration as ongoing data quality checks.
Planning Tolerances and Sign-Off
Exact equality is not always the right standard. Legacy and target platforms can legitimately differ in floating-point arithmetic, rounding, time zone handling and ordering. Agree in advance what counts as a match.
- Classify data by criticality. Financial balances, regulatory figures and customer-facing amounts usually require exact matches. Operational metrics may tolerate small relative differences. Exploratory data may need only aggregate parity.
- Define tolerances per class. For example, exact match for monetary amounts at the reporting precision, a small relative tolerance for floating-point statistics, and documented acceptance of known semantic differences.
- Document known differences. Some differences are intentional, such as bug fixes, improved logic or changed definitions. Record them, get owner approval and communicate them before users discover them.
- Assign sign-off owners. Each data domain needs a business owner who signs off based on the validation evidence. Technical teams prepare evidence; business owners accept it.
- Set exit criteria for parallel runs. For example, a set number of consecutive load cycles with all critical validations passing and no unexplained differences.
Common Sources of Mismatches
Knowing where differences usually come from speeds up root cause analysis.
- Null handling: differences in how platforms treat nulls in comparisons, concatenation, aggregation and sorting.
- Data types and precision: numeric precision and scale, implicit casting, integer division and floating-point representation.
- Strings: trailing spaces, case sensitivity, collation and character encoding differences.
- Dates and times: time zones, daylight saving transitions, timestamp precision and date arithmetic functions.
- Non-deterministic logic: queries that rely on implicit ordering, such as taking the first row without an explicit ORDER BY, or ties in ranking functions.
- Load timing: source data changing between legacy and target loads, making snapshots inconsistent.
- Converted logic errors: subtle changes in joins, filters, or procedural logic introduced during manual or AI-assisted conversion.
Step-by-Step Playbook
- Inventory and prioritize. List all tables, views, reports and extracts in scope. Rank them by business criticality and usage, using query logs where available.
- Capture baselines early. Before migration begins, record row counts, aggregates and key business metrics from the legacy system for a defined snapshot. These become the reference.
- Choose and set up tooling. Select validation tools, configure connections to both platforms and define where results will be stored and reported.
- Encode validations as code. Store validation definitions in version control alongside the migrated code, so they run automatically in CI/CD and can be reused after migration.
- Validate each wave. As each set of objects is migrated, run schema, aggregate and row-level validations. Triage differences into conversion bugs, data timing issues, platform differences and intended changes.
- Run parallel operations. Operate both systems on the same inputs for a defined period, running validations on each cycle. Use watermarks or consistent snapshots to avoid timing noise.
- Validate business outputs. Reconcile key reports, dashboards and extracts with business owners.
- Sign off and cut over. When exit criteria are met, obtain domain sign-offs, switch consumers to the new platform and keep the legacy system read-only for a defined fallback period.
- Keep validations as monitors. After cutover, convert key validations into ongoing data quality checks.
Validating Large Tables Without Breaking the Budget
Row-level comparison of multi-billion-row fact tables can be slow and expensive, especially when data must be read from both platforms. Several techniques keep costs manageable while preserving confidence.
- Hash in place. Compute row or segment hashes inside each platform and compare the hashes rather than moving full rows across the network. Drill down into individual rows only for segments whose hashes differ.
- Partition and parallelize. Split comparisons by date, key range or partition, and run them concurrently within the limits each platform can tolerate.
- Validate incrementally. After an initial full comparison, validate only new and changed partitions on each cycle.
- Sample deliberately. For lower-criticality tables, compare stratified samples that cover key segments, edge dates and known problem areas rather than random rows alone.
- Schedule off-peak. Run heavy validations when the legacy system has spare capacity, since it is often the constrained side.
Beyond Data: Validating Performance, Security and Operations
Matching numbers are necessary but not sufficient. A migration also needs to show that the new platform behaves acceptably in production.
- Performance: compare run times for critical batch jobs and response times for key reports against agreed targets, not only against the legacy system.
- Security: verify that roles, row-level security, masking and auditing reproduce the intended access model, and that no data became more widely visible during migration.
- Operations: confirm that scheduling, alerting, restart behavior and recovery procedures work, and that support teams have run through them.
- Cost: check that actual consumption during parallel runs is in line with the business case, and investigate outliers before cutover.
Reporting Validation to Stakeholders
Validation produces a lot of detail. Stakeholders need a clear view of progress and risk. A good validation report includes:
- A summary of objects validated, passing and failing, by domain and criticality.
- Aggregate parity results for key metrics, with trends over parallel-run cycles.
- Row-level discrepancies for critical tables, with root cause and resolution status.
- A register of known and accepted differences with owner approvals.
- Readiness against exit criteria for each domain.
Datafold’s guide makes a similar recommendation: summaries of aggregate parity, granular discrepancies with root cause analysis, and trends over time. Keeping this report current is one of the most effective ways to build confidence and keep migration momentum.
A Hypothetical Worked Example
A hypothetical insurer is migrating a legacy on-premises warehouse with about 3,000 tables and 1,500 stored procedures to a cloud data platform. Procedures are converted with AI-assisted tooling and reviewed by engineers.
- The team uses query logs to identify about 400 tables and 150 reports used regularly, which become the priority scope. Rarely used objects are reviewed for retirement.
- Baselines for row counts, aggregates and 50 key financial metrics are captured from the legacy system at a fixed snapshot.
- Validations are defined as configuration files in the same repository as the converted code and run automatically after each deployment.
- In the first wave, row-level diffs reveal differences in premium calculations traced to integer division and rounding behavior in converted procedures. The conversion rules are corrected and re-run.
- During a six-week parallel run, critical financial tables must match exactly on every cycle; operational metrics are allowed small documented tolerances. Two intended logic fixes are documented and approved by finance.
- After four consecutive clean weeks, finance and actuarial owners sign off, consumers switch, and the legacy system remains read-only for three months.
The example is illustrative; timelines vary with scope and data quality. The pattern of prioritization, automated validation and owner sign-off is what matters.
Pitfalls to Avoid
- Validating only row counts. Counts can match while values are wrong. Use aggregates and row-level diffs for critical data.
- Late validation. Starting validation after conversion is complete creates a huge backlog. Validate wave by wave.
- Unclear tolerances. Without agreed tolerances, every small difference becomes a debate. Agree them up front.
- Ignoring timing. Comparing systems loaded at different times produces false differences. Use consistent snapshots or watermarks.
- No business ownership. Engineers cannot sign off on financial or regulatory numbers. Involve business owners from the start.
- Throwing validations away. Validation code is a valuable asset for ongoing data quality. Keep it.
Outlook
Validation is following conversion toward automation. Expect migration agents to generate validation suites alongside converted code, diffing tools to integrate more tightly with CI/CD and data platforms, and AI-assisted root cause analysis to speed triage of discrepancies. Human judgment will remain essential for tolerances, intended changes and sign-off, but the mechanical work of comparing billions of rows will increasingly run in the background.
Conclusion
Fast conversion means little if nobody trusts the new numbers. Treat validation as a first-class workstream: capture baselines early, layer schema, aggregate, row-level and business output checks, agree tolerances and owners, automate everything you can, and keep validations as ongoing monitors after cutover. That is how a warehouse migration earns the confidence it needs to switch off the legacy system.
How Hanoi Technology Can Help
Hanoi Technology runs warehouse migrations with automated validation, parallel runs and reconciliation reporting that give business owners the evidence they need to sign off.