---
title: "Sets match"
description: "You expect the results of two queries to be the same"
url: "https://docs.justcat.it/reference/tests/expectations/sets-match/"
---
# Sets match


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


**Properties**



Name
: All customers are loaded

Suite
: smoke tests

First Data Source
: CRM

First Query
: ```sql
  SELECT  CustomerID
  FROM    Contacts.Customer
  ORDER BY CustomerID
  ```

Second Data Source
: DWH

Second Query
: ```sql
  SELECT  CustomerID
  FROM    Dimension.Customer
  ORDER BY CustomerID
  ```

Expectation
: sets match





**YAML**


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

> **🐱:** **The order of the rows in both sets is important.** Either the queries return the sets sorted (`ORDER BY` — the database sorts far better than CAT ever will) or you put `Sort data: true` on the test and CAT sorts both sets itself. Both paths, and the optional `Key`, are on [Order and key](https://docs.justcat.it/reference/tests/order-and-key/ "Order and key").



## 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](https://docs.justcat.it/reference/tests/order-and-key/ "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](https://docs.justcat.it/reference/tests/order-and-key/ "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 `NULL`s 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](https://docs.justcat.it/reference/tests/tolerance/ "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](https://docs.justcat.it/reference/tests/order-and-key/ "Order and key"). |
| `Sort data` | `false` | CAT sorts both sets itself instead of relying on `ORDER BY`. See [Order and key](https://docs.justcat.it/reference/tests/order-and-key/ "Order and key"). |
| `Tolerance`, `Tolerance mode` | `0`, `Absolute` | How far two numbers may be apart and still match. See [Tolerance](https://docs.justcat.it/reference/tests/tolerance/ "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](https://docs.justcat.it/reference/tests/failure-message/ "Failure message"). |
| `Maximum sample column length` | `25` | Characters kept per column in the sample. See [Failure message](https://docs.justcat.it/reference/tests/failure-message/ "Failure message"). |

## Related

* [Order and key](https://docs.justcat.it/reference/tests/order-and-key/ "Order and key") — ordering, `Sort data`, and the key: why, where and the rules.
* [Failure message](https://docs.justcat.it/reference/tests/failure-message/ "Failure message") — reading the table of differences; `Maximum errors logged` in detail.
* [Contains](https://docs.justcat.it/reference/tests/expectations/contains/ "Contains") — when one set is allowed to hold more rows than the other.