Data Reconciliation in Performance & Migration Testing

When testing and improving the performance of an enterprise system, it is crucial to verify that the data generated after an optimisation matches the data generated before.

Consider an End-of-Day (EOD) batch process in a bank or asset manager that generates regulatory or client reports. If you cut its runtime in half, you must prove with 100% confidence that every figure in the output remains identical (or within defined numerical tolerances).

Typical Reconciliation Requirements

• Compare two large datasets (millions of rows)
• Match records on composite primary keys
• Allow configurable numeric or timestamp tolerances
• Drill down from high-level break summaries into exact field-level mismatches
• Run on-demand or on an automated schedule

Comparing the Options

1. Unix diff & Beyond Compare

• **diff** is fast for small flat files but brittle—it compares raw text lines and requires identical sort orders.
• Beyond Compare handles key columns and column-by-column comparisons well for interactive desktop checks, though it isn't built for automated enterprise pipelines.

2. Excel Pivot Tables

Feasible for small datasets by combining sheets with a Source column and pivoting on keys, but quickly breaks down at scale and requires manual column wiring.

3. Python (datacompy + Pandas)

For engineering teams comfortable with Python and NumPy/Pandas, libraries like datacompy provide fast, scriptable key-based DataFrame comparisons with tolerance support.

4. Datascade

For teams wanting a dedicated data reconciliation engine with data-source connectors (files, databases, SFTP), visual summary matrices, and instant double-click drill-downs into mismatched cells, Datascade bridges the gap between ad-hoc spreadsheets and custom Python pipelines.