When I was doing this stuff for a telco 10 years ago (comparing before/after for CDR mediation changes), I found it was much faster to dump the two x00M row tables as CSV, sort them, and then use a Perl script to compare on a row by row basis. The join approach taken by the Oracle expert took many hours; my dump-sort-scan took under an hour.

The alternative is sampling. With analytical data often you have to accept that data isn't perfect, and that it doesn't need to be. So if it can be guaranteed that amount of "bad" data is, say, less than 0.001% than it may just be good enough. So cheap checks, like "how many PKs are null" can be done on full tables, anything that requires joins or mass-comaring all values can be done on sampled subsets.

>> ...but creating representative sample of differences is difficult to do with hand-written sql. > I'm not sure what you mean.

Let's say column "a" has completely different values in both tables. Column "b" has differences in 0.1% of rows. If we limit diff output to 100 rows in a straightforward way, most likely it won't capture differences in column "b" at all. So you'll need to fix column "a" first or remove it from diff, then you'll discover that "b" has problems too, then the issues with some column "c" may become visible. With right sampling you'll get 20 examples for each of "a", "b", "c" and whatever else is different, and can fix ETL in one go. Or maybe it'll help to uncover the root issue below those discrepancies faster. One very well may do without this feature, but it saves time and effort.

Generally that's what the tool does, it saves time and effort. On writing one-off SQL, or building more generic scripts that do codegen, or building a set of scripts that handle edge cases, have a nice UX and can be used by teammates, or putting a GUI on top of that.

> Solutions are roundtripping if the amount of data is not overwhelming, checksumming otherwise

Agree, checksumming or sampling again. Since data is often expected to have small amounts of discrepancies, checksums would need to be done on blocks to see be able to drill down into failed blocks to see which rows caused failures.

> Then you got a software quality problem. It's also not difficult (with joins against primary keys), to efficiently pick up the difference and to make things idempotent - just re-run the script. I've done that too.

Agree, but software quality problems are bound to happen since requirements change, what was once a perfect architecture becomes a pain point. Attempts to fix some piece of the system often requires creating a new one from scratch and running both side-by-side during transitional period. Some companies begin to realize that they don't have enough visibility into their data migration processes, some understand the need for tooling, but can't afford to spend time or resources to build it in-house, especially if they'll need it only during migration. If we can help companies by providing ready to go solution, that's a great thing.

> this leads to very interesting question about upskilling of employees versus the cost of tools

Right, it's a complicated question involving multiple tradeoffs...

Philosophically, I see our diff tool playing a similar role to autocomplete/autorefactor in an IDE. Can you type/change every word manually? Yes. Is it a good use of your time though?