Migration Validation Guide

Prev Next

Migration validation proves that a new platform holds the same data, and produces the same numbers, as the platform it replaces. This guide covers the method for any move: warehouses and databases into Snowflake or Databricks, ETL and ELT logic rebuilt in a new tool, and reports moved between BI tools. It explains the checks and the layers behind them, the acceptance rules to settle first, the traps each kind of migration hits, checks you can run today, and the test suite a sign-off needs.

What migration validation proves

Validation answers four questions, in order. Each one has to pass before the next one means anything.

  1. Can the two sides be compared? Columns line up, types map as agreed, and a key exists that is unique and never null on both sides.
  2. Is every row there? No load, partition, month, or key is missing from the new side.
  3. Is anything extra? No duplicates, repeated loads, or rows that should have been filtered out.
  4. Did any values change? Every compared value matches, or differs only within a rule the data owner agreed to.

When all four pass on the final load, the data owners have what they need to sign off. After cutover the same checks keep running on the new platform as regression tests, so a later change that breaks parity shows up on the next run instead of in a report.

The layers of a comparison

The four questions get answered across hundreds of tables in layers, from cheap checks that run everywhere to row-by-row comparison where it matters most.

Layer What it compares What it catches
Schema Columns, data types, nullability, precision and scale Dropped columns, narrowed types, lost decimal places
Row counts Every table, plus counts per segment (load date, region, partition, source system) Missing loads, duplicate loads, a missing month
Column profiles and aggregates Sum, min, max, distinct count, and null count per column Rounding, truncation, shifted dates, NULLs that became blanks
Row level by key Every compared column, matched on a key The exact records that differ, with both versions side by side
History Effective dates, current-row flags, versions per key History rows opened or closed on the wrong dates, lost versions
Pipeline output Old and new pipeline output over a parallel run Logic that changed in the rebuild, late rows skipped by an incremental window
Report numbers The same measure at the same filters in the old and new report Grain changes, one-sided filters, blank handling, totals

Warehouse and database moves use the first five layers. ETL and ELT rebuilds add the parallel-run output layer. BI moves compare report numbers and reconcile both reports to the warehouse.

Why row counts are not enough

A row count proves that the right number of rows arrived. It says nothing about whether they arrived intact. A description truncated at 50 characters, a timestamp shifted five hours by a time zone setting, a balance rounded to two places: none of them change a count, and all of them change a report. Duplicates can also hide missing rows when the two happen to cancel out.

Counts still earn their place. They are the cheapest signal, so they run on every table on every cycle. Profiles and aggregates come next and catch most value-level problems at low cost. Row-level comparison by key is the most expensive layer and the most precise, so it runs on the tables your owners name as critical, or on all of them when the platforms can carry the load.

Acceptance rules

Some differences are intended. A column was widened on purpose, a calculation was corrected, timestamps gained precision. Keys, excluded columns, and accepted differences are decisions for the data owner, so each data domain gets acceptance rules before the first comparison runs. Every rule takes one of four forms:

  • Exact match for keys, amounts, and anything an auditor will ask about.
  • A tolerance for computed values, such as agreed decimal places or a percentage.
  • A normalization, such as trimming text or comparing timestamps to the second, when the platforms store the same value differently.
  • An accepted difference, written down with the owner who accepted it and the reason.

These are the decisions that come up in almost every migration, with the starting point we use:

