Compare and reconcile data between two databases or environments

August 29, 2026

After a migration, a sync, or a release, you need to prove PROD still matches the source: row counts, checksums, key differences, often across different vendors and without a diff tool of its own for either side.

The problem: proving integrity, differently every time

Reconciliation after a migration or a sync usually gets done by hand: a count here, a spot check there, whatever the person doing it happens to think of that day. It works, until someone asks how you know it worked, or until the same check needs doing again after the next release and nobody remembers exactly what was checked last time. Worse, "the data matches" usually hides two separate questions that get conflated: did the table's own structure survive the move intact, and does the data inside it actually match, row for row.

Step one: did the structure survive

Before comparing a single row, it is worth confirming both sides still agree on what a row looks like. A migration or a schema change can quietly resize a column, drop a default, or loosen a nullability constraint on one side only, and every downstream data check will pass while missing that entirely. COMPARE TABLE STRUCTURE checks this directly, without moving any data at all:

CONNECT source_db;
COMPARE TABLE STRUCTURE customer WITH target_db;

The report lists columns present on only one side, columns present on both sides but with a different type, size, decimal count, or nullability, and a count of columns that match exactly. target_db only needs to be a connection already registered in your CDF, it does not need to be the currently open one.

Step two: pull both sides somewhere you can compare them

PULL can copy a query's results from any connected database into a local H2 table, regardless of what database it came from, so two systems that don't speak the same SQL dialect still land in the same place for comparison:

CONNECT source_db;
PULL customer TO checks.source_customer AS H2;

CONNECT target_db;
PULL customer TO checks.target_customer AS H2;

CONNECT checks;
SELECT count(*) FROM source_customer;
SELECT count(*) FROM target_customer;

The same pattern compares environments rather than vendors just as easily, since an environment in BroadSQL is still just a connection: point the two PULL commands at your DEV and PROD connections instead, and everything below works unchanged. SHOW ALL ENVIRONMENTS and SHOW GROUP are useful here to confirm which connection name maps to which environment before you start, so the comparison actually runs against the pair you intended.

Step three: go past row counts, to row level differences

A matching row count is necessary, but it proves nothing about the rows themselves: an equal number of inserts and deletes cancels out to the same total. Once both sides are local H2 tables, finding exactly which keys differ, and how, is three ordinary queries:

-- Present in the source but missing from the target
SELECT s.id FROM source_customer s
LEFT JOIN target_customer t ON t.id = s.id
WHERE t.id IS NULL;

-- Present in the target but missing from the source
SELECT t.id FROM target_customer t
LEFT JOIN source_customer s ON s.id = t.id
WHERE s.id IS NULL;

-- Present on both sides, but with different values
SELECT s.id, s.email, s.status, t.email, t.status
FROM source_customer s
JOIN target_customer t ON t.id = s.id
WHERE s.email IS DISTINCT FROM t.email
   OR s.status IS DISTINCT FROM t.status;

The third query lists every column you actually care about; add or drop columns from the IS DISTINCT FROM chain to widen or narrow what counts as a mismatch. Save this trio in your query library (LIB RUN) once, and the same three-way reconciliation check runs identically next time, against whichever pair of environments or vendors you point the earlier PULL commands at.

Honest scope

This is scriptable, repeatable checking built out of ordinary SQL, COMPARE TABLE STRUCTURE, and a local H2 database, not a dedicated schema and data diff product with a UI and a visual conflict resolver of its own. The row level queries above assume a usable key column per table and a manageable table size for a JOIN to run comfortably in H2; for a very large table or a fully automated diff engine across an entire schema at once, a specialized tool may still be the better fit. Where this approach wins is when the checks are yours to define, need no separate license or install, and you want them to run the same way, against any two environments, every time.

Who this is for

Data stewards and data governance analysts responsible for proving data integrity after a migration, a sync, or a release, especially when sign off depends on it, and the application or production support engineers who get asked to confirm two environments actually match during an incident. See who BroadSQL is for for the fuller picture of both roles.

Build a repeatable checkpack

I help teams build a "data quality checkpack": structure checks, row counts, null checks, referential checks, and key level reconciliation, saved once and re-run on demand, then exported to Excel for sign off. Get in touch to talk about your next migration or release.