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

# Testing

> SQL unit tests with model chaining, macro support, assertions, and multi-model integration testing.

SQLBuild supports SQL-native unit tests that validate model logic by comparing actual query results against expected values. Tests can chain across multiple models, use macros for reusable mock data, include zero-row assertions, and run as full integration tests across your pipeline.

For end-to-end testing across many models with physical warehouse relations, see [Scenarios](/concepts/scenarios).

## How tests work

A test file defines mock inputs and expected outputs using CTEs. SQLBuild substitutes the mock data into the real model SQL, executes it, and compares the result against the expected CTE using `EXCEPT` queries. Zero mismatched rows means the test passes.

```sql theme={null}
-- tests/unit/test_stg_orders.sql
TEST();

WITH
__source__raw__orders AS (
  SELECT
    1 AS id,
    100 AS customer_id,
    2 AS waffle_type_id,
    3 AS quantity,
    '2026-04-01 10:00:00' AS ordered_at,
    'completed' AS status
),
__expected__stg_orders AS (
  SELECT
    1 AS order_id,
    100 AS customer_id,
    2 AS waffle_type_id,
    3 AS quantity,
    '2026-04-01 10:00:00' AS ordered_at,
    'completed' AS status
)
SELECT 1
```

The test:

1. Mocks the `raw__orders` source with the `__source__raw__orders` CTE
2. Runs the real `stg_orders` model SQL with the mock substituted in
3. Compares the output against `__expected__stg_orders`
4. Passes if row counts match and there are zero mismatched rows

The trailing `SELECT 1` is required as a ceremonial closing statement.

## CTE conventions

| Prefix               | Purpose                                                                                               |
| -------------------- | ----------------------------------------------------------------------------------------------------- |
| `__source__<name>`   | Mock a source. Replaces `__source("<name>")` in the model SQL.                                        |
| `__ref__<name>`      | Mock a model. Replaces `__ref("<name>")` in the model SQL.                                            |
| `__seed__<name>`     | Mock a seed. Replaces `__seed("<name>")` in the model SQL.                                            |
| `__expected__<name>` | Define expected output for a model. SQLBuild resolves the model's real SQL and compares against this. |
| `__assert__<name>`   | Zero-row assertion. Passes if the query returns no rows; fails with the returned rows as diagnostics. |
| `__macro__<name>`    | Mock a macro. Replaces every `@<name>(...)` call with the mock value.                                 |

Any CTE without one of these prefixes is treated as a helper CTE, available to all mock and model SQL in the test.

## Chaining across models

Tests can span multiple models in a single file. Mock your sources, define an expected output for the model you care about, and SQLBuild automatically resolves every intermediate model using its real SQL.

```sql theme={null}
-- Mock two sources, assert on the final mart.
-- stg_orders and stg_payments resolve automatically from their real SQL.
TEST();

WITH
__source__raw__orders AS (
  SELECT 1 AS id, 100 AS customer_id, 2 AS waffle_type_id, 3 AS quantity,
         '2026-04-01 10:00:00' AS ordered_at, 'completed' AS status
),
__source__raw__payments AS (
  SELECT 1 AS payment_id, 1 AS order_id, 1500 AS amount_cents,
         'credit_card' AS method, '2026-04-01 10:01:00' AS paid_at, 'success' AS status
),
__expected__fact_orders AS (
  SELECT 1 AS order_id, 100 AS customer_id, 1500 AS payment_amount_cents,
         'credit_card' AS payment_method
)
SELECT 1
```

SQLBuild topologically sorts the expected models, resolves each intermediate model's real SQL with mocks substituted, and chains the outputs forward. Every model between the mocked sources and the expected model is computed automatically.

## Mocking refs and seeds

You can mock models directly with `__ref__<name>` and seeds with `__seed__<name>`, not just sources. This skips the model's real SQL (or the seed's real CSV data) and provides controlled data instead:

```sql theme={null}
TEST();

WITH
__ref__stg_orders AS (
  SELECT
    1 AS order_id,
    100 AS customer_id,
    2 AS waffle_type_id,
    3 AS quantity,
    CAST('2026-04-01 10:00:00' AS TIMESTAMP) AS ordered_at,
    'completed' AS status
),
__ref__stg_payments AS (
  SELECT
    10 AS payment_id,
    1 AS order_id,
    2850 AS amount_cents,
    'card' AS payment_method,
    CAST('2026-04-01 10:05:00' AS TIMESTAMP) AS paid_at,
    'success' AS payment_status
),
__seed__waffle_types AS (
  SELECT
    2 AS waffle_type_id,
    'Liege' AS waffle_name,
    'sweet' AS category,
    950 AS price_cents
),
__expected__fact_orders AS (
  SELECT
    1 AS order_id,
    100 AS customer_id,
    2 AS waffle_type_id,
    'Liege' AS waffle_name,
    'sweet' AS waffle_category,
    3 AS quantity,
    2850 AS line_total_cents,
    CAST('2026-04-01 10:00:00' AS TIMESTAMP) AS ordered_at,
    'completed' AS order_status,
    TRUE AS is_completed_order,
    'card' AS payment_method,
    'success' AS payment_status,
    2850 AS payment_amount_cents
)
SELECT 1
```