Decision Options Usual starting point
Comparison key Business key, surrogate key, or a composite Business key. Rebuilt tables generate their own surrogate keys, so surrogates are checked through the business key instead of matched on
Case in keys and text Exact, case-folded, or chosen per column Exact, with case-folded matches reported separately for the owner
Trailing blanks Trim both sides, trim one side, or treat padding as a defect Trim both sides on former CHAR columns, exact on VARCHAR
Decimal tolerance Exact, fixed decimal places, or a percentage Exact on stored values, 0.01 on derived money totals
Timestamps Precision (microseconds, milliseconds, seconds) and zone (as stored, or normalized to UTC) The legacy column's precision, compared as stored unless the column carried a zone
NULL and empty string Kept distinct, or treated as equal Kept distinct
Known source duplicates A defect, carried over, or de-duplicated under a signed rule Carried over unless the owner signs a de-duplication rule
Rows excluded on purpose Listed by filter or by key A written filter per table, approved by the domain owner
History dates Same versions only, or same versions and dates Same versions and dates, under a written convention for end dates
Which side breaks a tie Legacy, new, or the source system The source system for pipelines, the warehouse for reports
Leaving a parallel run A count of consecutive clean days Five business days in a row with no unexplained differences

With the rules in place, results show only what still needs a decision.

Evidence to keep for sign-off

A sign-off needs more than a passing run. Keep:

  • Results by data domain and table for every validation cycle, not only the last one.
  • Every accepted difference, with the owner who accepted it and why.
  • The full run history, so anyone can see when a difference appeared and when it was fixed.
  • A sign-off page per data owner, listing the scope they are signing for.

Validatar keeps the execution history of every test, so this record builds as the cycles run.

How Validatar compares two platforms

Each side of a Validatar test runs on its own data source. One side can query SQL Server or Teradata while the other queries Snowflake or Databricks, and Validatar compares the two result sets. The data does not have to be copied to one place first.

Template tests cover every table without writing tests by hand. You mark tables with the Reconciliation Catalog Custom Fields, which set each column's role (key, value, or ignore) and name the target table, and each template materializes one child test per marked table. Bringing another table into scope means marking it.

The Key-Based Reconciliation Test Pack supplies the core templates: column alignment, key uniqueness and null checks, coverage summaries, row counts per key and per segment, totals per key, full row compares, a normalized compare, and an incremental compare for daily parallel runs. Reconciling Source and Target with Key-Based Templates walks through setting them up.

Note: The Key-Based Reconciliation templates are proven Snowflake to Snowflake and written for Snowflake, SQL Server, PostgreSQL, and Oracle. Variants for other platforms are adapted to your systems. Row Fingerprint per Key uses Snowflake's HASH function, so it is not used for a comparison that crosses platforms.

Connections. Snowflake, SQL Server, and PostgreSQL are built-in data sources. Databricks connects through its marketplace data source template with an OAuth machine-to-machine service principal. Teradata, Netezza, Db2, and Hive or Impala connect through ODBC; confirm the connection on your own system before you plan validation cycles. For BI, Validatar reads Power BI semantic models directly with DAX and reads Tableau view data through the Tableau REST API, then compares both with the warehouse.

Where comparisons run. Validatar reaches systems behind your firewall through the Data Agent, which runs inside your network, or runs entirely on your infrastructure as Validatar Server. Where a Snowflake or Databricks data processing engine is set up, the comparison work runs inside your own account.

Common traps by migration type

Each trap below is something a row count misses. The "How to test it" line names the check that catches it.

Warehouses and databases

Duplicates a constraint used to block. A Teradata SET table discards exact duplicate rows on insert without an error, so a lot of legacy loading code never removed duplicates itself. Hive never prevented them at all, and many Hive tables already hold full-row duplicates. Snowflake and Databricks do not enforce PRIMARY KEY or UNIQUE constraints, so the same loads keep every repeat. The row count comes out higher, or matches by accident because a different defect dropped the same number of rows elsewhere.

  • How to test it: count full-row duplicates on every former SET table, measure duplicates on the legacy side before the first cycle, and check key uniqueness on both sides.

Text that matched without case or with trailing blanks. Teradata character columns default to NOT CASESPECIFIC, and SQL Server's usual collation is case-insensitive, so 'ab12' and 'AB12' join as one key. Teradata also pads CHAR(n) values and ignores trailing blanks when it compares them. Snowflake and Databricks compare case and trailing spaces. Joins lose rows, and a GROUP BY that produced one group per customer can produce two.

  • How to test it: match keys exactly and after UPPER(RTRIM()), and report keys that match only when folded as their own category for the owner to decide.

