Get Help

Failure message

The message of a failed test — above all of sets match, contains and set is empty — and the properties that size it.

Every test ends with a result and a message. A Failed test’s message depends on the expectation:

  • Set is not empty: At least one row was expected but the query didn't return any results.

  • Set row count: The expected row count 5 did not match the actual row count 7.

  • Set is empty: No row was expected, but at least 1 row exists., a sample of the rows, and whether CAT read the whole set — the same shape as the two below.

  • Sets match and Contains — the rest of this page. The message contains:

  • a summary — the sets do not match, and how many differences were found;

  • a sample of the differing rows — with values; can be turned off;

  • whether the scan was complete or not;

  • warnings about the key, when one was set and the data broke its rules — see Order and key.

Summary

Example:

The sets do NOT match. 5 differences found.

Beware that the number of found differences depends on whether you specified a Key or not: without a key, a row that differs on both sides counts as two (missing on the left, missing on the right); with a key it is one difference.

Sample of differing rows

By default CAT stops at the very first difference, and that row goes to the message:

┌────┬────┬────────┬────────────────┬─────┬─────────┬─────────────┬────────────┬─────────────┐
│(1) │(2) │ID      │PASSPORT_NUMBER │AGE  │GENDER   │TICKET_PRICE │TICKET_TYPE │LUGGAGE_TYPE │
╞════╪════╪════════╪════════════════╪═════╪═════════╪═════════════╪════════════╪═════════════╡
│ <  │    │2185893 │! 118463324     │! 73 │! Female │! 109        │! 2         │4            │
│    │ >  │2185893 │! 128463324     │! 17 │! Male   │! 106        │! 1         │4            │
╞════╪════╪════════╪════════════════╪═════╪═════════╪═════════════╪════════════╪═════════════╡
│(1) │(2) │ID      │PASSPORT_NUMBER │AGE  │GENDER   │TICKET_PRICE │TICKET_TYPE │LUGGAGE_TYPE │
└────┴────┴────────┴────────────────┴─────┴─────────┴─────────────┴────────────┴─────────────┘

The first line of the table holds the column names from the first query, the last line those from the second query (the names may differ; CAT expects the same order of columns in both queries and does no mapping). < and > are the two rows CAT was looking at when it found the difference — the first set’s and the second set’s — and ! marks the values that differ between them.

When CAT is allowed to collect more than one difference, rows also come marked with an exclamation mark in column (1) — found in the first set but not in the second — or in column (2) — found in the second set but not in the first. See Reading the sample.

Maximum errors logged

The default aims at the best performance. For troubleshooting you may want a bigger sample, not just the first difference; for compliance you may want no data in messages and logs at all. That is what Maximum errors logged is for. It is a property of the test — add it the way you add Name or First query — and it is read by sets match, contains and set is empty:

Maximum errors logged Behavior
0 No data is logged anywhere. The comparison ends at the first difference.
1 (default) The first difference goes to the message. The comparison ends there.
2 to 50 The comparison continues after the first difference, although CAT already knows the test will fail, until at least that many differences are found.
51 or higher CAT writes a warning to the log and continues as if the setting were 50.

Because of how the comparison without a key works, you may get slightly more differences in the message than you asked for; that costs nothing extra. Also accepted as Maximum error count.

The setting is on the test, not global — on purpose, because it is crucial for performance. Imagine two sets with a few million rows that differ on the first and the last row only: by default CAT completes the test in no time; with Maximum errors logged: 20 it reads and examines millions of rows to find the second difference. 0 and 1 are the fastest settings; use higher values when you need to work with the rows and have no other easy way to get them, and lower them when you troubleshoot performance.

With 0 you get this instead of the table:

MaximumErrorsLogged is set to 0. Data related to the found problem is intentionally missing here.
Raise the MaximumErrorsLogged to 1 or higher, if security and compliance rules allow you to have data in logs.

If you don’t want any data in the logs, set Maximum errors logged: 0 on every test.

Maximum sample column length

Every value in the sample is cut to Maximum sample column length characters, 25 by default (10 to 32767). Raise it when the interesting part of a long text is beyond the 25th character.

Log number of errors

Set is empty only: CAT reads the whole set so the message can carry the total number of rows, not only the sample. Described, with its performance warning, on Set is empty.

Reading the sample

Without a key

CAT is designed to find the first differences between the two sets — up to Maximum errors logged. The comparison without a key works like this:

  • CAT expects both sets ordered.
  • It reads row by row on each side. If the row is the same on both sides, it continues.
  • Once CAT spots a difference, it tries to find the row on the other side, reading ahead as many rows as Maximum errors logged allows (at most 50) on each side.
  • If a match is found in that window, the rows read over are reported as missing and the comparison continues.
  • If not, it is not clear whether the same row will appear on the other side later. CAT cannot tell for sure the row is missing — so it marks the rows from the window with question marks and stops.

