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:

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

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.
__table_fn__<name>	Mock a table function in model mode. Replaces the complete __table_fn("<name>")(...) invocation with the fixture relation.
__expected__<name>	Define expected output for a model. SQLBuild resolves the model’s real SQL and compares both ways on the listed columns.
__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 mock, model, __expected__, and __assert__ SQL in the test.

An __expected__<model> CTE compares only the columns it lists, matched by name, so column order does not matter and unlisted model columns are ignored. Row counts are always compared. Listing a column the model does not output is a test error. When the expected CTE’s columns are not explicit (for example SELECT * from another CTE), SQLBuild compares every column by position.

Helper CTEs can read other helpers and mocks by CTE name, such as __ref__stg_orders. Shared expected rows can therefore live in one helper that both an __expected__ CTE and an assertion read:

TEST();

WITH
__source__raw__orders AS (
  SELECT 1 AS id, 3 AS quantity UNION ALL SELECT 2 AS id, 5 AS quantity
),
expected_orders AS (
  SELECT 1 AS order_id, 3 AS quantity UNION ALL SELECT 2 AS order_id, 5 AS quantity
),
__expected__stg_orders AS (
  SELECT order_id, quantity FROM expected_orders
),
__assert__no_unexpected_order_ids AS (
  SELECT order_id FROM __ref("stg_orders")
  EXCEPT
  SELECT order_id FROM expected_orders
)
SELECT 1

SQLBuild emits each helper and mock a query needs once, in dependency order, including mocks that a helper reaches through another mock.

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.

Mocking table functions in model tests

Use __table_fn__<name> when the model under test reads a managed table function but the test should provide rows directly. SQLBuild replaces the complete invocation, including its argument expressions, with the fixture relation. The mocked function is not deployed as a test dependency.

-- models/customer_order_totals.sql reads:
-- __table_fn("customer_orders")(42)
TEST();

WITH
fixture_orders AS (
  SELECT 101 AS order_id, 42 AS customer_id, 2500 AS amount_cents
),
__table_fn__customer_orders AS (
  SELECT order_id, customer_id, amount_cents FROM fixture_orders
),
__expected__customer_order_totals AS (
  SELECT 42 AS customer_id, 2500 AS total_cents
)
SELECT 1

The fixture name must match a declared table function. Helper CTEs are available inside the fixture, just as they are for source, ref, and seed mocks. A table-function fixture can also be used directly inside a zero-row assertion.

This is separate from TEST (mode table_fn), which tests the table function implementation itself. Model-mode fixtures isolate a model from that implementation; table-function mode executes and compares the real function.

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.

Cursor windows in tests

Models that call __cursor_start() and __cursor_end() run in a test over one wide window, never the model’s configured cursor_start and cursor_end: 1900-01-01 00:00:00 to 2999-12-31 00:00:00 for timestamp cursors, and -1000000000000000 to 1000000000000000 for integer cursors. The bounds render as the same typed literals as a real run, so date arithmetic such as DATEADD('day', -7, __cursor_start()::DATE) keeps working and mocked rows are not filtered out. Microbatch models run once over the whole window, not once per batch.

To test windowing itself, declare the window in the header. Either key is optional. As in model configuration, cursor_start is inclusive and cursor_end is exclusive:

TEST (name "daily_orders_skips_rows_outside_window", cursor_start "2026-02-01", cursor_end "2026-02-03");

The window applies to every model the test evaluates that uses the intrinsics. Values are checked against each model’s cursor_type and cursor_grain at compile time. Invalid, misaligned, or inverted bounds, and a window on a test whose models do not use the intrinsics, are compile errors. The compiled test SQL shows the rendered window.

Empty-input tests

Do not write a test whose mocks are all empty (WHERE FALSE, WHERE 1 = 0, LIMIT 0, or __EMPTY_FIXTURE()) and whose only checks are an empty __expected__ CTE or a bare SELECT ... FROM __ref("<model>") assertion. It cannot fail for a model whose rows come from its inputs. Rule SQBRTEST203 rejects it, and SQBRTEST202 does not count it toward min_tests_per_model. Mock representative rows and assert concrete output instead.

An empty-input test is legitimate when it asserts concrete output, for example a global aggregate that must return one zero-valued summary row:

TEST (name "order_summary__empty_inputs_return_zero_row");

WITH
__source__raw__orders AS (
  SELECT CAST(NULL AS INTEGER) AS id, CAST(NULL AS INTEGER) AS amount WHERE FALSE
),
__expected__order_summary AS (
  SELECT 0 AS order_count, 0 AS total_amount
)
SELECT 1

Assertions that filter for invalid rows, compare against expected rows, aggregate, or limit their result are not treated as bare existence checks. Reviewed exceptions are listed by test name in the project file; see Rule options.

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