Arithmetic that changed meaning. Dividing two integers truncates in Teradata and SQL Server (7/2 is 3) and returns a decimal in Snowflake and Databricks (3.5). Rounding can drift too: Teradata's rounding of DECIMAL results depends on a system setting, while Snowflake's ROUND rounds half away from zero. One row never shows the difference; a total over millions of rounded amounts can be off by cents.

  • How to test it: compare derived columns in converted views as well as base tables, and use Totals per Key with a tolerance written into the acceptance rules.

Values the old engine read as NULL. Hive applies the schema when it reads a file, so a value that does not fit the declared type comes back as NULL. Databricks SQL with ANSI mode raises an error instead, and Auto Loader with a rescued data column keeps the original value in _rescued_data. Hive text tables also write NULL as \N, while Spark's CSV reader treats an empty field as NULL. A column can have a 0.2% NULL rate on one side and 0% on the other while both look healthy.

  • How to test it: compare the NULL rate of every column side by side, and count literal \N, empty strings, and NULLs per text column.

Dates and timestamps that moved. Teradata stores DATE as an integer, and old SQL sometimes compares dates with numbers like 1240101; it can also export dates in a two-digit-year display format. Parquet timestamps written by Impala as INT96 read back shifted by the cluster's UTC offset unless Spark is told to convert them. Dates before 1582-10-15, including sentinels like 0001-01-01, shift under Spark 3's calendar unless the rebase setting is chosen deliberately. Time zone mapping (TIMESTAMP_NTZ, _TZ, or _LTZ in Snowflake) moves values by whole hours.

  • How to test it: compare MIN and MAX for every date column, count dates outside 1900 to 2100, and compare the time-of-day distribution of every timestamp column. The same shape moved by a fixed number of hours is a zone problem, not bad data.

Partitions. Hive keeps partition values in directory names, and Spark can infer them as integers, so region_code=02134 loses its leading zero. Hive jobs that ran INSERT OVERWRITE with dynamic partitions replaced only the partitions they wrote; Spark's default static mode replaces the whole table. The first cycle looks perfect, and yesterday's partitions disappear the next day. Hive ACID tables are a third case: read their files directly and you get every updated version and the rows Hive had deleted.

  • How to test it: compare row counts per partition across cycles, not only for the latest day, and compare totals for former ACID tables against Hive's own SELECT COUNT(*).

The surviving row changed. Teradata sorts NULLs first in ascending order, and Snowflake sorts them last by default. A de-duplication that keeps the first row of a window (ROW_NUMBER() ... = 1) keeps a different row when the ORDER BY column can be NULL. Row counts stay identical and the surviving values change.

  • How to test it: row-level comparison by key on every output built with ROW_NUMBER, RANK, or QUALIFY.

Characters lost in the export. Text exported through a session character set narrower than the data (Teradata LATIN through ASCII, a 1252 code page) loses accented names, the euro sign, and curly quotes. The counts match and the text is different.

  • How to test it: compare LENGTH and a count of non-ASCII characters per text column, and count '?' and U+FFFD on the new side.

ETL and ELT logic moving between tools

This covers ETL tools such as SSIS, Informatica PowerCenter, IBM DataStage, Talend, and Oracle Data Integrator, plus stored procedures, SQL scripts, and scheduled jobs, rebuilt as ELT in dbt, Coalesce, Databricks jobs and notebooks, or Snowflake tasks and procedures. Validatar compares the tables, files, and extracts that the old and new logic produce, so no connector to either tool is needed. The traps come from behavior the old tool applied outside the database, or from SQL defaults that changed with the platform.

Lookups returned one row; joins return every match. An SSIS Lookup returns the first match it finds, and an Informatica Lookup with a first-value or last-value policy returns one row. A SQL join in the rebuilt model returns all of them, so every duplicate key in the reference table multiplies output rows. An SSIS full-cache Lookup also compares keys in memory, case-sensitively and with trailing spaces significant, whatever the database collation.

  • How to test it: compare rows per key on every output fed by a lookup, check reference tables for duplicate keys before the first run, and match keys both exactly and folded.

