---
title: "Differences between systems"
description: "Why equal data looks different across systems — types, decimals, dates and time zones, NULL vs empty, spaces, case, booleans, text keys — and how to neutralize each in the query"
url: "https://docs.justcat.it/how-to-guides/test-patterns/differences-between-systems/"
---
# Differences between systems


CAT compares values leniently by design — NULL equals NULL, text ignoring case, numbers as numbers within a tolerance, dates as dates; the rules are on [Sets match](https://docs.justcat.it/reference/tests/expectations/sets-match/ "Sets match"). The table below is what still bites, and the fix for each. The principle throughout: **make both sides return the same shape**, rather than asking CAT to guess.

| Difference | What you see | Neutralize it |
|---|---|---|
| **Numeric types** — `INT` vs `DECIMAL`, `FLOAT` vs `NUMERIC`, money as `DECIMAL(19,4)` vs `FLOAT` | sums off by a cent, `12.5` vs `12.499999` | `Tolerance: 0.01` (absolute) or `Tolerance mode: percent`; `ROUND(x, 2)` on both sides when the tolerance would hide real differences |
| **Dates vs date-times** — `DATE` on one side, `DATETIME` with midnight on the other | compared as dates, usually fine; breaks when the datetime carries a time | `CAST(… AS DATE)` on both sides |
| **Time zones** — UTC in the lakehouse, local time in the source | every row differs by one or two hours | convert in the query (`AT TIME ZONE`, `CONVERT_TZ`, `from_utc_timestamp`) — to UTC on both sides; for watermarks compare dates, not instants |
| **NULL vs empty string** — a source that writes `''`, a warehouse that stores `NULL` | rows differ only in that column | `NULLIF(col, '')` or `COALESCE(col, '')` on both sides, consistently |
| **Trailing spaces** — `CHAR(n)` padding, a source that does not trim | `'ABC   '` vs `'ABC'` — a difference | `RTRIM` / `TRIM` on both sides |
| **Case** — `'Prague'` vs `'PRAGUE'` | forgiven by default (`Ignore case: true`) | set `Ignore case: false` when case *is* the difference you test |
| **Accents and Unicode** — `'Müller'` vs `'Muller'`, precomposed vs decomposed characters | differences on names | a system-side normalization function, or compare an ASCII-folded column; decide whether it is a real difference first |
| **Booleans** — `1`/`0` vs `true`/`false` vs `'Y'`/`'N'` | `1` vs `True` is a difference (text forms differ, only one reads as a number) | `CAST` to the same type on both sides, or `CASE WHEN … THEN 1 ELSE 0 END` |
| **Decimal separators and number formats in text** — `'1.234,50'` vs `1234.5` | text that does not read as a number under the default culture | `Culture: cs-CZ` on the test (also `Locale`), or cast to a number in the query |
| **GUIDs** — braces, case | `{ABC…}` vs `abc…` — a difference | `LOWER(CAST(id AS VARCHAR(36)))` on both sides |
| **Sort order of text keys** — systems sort text by different rules | a key "goes backwards" on one side; rows that exist on both sides are reported missing | key on a number, a date or an ID; `Sort data: true` for small sets |

## Keep it practical

A comparison does not have to be exact to the last byte; it has to run fast and go red only when the data really differs. Pick the cheapest fix from the table that works for your two systems — a cast, a trim, a round, an integer key, a tolerance where the cents do not matter — and move on. You will end up with a handful of such fixes for each pair of systems; reuse them in every comparison between the two.

## Related

* [Compare data across systems](https://docs.justcat.it/how-to-guides/test-patterns/compare-data-across-systems/ "Compare data across systems") — the tests these fixes belong to.
* [Order and key](https://docs.justcat.it/reference/tests/order-and-key/ "Order and key") · [Tolerance](https://docs.justcat.it/reference/tests/tolerance/ "Tolerance") · [Sets match](https://docs.justcat.it/reference/tests/expectations/sets-match/ "Sets match") — the reference, with the exact comparison rules.