--- title: "Compare data across systems" description: "Prove that two systems hold the same data — counts, counts by period or key, the IDs, the full rows — with sets match and contains: key, order, tolerance, what the failure message tells you, and how to keep it cheap on big volumes" url: "https://docs.justcat.it/how-to-guides/test-patterns/compare-data-across-systems/" --- # Compare data across systems ## 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. | Rung | Proves | Cost | |---|---|---| | **1 — counts** | the same number of rows (per table, per day) | trivial | | **2 — counts by period or key** | where the difference is — which month, which region | cheap | | **3 — the IDs** | which rows are missing on which side | the key column of every row, ordered | | **4 — the full rows** | every value agrees | every column of every row, ordered | 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 **Properties** 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 **YAML** ```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 **Properties** Name : Orders per month equal the source Suite : Reconciliation First data source : ERP First query : ```sql 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 : ```sql 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 **YAML** ```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 **Properties** 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 **YAML** ```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](https://docs.justcat.it/reference/tests/expectations/contains/ "Contains"). ### 4 — The full rows **Properties** Name : Customer rows equal the CRM Suite : Reconciliation First data source : CRM First query : ```sql 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 : ```sql 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 **YAML** ```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](https://docs.justcat.it/reference/tests/failure-message/ "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](https://docs.justcat.it/how-to-guides/test-patterns/differences-between-systems/ "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](https://docs.justcat.it/reference/tests/order-and-key/ "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](https://docs.justcat.it/how-to-guides/test-patterns/differences-between-systems/ "Differences between systems") — why equal data looks different, and how to neutralize it in the query. * [Test incremental loads](https://docs.justcat.it/how-to-guides/test-patterns/test-incremental-loads/ "Test incremental loads") — the same tests on a moving window. * [Sets match](https://docs.justcat.it/reference/tests/expectations/sets-match/ "Sets match") · [Contains](https://docs.justcat.it/reference/tests/expectations/contains/ "Contains") · [Order and key](https://docs.justcat.it/reference/tests/order-and-key/ "Order and key") · [Tolerance](https://docs.justcat.it/reference/tests/tolerance/ "Tolerance") — the reference. * [Test measures](https://docs.justcat.it/how-to-guides/data-platforms/power-bi/test-measures/ "Test measures") — the same ladder with a Power BI model on one side.