Rows that went to a reject path. ETL tools redirect failing rows to an error output, a reject link, or a bad file. An ELT model has no reject path: it loads the row or the run fails. A rebuild that switched to TRY_CAST loads the row with a NULL where the old job rejected it.

  • How to test it: reconcile counts per run. On the legacy side, rows read should equal rows loaded plus rows rejected; compare that with rows loaded on the new side and rows carrying failed conversions.

Silent truncation and code pages. ETL data flows carry fixed column lengths and encodings. When truncation was set to ignore, the old job cut strings to the destination length; the new model writes the full value. Non-Unicode types with a code page dropped or replaced characters that the UTF-8 target keeps.

  • How to test it: compare the longest value per text column, and compare rows with non-ASCII characters by key.

SQL defaults that changed with the platform. Logic lifted from a source query, an Execute SQL task, or a stored procedure inherits the old platform's rules. T-SQL CONCAT() treats NULL as an empty string; Snowflake and Databricks return NULL. Oracle treats an empty string as NULL. Case-insensitive collations made where status = 'open' match 'Open'. Integer division truncates on SQL Server. SSIS DT_DBTIMESTAMP holds three fractional digits, so timestamps the new model reads directly keep digits the old job dropped.

  • How to test it: compare NULL counts on derived string columns, totals per key on derived numbers, row counts per filtered segment, and timestamps at an agreed precision.

History tables. An SCD step in an ETL tool sorts columns into ones it overwrites (Type 1), ones that open a new version (Type 2), and ones it rejects. A dbt snapshot records a new version when any checked column changes, so columns the old job overwrote now create history rows. Date conventions differ: in a snapshot the old row's dbt_valid_to equals the new row's dbt_valid_from, while many legacy jobs end a row one day before the next starts. Hard deletes are ignored unless the snapshot is configured to close them.

  • How to test it: compare versions per key, check for exactly one current row per key with no gaps or overlaps, and compare how deleted keys and inferred members are handled.

Incremental windows and late rows. Legacy jobs often drive incremental loads from a high-water mark in a variable or control table. A dbt incremental model typically filters on max(updated_at) from the existing table, so a row that arrives late is skipped unless the model has a lookback window.

  • How to test it: compare incrementally over a window wider than either pipeline's load window, so late rows appear as differences instead of hiding outside the comparison.

The old job was the one that was wrong. Some legacy patterns never produced a deterministic result, such as a sort step that removes duplicates and keeps an arbitrary row, or a merge join on inputs sorted under a different collation than the tool assumed. A difference does not prove the new model is wrong.

  • How to test it: for each category of difference, trace a sample of keys back to the source system and record which side agrees with it.

The core of an ETL or ELT validation is the daily parallel run: both pipelines run on the same source state, and an incremental compare runs after both finish each day. The run ends when the agreed number of consecutive clean days is reached.

BI and reporting

A report rebuilt in a new tool can look identical and show different numbers, because BI tools disagree about filters, blanks, totals, and calendars. Screenshots side by side catch the obvious misses on a handful of dashboards and almost nothing on two hundred. The examples below are Tableau and Power BI; the same questions apply to any pair. Legacy tools without an API, such as Cognos, BusinessObjects, or MicroStrategy, are compared through their exported report output.

Filter context. A Tableau FIXED level-of-detail expression ignores ordinary dimension filters but respects context filters. In DAX, the measure's author decides which filters to remove. A measure that matches with no slicers set can drift once someone filters by a column the translation did not consider.

  • How to test it: compare each measure unfiltered and under every filter the report exposes, one at a time.

Blanks. Tableau COUNTD ignores NULL; DAX DISTINCTCOUNT counts BLANK as a value, so a column with a blank member gives 12 in one tool and 13 in the other. DAX also treats BLANK as zero in addition, so sales + adjustments shows a number where Tableau showed nothing.

  • How to test it: compare every distinct count, and every measure built from more than one input on rows where an input is missing.

