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 (multi-model tests), use macros for reusable mock data, and include zero-row assertions. For end-to-end testing across many models with physical warehouse relations, see 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.

-- 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:

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

When an expected-output comparison fails, SQLBuild reports the unexpected and missing row counts and best-effort samples from each direction. Samples are deliberately bounded to three rows, 12 columns, and 120 characters per value, and pass through diagnostic redaction. They are also present as structured unexpected_samples and missing_samples in JSON output. Sampling failure never replaces the known test failure. When exactly one row exists in each difference direction and the bounded samples have the same columns, SQLBuild also aligns the rows and reports only changed columns with their redacted actual and expected values. It does not guess an alignment when several rows differ or sampling is incomplete. Before opening a warehouse connection, SQLBuild statically checks fixture shapes when SQL analysis is enabled. Missing columns are reported together with the test path, fixture resource, and models that read them. Statically provable collection-versus-scalar type conflicts identify the affected column and suggest explicit CAST, ARRAY_CONSTRUCT, or PARSE_JSON expressions. SQLBuild never invents missing values. 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.

Multi-model tests

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.

-- 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. Inspect that boundary without opening a warehouse connection:

sqb test --select fact_orders --inspect

The resolved plan lists mocked sources, refs, seeds, real models in execution order, expected models, and any unsatisfied leaf dependencies. A mocked ref is called out explicitly because its real model SQL will not execute. Inspection exits non-zero when the plan contains unresolved dependency errors.

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:

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:

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. When a test defines __expected__model_name output, it may also use the public enums and constants available to that model. A test that checks several models may use the public enums and constants available to each of them. Public names are unique, so those values cannot conflict. Only explicit expected CTEs create grants. A matching test filename, __ref__ mock, or nearby model path does not. Model-private declarations and macros are never granted through expected models.

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:

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. Test SQL uses macros, enums, and constants available from the test file’s directory under tests/unit/. Public enums and constants available to a model are also available when the test defines __expected__model_name output for that model. Model-private values are not available to tests. See How Visibility Works.

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:

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.

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__:

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__:

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__:

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:

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.

Repeating test logic

SQLBuild supports three repetition patterns:

Pattern	Use when	Result identity
Multiple `TEST` blocks	Cases need different SQL shapes	One result per block
Macro or `VALUES` case table	One aggregate comparison is sufficient	One result for the table
Native parameters and cases	Cases share a template but need independent status	One result per named case

Native independent cases

Declare a typed schema and ordered named cases in one header:

TEST (
  name "order_status_maps_source_states",
  parameters (
    source_status string,
    expected_status string,
  ),
  cases (
    completed (source_status "completed", expected_status "completed"),
    cancelled (source_status "cancelled", expected_status "excluded"),
    pending (source_status "pending", expected_status "open"),
  ),
);

WITH
__source__raw__orders AS (
  SELECT 1 AS id, @param("source_status") AS status
),
__expected__stg_orders AS (
  SELECT 1 AS id, @param("expected_status") AS status
)
SELECT 1

The cases report independently as order status: maps source states [completed], [cancelled], and [pending]. Authored order controls display order. Selecting stg_orders selects every case by default. Add --case to run one named case without changing the test file:

sqb test --select stg_orders --case cancelled

If the case does not exist within the parent/model selection, SQLBuild reports the available case names. Supported scalar types are string, integer, boolean, float, and exact decimal. Decimal values are quoted, such as tax_rate "0.2000", so they never pass through binary float. Boolean and integer remain distinct. Raw SQL and collection parameters are rejected. Nullable parameters use an expanded declaration and are valid where SQL can infer the null type:

parameters (
  cancellation_reason (type string, nullable true),
),
cases (
  completed (cancellation_reason null),
)

Every case provides exactly the declared parameters, and every declaration must appear as @param("name") in the template. SQLBuild rejects missing, extra, incompatible, undeclared, and unused values before execution. Expansion is deterministic:

parse TEST template and cases
  -> render typed @param values with the active adapter
  -> expand @const and @enum references
  -> expand macros
  -> validate SQL and compile expected models/assertions

Values can therefore feed macro arguments, for example @order_fixture(@param("source_status")). Parameters work in model, macro, UDF, and table-function modes and preserve every expected model in multi-model tests. Text and JSON output include parent/case identity and safe typed values. Compile JSON, manifests, DAG checks, and compiled/runtime SQL artifacts retain source, block, case, and content-fingerprint provenance. Changing one case changes that case’s fingerprint without reassigning another case’s stable identity. Scenarios are intentionally not parameterized. Keep one scenario per coherent business world so capture and replay identity remains explicit.

Aggregate SQL case tables

When independent status is unnecessary, a normal SQL case table remains concise:

WITH cases(case_name, source_status, expected_status) AS (
  VALUES
    ('completed', 'completed', 'completed'),
    ('cancelled', 'cancelled', 'excluded')
),
__expected__status_mapping AS (
  SELECT case_name, expected_status FROM cases
)
SELECT 1

This produces one aggregate test result. Use native cases when each row must pass or fail independently. Quoted strings in VALUES rows may contain commas, brackets, and parentheses. SQLBuild preserves these literals while extracting test CTEs; use normal SQL quote escaping for embedded quotes.

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      # multi-model test
    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:

sqb test

Scope to specific models:

sqb test --select stg_orders

Getting Started

dbt Compatibility

Concepts

Advanced Concepts

Python Nodes

Virtual Environments (Alpha)

Integrations

CLI Reference

How tests work

CTE conventions

Multi-model tests

Mocking refs and seeds

Multiple expected models

Macro-powered mocks

Macro mocking

Assertions

Test modes

Macro tests

UDF tests

Table function tests

Mode rules

Multiple tests per file

Repeating test logic

Native independent cases

Aggregate SQL case tables

Test placement

Running tests

​How tests work

​CTE conventions

​Multi-model tests

​Mocking refs and seeds

​Multiple expected models

​Macro-powered mocks

​Macro mocking

​Assertions

​Test modes

​Macro tests

​UDF tests

​Table function tests

​Mode rules

​Multiple tests per file

​Repeating test logic

​Native independent cases

​Aggregate SQL case tables

​Test placement

​Running tests

How tests work

CTE conventions

Multi-model tests

Mocking refs and seeds

Multiple expected models

Macro-powered mocks

Macro mocking

Assertions

Test modes

Macro tests

UDF tests

Table function tests

Mode rules

Multiple tests per file

Repeating test logic

Native independent cases

Aggregate SQL case tables

Test placement

Running tests