MINUS the Pain: Regression Testing at Scale
A decade ago, I was fortunate enough to work on a regression testing suite for a critical banking system. The technology stack was a greatest hits collection from another era: Oracle, SQL, PL/SQL, Perl and AutoSys.
The goal was straightforward: run the same full batch through two identical, production-like environments using identical inputs, compare every output, and ensure the batch completed without errors. This was parallel testing at scale. Parallel testing is the approach; regression testing is the purpose. And to put the scale into perspective, the batch consisted of around 3,000 individual jobs and produced roughly 1,000 extracts.
Back then, we solved the comparison problem with dynamically generated SQL, using row count comparisons and two-way MINUS queries to identify differences between the outputs.
MINUS comparison runs A MINUS B and B MINUS A to identify rows that exist in one dataset but not the other.We then built a reporting layer to turn those differences into something people could actually read and investigate. It worked, but it wasn't exactly elegant. You had to build the SQL, handle different tables and keys, interpret the results, and deal with all the edge cases that come with comparing datasets.
Fast forward a decade, and I still find myself solving similar problems in the Databricks world. This time, instead of wrangling SQL statements and building our own comparison reports, I reached for DataComPy.
DataComPy
DataComPy is a Python package for comparing datasets. Originally created for pandas, it also supports Spark DataFrames, making it a natural fit for Databricks.
Using it is quite simple. Set up two DataFrames, specify the join columns, then run the report.
Basic Example
Let's say we have the same account output generated in two environments and want to verify that they are identical.
# SparkSQLCompare
env01_df = spark.table("env01.account")
env02_df = spark.table("env02.account")
compare = SparkSQLCompare(
spark,
env01_df,
env02_df,
join_columns='acct_id',
df1_name='env01', # friendly name on report
df2_name='env02', # friendly name on report
)
compare.matches()
print(compare.report())
matches() gives you a quick boolean answer, while report() provides the detailed explanation of where the differences are.
The report
This is where DataComPy starts to feel familiar to me. In the old system, we had to build our own reporting layer to turn raw comparison results into something investigators could work with. We also had to compare row counts because MINUS doesn't account for duplicates.
DataComPy gives you much of that out of the box.
DataFrame Summary
-----------------
DataFrame Columns Rows
0 env01 5 5
1 env02 4 5
Column Summary
--------------
Number of columns in common: 4
Number of columns in env01 but not in env02: 1
Number of columns in env02 but not in env01: 0
Row Summary
-----------
Matched on: id
Any duplicates on match values: No
Absolute Tolerance: 0
Relative Tolerance: 0
Number of rows in common: 4
Number of rows in env01 but not in env02: 1
Number of rows in env02 but not in env01: 1
Number of rows with some compared columns unequal: 4
Number of rows with all compared columns equal: 0
Column Comparison
-----------------
Number of columns compared with some values unequal: 3
Number of columns compared with all values equal: 1
Total number of values which compare unequal: 6
Inspecting the data
The report tells you that something is different. The next question is what exactly is different?
This is where the other attributes and methods of the SparkSQLCompare class come in handy. It exposes DataFrames that are useful for investigating the differences. In Databricks, these can be displayed using the notebook's display() function, which makes the investigation much easier.
df1_unq_rowsanddf2_unq_rowslet me inspect rows that exist in only one of the DataFrames.
all_mismatch()gives me a DataFrame containing the join key and the columns values for each DataFrame.
The hard yards
DataComPy makes the comparison part surprisingly easy. But anyone who has built a serious regression-testing framework knows that comparison is only one piece of the puzzle.
The real work is in:
Setting up the parallel environments, inputs and automated output collection
Investigating differences
Reducing noise
I remember the first few iterations of our regression testing. The reports were full of differences. It took weeks to work through them.
The challenge wasn't simply finding differences. It was determining which differences mattered.
A difference is not necessarily a defect.
Some were genuine defects. Others were noise:
Batch timestamps
Run-specific identifiers
Control columns
Null vs default values
Schema or datatype differences
Floating-point and rounding differences
Non-deterministic processing
Differences caused by jobs running in a different sequence
Legitimate differences introduced by an intentional change
The goal wasn't to get the number of differences to zero. It was to get the number of unexpected differences to zero.
As the process became more automated and routine, we started to see the payoff. We saw fewer unexpected defects make it into production. Developers spent less time diagnosing problems after deployment, and significant migrations and platform changes could be made with much greater confidence.
Regression testing became less of a safety net we hoped we wouldn't need and more of an enabler for making changes safely.
Why I'm at it again
The temptation with regression testing is to see it as another testing framework to build, another pipeline to maintain, and another set of reports for developers to work through.
And honestly, getting it started can feel like a lot of work.
But once you have it running, something interesting happens.
You stop being afraid of making big changes.
Want to upgrade the Databricks runtime? Compare it.
Want to refactor a complicated pipeline? Compare it.
Want to migrate workloads or introduce a performance optimisation? Compare them.
Run the same inputs through the old and new implementations. Reconcile the outputs, investigate the differences and reduce the noise. What you end up with is much more valuable than a collection of unit tests.
You have evidence.
Evidence that the new implementation behaves like the old one. Evidence that a migration hasn't silently changed the numbers. Evidence that a performance improvement hasn't introduced a subtle business logic regression.
That's the real value of parallel testing.
It's not about proving that two systems are identical. They're often not supposed to be. It's about making the differences visible, explainable and intentional.
The investment is not in building another testing framework.
The investment is in buying your team the confidence to change things.
And for systems that matter, that can be worth a lot.