Get Help

Contains

You expect one set to contain every row of the other set — the other may hold more.

Example

In this example we check that all data from a MySQL source system are present in a staging table in SQL Server. The staging table is persistent and is expected to contain more rows than the source (those that were deleted from the source already, but are kept in staging). The goal is to check that every single row from the source is present in the staging and we are not missing any rows.

Tests:
- Suite: Incremental load tests
  Name: We have all customers from CRM
  First Data Source: DWH # this data source is defined with SqlServer@2 provider
  First Query: |
    SELECT  id, name
    FROM    StagingCrm.Customer
    WHERE   created_on < CAST(GETDATE() AS DATE)
    ORDER BY id
  Second Data Source: CRM # this data source is defined with MySql@1 provider
  Second Query: |
    SELECT  id, name
    FROM    contacts.customer
    WHERE   created_on < curdate()
    ORDER BY id
  Expectation: contains
  Key: id

Queries

Two, exactly as for Sets match: the same number of columns, compared by position. By default the first set is the superset and the second the subset: every row of the second must be found in the first.

The opposite direction

Expecting the superset in the first query may sometimes be awkward and unintuitive. Change the expectation name instead of swapping the queries:

Expectation: second contains first

You can also be explicit about the default direction. These two are technically the same:

Expectation: first contains second
Expectation: contains

Result

The test result is Passed when:

  • the superset contains all rows of the subset (the superset may contain additional rows, missing in the subset);
  • the subset is empty (and there are no errors in the queries).

The test result is Failed when the subset contains at least one row missing in the superset — or, with a key, a row whose key exists on both sides but whose values differ.

If the underlying provider returns an exception for any of the sets, the result is Error.

Properties it reads

The same as Sets match: Key, Sort data, Tolerance and Tolerance mode, Ignore case, Culture, Maximum errors logged, Maximum sample column length. The rules for the key — sorted by it, unique, compatible types, composite keys, ordinal positions — are on Order and key.

Remarks

Set the Key. Tests with the contains expectation perform better and give much better failure messages when a key is set. Without a key CAT still works, but it can only report the first row of the subset that is missing, and Maximum errors logged has no effect — the scan stops at that row. With a key, CAT tells a missing row from a different one and collects up to Maximum errors logged of them:

Tests:
- Suite: Incremental load tests
  Name: We have all customers from CRM
  #
  # abbreviated
  #
  Expectation: contains
  Key: id # optional, but highly recommended — unique and sorted
  Maximum Errors Logged: 20 # has an effect only when a key is set

When the subset is exhausted and every row was found, CAT stops — it does not read the rest of the superset. The failure message then says the scan was complete for the subset side.

← Sets match
Order and key →