Row counts, aggregates and hashes answer three different questions after a migration: whether all rows arrived, whether the business totals are the same, and whether every individual value is identical. Use them in that order and together, because each one catches errors the others miss, and none of them on its own proves that a migrated data warehouse is correct.
We at DWHPro lead Teradata migrations to cloud platforms, and data comparison is where programmes most often overestimate what their evidence shows. Our article on proving a Teradata migration before cutover covers the overall validation levels. Here we focus on the data level itself: what each comparison technique proves, where it misleads, and what a programme lead should ask for.
The three techniques at a glance
| Technique | What it proves | What it misses | Effort |
|---|---|---|---|
| Row counts | The same number of rows exists in source and target | Wrong values, duplicates offset by missing rows, truncated text, shifted dates | Low |
| Aggregates | Business totals and distributions match, overall and per group | Errors that cancel each other out, wrong values in non-numeric columns | Medium |
| Hashes (row-level fingerprints) | Each row’s content matches exactly, once values are normalised the same way | Nothing within the compared columns, but false alarms are common without careful preparation | High |
Row counts: necessary, never sufficient
Counts are cheap and fast, so run them on every table after every load. A count difference is an immediate stop signal. But equal counts are weak evidence. A load can drop a hundred rows and duplicate a hundred others. A date conversion can shift every transaction into the wrong month without changing a single count.
Count the business key as well as the rows. Snowflake standard tables do not enforce primary key or unique constraints, BigQuery keys are declared as not enforced, and Databricks treats primary keys as informational. A Teradata table that could never hold duplicates may hold them after migration. Comparing the number of distinct keys with the number of rows exposes this quickly.
In our Swiss retail migration from Teradata to Snowflake, this was the explicit principle: matching row counts alone would not establish that operational or financial totals were correct, so counts were combined with aggregates and business-logic checks.
Aggregates: the numbers the business actually reads
Aggregates bring validation close to what decision makers care about. Choose measures that matter to the business: revenue, quantities, stock levels, balances. Then compare them not only as one grand total but by the dimensions people report on, such as day, store, product group or legal entity. A grand total can match while two regions are swapped. A breakdown by month and region will not.
Include simple profiles for non-numeric data too: the number of distinct values, minimum and maximum dates, the count of empty values. These catch truncated codes and default dates that sums never see.
Expect small differences in calculated amounts and decide upfront how to handle them. Teradata and Snowflake can round intermediate results differently, as we explain in rounding differences between Teradata and Snowflake. Agree a tolerance per measure with the business owner before the first comparison, not after.
Hashes: precise, but only after normalisation
A hash comparison reduces each row to a fingerprint and compares fingerprints between source and target. It is the only practical way to prove value-by-value equality on large tables, and it points directly to the rows that differ.
The catch is that two platforms rarely produce the same fingerprint for the same business data out of the box. Before a hash means anything, both sides must present values identically. The typical sources of false differences are:
- Data types: decimals with different scale, timestamps with different precision, dates rendered in different formats.
- Empty values: NULL and an empty string may be treated differently, and must be mapped to the same placeholder on both sides.
- Whitespace and case: trailing spaces and upper or lower case can differ even when the business meaning is identical.
- Column order and character encoding: values must be combined in the same order and the same encoding.
- Hash function availability: not every function exists on every platform. Google’s open-source Data Validation Tool, for example, notes that SHA-256 is not available on Teradata without deploying an additional function.
Normalisation rules are business decisions in disguise. Ignoring trailing spaces or case is fine for most text, but not for a code where case carries meaning. Document every rule and have the data owner accept it.
Because hashing is the most expensive technique, reserve full row-level comparison for critical tables and use samples or recent partitions elsewhere.
A sensible sequence
- Counts on every table, every load, including distinct-key counts.
- Aggregates on business measures, broken down by the main reporting dimensions.
- Row-level hashes on critical tables, and wherever counts or aggregates point to a problem.
- Business-result checks on the reports and extracts people actually use.
Each step narrows the search. When an aggregate fails for one store and one month, a row-level comparison of that slice usually finds the cause within hours instead of days.
What to insist on
- A frozen reference point, so source and target are compared on the same data.
- Agreed tolerances and normalisation rules, signed off before testing starts.
- Automated, repeatable comparisons with stored results.
- Every difference traced to a root cause before cutover.
How DWHPro helps
We define which comparisons each table needs, set up automated reconciliation and lead the investigation of differences until they are explained. For Snowflake targets, see our Teradata to Snowflake migration service, which starts with a fixed-price assessment or a four-day diagnostic.
Common questions
Are matching row counts enough to sign off a table?
Only for low-risk supporting tables. For anything that feeds reporting, counts must be combined with business aggregates, and critical tables need row-level comparison.
Can we compare hashes calculated on Teradata and on the target directly?
Only if both sides normalise values in exactly the same way and use the same hash function. Plan and test this on a pilot table before relying on it.
How large a tolerance is acceptable for aggregates?
That is a business decision per measure. Keys and counts should match exactly; calculated amounts may allow an agreed rounding tolerance that the business owner signs off.
Planning a Teradata-to-Snowflake migration?
Review the assessment, conversion, reconciliation and cutover work needed for a controlled transition.