Testing

A unit test checks a model’s logic on rows you write. You mock the model’s inputs with CTEs, SQLBuild runs the model’s real SQL over them, and the test passes when the output matches the rows you expect:

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

Tests can chain several models, mock models, seeds, macros and table functions, assert properties that must hold for every row, and test macros, UDFs and table functions directly. For end-to-end tests over physical warehouse relations, see Scenarios.

SQL tests are planned with SQLBuild’s native parser for the adapter’s SQL analysis dialect. On an adapter whose dialect has no native parser, including a custom adapter without sql_analysis_dialect_name, a project with SQL tests fails to plan them, with a message that says SQL tests are unavailable for that dialect.

How tests work

The test above:

1. mocks the raw__orders source with the __source__raw__orders CTE;
2. runs the real stg_orders SQL with the mock in place of the source;
3. compares the output with __expected__stg_orders in both directions;
4. passes when the row counts match and no row differs.

The TEST header

Every test starts with a TEST() header. All of its fields are optional: TEST(); is a complete header, and a test without a name is named after its file. Fields name the test, set its mode or cursor window, declare parameters, set a time limit, or skip SQL analysis.

A test ends after its last CTE. A trailing SELECT 1 is accepted; nothing else may follow the last CTE.

Failure output

When the output doesn’t match, SQLBuild reports how many rows were unexpected and how many were missing, with samples of each. Samples are limited to three rows, 12 columns and 120 characters per value, and are redacted like other diagnostics. JSON output has them as unexpected_samples and missing_samples. If sampling fails, the test still fails with its counts.

When exactly one row differs in each direction and both samples have the same columns, SQLBuild lines the two rows up and shows only the changed columns, with their actual and expected values. With several differing rows it doesn’t guess which rows belong together.

target/run/tests/ keeps the complete comparison query of each test, which you can run by hand.

Fixture checks

When SQL analysis is on, SQLBuild checks fixture shapes before it connects to the warehouse:

• Missing columns are reported together, with the test path, the fixture and the models that read them.
• A collection value where a scalar is expected, or the reverse, names the column and suggests an explicit CAST, ARRAY_CONSTRUCT or PARSE_JSON.

SQLBuild never invents missing values, except the typed nulls described in Fixture columns.

Test files

Put unit tests under tests/unit/. SQLBuild finds every .sql file there, 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/
    ...

Multiple tests per file

A file can hold several TEST() blocks. Each block in such a file needs its own name; a file with one test can leave it out.

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
)

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
)

Test names are unique across the project: a test can’t share a name with another test, a scenario, a model, a source, a seed, a function or a Python node. Name a test after the behaviour it checks, such as orders_status_rules, rather than after the model. The cases of one parameterized test share its name.

CTE conventions

A CTE’s prefix says what it does:

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. Replaces the whole __table_fn("<name>")(...) call
__expected__<name>	Expected output of a model, compared in both directions on the listed columns
__assert__<name>	Assertion. Passes when the query returns no rows; returned rows are reported
__macro__<name>	Mock a macro. Replaces every @<name>(...) call with the mock value
any other name	Helper CTE, shared by the test’s mocks, helpers, __expected__ and __assert__ CTEs

An __expected__<model> CTE compares only the columns it lists, matched by name. Column order doesn’t matter and unlisted model columns are ignored, but row counts are always compared. Listing a column the model doesn’t output is an error. Scenario expected output is compared the same way.

Helper CTEs

Models never see helpers, so a helper can share a name with a model’s CTE or a table. Helpers are emitted as __helper__<name>, so __helper__ names are reserved. A CTE inside another test CTE must not redefine a helper’s name, and a model CTE must not use a name SQLBuild generates for the test query, such as __ref__orders or __helper__expected_rows.

Helpers can read other helpers and mocks by CTE name, such as __ref__stg_orders. Shared expected rows can therefore live in one helper that an __expected__ CTE and an assertion both 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
)

