Sets Match - Using Key
How and Why to Specify a Key for Comparison
Why Comparison Key?
When you use sets match expectation, you need to specify only the two queries and against what datasources they should be run. That’s it, nothing else. Both the sets need to be sorted (this is a job the database will do with the best performance).
CAT by default does the job and in fact, you don’t need to care. If you are happy with the error messages you get, this topic is of no interest for you.
Specifying a comparison key in fact provides only one additional benefit:
Without key, there is simply no way to recognize, whether the row is similar enough to be reported as different. When you don’t specify the key, you only get report of
- rows missing in the first set
- rows missing in the second set
When you specify a comparison key, you get report of:
- rows missing in the first set
- rows missing in the second set
- different rows.
Remember the Key is opitonal for sets match comparison. It only gives you better error messages.
Where Do I Set the Comparison Key?
Just provide the Key in YAML file, in the database table, in the Excel sheet - whereever you store your tests. Example of a test in a project file:
Tests:
- Name: Compare staging table with fact table - Passengers
Suite: Consistency checks
First Data Source: DWH
First Query: |
SELECT * FROM STAGE.PASSENGERS ORDER BY ID
Second Data Source: DWH
Second Query: |
SELECT * FROM FACT.PASSENGERS ORDER BY ID
Expectation: sets match
Key: ID
Maximum Errors Logged: 20
Instead of column name(s), you can use ordinal position of the column(s). If the ID in the example above is the first column, just provide 1, if second, provide 2 etc.
You can also tell CAT that the first column is the key. In that case you do not put the column name, but one of these constants: First is Key or The First Column is Key.
Requirements for Key column(s)
When using the keys, beware of three things:
‼️ The sets need to be sorted based on the key, in ascending order. CAT expects both the sets are sorted, even though you provided the comparison key. Your database is best at sorting data, let it do it. CAT will then very efficiently compare the values, with no pressure on your computer’s resources. The sorting must be in ascending order. Sorting on both sides must use the same collation. We don’t recommend using columns with non-ASCII characters as key columns - if there are non-ASCII characters, binary collation is needed.
‼️ CAT expects the key is unique.
‼️ Key columns on both sides must have “similar enough” data types. If you specify column of bigint on one side and int on the other, it is OK. If one is datetime and other boolean, CAT will refuse to compare the sets.
OK, “similar enough data types” is not very clear. Here is what the algorithm considers acceptable. If types of both sides fit to exactly one of these bullets, you are OK:
- exactly same types
- all types of integers and decimal numbers
- date/time types
- types that based on their type or length can possibly be unique identifiers
- string values (such as
char,varchar, arrays of chars)
Names of key columns can be different. Use name from either first or second set when specifying the key.
If the rules for uniqueness or sort are not met, CAT issues a warning, which is part of the error message. If the rule for compatible data types in key columns is not met, CAT sets the test result to Error and stops its evaluation.
Composite Key
In database world, it is very common that rather then one column, a combination of two or more columns is unique. CAT supports that.
- You can define more columns in key by using names, use comma or semicolon as splitter:
TerminalID, GateID - You can use “number” of the column instead of name - first column is refered to as 1, second as 2 etc:
1, 2 - You can even combine the two approaches:
1, GateID - You can tell CAT that the all columns except the last one are key columns:
All Except Last,All Columns Except the Last One
The data types of the columns in the key may differ, but they must match with the other side (see the rule described above).
What the Difference Looks Like?
So OK, providing a key gives me better error message, but is it important?
It is up to you. Here you can see an error message without and with the key provided.
Key Was NOT Provided
┌────┬────┬────────┬────────────────┬────┬───────┬─────────────┬────────────┬─────────────┬────────────┬──────────────┬─────────────────────┐
│(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 │
└────┴────┴────────┴────────────────┴────┴───────┴─────────────┴────────────┴─────────────┴────────────┴──────────────┴─────────────────────┘
Key WAS provided
┌────┬────┬────────┬────────────────┬─────┬─────────┬─────────────┬────────────┬─────────────┬────────────┬──────────────┐
│(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 │
└────┴────┴────────┴────────────────┴─────┴─────────┴─────────────┴────────────┴─────────────┴────────────┴──────────────┘
Remarks
Notice a few important things:
- Key columns always come first in the erroneous data sample and are enclosed in brackets. (Notice the
(ID)column in the example.) - When key is specified, only columns with difference are reported (Notice the
SYS_INSERTED_DTcolumn is missing in the second example.) - When key is specified, values with the difference have
!prefix. There is always one exclamation mark in each non-key column. (Notice the 73 vs 17 age for ID 2185893.) - Different rows (with the same key, but at least one different column) are marked as
<(values from first set) and>(values from second set). - Note that the number of rows found differs based on whether you specified the key or not. Even though the same
Maximum Errors Loggedsetting was specified. Without the key specified, same row with difference is counted as two (missing on left an missing on right), while with the key specified this counts as one difference.