┌────┬────┬────────┬────────────────┬────┬───────┬─────────────┬────────────┬─────────────┬────────────┬──────────────┬─────────────────────┐
│(1) │(2) │ID      │PASSPORT_NUMBER │AGE │GENDER │TICKET_PRICE │TICKET_TYPE │LUGGAGE_TYPE │NATIONALITY │FLIGHT_NUMBER │SYS_INSERTED_DT      │
╞════╪════╪════════╪════════════════╪════╪═══════╪═════════════╪════════════╪═════════════╪════════════╪══════════════╪═════════════════════╡
│ !  │    │125156  │044468502       │17  │Female │93           │3           │1            │Pole        │XXX480481     │8/15/2023 2:15:03 PM │
│ !  │    │662636  │I49555994       │36  │Female │176          │3           │1            │Other       │XXX830110     │8/15/2023 2:15:24 PM │
│    │ !  │662636  │I49555994       │36  │Male   │175          │3           │1            │Other       │XXX830110     │8/15/2023 2:15:24 PM │
│ !  │    │1779187 │A16052548       │24  │Female │78           │3           │2            │Czech       │XXX513423     │8/15/2023 2:16:08 PM │
│    │ !  │1868884 │740629805       │60  │Male   │99           │3           │1            │German      │XXX823782     │8/15/2023 2:16:11 PM │
╞════╪════╪════════╪════════════════╪════╪═══════╪═════════════╪════════════╪═════════════╪════════════╪══════════════╪═════════════════════╡
│(1) │(2) │ID      │PASSPORT_NUMBER │AGE │GENDER │TICKET_PRICE │TICKET_TYPE │LUGGAGE_TYPE │NATIONALITY │FLIGHT_NUMBER │SYS_INSERTED_DT      │
└────┴────┴────────┴────────────────┴────┴───────┴─────────────┴────────────┴─────────────┴────────────┴──────────────┴─────────────────────┘

With a key

With a Key, CAT pairs the rows by it, so the report can tell a missing row from a different one:

  • Key columns always come first in the sample and their names are in brackets — (ID).
  • Only columns with at least one difference are shown; the others are left out.
  • Different rows (same key, at least one different value) come as a pair, < for the values of the first set, > for the second; every differing value has a ! prefix.
  • ! in (1) or (2) still marks a row missing on the other side.

The same data, without and with Key: ID and the same Maximum errors logged:

┌────┬────┬────────┬────────────────┬────┬───────┬─────────────┬────────────┬─────────────┬────────────┬──────────────┬─────────────────────┐
│(1) │(2) │ID      │PASSPORT_NUMBER │AGE │GENDER │TICKET_PRICE │TICKET_TYPE │LUGGAGE_TYPE │NATIONALITY │FLIGHT_NUMBER │SYS_INSERTED_DT      │
╞════╪════╪════════╪════════════════╪════╪═══════╪═════════════╪════════════╪═════════════╪════════════╪══════════════╪═════════════════════╡
│ !  │    │2185893 │118463324       │73  │Female │109          │2           │4            │Slovak      │XXX331759     │8/15/2023 2:16:23 PM │
│    │ !  │2185893 │128463324       │17  │Male   │106          │1           │1            │British     │XXX331759     │8/15/2023 2:16:23 PM │
│ !  │    │2453078 │K27742062       │42  │Female │110          │3           │4            │Czech       │XXX260037     │8/15/2023 2:16:34 PM │
│    │ !  │2453078 │K27742062       │42  │Female │110          │3           │4            │Czech       │XXX677662     │8/15/2023 2:16:34 PM │
│    │ !  │2888700 │A27834187       │35  │Female │110          │3           │4            │Czech       │XXX948757     │8/15/2023 2:16:50 PM │
│    │ !  │3048201 │P77272017       │39  │Female │172          │3           │4            │Slovak      │XXX594553     │8/15/2023 2:16:57 PM │
│ !  │    │3104872 │K21988419       │43  │Female │247          │2           │1            │Austrian    │XXX303521     │8/15/2023 2:16:59 PM │
│ !  │    │3435893 │705598821       │57  │Female │140          │3           │3            │Czech       │XXX697294     │8/15/2023 2:17:12 PM │
│    │ !  │3590747 │920512139       │44  │Female │64           │3           │1            │Czech       │XXX825049     │8/15/2023 2:17:17 PM │
│ !  │    │4164729 │521221195       │37  │Male   │91           │3           │2            │British     │XXX732170     │8/15/2023 2:17:39 PM │
│    │ !  │4164729 │521221195       │37  │Male   │91           │3           │3            │British     │XXX732170     │8/15/2023 2:17:39 PM │
╞════╪════╪════════╪════════════════╪════╪═══════╪═════════════╪════════════╪═════════════╪════════════╪══════════════╪═════════════════════╡
│(1) │(2) │ID      │PASSPORT_NUMBER │AGE │GENDER │TICKET_PRICE │TICKET_TYPE │LUGGAGE_TYPE │NATIONALITY │FLIGHT_NUMBER │SYS_INSERTED_DT      │
└────┴────┴────────┴────────────────┴────┴───────┴─────────────┴────────────┴─────────────┴────────────┴──────────────┴─────────────────────┘

