Warehouse migrations are validated with a row count more often than anyone likes to admit. Source has 41,882,113 rows, target has 41,882,113 rows, the ticket closes. It is an appealing check because it is cheap, produces a clean binary answer, and is almost always green.
It is also close to meaningless on its own. A row count confirms that the right number of rows arrived. It says nothing about whether they hold the right values, whether a decimal lost precision, whether a timezone shifted, or whether two different source rows collapsed into one and a third was written twice to compensate.
Key Takeaways
- Matching row counts are necessary and nowhere near sufficient, they survive precision loss, timezone drift, silent truncation and compensating duplicates.
- Type coercion between the source engine and Snowflake is where most silent corruption enters, and it never raises an error.
- Test the second run, not just the first. Migrations are rehearsed as one-way events and then operated as incremental pipelines.
- Reconcile at column level with checksums on the columns that matter, not on every column, which is unaffordable and unnecessary.
- Rehearse rollback at production volume. A rollback that has never been executed is a plan, not a capability.
Where the corruption actually enters
- Numeric precision. A source
DECIMAL(18,4)landing in a target typed to two decimal places rounds every row. Sums stay plausible, which is what makes it survive review, and only reconcile to the penny against the source once someone checks. - Timestamps and timezones. A naive timestamp interpreted as UTC on one side and local on the other shifts the entire dataset by hours. Daily aggregates move rows across day boundaries, and month-end is where it surfaces.
- Character truncation. A wider source column into a narrower target clips silently under many load paths. The rows arrive, the count matches, the tails are gone.
- Collation and case. Join keys that matched under a case-insensitive collation stop matching under a case-sensitive one. Orphans appear in tables that had none.
- NULL versus empty string. They are distinct in one engine and interchangeable in another. Downstream
COALESCElogic quietly changes meaning.
What a real reconciliation looks like
The goal is evidence a data owner can read, not a green tick. That means a small set of checks applied deliberately rather than an exhaustive comparison nobody can run inside a maintenance window.
| Check | Catches | Cost |
|---|---|---|
| Row count by partition | Whole-partition load failures | Trivial |
| Column checksums on business-critical columns | Precision loss, truncation, coercion | Low |
| Aggregate comparison (sum, min, max, distinct) | Rounding, timezone shift, collation changes | Low |
| Full row hash on a sampled slice | Anything the above misses, cheaply | Moderate |
| Referential integrity across migrated tables | Orphans created by key coercion | Moderate |
| Business rule assertions | Logic that was reimplemented, not moved | Highest value |
The last row is the one teams skip and the one that pays. Migrations rarely move transformation logic unchanged, it gets rewritten from SQL into dbt models, or from stored procedures into pipeline code. Reconciling the output of a rewritten transformation against the specification it was meant to implement, rather than against its own previous output, is the only way to catch a rule that was reimplemented slightly wrong.
The second run is the one that breaks
A migration gets rehearsed as a single event: extract, load, verify, cut over. Then it becomes an incremental pipeline, and the failure modes change completely. Late-arriving records land after the window they belong to. A replay reprocesses a day that was already loaded. A slowly changing dimension that behaved correctly on a full load starts overwriting history on the second pass, quietly converting a type-2 dimension into a type-1 one and destroying the ability to answer any question about the past.
Test the second run, the replay, and the late arrival, before cutover rather than after. Idempotency is not a property anyone should discover in production.
Rollback is not a paragraph in a runbook
Every migration plan contains a rollback section. Very few have been executed at production volume against production-shaped data. A rollback that has never run is an assumption, and the moment it is needed is the worst possible moment to discover how long it takes or that it depends on a snapshot nobody retained.
Rehearse it. Time it. Write down what it costs. That number is what makes a cutover decision a decision rather than a hope.
A workable order of operations
That is the shape of our ETL and data warehouse Testing engagements, and the reconciliation suite stays in your repository afterwards. If a migration is coming, a scoped assessment on one pipeline will tell you more about your data quality than a month of planning.
- Read the mapping specification against both schemas and list every type change, not just the ones flagged as risky.
- Build reconciliation as re-runnable code in the repository, so it survives the migration and becomes the regression suite afterwards.
- Validate transformations against the specification, never against the pipeline's own output.
- Exercise incremental behaviour: replay, late arrival, dimension history.
- Rehearse rollback at volume, and record the elapsed time.
Want us to run this on your product?
A free 30-minute assessment. We'll tell you what's working, what's costing you time, and where to start. Findings delivered within days.
Get a Free QA Assessment