SQL Testing

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

Model test

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:

TEST() with an optional name
at least one source or model mock
at least one expected target or zero-row assertion
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

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:

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

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.

Getting Started

Core Concepts

CLI Reference

Examples

Model test

CTE roles

Zero-row assertion

Expected values

Macro test

Multiple blocks

Run tests

​Model test

​CTE roles

​Zero-row assertion

​Expected values

​Macro test

​Multiple blocks

​Run tests

Model test

CTE roles

Zero-row assertion

Expected values

Macro test

Multiple blocks

Run tests