SQLBuild emits each helper and mock once, in dependency order, after the mocks and models it reads. A helper that no __expected__ or __assert__ CTE reaches, directly or through other helpers and mocks, is left out of the test query, and its references are neither checked nor run.

References in test CTEs

__expected__ and __assert__ CTEs, and the helpers they read, can call __ref(), __source(), __seed() and __dbt_ref(). They resolve the same way everywhere in a test:

• A mocked name means the mock. __ref("stg_orders") reads __ref__stg_orders when the test defines it. A mocked model isn’t run, and sqb test --select stg_orders doesn’t select the test because of it.
• An unmocked __ref("<model>") runs the model’s real SQL, and sqb test --select <model> selects the test, even when only a helper reads it.
• __source(), __seed() and __dbt_ref() must be mocked, for example with __source__raw__orders AS (SELECT ...).

Mocks are defined before the models a test runs, so a mock reads another mock by CTE name, such as FROM __ref__stg_orders, not through __ref("stg_orders"):

TEST();

WITH
__ref__stg_orders AS (
  SELECT 1 AS order_id, 3 AS quantity
),
order_totals AS (
  SELECT order_id, quantity FROM __ref("orders")
),
__assert__quantities_are_positive AS (
  SELECT order_id FROM order_totals WHERE quantity <= 0
)

Compile rejects references the test query can’t resolve with P013, at the CTE or call that causes it:

• a __ref() to a model that doesn’t exist;
• an unmocked __source(), __seed() or __dbt_ref();
• helpers or mocks that read each other in a cycle;
• a mock or __table_fn__ fixture the test uses that reads a helper calling a reference.

A malformed reference call in a helper or expected CTE, such as __source("raw", "orders"), is a P012 error at the call. Reference calls are found outside comments and quoted text, following the project adapter’s quoting and comment rules.

CTE syntax

SQLBuild reads test CTEs strictly. Other forms are compile errors that name the CTE:

Rule	Error example
A CTE name is an unquoted ASCII identifier: letters, digits and underscores, not starting with a digit	"recent orders", `recent orders`, names with $ or non-ASCII characters
The CTE body follows AS directly	AS MATERIALIZED (...), AS NOT MATERIALIZED (...)
Every column of an expected CTE has a name	amount IS NULL or CASE ... END without an alias
An expected CTE is a plain SELECT, optionally combined with UNION, INTERSECT or EXCEPT	Parenthesized branches, a WITH inside the CTE
An expected CTE lists its columns	SELECT *, SELECT DISTINCT *, t.*

Keywords such as WITH, AS and SELECT match in any case. Expected CTEs are __expected__<model>, __macro_expected__, __udf_expected__ and __table_fn_expected__. A column reference such as order_id or o.order_id names itself, and an alias without AS, such as 1 order_id, names the column too. For shared rows, use a helper CTE instead of a nested WITH. The one SELECT * allowed is SELECT * FROM __empty_fixture() in a contracted __expected__ CTE.

Multi-model tests

A test can span several models. Mock the sources, give the expected output of the model you care about, and SQLBuild runs every model in between from 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
)

Inspect what a test will run without connecting to the warehouse:

sqb test --select fact_orders --inspect

The plan lists the mocked sources, refs and seeds, the real models in execution order, the expected models, and any unsatisfied leaf dependencies. A mocked ref is called out, because its real SQL won’t run. Inspection exits non-zero when the plan has unresolved dependencies.

Each real model adds to the cost of the test; see Cost guards.

Mocking refs and seeds

Mock a model with __ref__<name> or a seed with __seed__<name>. The model’s SQL, or the seed’s CSV data, is then replaced by your rows:

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
)

__source__, __ref__ and __seed__ mocks can be mixed. The test resolves as long as every leaf dependency is mocked or is in the expected chain.

Fixture columns

A fixture can leave out a column the test needs when SQLBuild knows the column’s type and the column isn’t declared nullable false. SQLBuild fills it with a typed null, such as CAST(NULL AS VARCHAR), in the test only; it never changes model SQL or warehouse defaults. Supply these columns yourself:

