Skip to main content

Validate a migration

A converted pipeline that runs is not yet a migrated pipeline. Before you switch a job over, run the original and the Hop version on the same input and compare what they write. This guide does that with Apache Hop's own transforms, so it works for any source tool: Talend, Informatica or SSIS.

You need:

  • the original job, and a way to run it once more (in Talend Studio, on the Informatica server, with SSIS, or from its last production output);
  • the converted Hop pipeline, with its connections and files pointing at a test target;
  • the same input data for both runs.

1. Fix the input​

Run both versions against a frozen copy of the input: a database snapshot, or a copy of the input files. Comparing two runs on data that changed in between proves nothing.

Take out what changes from run to run, too. A job that uses today's date, a sequence or a generated id writes something else every time: give it the date as a variable (an "as of" date) instead, and leave generated ids out of the comparison.

2. Keep the original's output as a reference​

Run the original once and keep what it wrote: the files, and the tables it filled, exported the way step 4 reads them. That is your reference: you compare every later Hop run with it, without running the original again.

Record what the reference was made from: a checksum of every job definition and input file, and the exit code of every job. When a job or an input changes, the checksums show that the reference is out of date, so you never compare against a stale one by accident.

3. Run the Hop version into its own target​

Point the Hop pipeline at target B: another schema, table or folder than the original's. For a database target, a Hop environment per side is the simplest way to switch connection details without editing the pipeline.

4. Compare in Hop​

Create a comparison pipeline:

  1. Read the reference and target B, each with the transform that matches the target (Table input, Text file input, …).
  2. Sort both on the business key with Sort rows, unless the reads already order by it.
  3. Join them with Merge rows (diff), the reference as the reference stream, B as the compare stream, the business key as the key fields and every other column as the values to compare.
  4. Filter rows on the flag field: keep everything that is not identical.
  5. Write those rows to a file or table.

An empty result means the two runs wrote the same rows. Every row in it is a difference: new and deleted rows point at filters and joins, changed rows at expressions and data types.

When a file has no business key, compare it line by line: read each line as one field, sort both sides, and use the whole line as the key. Row order then does not count.

Normalise first​

Compare content, not layout. Before the comparison, bring both sides into the same form, the same way:

  • Database tables: read both with the same query and the same connection type, and write them as CSV. The two databases may be different products; their rows are what matter.
  • XML and JSON: extract the elements that carry data (with Get data from XML, for example) into rows. Indentation, attribute order and the declaration differ between tools.
  • Numbers and dates: the same scale and the same format on both sides.

5. Read the result per job​

Look at every job's output, not only at whether it ran:

  • A job that fails is the easy case. The log says why.
  • A job that runs can still be wrong. Embedded code the importer could not translate leaves a column empty; a filter it could not translate lets every row through; a job that loads a table from a file another job failed to write loads nothing, successfully. Only the comparison shows these.
  • Some differences are accepted. A rounding difference the business does not care about, or a column that holds a run's timestamp: record each one with the reason, so that a difference stays either fixed or explained.

6. Turn the comparison into a test​

Once a pipeline matches, keep it that way: save the input and the expected output as Hop data sets and add a unit test to the pipeline. Hop runs it with hop-run or from the GUI, and it fails the moment a later change alters the output.

What usually differs​

  • Data types and rounding. Decimal precision, and dates with or without time, differ between tools. Compare with the precision the business needs.
  • Nulls and empty strings. Talend and Informatica treat them differently from Hop in places. SSIS and SQL Server keep an empty string apart from NULL, and Hop does only when HOP_EMPTY_STRING_DIFFERS_FROM_NULL is Y.
  • Row order. Only meaningful if the target depends on it; otherwise sort before comparing.
  • Expressions the report flagged. Start with those: they are flagged because they are the most likely to differ.

A worked example​

From import to verified project does all of this for a Talend project of 16 jobs: a Talend reference with checksums, the Hop run, a comparison of every file and table in Hop pipelines, and the fixes that turn the differences into matches.

For why and how to move your ETL to Apache Hop, see putki.io/migrations.