--- title: "Which expectation when" description: "The five expectations and the question each one answers — one page to decide, with an example of each as you would fill it in CAT Studio and as YAML" url: "https://docs.justcat.it/how-to-guides/test-patterns/which-expectation-when/" --- # Which expectation when | You want to prove… | Expectation | Queries | Shape of the query | |---|---|---|---| | *no row breaks this rule* | [`set is empty`](https://docs.justcat.it/reference/tests/expectations/set-is-empty/ "Set is empty") | 1 | `SELECT … WHERE ` — the offending rows | | *there is data here* | [`set is not empty`](https://docs.justcat.it/reference/tests/expectations/set-is-not-empty/ "Set is not empty") | 1 | `SELECT TOP 1 … FROM ` (or a partition, a day) | | *exactly N rows* | [`set row count`](https://docs.justcat.it/reference/tests/expectations/set-row-count/ "Set row count") | 1 + the number | `SELECT DISTINCT FROM …` with *Expected row count* N | | *these two sets are the same* | [`sets match`](https://docs.justcat.it/reference/tests/expectations/sets-match/ "Sets match") | 2 | the same columns from both sides, both `ORDER BY` the key | | *everything here is also there* | [`contains`](https://docs.justcat.it/reference/tests/expectations/contains/ "Contains") | 2 | same as sets match; the first set may hold more | 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. **Properties** Name : No order without a customer Suite : Data quality Data source : DWH Query : ```sql 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 **YAML** ```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. **Properties** Name : Yesterday's sales were loaded Suite : Smoke tests Data source : DWH Query : ```sql SELECT TOP 1 1 FROM fact.Sales WHERE SaleDate = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) ``` Expectation : set is not empty **YAML** ```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. **Properties** Name : All five regions are present Suite : Smoke tests Data source : DWH Query : ```sql SELECT DISTINCT Region FROM dim.Store ``` Expectation : set row count Expected row count : 5 **YAML** ```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. **Properties** Name : Customers in the warehouse equal the CRM Suite : Reconciliation First data source : CRM First query : ```sql SELECT CustomerId, Email FROM dbo.Customers ORDER BY CustomerId ``` Second data source : DWH Second query : ```sql SELECT SourceId, Email FROM dim.Customer WHERE SourceSystem = 'CRM' ORDER BY SourceId ``` Expectation : sets match Key : 1 **YAML** ```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. **Properties** Name : Every source order reached staging Suite : Reconciliation First data source : DWH First query : ```sql SELECT OrderId, Amount FROM stage.Orders ORDER BY OrderId ``` Second data source : ERP Second query : ```sql SELECT OrderId, Amount FROM dbo.Orders ORDER BY OrderId ``` Expectation : contains Key : OrderId **YAML** ```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](https://docs.justcat.it/how-to-guides/test-patterns/my-expectation-does-not-exist/ "My expectation does not exist"). > **🐱:** **Let an assistant write the query.** Turning a rule into a violation query is exactly what [CAT Pilot](https://docs.justcat.it/reference/cat-studio/cat-pilot/ "CAT Pilot") in CAT Studio — or any AI assistant — is good at: describe the rule in a sentence, name the data source, and you get the query and the expectation to go with it. Run it once, read the result, then save it as a test. 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](https://docs.justcat.it/how-to-guides/test-patterns/compare-data-across-systems/ "Compare data across systems"). ## Related * [Find problems with set is empty](https://docs.justcat.it/how-to-guides/test-patterns/find-problems-with-set-is-empty/ "Find problems with set is empty") · [Smoke tests with set is not empty](https://docs.justcat.it/how-to-guides/test-patterns/smoke-tests-with-set-is-not-empty/ "Smoke tests with set is not empty") · [Test incremental loads](https://docs.justcat.it/how-to-guides/test-patterns/test-incremental-loads/ "Test incremental loads") * [Expectations — overview](https://docs.justcat.it/reference/tests/expectations/overview/ "Expectations overview") — the reference.