┌────┬────┬────────┬────────────────┬─────┬─────────┬─────────────┬────────────┬─────────────┬────────────┬──────────────┐
│(1) │(2) │(ID)    │PASSPORT_NUMBER │AGE  │GENDER   │TICKET_PRICE │TICKET_TYPE │LUGGAGE_TYPE │NATIONALITY │FLIGHT_NUMBER │
╞════╪════╪════════╪════════════════╪═════╪═════════╪═════════════╪════════════╪═════════════╪════════════╪══════════════╡
│ <  │    │2185893 │! 118463324     │! 73 │! Female │! 109        │! 2         │! 4          │! Slovak    │XXX331759     │
│    │ >  │2185893 │! 128463324     │! 17 │! Male   │! 106        │! 1         │! 1          │! British   │XXX331759     │
│ <  │    │2453078 │K27742062       │42   │Female   │110          │3           │4            │Czech       │! XXX260037   │
│    │ >  │2453078 │K27742062       │42   │Female   │110          │3           │4            │Czech       │! XXX677662   │
│    │ !  │2888700 │A27834187       │35   │Female   │110          │3           │4            │Czech       │XXX948757     │
│    │ !  │3048201 │P77272017       │39   │Female   │172          │3           │4            │Slovak      │XXX594553     │
│ !  │    │3104872 │K21988419       │43   │Female   │247          │2           │1            │Austrian    │XXX303521     │
│ !  │    │3435893 │705598821       │57   │Female   │140          │3           │3            │Czech       │XXX697294     │
│    │ !  │3590747 │920512139       │44   │Female   │64           │3           │1            │Czech       │XXX825049     │
│ <  │    │4164729 │521221195       │37   │Male     │91           │3           │! 2          │British     │XXX732170     │
│    │ >  │4164729 │521221195       │37   │Male     │91           │3           │! 3          │British     │XXX732170     │
│ <  │    │4684428 │124528923       │36   │Male     │127          │3           │2            │British     │! XXX260038   │
│    │ >  │4684428 │124528923       │36   │Male     │127          │3           │2            │British     │! XXX260039   │
│ !  │    │4890294 │225776255       │5    │Male     │433          │2           │1            │Czech       │XXX889446     │
╞════╪════╪════════╪════════════════╪═════╪═════════╪═════════════╪════════════╪═════════════╪════════════╪══════════════╡
│(1) │(2) │(ID)    │PASSPORT_NUMBER │AGE  │GENDER   │TICKET_PRICE │TICKET_TYPE │LUGGAGE_TYPE │NATIONALITY │FLIGHT_NUMBER │
└────┴────┴────────┴────────────────┴─────┴─────────┴─────────────┴────────────┴─────────────┴────────────┴──────────────┘

Notice the SYS_INSERTED_DT column is gone from the second sample (no difference in it), the 73 vs 17 age for ID 2185893 is marked on both rows, and the count of rows differs — without a key, one differing row counts as two (missing left, missing right).

Complete or incomplete

CAT does NOT compare the sets completely by default — Maximum errors logged stops it. So the message ends with one of:

The scan was not complete. There may be also other errors. Raise MaximumErrorsLogged setting if necessary.

CAT did not compare the sets to the end, because it already found the maximum number of differences it was asked for. Or:

CAT scanned both sets completely - there are no other differences.

Then you can be sure there are no other differences (if the data did not change since the test ran). E.g., with Maximum errors logged: 20 and only 3 differing rows, CAT scanned both sets to the very end, trying to find a fourth.

Wide tables

When the queries return many columns, the table comes out distorted in a narrow console window or in Azure DevOps test details — columns and rows wrap for lack of horizontal space:

Distorted table

What helps: select only the columns that need comparing; set a Key, so the sample holds only columns with at least one difference; read the message in the log or an output rather than on the console (by default CAT logs to the Logs directory next to the .cat.yaml file).

← Results
Introduction →