Building a data reconciliation system from scratch requires decisions at several layers: how to connect to sources, how to run comparisons, how to orchestrate runs, and how to alert on findings. Each of these layers has free and open-source tooling that is production-grade. Some tools address a single layer; others span multiple.
This list covers the tools worth evaluating at each layer, what they specifically do well for reconciliation use cases, and where they fit in a complete reconciliation architecture.
1. Great Expectations (Data Validation and Quality Gates)
Great Expectations is the most widely adopted open-source data validation framework for Python. You define "expectations" - assertions about what your data should look like - and run validation suites that check whether your data meets them.
For reconciliation, Great Expectations is most useful at the source validation layer: confirm that each source's data is internally consistent before attempting cross-source comparison. If source A's records have unexpected null rates or value distributions, that signal belongs in the comparison context.
It also supports cross-dataset expectations in newer versions, allowing assertions that reference values from a separate data source. This is not full reconciliation, but it covers a subset of comparison scenarios without requiring custom code.
Best for: Teams that want declarative data quality rules they can version-control and run as part of a CI/CD or pipeline step, alongside custom reconciliation logic.
Limitation: Does not manage discrepancy state or handle the lifecycle of identified issues. It reports findings but does not track them across runs.
2. dbt (Data Build Tool - Testing Layer)
dbt is primarily a SQL-based data transformation tool, but its testing framework is useful for reconciliation in warehouse environments. dbt tests support singular tests (custom SQL assertions) and generic tests (schema tests like not_null, unique, relationships).
The relationships test validates that a foreign key in one model resolves to a valid record in another model - a form of cross-source reconciliation within the same data warehouse. The dbt-audit-helper package adds comparison tests that compare two versions of the same model and surface field-level differences.
For teams already using dbt to transform data from multiple sources into a warehouse, the testing framework provides reconciliation coverage with minimal additional infrastructure.
Best for: Warehouse-centric teams that want reconciliation coverage expressed in SQL, tested alongside their transformation models.
Limitation: Works within a single database connection. Cross-system reconciliation (comparing a live API against a database) requires custom source connectors before dbt can handle the comparison.
3. Debezium (Change Data Capture)
Debezium is an open-source CDC (change data capture) platform that streams database changes - inserts, updates, deletes - from supported databases (PostgreSQL, MySQL, MongoDB, SQL Server) to downstream consumers via Apache Kafka.
For event-driven reconciliation architectures, Debezium is the source connector layer. When you need to trigger reconciliation checks in near-real-time as changes occur in a database, Debezium captures those changes and publishes them to a Kafka topic. The reconciliation system subscribes to the topic, waits for the propagation window, and checks the destination system.
Best for: Teams with relational database sources who want event-driven reconciliation without instrumenting application code to emit events manually.
Limitation: Requires a Kafka cluster (or Kafka-compatible service) as the transport layer. Adds operational complexity compared to simpler polling approaches.
4. Apache Kafka (Event Streaming for Event-Driven Reconciliation)
Apache Kafka is the most widely used distributed event streaming platform and the standard transport layer for event-driven reconciliation architectures.
In a reconciliation context, Kafka serves as the queue between change events (emitted by source systems or CDC tools) and the reconciliation consumer (which reads events and triggers comparisons). Kafka's consumer group model allows multiple reconciliation consumers to process events in parallel without duplicate processing.
Kafka's retention configuration is important for reconciliation: retaining events long enough that a consumer outage does not result in missed events during recovery.
Best for: Organizations building event-driven reconciliation where multiple source systems emit changes and the reconciliation layer needs to consume from all of them reliably.
Limitation: Kafka has non-trivial operational overhead. For teams without existing Kafka infrastructure, starting with scheduled batch reconciliation is simpler. Kafka makes sense when you need near-real-time detection and have the infrastructure to support it.
5. Prefect (Workflow Orchestration)
Prefect is a Python-based workflow orchestration framework. Its free tier (Prefect OSS + Prefect Cloud free) provides scheduling, run history, retry policies, and a monitoring UI for Python-based workflows.
For reconciliation, Prefect handles the scheduling and operational concerns: trigger runs on a schedule or in response to events, retry failed runs with configurable backoff, surface run health in a dashboard, and notify on run failures.
Prefect's flow and task model maps naturally to reconciliation: source extraction is a set of tasks, comparison is a task, discrepancy persistence is a task, and alerting is a task. Each can retry independently on failure.
Best for: Teams building reconciliation in Python who want scheduling, retry, and monitoring without deploying Apache Airflow's full infrastructure.
Limitation: The free tier has limitations on concurrent runs and cloud-managed infrastructure. Self-hosted Prefect requires running the orchestration server separately.
6. Pandas (In-Memory Comparison Engine)
Pandas is the standard Python library for tabular data manipulation. For reconciliation jobs that operate on data sets that fit comfortably in memory (up to several million rows depending on column count and available RAM), Pandas provides efficient merge and comparison operations that would otherwise require custom SQL or database joins.
The core pattern: load each source into a DataFrame, merge on the comparison key, and compare columns across the merged result. Pandas handles the row matching efficiently with DataFrame.merge() using how='outer' to surface unmatched rows from either source.
import pandas as pd
merged = pd.merge(df_a, df_b, on='customer_id',
suffixes=('_a', '_b'), how='outer', indicator=True)
# Records in A only
only_in_a = merged[merged['_merge'] == 'left_only']
# Records in B only
only_in_b = merged[merged['_merge'] == 'right_only']
# Matched but disagreeing on email field
field_discrepancies = merged[
(merged['_merge'] == 'both') &
(merged['email_a'] != merged['email_b'])
]
Best for: Reconciliation scripts operating on data sets that fit in memory, particularly for prototyping comparison logic before optimizing for scale.
Limitation: Memory-bound. For data sets with tens of millions of rows, database-side comparison or distributed processing (Apache Spark) is more appropriate.
7. Apache Spark (Large-Scale Distributed Comparison)
Apache Spark provides distributed in-memory data processing and is the appropriate tool when the data set to be reconciled does not fit in a single machine's memory, or when parallelizing the comparison across a cluster would reduce runtime from hours to minutes.
Spark's DataFrame API mirrors Pandas in most comparison patterns, so Pandas-based comparison logic can be ported to Spark with moderate effort. The key difference is that Spark partitions data across the cluster and processes partitions in parallel, making it suitable for comparing data sets with hundreds of millions of rows.
PySpark is the Python interface and is free. Deploying a Spark cluster requires infrastructure (Databricks, Amazon EMR, Google Dataproc, or self-managed) which has associated costs.
Best for: Financial institutions, large e-commerce platforms, or any organization where reconciliation involves data sets with hundreds of millions of records.
Limitation: Operational overhead of a Spark cluster is significant. Most teams should start with Pandas or database-side SQL comparison and move to Spark only when scale requires it.
Putting the Stack Together
A practical reconciliation stack for a mid-size team:
- Extraction: custom connectors + Debezium for database sources
- Comparison engine: Pandas for data sets under 10M rows
- Orchestration: Prefect for scheduling, retry, and monitoring
- Validation at source: Great Expectations for pre-comparison quality checks
- Transport (event-driven layer): Kafka via Confluent free tier or self-managed
The guide on building an automated data reconciliation system at 137Foundry covers the comparison engine and discrepancy tracker architecture that sits between the extraction tools and the orchestration layer - the core logic that these tools plug into.
Start with the simplest stack that handles your data volume. Prefect + Pandas + a PostgreSQL discrepancy tracker is enough for most use cases. Add Kafka and Debezium when detection latency becomes an operational requirement, and move to Spark when Pandas starts to strain under the data volume.
Top comments (0)