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:

🐱

With key specified, CAT can tell whether the row is missing on the other side or is different on the other side.

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 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. 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

🐱

The sets need to be sorted based on the key. CAT expectes the sets to be sorted, even though you provided the comparison key. You 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.

Both key columns must have the same data type. Name can be different (use name from either first or second set), but the data type must be the same. So if you have e.g., int in first set and bigint in the second, cast both to bigint.

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 column is always first in the table and is enclosed in brackets.
When key is specified, only columns with difference are reported (notice the SYS_INSERTED_DT column is missing in the second example.
When key is specified, values with the difference have ! prefix.
Different rows (with the same key, but at least one different column) are marked as < (values from first set) and > (values from second set).
Because without the key specified rows are reported only as missing in first or missing in second, you get more errors with the key specified - even though the same Maximum Errors Logged setting was specified.

Sets Match - Using Key

Why Comparison Key? #

Where Do I Set the Comparison Key? #

What the Difference Looks Like? #

Key Was NOT Provided #

Key WAS provided #

Remarks #