This blog explains how Datagaps validates data movement between on-premises databases and Snowflake, and between Snowflake instances, using DataFlow. A real client case study covers migrating 400 SQL Server tables (500M+ rows each) to Snowflake, surfacing issues like numeric precision differences, null inconsistencies, and truncation. It also covers transitioning from ETL to ELT, BI Validator-driven regression testing, and AWS EMR cluster auto-scaling to over 30 nodes—achieving roughly a 50% reduction in test cycle time.
Key Takeaways
- Bulk migration testing catches issues at scale — using the Data Migration Wizard, a client generated comparison tests for 400 SQL Server tables (500M+ rows each) in just a few hours, surfacing numeric precision, null value, and truncation issues.
- DataFlow supports the ETL-to-ELT transition — as the client shifted their pipeline architecture toward Snowflake, DataFlow validated accuracy across iterations until source and target data were fully in sync.
- BI Validator extends testing to the reporting layer — clients use it for regression, performance, and stress testing to compare BI dashboards and reports between the old warehouse and new Snowflake implementation.
- AWS EMR auto-scaling improves testing efficiency — DataOps Suite’s cluster integration scaled EMR nodes up to 30+ during comparisons of 500M-record tables, then scaled down after, cutting testing costs while achieving an estimated 50% reduction in test cycle time.
Data validation in snowflake
Validating data during a migration to Snowflake means confirming that records, data types, and precision all carry over correctly from the source system — and this article discusses why and how to do that, diving into the challenges of bulk Data Migration to Snowflake.
Why and How?
This is where Datagaps come in to play.
We provide the ability to run data reconciliationon millions & even billions of records between source and target structures of different types, such as your on-premises database and Snowflake.
Benefits of using Datagaps to test data movement into Snowflake
We recently sat down with one of our clients that uses our DataFlow product for testing the data migration from on-Prem SQL Server to Snowflake in the cloud running in AWS.
Implementation
They started the initial migration by performing bulk loads from 400 SQL Server tables to Snowflake with minimal transformations. This was stage 0, where they could perform source and target data comparison for over 500 million rows of data per table. Making use of the Data Migration wizard, the client was able to generate comparison tests for 400 tables in just a few hours. Even though there were few changes at this stage, they still encountered errors that were surfaced by our
DataFlow product.
Examples: Issues include numeric precision differences, null value inconsistencies, truncation issues and character interpolations.
Next, they began to perform incremental new data migrations where they continued to find similar issues that had to be corrected. As this continued, they wanted to transition from this incremental new data migration from SQL Server to loading the new data directly into Snowflake to reap the benefits stated earlier. To accomplish this, their initial ETL processes needed to be migrated to an ELT process aimed at Snowflake.
DataFlow was used once again to check the accuracy between the two systems once the new processes were in place. The validations exposed issues in the new ELT process through several iterations until the transformation were in sync. After a short period of testing, they could cut over to the new system and deprecate the SQL server environment. Now DataFlow continued to validate the incremental data as it was moved into Snowflake, finding issues earlier in the cycle where they are less costly to fix in time and lost credibility.
How it makes a difference?
Reduction in Testing
Time by
Improved Testing ROI by
Amount of data tested increased from manual testing of 10,000 sample records to complete testing of 500 M.
| Facts | Detail |
|---|---|
| Migration path | On-premise SQL Server → Snowflake (AWS) |
| Initial bulk load scope | 400 SQL Server tables, over 500 million rows per table compared |
| Test case generation time | Comparison tests for 400 tables generated in a few hours via the Data Migration wizard |
| Compute scaling | AWS EMR cluster auto-scaled up to 30 nodes based on data volume, then scaled back down |
| Manual vs. automated testing coverage | From manual testing of 10,000 sample records to complete testing of 500 million |
Conclusion
In conclusion, the goals of the migration project of agility, cost savings and performance improvements were achieved.
They also realized these benefits months earlier as a result of the improvement in the migration process due to the impact of the DataFlow products contribution in an estimated 50% test cycle reduction.
One of our clients reports comparing a file against a Snowflake instance with
FAQs: Snowflake Data Migration Validation
1) What kind of data validation is needed when migrating to Snowflake?
Snowflake migrations require validating data transferred from on-premises databases as well as comparisons between Snowflake environments. Validation helps identify issues such as numeric precision differences, null value mismatches, data truncation, and other inconsistencies introduced during migration.
2) How does the Data Migration Wizard help with large-scale migrations?
The Data Migration Wizard automatically generates comparison tests for large numbers of tables, significantly reducing manual effort. For example, it has been used to validate hundreds of SQL Server tables containing hundreds of millions of rows, enabling organizations to quickly identify migration discrepancies at scale.
3) How does BI Validator support Snowflake migrations beyond the database layer?
BI Validator extends migration validation to reports and dashboards by performing automated regression testing, performance testing, and stress testing. This helps ensure reporting outputs remain accurate and consistent after migrating from a legacy data warehouse to Snowflake.
4) How does AWS EMR cluster integration improve testing performance during migration?
DataOps Suite can automatically scale AWS EMR clusters to support resource-intensive comparisons of very large datasets and then scale them down when processing is complete. This approach improves performance, optimizes infrastructure costs, and helps reduce overall migration testing time.
Rajesh Kumar A
Digital Marketing Manager, Datagaps
Digital Marketing Manager at Datagaps. Drives data-driven growth through content, performance campaigns, and marketing technology.
S P S Murthy Akella
Director, Technology Strategy, Datagaps
Director of Technology Strategy at Datagaps. Business solutions architect and Certified Scrum Master in data engineering, responsible AI, and ML across BFSI, telecom, aviation, and energy.
