Skip to main content

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.

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, and run as full integration tests across your pipeline.

​
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/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

PrefixPurpose
__source__<name>Mock a source. Replaces __source("<name>") in the model SQL.
__ref__<name>Mock a model or seed. Replaces __ref("<name>") in the model SQL.
__expected__<name>Define expected output for a model. SQLBuild resolves the model’s real SQL and compares against this.
__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

You can mock models directly with __ref__<name>, not just sources. This skips the model’s real SQL and provides controlled data instead: 
-- Mock stg_orders directly instead of mocking the raw source and resolving through it.
TEST();

WITH
__ref__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
),
__ref__stg_payments AS (
  SELECT 1 AS payment_id, 1 AS order_id, 1500 AS amount_cents,
         'credit_card' AS payment_method, 'success' AS payment_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
You can mix __source__ and __ref__ mocks in the same test. As long as every leaf dependency is satisfied (either by a source mock, a ref 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. environment-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 environment-specific macros without depending on environment config
  • Controlling dynamic SQL generation to produce predictable test inputs
  • Isolating model logic from macro implementation details

​
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 test files under tests/ in your project directory. SQLBuild discovers all .sql files in this directory automatically.

​
Running tests

Tests run automatically during sqb build in DAG order, before their target model is materialized. Run tests standalone: 
sqb --project-dir examples/waffle_shop test
Scope to specific models: 
sqb --project-dir examples/waffle_shop test --select stg_orders