• columns declared nullable false;
• columns whose type is unknown.

When a relation’s full column list is known, a misspelled or unknown fixture column is rejected.

A NULL AS column_name projection gets the relation’s declared type in the compiled test SQL, including in expected CTEs for contracted models. To mock a contracted relation with no rows, write SELECT * FROM __empty_fixture() as the whole body of a __ref__, __source__ or __seed__ fixture, or of a contracted __expected__ CTE. SQLBuild expands it to the relation’s typed columns with a false filter.

Mocking table functions in model tests

Use __table_fn__<name> when the model under test reads a managed table function and the test should supply the rows. SQLBuild replaces the whole call, arguments included, with the fixture. The mocked function isn’t deployed for the test.

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

The fixture name must match a declared table function. Fixtures can read helper CTEs, and an assertion can read a table-function fixture directly. To test the table function itself, use a table function test.

Multiple expected models

One test can check several models. Each is compared on its own:

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
)

When the expected models form a chain, such as stg_orders to fact_orders to dim_customers, they run in dependency order, each reading the output of the one before.

Macros, enums and constants in tests

Test SQL can call macros, so mock data can come from a reusable generator:

TEST();

WITH
__source__raw__orders AS (
  @py.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
)

@py.mock_orders() expands at compile time to the SQL the Python macro returns.

A test can use:

• the macros, enums and constants visible from its directory under tests/unit/;
• the public enums and constants visible to each model it defines __expected__ output for.

Only an __expected__ CTE grants that access. A matching file name, a __ref__ mock or a nearby model path doesn’t. Model-private values and macros are never available to tests. See How Visibility Works.

A macro used only by tests must be private to them. Put it in a _sqlbuild/_macros/ folder among the tests, such as tests/unit/orders/_sqlbuild/_macros/, or in the _sqlbuild/_macros/ of a model the tests define __expected__ output for. The project macros/ directory rejects a test-only macro with S024, which names the folder to use, and tests/unit/_sqlbuild/ is rejected with D013.

Macro mocking

To control a macro’s output in a test, such as target-specific logic or generated SQL, mock it with a __macro__<name> CTE:

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
)

Every @py.country_filter(...) call in the model SQL the test runs is replaced with country_code = 'US'. The macro isn’t called, and its arguments are ignored.

• The mock is a single SELECT of one string literal, optionally named with AS. Escape quotes by doubling them.
• SQL macros are mocked the same way: __macro__net_amount replaces each @sql.net_amount(...) call written in model SQL. A call inside another SQL macro’s body is expanded as usual.
• Macro names are unique across both kinds, so the mock name has no sql. or py. prefix.

Assertions

An __assert__<name> CTE checks a property. It passes when it returns no rows; each returned row is a failing example.

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
),
__assert__order_ids_are_not_null AS (
  SELECT * FROM __ref("stg_orders") WHERE order_id IS NULL
)

Use assertions when the natural check is “no row breaks this rule”, such as duplicates, negative values or conditional business rules. They can be mixed with __expected__ CTEs or used alone. sqb test and sqb build show each assertion as a nested check row.

An assertion must read a model the test runs. A test whose checks read only mocks, such as __ref("stg_orders") in a test that also defines __ref__stg_orders, has nothing to run, and compile rejects it with P013. Mock the model’s inputs instead, as above, or remove the test.

Cursor windows in tests

Models that call __cursor_start() and __cursor_end() run in a test over one wide window, never the model’s own cursor_start and cursor_end:

Cursor type	Window
Timestamp	1900-01-01 00:00:00 to 2999-12-31 00:00:00
Integer	-1000000000000000 to 1000000000000000

The bounds render as the same typed literals as in a real run, so date arithmetic such as DATEADD('day', -7, __cursor_start()::DATE) works and mocked rows aren’t filtered out. Microbatch models run once over the whole window.

