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

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.
← Set is not Empty
XLSX →