Get Help

Sets match

You expect the results of two queries to be the same.

Sets match compares two sets from the same data source or from two different ones — Azure Databricks against a Power BI workspace, PostgreSQL against Snowflake. It is built for large volumes on modest hardware: CAT steps through both sorted sets row by row and never loads them whole.

Example

You may want to verify that all customers from your CRM system are also present in your analytical system:

Tests:
- Name: All customers are loaded
  Suite: smoke tests
  First Data Source: CRM
  First Query: |
    SELECT  CustomerID
    FROM    Contacts.Customer
    ORDER BY CustomerID
  Second Data Source: DWH
  Second Query: |
    SELECT  CustomerID
    FROM    Dimension.Customer
    ORDER BY CustomerID
  Expectation: sets match

Queries

Two: First data source + First query and Second data source + Second query. The two data sources can use different providers — that is how you compare across systems.

Both queries must return sets with the same number of columns; otherwise the test ends with Failed at once and the message lists the columns of both sides next to each other. Column names and data types may differ. The order of columns is what matters — CAT compares the first column with the first, the second with the second; it does no mapping by name.

Result

If both queries return the same rows — no missing and no additional rows, no different values — the result is Passed.

If there is at least one difference, the result is Failed. If one set runs out of rows sooner than the other, the remaining rows are differences too.

If the underlying provider of any of the queries throws an exception, the result is Error. So does a key that cannot be compared — see Order and key.

How the comparison runs

CAT iterates through the first and the second set simultaneously, row by row, so it does not read the entire sets into RAM (unless you ask it to sort them). There are two ways of stepping through the sets — with a key or without, see Order and key — which differ in what the failure message can tell you; the value comparison is the same.

Two rows match when every pair of values matches, column by column:

  • Two NULLs match. A NULL against a value does not.
  • Values equal as the provider returned them match; so do values whose text forms are equal, ignoring case — Some Data is the same as some data (Ignore case: false turns this off).
  • Otherwise, if both can be read as numbers, they are compared as numbers — within the Tolerance when one is set.
  • Otherwise, if both can be read as dates and times, they are compared as such.
  • Anything else is a difference.

The text forms are read using the en-US culture unless the test sets Culture (also Locale).

Properties it reads

Property Default Meaning
Key — Column(s) that identify a row; lets CAT report different rows, not only missing ones. See Order and key.
Sort data false CAT sorts both sets itself instead of relying on ORDER BY. See Order and key.
Tolerance, Tolerance mode 0, Absolute How far two numbers may be apart and still match. See Tolerance.
Ignore case true Text compared without regard to case.
Culture (Locale) en-US Culture used to read numbers and dates out of text values.
Maximum errors logged 1 How many differences CAT collects before it stops, and shows in the message (0–50). See Failure message.
Maximum sample column length 25 Characters kept per column in the sample. See Failure message.
  • Order and key — ordering, Sort data, and the key: why, where and the rules.
  • Failure message — reading the table of differences; Maximum errors logged in detail.
  • Contains — when one set is allowed to hold more rows than the other.
← Set row count
Contains →