Compare data across systems

Source against warehouse, lakehouse against model, old system against new: one test, two queries, two technologies, and CAT steps through both results side by side. Climb the ladder — counts, counts by key, the IDs, the full rows — as far as the question needs.

The ladder #

Each rung costs more and proves more. Most projects keep tests on every rung — the cheap ones run after every load, the expensive ones nightly or on demand.

You have the query language on both sides, so you decide the grain, the filter, the columns — CAT only compares what comes back. Same number of columns on both sides, compared by position; names and types may differ.

1 — Counts #

• Form view
• YAML

Tests:
- Name: Orders count equals the source
  Suite: Reconciliation
  First data source: ERP
  First query: SELECT COUNT(*) FROM dbo.Orders WHERE Status <> 'Draft'
  Second data source: DWH
  Second query: SELECT COUNT(*) FROM fact.Orders
  Expectation: sets match

One row each; a difference is reported with both numbers.

2 — Counts by period or key #

• Form view
• YAML

- Name: Orders per month equal the source
  Suite: Reconciliation
  First data source: ERP
  First query: |
    SELECT  YEAR(OrderDate) AS Y, MONTH(OrderDate) AS M, COUNT(*) AS Orders, SUM(Amount) AS Amount
    FROM    dbo.Orders WHERE Status <> 'Draft'
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)
    ORDER BY Y, M
  Second data source: DWH
  Second query: |
    SELECT  d.Year, d.Month, COUNT(*), SUM(f.Amount)
    FROM    fact.Orders f JOIN dim.Date d ON d.DateKey = f.OrderDateKey
    GROUP BY d.Year, d.Month
    ORDER BY d.Year, d.Month
  Expectation: sets match
  Key: 1, 2
  Tolerance: 0.01
  Maximum errors logged: 20

The key (Y, M — ordinals 1, 2 work on both sides whatever the names) lets the message say which month differs and show both rows; Tolerance absorbs rounding of the sums; Maximum errors logged: 20 lists up to twenty months, not one.

3 — The IDs #

• Form view
• YAML

- Name: Every source order is in the warehouse, and nothing more
  Suite: Reconciliation
  First data source: ERP
  First query: SELECT OrderId FROM dbo.Orders WHERE Status <> 'Draft' ORDER BY OrderId
  Second data source: DWH
  Second query: SELECT SourceOrderId FROM fact.Orders ORDER BY SourceOrderId
  Expectation: sets match
  Maximum errors logged: 50

One column, ordered on both sides. The message lists IDs missing left and missing right. When the target is allowed to hold more — a permanent staging area that keeps deleted rows — use contains instead: First query the superset, Second query the subset (or write second contains first); see Contains.

4 — The full rows #

• Form view
• YAML

- Name: Customer rows equal the CRM
  Suite: Reconciliation
  First data source: CRM
  First query: |
    SELECT  CustomerId, UPPER(Email), FirstName, LastName, CAST(CreatedOn AS DATE), CreditLimit
    FROM    dbo.Customers WHERE IsDeleted = 0 ORDER BY CustomerId
  Second data source: DWH
  Second query: |
    SELECT  SourceId, UPPER(Email), FirstName, LastName, CreatedDate, CreditLimit
    FROM    dim.Customer WHERE SourceSystem = 'CRM' AND IsCurrent = 1 ORDER BY SourceId
  Expectation: sets match
  Key: 1
  Tolerance: 0.001
  Maximum errors logged: 20

With the key, the message shows different rows as a pair with the differing values marked, plus rows missing on either side — only the columns that differ anywhere are shown. Without a key it can only say “missing left / missing right”. Failure message reads one for you.

The rules that make it work #

• Both sets ordered the same way — ORDER BY the key on both sides (the database sorts better than anything), or Sort data: true on the test and CAT sorts both sets in memory (fine up to a few hundred thousand rows; a warning above a million). Key on a number, a date or an ID — they sort the same on every system; text is something to compare, not to sort by. Why, and the other things that make equal data look different: Differences between systems.
• A key when you want to know what differs — a column name from either side, an ordinal, or several (Key: 1, 2). Sorted by it, unique, no NULL, compatible types; Order and key has the rules.
• Values compare leniently, on purpose — NULL equals NULL; text equal ignoring case (Ignore case: false to be strict); two values that both read as numbers compare as numbers within Tolerance (absolute, or Tolerance mode: percent with the higher value as base); dates as dates. 1 and 1.0 agree; 'ABC' and 'ABC ' do not — trim in the query.
• Maximum errors logged decides how far CAT reads — 1 (default) stops at the first difference, the fastest setting; 20 keeps going until it has twenty. The message always says whether the scan was complete.

Keeping it cheap on big volumes #

The ladder is the starting point, not the ceiling: teams that compare a lot get inventive about queries that give trust without reading every row — and because the query is yours, CAT runs whatever you come up with. A few that come back again and again:

• Checksums per group — SUM, AVG, MIN, MAX, COUNT(DISTINCT …) of the important columns per month or per key range, compared with a tolerance: a few hundred rows per side prove a few hundred million agree, and a group that differs tells you where to look.

• The IDs only on the full range, the full rows on a window — rung 3 cheap everywhere, rung 4 where the data moves.

• Sampling by key — WHERE OrderId % 100 = 7 on both sides: one percent of the rows, the same one percent, compared in full; rotate the remainder per run and you cover everything over time.

• Extremes and edges — the newest and oldest row per group, the largest amounts, the rows around a boundary — where loads break first.

• Climb the ladder in order: a failing count makes rung 4 pointless.

• Aggregate where you can — rung 2 over a year of data is a thousand rows, not a billion; rung 4 on a window (WHERE OrderDate >= DATEADD(DAY, -3, GETDATE())) is the nightly test; the full rung 4 is the migration test you run once.

• Select only the columns you compare; cast to the same shape on both sides (CAST(… AS DATE), ROUND) so CAT does not have to be lenient.

• Hash the row when it is wide — within one technology: key plus one hash column on both sides (HASHBYTES('SHA2_256', CONCAT_WS('|', …)) on two SQL Servers, md5(concat_ws('|', …)) on two PostgreSQL or Databricks systems) and rung 4 becomes two columns per row; the message names the rows that differ, and you rerun the full rung on those keys to see what. Across different systems it is a trap: the functions differ, HASHBYTES hashes UTF‑16 where md5 hashes UTF‑8, NULLs and numbers and dates stringify differently — two correct systems, two different hashes. There, select the columns and let CAT compare them; lenient comparison is what it is for.

• Let the databases sort. Sort data: true is for small sets and for collation trouble, not for speed.

• A Timeout on the test caps a runaway query.

Related #

• Differences between systems — why equal data looks different, and how to neutralize it in the query.
• Test incremental loads — the same tests on a moving window.
• Sets match · Contains · Order and key · Tolerance — the reference.
• Test measures — the same ladder with a Power BI model on one side.