You can mix `__source__`, `__ref__`, and `__seed__` mocks in the same test. As long as every leaf dependency is satisfied (either by a source mock, a ref mock, a seed mock, or by being in the expected chain), the test resolves.

## Multiple expected models

A single test can assert on multiple models. SQLBuild resolves and compares each one independently:

```sql theme={null}
TEST();

WITH
__source__raw__orders AS (
  SELECT 1 AS id, 100 AS customer_id, 2 AS waffle_type_id, 3 AS quantity,
         '2026-04-01 10:00:00' AS ordered_at, 'completed' AS status
),
__source__raw__payments AS (
  SELECT 1 AS payment_id, 1 AS order_id, 1500 AS amount_cents,
         'credit_card' AS method, '2026-04-01 10:01:00' AS paid_at, 'success' AS status
),
__source__raw__customers AS (
  SELECT 100 AS id, 'Leslie' AS first_name, 'Knope' AS last_name,
         'leslie@pawnee.gov' AS email, '2026-01-15 09:00:00' AS created_at
),
__expected__fact_orders AS (
  SELECT 1 AS order_id, 100 AS customer_id, 1500 AS payment_amount_cents,
         'credit_card' AS payment_method
),
__expected__dim_customers AS (
  SELECT 100 AS customer_id, 1 AS total_orders, 1500 AS lifetime_spend_cents
)
SELECT 1
```

If the expected models form a chain (e.g. `stg_orders` feeds into `fact_orders` which feeds into `dim_customers`), SQLBuild resolves them in dependency order, using the output of earlier steps as input to later ones.

## Macro-powered mocks

Because unit tests are written in SQL, they support macro calls. This lets you write reusable mock generators instead of copy-pasting mock data across test files:

```sql theme={null}
TEST();

WITH
__source__raw__orders AS (
  @mock_orders()
),
__source__raw__payments AS (
  SELECT 1 AS payment_id, 1 AS order_id, 1500 AS amount_cents, 'credit_card' AS method
),
__expected__fact_orders AS (
  SELECT 1 AS order_id, 100 AS customer_id, 1500 AS total_cents,
         'credit_card' AS payment_method
)
SELECT 1
```

The `@mock_orders()` call expands at compile time to whatever SQL the Python macro function returns.

## Macro mocking

When a model uses macros that you want to control in tests (e.g. target-specific logic, dynamic SQL generation), you can override their output with `__macro__<name>` CTEs:

```sql theme={null}
TEST();

WITH
__macro__country_filter AS (
  SELECT 'country_code = ''US'''
),
__source__raw__orders AS (
  SELECT 1 AS id, 100 AS customer_id, 'US' AS country_code, 'completed' AS status
),
__expected__stg_orders AS (
  SELECT 1 AS order_id, 100 AS customer_id, 'completed' AS status
)
SELECT 1
```

When a `__macro__` mock is defined, every call to `@country_filter(...)` in any model SQL resolved by the test is replaced with the mock value (`country_code = 'US'`). The macro's actual Python function is not called, and the arguments are ignored.

The mock value must be a single `SELECT` with one string literal. Use doubled single quotes for quotes within the value (standard SQL escaping).

This is useful for:

* Testing models that use target-specific macros without depending on target config
* Controlling dynamic SQL generation to produce predictable test inputs
* Isolating model logic from macro implementation details

## Assertions

Unit tests can include `__assert__<name>` CTEs for property-based checks. An assertion passes if the query returns zero rows - any returned rows are failing examples.

```sql theme={null}
TEST();

WITH
__ref__stg_orders AS (
  SELECT 1 AS order_id, 100 AS customer_id, 3 AS quantity,
         CAST('2026-04-01 10:00:00' AS TIMESTAMP) AS ordered_at, 'completed' AS status
),
__assert__order_ids_are_not_null AS (
  SELECT * FROM __ref("stg_orders") WHERE order_id IS NULL
)
SELECT 1
```

Assertions can be mixed with `__expected__` CTEs in the same test, or used on their own. They are useful when the natural check is "no rows should violate this rule" rather than "the output should exactly equal these rows" - for example, duplicate checks, negative-value constraints, or conditional business rules.

During `sqb test` and `sqb build`, assertion results appear as nested check rows alongside expected comparisons.

## Test modes

By default, `TEST()` runs in model mode - mocking sources/refs and comparing model outputs. Three additional modes let you test reusable logic directly without needing a model chain.

### Macro tests