To test windowing itself, set the window in the header. Either key is optional; cursor_start is inclusive and cursor_end 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 in the test that uses the cursor functions, and is checked against each model’s cursor_type and cursor_grain. Invalid, misaligned or inverted bounds, and a window on a test whose models don’t use the cursor functions, are compile errors. The compiled test SQL shows the rendered window.

Empty-input tests

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 can’t fail for a model whose rows come from its inputs. Rule SQBRTEST203 rejects it, and SQBRTEST202 doesn’t count it toward min_tests_per_model. Mock representative rows and assert concrete output instead.

An empty-input test is fine when it asserts concrete output, such as a global aggregate that must return one zero 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
)

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

Test modes

Model tests mock inputs and compare model output. Three more modes test reusable logic directly, without a model chain:

Mode	Actual CTE	Expected CTE	Also allowed
model	none: the model chain	__expected__<model>	__source__, __ref__, __seed__, __assert__, __macro__
macro	__macro_actual__	__macro_expected__	Helper CTEs, @py.name() and @sql.name() calls in actual, CTE-list @sql.name() calls in the WITH list
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

SQLBuild infers the mode from the actual CTE, so TEST (mode ...) is optional. An explicit mode that contradicts the CTEs is a compile error, and so is a prefix from another mode, such as __source__ in a macro test.

Only the actual CTE calls the macro, UDF or table function under test. Helpers don’t call it either, so a helper of a macro test must not call __udf() or __table_fn(). Expected CTEs must not call macros, UDFs or table functions, so the expected data stays independent and easy to read.

Macro tests

Call the macro in __macro_actual__ and give the result in __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 @py.line_total_cents("price_cents", "quantity") AS line_total_cents
  FROM input_values
),
__macro_expected__ AS (
  SELECT 2850 AS line_total_cents
)

Macros expand at compile time. During sqb build, a macro test runs before any model that uses the macro.

SQL macros are tested the same way. Call an expression macro in __macro_actual__:

TEST (mode macro, name "cents_to_dollars_rounds");

WITH
input_values AS (SELECT CAST(1250 AS BIGINT) AS amount_cents),
__macro_actual__ AS (
  SELECT @sql.cents_to_dollars(amount_cents) AS dollars FROM input_values
),
__macro_expected__ AS (SELECT CAST(12.5 AS DECIMAL(18, 2)) AS dollars)

Call a column-list macro as an item of the __macro_actual__ select list, and pass a helper CTE as each RELATION argument:

TEST (mode macro, name "audit_columns_flag_updates");

WITH
input_rows AS (
  SELECT TIMESTAMP '2026-01-01' AS created_at, TIMESTAMP '2026-01-02' AS updated_at
),
__macro_actual__ AS (SELECT @sql.audit_columns(input_rows) FROM input_rows),
__macro_expected__ AS (
  SELECT TIMESTAMP '2026-01-01' AS created_at, TIMESTAMP '2026-01-02' AS updated_at,
    TRUE AS was_updated
)

Call a CTE-list macro in the test’s WITH list, and select from one of its CTEs in __macro_actual__:

TEST (mode macro, name "clean_orders_keeps_latest_live");

WITH
input_orders AS (
  SELECT 1 AS order_id, TIMESTAMP '2026-01-01' AS updated_at, FALSE AS is_deleted
  UNION ALL SELECT 1, TIMESTAMP '2026-01-02', FALSE
  UNION ALL SELECT 2, TIMESTAMP '2026-01-01', TRUE
),
@sql.clean_orders(input_orders)
__macro_actual__ AS (SELECT order_id, updated_at FROM latest),
__macro_expected__ AS (SELECT 1 AS order_id, TIMESTAMP '2026-01-02' AS updated_at)

This is the only place outside __macro_actual__ where a macro test may call a macro. The call may pass __cte := name, as in a model, to call the macro more than once or to rename its CTEs. Other modes can’t call a CTE-list macro in their WITH list.

UDF tests

Call the UDF in __udf_actual__ and give the result in __udf_expected__:

TEST (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
)

During sqb build, a UDF test runs after the function is created and before any model that uses it.

