Testing - SQLBuild

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.

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

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.

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

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.

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.

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.

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:

sqb test

Scope to specific models:

sqb test --select stg_orders

​How tests work

​CTE conventions

​Chaining across models

​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

​Test placement

​Running tests