Test macro output by calling the macro in `__macro_actual__` and comparing against `__macro_expected__`:

```sql theme={null}
TEST (mode: macro, name: "calculates line total cents");

WITH
input_values AS (
  SELECT 950 AS price_cents, 3 AS quantity
),
__macro_actual__ AS (
  SELECT @line_total_cents("price_cents", "quantity") AS line_total_cents
  FROM input_values
),
__macro_expected__ AS (
  SELECT 2850 AS line_total_cents
)
SELECT 1
```

Macros are compile-time code, so macro tests expand the macro at compile time and compare the results. During `sqb build`, macro tests run before any model that uses the tested macro.

### UDF tests

Test scalar UDFs by calling them in `__udf_actual__` and comparing against `__udf_expected__`:

```sql theme={null}
TEST (mode: udf, name: "detects completed orders");

WITH
input_values AS (
  SELECT 'completed' AS order_status
  UNION ALL
  SELECT 'pending' AS order_status
),
__udf_actual__ AS (
  SELECT
    order_status,
    __udf("udf__is_completed_order")(order_status) AS is_completed_order
  FROM input_values
),
__udf_expected__ AS (
  SELECT 'completed' AS order_status, TRUE AS is_completed_order
  UNION ALL
  SELECT 'pending' AS order_status, FALSE AS is_completed_order
)
SELECT 1
```

UDFs are warehouse objects, so the function is created before the test runs. During `sqb build`, UDF tests run after the function is created but before any model that uses it.

### Table function tests

Test table functions by calling them in `__table_fn_actual__` and comparing against `__table_fn_expected__`:

```sql theme={null}
TEST (mode: table_fn, name: "returns customer orders");

WITH
__table_fn_actual__ AS (
  SELECT order_id, order_status, is_completed_order
  FROM __table_fn("table_fn__customer_orders")(1)
),
__table_fn_expected__ AS (
  SELECT 1 AS order_id, 'completed' AS order_status, TRUE AS is_completed_order
  UNION ALL
  SELECT 2 AS order_id, 'completed' AS order_status, TRUE AS is_completed_order
)
SELECT 1
```

Table function tests run after the function is created. Since table functions are terminal (models cannot depend on them), these tests validate the function independently.

### Mode rules

Each mode has strict CTE validation:

| Mode              | Actual CTE                  | Expected CTE            | Allowed                                                        |
| ----------------- | --------------------------- | ----------------------- | -------------------------------------------------------------- |
| `model` (default) | existing model-chain syntax | `__expected__<model>`   | `__source__`, `__ref__`, `__seed__`, `__assert__`, `__macro__` |
| `macro`           | `__macro_actual__`          | `__macro_expected__`    | Helper CTEs, `@macro()` calls in actual                        |
| `udf`             | `__udf_actual__`            | `__udf_expected__`      | Helper CTEs, `__udf()` calls in actual                         |
| `table_fn`        | `__table_fn_actual__`       | `__table_fn_expected__` | Helper CTEs, `__table_fn()` calls in actual                    |

CTE prefixes from other modes are not allowed. For example, `__source__` in a macro test or `__macro_actual__` in a model test will produce a clear error pointing you to the right mode. Expected CTEs must not call macros, UDFs, or table functions - they should be independent, inspectable expected data.

## Multiple tests per file

A single test file can contain multiple `TEST()` blocks. Each block must have a unique `name`:

```sql theme={null}
TEST (name: "completed orders only");

WITH
__source__raw__orders AS (
  SELECT 1 AS id, 100 AS customer_id, 'completed' AS status
),
__expected__stg_orders AS (
  SELECT 1 AS order_id, 100 AS customer_id, 'completed' AS status
)
SELECT 1

TEST (name: "cancelled orders excluded");

WITH
__source__raw__orders AS (
  SELECT 1 AS id, 100 AS customer_id, 'cancelled' AS status
),
__expected__stg_orders AS (
  SELECT 1 AS order_id, 100 AS customer_id, 'cancelled' AS status
)
SELECT 1
```

A file with a single test can omit the `name` field. Files with multiple tests require names on every block.

## Test placement

Place unit test files under `tests/unit/` in your project directory. SQLBuild discovers all `.sql` files in this directory recursively.

```
tests/
  unit/
    test_stg_orders.sql              # model test
    test_fact_orders.sql              # model test with assertion
    test_daily_revenue_chain.sql      # chain test across multiple models
    test_line_total_cents_macro.sql   # macro test
    test_is_completed_order_udf.sql   # UDF test
    test_customer_orders_table_fn.sql # table function test
  scenarios/
    ...
```

## Running tests

Tests run automatically during `sqb build` in DAG order, before their target model is materialized.

Run tests standalone:

```bash theme={null}
sqb test
```

Scope to specific models:

```bash theme={null}
sqb test --select stg_orders
```