Order and key

Sets match and contains step through two sorted sets; a key tells CAT which rows belong together.

Sets match and Contains compare two sets the same way: CAT reads both from the top, row by row, and steps forward on the side that is behind. That only works when both sets arrive in the same order. This page covers the two ways of getting them there and the optional Key.

Both sets must be sorted #

The comparison never loads the sets whole — it walks them. A row that is out of order is a row CAT cannot find on the other side, and the test fails for the wrong reason. Two paths, pick the one that fits the test:

• ORDER BY in the queries — the performance path. The database sorts far better than CAT ever will, and nothing is read to RAM. Sort by every compared column, or by the key when you use one; ascending; both sides with the same collation.
• Sort data: true on the test — the convenience path. CAT reads both sets into memory, sorts them itself and only then compares. No ORDER BY needed, and both sides are ordered by one and the same rules, so a different collation on the two systems cannot bite you. Either set above a million rows gets a warning in the log recommending you sort in the source instead — CAT still sorts.

Tests:
- Name: All customers are loaded
  #
  # abbreviated
  #
  Expectation: sets match
  Sort Data: true

Sort data sorts by the key when one is set, by all columns otherwise. It changes who sorts, not what is accepted: the rules below apply either way.

Why a key #

Without a key, when a row is not found on the other side CAT can only say so. It cannot tell whether the row is missing or whether a similar row exists with different values — there is nothing that says which rows belong together. So you only get:

• rows missing in the first set,
• rows missing in the second set.

With a key, CAT pairs the rows by it and you get:

• rows missing in the first set,
• rows missing in the second set,
• different rows — same key, at least one different value — with the differing values marked.

Where to set it #

Put Key on the test definition — in the YAML file, in the database table, in the worksheet, wherever your tests live:

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 value can be:

A key that resolves to no column is an error; the message lists the columns of both sets to choose from.

Rules for key columns #

• Sorted by the key, ascending, on both sides — with ORDER BY, or with Sort data. Same collation on both sides; for non-ASCII characters that means a binary collation, so we do not recommend such columns as keys. A key that goes down somewhere is reported as a warning inside the failure message (Found decreasing key in the first set), and the comparison is not reliable from that point on.
• Unique. A repeated key is reported as a warning inside the failure message (Found duplicity in the key).
• No NULL. A NULL in a key column ends the test with Error.
• Compatible types on the two sides. The values need not be the same type, but they must fall into one of these groups together: the same type; any integer or decimal number; any date or time; a unique identifier (a GUID, or 16 bytes); text (char, varchar, character arrays). A bigint against an int is fine; a date against a boolean is not — the test ends with Error.
• Names of the key columns can differ between the sets; use the name from either side.

Related #

• Failure message — what the two kinds of report look like, with and without a key.
• Sets match · Contains — the expectations that read Key and Sort data.