Functions in sqb test

sqb test tests the current version of your functions without a build. Before the tests run, SQLBuild creates a temporary copy of every project UDF and table function the selected tests call, and of the functions those call. The tests call the copies, so an edited function is tested without sqb build, and the built function in the target schema is never created, replaced or dropped. The copies disappear when the test connections close.

Adapter	Temporary copy
DuckDB, MotherDuck	CREATE TEMP MACRO, called as temp.main.<name>. Python UDFs are registered on each test connection
PostgreSQL	SQL function in the session’s pg_temp schema, called as pg_temp.<name>
Snowflake	CREATE TEMPORARY FUNCTION __sqb_test__<name> in the session’s current schema, for SQL and Python UDFs and table functions
Databricks	CREATE TEMPORARY FUNCTION __sqb_test__<name> for SQL functions
BigQuery, SQL Server, ClickHouse	No copy; tests call the built function (ClickHouse functions are server-wide)
SQLite	Not applicable; SQLite has no SQL functions

When a copy can’t be made, because the adapter doesn’t support it (a Databricks Python UDF, for example) or the warehouse refuses (the role lacks permission, or the Snowflake session has no current schema), the test calls the built function. If that function isn’t built either, the test fails with an error naming the function and the reason, and asks you to run sqb build first. A function whose callee has no copy calls the built functions too, so a test never mixes copies and built functions.

Every test connection gets the same copies as the first one. If a later connection can’t create a copy, sqb test stops with T006, naming the function and the connection.

On PostgreSQL, the copies live in one server session. Run sqb test for projects with functions through a direct connection or a session-mode pool, not a transaction-mode connection pooler.

Table function tests

Call the table function in __table_fn_actual__ and give its rows in __table_fn_expected__:

TEST (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
)

Table function tests run after the function is created. Models can’t depend on table functions, so these tests check the function on its own.

Repeating test logic

Pattern	Use when	Results
Several TEST blocks	Cases need different SQL	One per block
A VALUES case table or macro	One comparison over all cases is enough	One for the table
Parameters and cases	Cases share a template but each needs its own result	One per named case

Native independent cases

Declare typed parameters and ordered, named cases in the 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
)

Each case reports on its own, in the order written: order status: maps source states [completed], [cancelled] and [pending]. Selecting stg_orders runs every case. Run one case with --case:

sqb test --select stg_orders --case cancelled

An unknown case name lists the available ones.

Parameter types are string, integer, boolean, float and exact decimal. Quote decimal values, such as tax_rate "0.2000", so they never pass through binary floating point. Booleans and integers stay distinct. Raw SQL and collection parameters are rejected.

A nullable parameter uses the expanded form, and works where SQL can infer the null’s type:

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

Every case gives exactly the declared parameters, and every parameter appears as @param("name") in the test. Missing, extra, incompatible, undeclared and unused values are rejected before anything runs.

Parameters are rendered first, as typed literals for the active adapter, then @const and @enum references, then macros. A parameter can therefore be a macro argument, as in @py.order_fixture(@param("source_status")). Parameters work in every mode and keep every expected model of a multi-model test.

Text and JSON output show the test, the case and its typed values. Compile JSON, manifests, DAG checks and compiled SQL record each case’s source, block and fingerprint. Changing one case changes only that case’s fingerprint.

Scenarios can’t be parameterized: keep one scenario per coherent business world.

Aggregate SQL case tables

When cases don’t need their own results, a VALUES table is shorter:

TEST();

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
)

This gives one test result. Quoted strings in VALUES rows may contain commas, brackets and parentheses; escape embedded quotes as usual in SQL.

Running tests

Tests run during sqb build in DAG order, before the model they test is built. Run them on their own with sqb test:

sqb test
sqb test --select stg_orders

Cost guards

A unit test runs the real SQL of every unmocked model between its mocks and the model under test as one warehouse statement. The warehouse compiles and optimises that whole chain at once, and the cost can grow much faster than the query’s size. SQLBuild bounds each test in two ways.