Totals and table calculations. Running totals, percent of total, and rank are computed on the view in Tableau, in the direction set by Compute Using, and become DAX measures that must reproduce the same partitioning. A Tableau sheet set to total using SUM adds displayed values, which gives a different total for a ratio or a distinct count. Tableau blends aggregate the secondary source before joining; a Power BI relationship joins rows first and can double count.

  • How to test it: compare at the visual's grain, then subtotals and grand totals separately, and check the first and last rows of each partition.

Calendars and refresh timing. Fiscal year starts and week numbering live in different places in each tool; DAX WEEKNUM defaults to Sunday weeks with week 1 containing January 1, and ISO weeks need return type 21. Extract filters, incremental refresh, and refreshes at different times also make two correct reports disagree.

  • How to test it: compare by fiscal period and by week, including the weeks around January 1, and record both refresh times with every comparison.

Labels and row-level security. Fact rows with no matching dimension row show as "(Blank)" in Power BI and "Null" in Tableau, which creates false mismatches when labels are compared. Security rules are written separately in each tool and drift.

  • How to test it: normalize the two labels before comparing, and for a set of named users compare the members and totals each one sees, including a user who should see nothing.

Whatever the tools, reconcile both reports to the warehouse. When the old and new report disagree, the warehouse shows which one is right.

Checks you can run today

These checks need no Validatar setup. Run each on both sides and compare the results. Adjust table names, column names, and minor syntax for your platform.

1. Rows per segment. Run on both sides and diff the outputs. A missing or short segment points at a lost load, a filter, or an overwritten partition.

SELECT load_date, COUNT(*) AS row_count
FROM sales.orders
GROUP BY load_date
ORDER BY load_date;

2. A column profile. The cheapest value-level comparison. Differences in nulls, distinct count, or totals narrow the search before any row-level work.

SELECT COUNT(*)                     AS row_count,
       COUNT(customer_code)         AS non_null,
       COUNT(DISTINCT customer_code) AS distinct_values,
       MIN(order_date)              AS earliest,
       MAX(order_date)              AS latest,
       SUM(order_amount)            AS total_amount
FROM sales.orders;

3. Duplicate keys. Run on both sides. Rows here on the new side and not the old usually mean a constraint, a SET table, or a lookup used to prevent them.

SELECT order_id, COUNT(*) AS copies
FROM sales.orders
GROUP BY order_id
HAVING COUNT(*) > 1;

4. Keys that differ only by case or trailing blanks. Run on the new side. A non-zero result counts values the old platform treated as one key and the new one treats as several. On SQL Server, count distinct CAST(customer_code AS varbinary(200)) instead, because a case-insensitive collation hides the variants.

SELECT COUNT(*) AS colliding_keys
FROM (
  SELECT UPPER(RTRIM(customer_code)) AS folded
  FROM sales.customer
  GROUP BY UPPER(RTRIM(customer_code))
  HAVING COUNT(DISTINCT customer_code) > 1
) k;

5. NULLs, empty strings, and markers. The three counts should agree across platforms. A literal \N on the new side is almost always a load defect. The '\\N' literal is written for Snowflake and Databricks, where a backslash escapes; on SQL Server, Teradata, Oracle, and PostgreSQL, write '\N'.

SELECT SUM(CASE WHEN customer_name IS NULL THEN 1 ELSE 0 END) AS nulls,
       SUM(CASE WHEN customer_name = ''    THEN 1 ELSE 0 END) AS empty_strings,
       SUM(CASE WHEN customer_name = '\\N' THEN 1 ELSE 0 END) AS literal_backslash_n
FROM sales.customer;

6. Dates outside a sane range. A non-zero result usually means a two-digit year, a calendar rebase, or a sentinel nobody mapped.

SELECT SUM(CASE WHEN order_date < DATE '1900-01-01'
                  OR order_date > DATE '2100-12-31' THEN 1 ELSE 0 END) AS out_of_range
