> ## Documentation Index
> Fetch the complete documentation index at: https://docs.streambuild.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Testing

> Model-chain tests, zero-row assertions, macro tests, and bag comparison.

StreamBuild discovers `tests/**/*.sql`, assembles each test as one ClickHouse query, and compares
actual and expected rows with duplicate-aware bag semantics.

## Model test

```sql theme={null}
TEST (name: "line total computes correctly");

WITH __ref__orders AS (
  SELECT 'ord_001' AS order_id, 2 AS quantity, 10.0 AS unit_price
),
__expected__order_items AS (
  SELECT 'ord_001' AS order_id, 20.0 AS line_total
)
SELECT 1
```

A model-mode test contains:

1. `TEST()` with an optional name
2. at least one source or model mock
3. at least one expected target or zero-row assertion
4. a ceremonial final `SELECT 1`

## CTE roles

| Prefix                | Purpose                          |
| --------------------- | -------------------------------- |
| `__source__<name>`    | Mock `__source("name")`          |
| `__ref__<name>`       | Mock `__ref("name")`             |
| `__expected__<model>` | Expected rows for a model target |
| `__assert__<name>`    | Query that must return zero rows |

Arbitrary helper CTEs are allowed. A mock cuts dependency assembly at that relation; dependencies
without a mock are assembled from the real compiled model graph. Multiple expected targets are
executed in dependency order.

## Zero-row assertion

```sql theme={null}
TEST (name: "line totals are non-negative");

WITH __ref__orders AS (
  SELECT 'ord_001' AS order_id, 2 AS quantity, 10.0 AS unit_price
),
__assert__no_negative_totals AS (
  SELECT order_id
  FROM __ref("order_items")
  WHERE line_total < 0
)
SELECT 1
```

## Expected values

Expected CTEs may project only the columns under test. StreamBuild casts those values to the exact
compiled target types before comparison.

Comparison is bidirectional and NULL-safe:

* duplicate multiplicity matters
* missing and unexpected rows are both reported
* NULL equals NULL through ClickHouse `isNotDistinctFrom`

## Macro test

Use macro mode to test an otherwise standalone project macro:

```sql theme={null}
TEST (mode: macro, name: "line total expression handles nulls");

WITH input_values AS (
  SELECT 2 AS quantity, 10.0 AS unit_price
),
__macro_actual__ AS (
  SELECT @line_total_expression("quantity", "unit_price") AS line_total
  FROM input_values
),
__macro_expected__ AS (
  SELECT 20.0 AS line_total
)
SELECT 1
```

Macro calls are allowed in `__macro_actual__`, not in macro expected or helper CTEs.

## Multiple blocks

A file may contain multiple `TEST` blocks. Every block in a multi-test file must have a unique
`name`. A single block may remain unnamed.

## Run tests

```bash theme={null}
stb test
stb test tests/order_events/test_line_total.sql
stb test tests/order_events/
stb test --select order_items
stb test --select +order_items+
stb test --verbose
```

Test selectors support bare model names, `pipeline:<name>`, and optional graph operators:

| Selector        | Included target tests                |
| --------------- | ------------------------------------ |
| `order_items`   | the selected model                   |
| `+order_items`  | selected model and upstream models   |
| `order_items+`  | selected model and downstream models |
| `+order_items+` | selected model in both directions    |

Runtime SQL is written byte-for-byte under `target/run/tests/`. One warehouse execution error is
reported as `ERROR` without preventing the remaining tests from running.

Unsupported test surfaces include authored seeds, dbt refs, UDFs, table functions, and scenario
mode.