Differences between systems
Two systems that hold the same data rarely return it the same way. Most of the differences CAT already forgives; the rest you neutralize in the query, on purpose, so a failing comparison means a real difference.
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. 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 — the tests these fixes belong to.
- Order and key · Tolerance · Sets match — the reference, with the exact comparison rules.