FROM sales.orders;

7. Former SET tables (Teradata only). Every table this returns needs a full-row duplicate check on the new side. CheckOpt records whether a table allows duplicate rows, and 'N' usually means a SET table; confirm any table you are unsure of with SHOW TABLE.

SELECT DatabaseName, TableName
FROM DBC.TablesV
WHERE TableKind = 'T'
  AND CheckOpt = 'N'
ORDER BY 1, 2;

8. Distinct counts with and without blanks (Power BI). Two different numbers mean every measure built on DISTINCTCOUNT over that column will be one higher than Tableau's COUNTD.

EVALUATE
ROW (
    "With blank", DISTINCTCOUNT ( 'Product'[Category] ),
    "Without blank", DISTINCTCOUNTNOBLANK ( 'Product'[Category] )
)

The test suite

A sign-off suite is built from Validatar template tests, so nobody writes a test per table. Each in-scope table, pipeline output, or report is marked in the catalog (or listed in a mapping workbook for reports), and every template selects what is marked. Adding something to scope is a catalog edit.

Folder What it proves Built from
1 Structure Columns align, types map as agreed, and the business key is unique and never null on the legacy side Reconciliation - Columns Align; Reconciliation Key Finder; a type-mapping template
2 Completeness Total rows and distinct keys agree; counts per segment agree; no key is missing, extra, repeated, or null on either side Reconciliation - Key Coverage Summary; Row Count per Segment; Row Count per Key; Key Is Unique on Both Sides; Key Has No Nulls on Both Sides
3 Values Column profiles agree on every table; totals per key agree to agreed decimals; critical tables match cell by cell, raw and normalized, with load timestamps and batch ids left out Reconciliation - Totals per Key; Full Row Compare; Normalized Full Row Compare; Full Row Compare, Named Columns Excluded; a profile template
4 Migration-specific The traps that apply to your platforms, such as former SET table duplicates, case-folded key matches, padding-only differences, text that survived the export, date ranges, and lookup multiplicity New template tests, selected by catalog flags
5 Parallel run and history Each day's new and changed rows match over a wide window; history tables carry the same versions and dates; deletes are handled the same way Reconciliation - Incremental Full Row Compare; history templates
6 Report parity Each measure matches under each exposed filter, at the visual's grain and in totals; named users see the same rows; both reports reconcile to the warehouse Power BI Sample Tests for the Power BI side; comparison tests built from the mapping workbook
7 Cutover run The whole suite on the final load: the result your owners sign Folders 1 to 6

Warehouse moves use folders 1 to 4 and 7. ETL and ELT rebuilds add folder 5. BI moves use folders 6 and 7, with the warehouse tables behind the reports in folders 2 and 3.

Heavy queries against a busy legacy system need care. The suite pushes aggregation down first, so counts and totals per segment run inside the legacy platform and return a handful of rows. Rows are pulled only for segments that disagree, and large pulls are scheduled with your DBA.

Two ways to run it

Your team runs it. Everything in this guide runs in Validatar. Settle the acceptance rules above, then set up the templates with Reconciling Source and Target with Key-Based Templates.

Our team runs it. In the Migration Validation Program, Validatar's team scopes, builds, and runs the validation, then hands you the evidence to sign off. It is a fixed-scope project in your own private Validatar instance. After each migration cycle you get a parity report and a difference log sorted by cause, with sample rows, for your migration team or SI to fix. The program ends with a cutover run and a Sign-off Evidence Pack: results by domain and table, every accepted difference with who accepted it and why, the full run history, and a sign-off page for each owner.

  • Book a free Mini Validation. We connect to your legacy and new platforms, compare up to 10 of your tables (or 10 pipeline outputs, or 5 reports), and send written findings and a scope within 10 business days after access works.
  • Get a fixed price. The Mini Validation scopes the rest of the work, and your fixed price comes from that scope.

To book a Mini Validation, contact us.

Related articles