Which expectation when

Five expectations cover data testing because the query does the thinking. Pick by the question you are asking: nothing should come back, something should, exactly this many, these two sets agree, one set is inside the other.

Each example below is shown twice: as the fields you fill in on the Tests page of CAT Studio, and as the same test in a project file.

No row breaks this rule — set is empty #

The rule is written as find the violations; the test passes when there are none, and when it fails the offending rows are in the message.

• Form view
• YAML

Tests:
- Name: No order without a customer
  Suite: Data quality
  Data source: DWH
  Query: |
    SELECT  o.OrderId, o.CustomerKey
    FROM    fact.Orders AS o
    WHERE   NOT EXISTS (SELECT 1 FROM dim.Customer AS c
                        WHERE c.CustomerKey = o.CustomerKey)
  Expectation: set is empty

There is data here — set is not empty #

The smoke test. One row is enough and CAT reads no more — TOP 1 keeps it free.

• Form view
• YAML

- Name: Yesterday's sales were loaded
  Suite: Smoke tests
  Data source: DWH
  Query: |
    SELECT  TOP 1 1
    FROM    fact.Sales
    WHERE   SaleDate = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
  Expectation: set is not empty

Exactly N rows — set row count #

For the counts you know: five regions, twelve months, the number of entities in a list.

• Form view
• YAML

- Name: All five regions are present
  Suite: Smoke tests
  Data source: DWH
  Query: |
    SELECT  DISTINCT Region
    FROM    dim.Store
  Expectation: set row count
  Expected row count: 5

These two sets are the same — sets match #

Two data sources, two technologies, one test. Both queries return the same columns in the same order, both sorted by the key.

• Form view
• YAML

- Name: Customers in the warehouse equal the CRM
  Suite: Reconciliation
  First data source: CRM
  First query: |
    SELECT  CustomerId, Email
    FROM    dbo.Customers
    ORDER BY CustomerId
  Second data source: DWH
  Second query: |
    SELECT  SourceId, Email
    FROM    dim.Customer
    WHERE   SourceSystem = 'CRM'
    ORDER BY SourceId
  Expectation: sets match
  Key: 1

Everything here is also there — contains #

When one side is allowed to hold more — a staging area that keeps deleted rows, a history table. The first set is the superset.

• Form view
• YAML

- Name: Every source order reached staging
  Suite: Reconciliation
  First data source: DWH
  First query: |
    SELECT  OrderId, Amount
    FROM    stage.Orders
    ORDER BY OrderId
  Second data source: ERP
  Second query: |
    SELECT  OrderId, Amount
    FROM    dbo.Orders
    ORDER BY OrderId
  Expectation: contains
  Key: OrderId

The principle behind the short list #

The expectations are few on purpose. CAT does not try to express your rule — the query does, in the language you already know and test against the real engine. An expectation only says what the result of that query must look like. So when you think “there is no expectation for …”, the answer is almost always to move the condition into the query and use set is empty — see My expectation does not exist.

Two of the five — sets match and contains — are the ones that compare across systems: two data sources, two technologies, one test. That is the pattern nobody else runs for you; it has its own page, Compare data across systems.

Related #

• Find problems with set is empty · Smoke tests with set is not empty · Test incremental loads
• Expectations — overview — the reference.