Real-model limit

[settings] unit_test_max_real_models (default 10) caps how many real models one test may run, counting the model under test. It can also be set in sqlbuild_local.toml. A test over the limit fails compilation, in sqb test and in sqb build when it runs tests, before any warehouse connection opens:

error[S000]: Invalid SQL test fixtures:
- SQL unit test 'customer_totals_rollup' executes 3 real models (limit 2).
  Test file:        tests/unit/customer_totals_rollup.sql
  Model under test: customer_totals

Real models executed, and the dependency path that pulls each one in:
  customer_totals  model under test
  order_totals     customer_totals -> order_totals
  stg_orders       customer_totals -> order_totals -> stg_orders

Options:
  1. Mock the direct inputs of the model under test to cut the chain:
       __ref__order_totals AS (SELECT ...)
  2. If this test checks behaviour across several models, consider a scenario
     (tests/scenarios/). A unit test inlines every real model into one statement,
     so the warehouse compiles and optimises the whole chain at once; a scenario
     builds each model step by step, compiling each statement separately.
  3. Raise the limit in sqlbuild_project.toml (or sqlbuild_local.toml):
       [settings]
       unit_test_max_real_models = 3

sqb test shows each test’s count on its row, such as 3 real models, so a growing chain is visible early. Macro, UDF and table function tests run no models and aren’t counted.

Time limits

Each unit test has a time limit, 30s by default. The first of these that is set applies:

1. --test-timeout on sqb test or sqb build
2. TEST(timeout "45s") in the test file
3. [targets.<target>.execution_limits] test_timeout
4. [settings] test_timeout, in sqlbuild_local.toml or sqlbuild_project.toml
5. The 30s default

Each takes a positive fixed duration, such as "45s" or "10m", or "disabled".

The clock starts at the test’s first warehouse statement. Rendering, warehouse queueing where the warehouse reports it, and cleanup after the comparison don’t count. See Per-item time limits for how each adapter enforces the limit, and Duration limit for how each adapter cancels a running statement.

At the limit, SQLBuild cancels the running statement, reports the test as TIMEOUT, and continues with the other tests. The report gives the elapsed time, the limit, where it was set, the cancellation outcome and the query IDs it saw. When the test runs real models beyond the model under test, it also lists them, with the same options as the real-model limit:

SQL unit test 'order_scans_kept' timed out after 1.0s (limit 1s).
  Limit set by:  TEST() timeout in tests/unit/order_scans_kept.sql
  Cancellation:  confirmed; the running statement stopped

This test inlines 1 real model beyond the model under test into one statement:
  stg_orders

Options:
  1. Mock the inlined models closest to the model under test to cut the chain,
     for example __ref__stg_orders AS (SELECT ...).
  2. If this test checks behaviour across several models, consider a scenario
     (tests/scenarios/). A unit test inlines every real model into one statement,
     so the warehouse compiles and optimises the whole chain at once; a scenario
     builds each model step by step, compiling each statement separately.
  3. To allow more time, set TEST(timeout "3s") on this test, [settings] test_timeout = "3s"
     in sqlbuild_project.toml (or sqlbuild_local.toml), or --test-timeout for one run.

A test that runs only the model under test reports the timeout, the limit, where it was set and how to change it, without the scenario advice.

Scenarios have their own limit, scenario_timeout; see Scenario timeouts.

Skipping SQL analysis

Compile checks test fixtures and assertions like model SQL. For SQL the parser can’t read, add sql_analysis false to the TEST header; the test still runs. Tests of a model with sql_analysis false, and every test in a --no-sql-analysis run, aren’t analysed. With [settings] require_sql_analysis = true, the opt-out is accepted only on test SQL that can’t be parsed; see Requiring SQL analysis.

Details

• On DuckDB, MotherDuck, PostgreSQL and Snowflake, a test evaluates each compared model’s full output once. If that output fails, for example because a fixture value can’t be converted in a column the expected rows leave out, the test errors with the warehouse’s message.