One way of refactoring legacy code is to use diff tests; checking what changes when you change the code.
While it can be easy to
diff files, it's a little less obvious how to do this with SQL pipelines.
Fortunately there are a few different techniques to do this.
For exact matching you can use union all to find the number of rows that don't occur in both datasets. For approximate matching you can use a join to check whether the differences are within some bounds. These techniques work well together: for an approximate match you can first check the keys with union all, and then check the values with join.
Exact Matching: Union All
A simple way of checking whether two tables are the same is to use
This query will return all the rows that occur in only one or the other table, and which table ('old' or 'new') that they occur in.
SELECT col_1, col_2, ..., col_n, count(*) as mult, max(source) as source FROM ( SELECT col_1, col_2, ..., col_n, 'old' as source FROM A UNION ALL SELECT col_1, col_2, ..., col_n, 'new' as source FROM B ) GROUP BY col_1, col_2, ..., col_n HAVING COUNT(DISTINCT source) <> 2
The only downside of this is it can be a lot of typing if the tables have a lot of columns to type in.
You could always template this in a programming language; for example in the excellent
dbplyr you can easily do a variation like this:
union_all(table_a %>% mutate(source='old'), table_b %>% mutate(source='new')) %>% group_by(across(-c('source'))) %>% filter(n_distinct(source) != 2)
Approximate Matching: Join
Sometimes the results are allowed to vary a little bit because of slight changes to the data, random inputs and race conditions. In this case the union all approach will return much more than we want. The union all is still useful for checking both tables have the same keys; we can then use a join to check the values are similar.
For example to get all rows where the column
val changes by more than 5% you could use something like:
SELECT * FROM A JOIN B on a.key_1 = b.key_1 and a.key_2 = b.key_2 ... WHERE a.val NOT BETWEEN 0.95 * b.val and 1.05 * b.val
These kinds of approximate joins require a lot more thought; how much variation is expected? Maybe it's ok for 1% of the rows to vary by more than 5%, but only 0.1% of the rows to vary by more than 10%. However when you've decided the rules they're pretty straightforward to implement in SQL.