Scenarios

Scenarios are end-to-end tests for coherent slices of your warehouse. You define fixture inputs, SQLBuild builds the real project graph against them in isolated physical relations, and you assert that the result is correct.

When to use scenarios vs unit tests

SQL unit tests compile to a single comparison query. They are fast, inline, and work well for checking individual model transformations. But when you need to test across many models with related entities - customers that have orders, orders that have payments, refunds that reference original orders - a single query can become unwieldy or hit SQL size limits.

Scenarios materialize physical relations in the warehouse and build the actual model graph. This means:

• Fixture inputs are real tables, not CTEs inside one query
• Models execute with real SQL, real materializations, and real dependency ordering
• You can test end-to-end business logic across an entire pipeline slice
• Debugging is easier because you can inspect intermediate tables with --retain

Use unit tests for transformation logic. Use scenarios for coherent end-to-end validation.

Cost is a second reason to move a check into a scenario. A unit test inlines every real model in its chain into one SQL statement, so the warehouse compiles and optimises the whole chain at once, and that cost can grow much faster than the query’s size. A scenario materialises each model step by step, so each statement is compiled separately, as in a real build. SQLBuild limits how many real models one unit test may execute (see Cost guards); when a test reaches that limit because it checks behaviour across several models, it may belong here.

Authoring scenarios

Scenario files live under tests/scenarios/ and use one file per scenario:

tests/
  scenarios/
    revenue/
      daily_revenue_minimal.sql
      daily_revenue_multi_order.sql
    fulfillment/
      fulfillment__late_shipment.sql

Folders are organizational only. The scenario name is the filename stem. Like model, source, seed, and unit-test names, it must be globally unique across the project.

Scenario format

Each file has a SCENARIO() header followed by CTEs that define inputs, expected outputs, and assertions:

SCENARIO (
  description "Daily revenue includes only successful payments",
  tags ["revenue", "example"]
);

WITH
__ref__stg_orders AS (
  SELECT
    1 AS order_id, 10 AS customer_id, 1 AS waffle_type_id,
    2 AS quantity, CAST('2026-04-01 09:15:00' AS TIMESTAMP) AS ordered_at,
    'completed' AS status
),

__ref__stg_payments AS (
  SELECT
    1 AS payment_id, 1 AS order_id, 1700 AS amount_cents,
    'credit_card' AS payment_method,
    CAST('2026-04-01 09:16:00' AS TIMESTAMP) AS paid_at,
    'success' AS payment_status
  UNION ALL
  SELECT
    2 AS payment_id, 2 AS order_id, 1050 AS amount_cents,
    'credit_card' AS payment_method,
    CAST('2026-04-01 10:01:00' AS TIMESTAMP) AS paid_at,
    'failed' AS payment_status
),

__expected__daily_revenue AS (
  SELECT
    CAST('2026-04-01' AS DATE) AS revenue_date,
    1 AS order_count, 2 AS waffles_sold,
    1700 AS total_revenue_cents
),

__assert__all_orders_have_payments AS (
  SELECT *
  FROM __ref("fact_orders")
  WHERE payment_amount_cents IS NULL
)

CTE conventions

Prefix	Purpose
__source__<name>	Fixture source input. Materializes as a scenario table.
__ref__<model>	Fixture model boundary. Prevents SQLBuild from building that model upstream.
__seed__<seed>	Fixture seed data.
__expected__<model>	Expected output. Compared order-insensitively against the scenario-built model on the listed columns.
__assert__<name>	Zero-row assertion. Passes if the query returns no rows; fails with the returned rows as diagnostics.

As in unit tests, 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, and rows are compared in both directions: mismatched counts model rows the expected output lacks and missing counts expected rows the model did not produce. An expected CTE without explicit column names, such as SELECT *, is compared on every column by position.

When the CTE’s columns and the model’s output columns are both known statically, listing a column the model does not output fails planning with S512 before the scenario runs. Set expected_columns "all" in the SCENARIO() header to also require every model output column to be listed; a missing column then fails with S512, naming it.

Any other CTE is a helper. As in unit tests, fixtures, expected CTEs and assertions can read helpers, and helpers can read other helpers, __ref(...) and the other relation calls, and fixtures by CTE name, such as __ref__orders. SQLBuild places each helper a query reads, directly or through other helpers, ahead of it in dependency order; a fixture read by name reads the fixture’s materialised table. A model an assertion reads through a helper is built like one it reads directly.

A comparison or assertion that fails to run reports its error with an ERROR status and no row counts. In --json output an expected result’s comparison_status, or an assertion result’s query_status, is not_run, and its row counts are null.

For sources with two-part identity, use double underscores: __source__raw__orders.

To mock a contracted source, ref, or seed with no rows, use SELECT * FROM __empty_fixture() as the complete fixture body. SQLBuild expands it to the relation’s declared, typed columns with a false filter, as in unit tests.

Every scenario must have at least one fixture CTE and at least one __expected__ or __assert__ CTE. The file ends after its last CTE; a trailing ceremonial SELECT 1 is accepted but not required.

Scenario CTEs follow the same strict rules as unit test CTEs, and other forms are compile errors that point at the offending text:

• A CTE name is an unquoted identifier of ASCII letters, digits and underscores that starts with a letter or underscore. Quoted names such as "__source__raw_orders" or `recent orders`, and names with $ or non-ASCII characters, are errors.
• A CTE body follows AS directly. Materialization hints such as AS MATERIALIZED (...) or AS NOT MATERIALIZED (...) are not supported in scenario CTEs.

Scenario SQL uses macros, enums, and constants available from the scenario file’s directory under tests/scenarios/. Public enums and constants available to a model are also available when the scenario defines __expected__model_name output for that model. Model-private values and macros available only to the model are not included. See How Visibility Works.

SCENARIO() header

The header holds metadata, an optional time limit and the expected-column mode:

Field	Description
description	Required description of the behaviour the scenario proves (Descriptions)
tags	Optional list of string tags
expected_columns	Optional. "listed" (default) compares only the columns each __expected__ CTE lists; "all" also requires every model output column to be listed
timeout	Optional time limit for the scenario’s warehouse run, such as "10m", or "disabled"; overrides [settings] scenario_timeout and the target’s scenario_timeout

At the limit SQLBuild cancels the scenario’s running statement, still drops the relations the scenario created unless --retain is set, and reports the scenario as failed with C417, the elapsed time, the limit and where it was set.

How scenarios execute

Graph inference

SQLBuild infers which models to build from your scenario:

1. Target models come from __expected__<model> CTE names and __ref(...) calls inside __assert__ CTEs
2. SQLBuild walks upstream from targets, building every required model
3. __ref__<model> fixtures act as boundaries - upstream traversal stops there
4. All required sources must be provided by __source__ fixtures
5. Required project seeds are loaded automatically unless overridden by __seed__ fixtures

Isolation

Scenario artifacts are physically isolated from production:

• Every scenario-owned relation uses a deterministic prefixed name (based on a hash of the project and scenario identity)
• All ref(), source(), and seed() calls resolve to scenario-owned relations, never production tables
• Fixture CTEs can read from existing warehouse relations if you choose, but downstream models always read the materialized scenario fixture, not the original

Execution flow

1. Drop any scenario artifacts a previous run left in the effective run namespace (one catalog read finds them)
2. Materialize source, ref, and seed fixtures as physical tables
3. Load required project seeds
4. Build required models in dependency order (incremental models run as full-refresh in scenarios)
5. Run expected-output comparisons (order-insensitive)
6. Run zero-row assertions
7. Clean up this namespace’s scenario-owned artifacts (unless --retain), including after a failure or Ctrl-C

Table models use the same promotion as sqb build: the project’s table_promotion_mode, or the adapter default (staged) when it is unset. An enforced contract needs staged promotion, so its runtime contract check runs before the scenario reaches its expectations; with table_promotion_mode = "immediate" such a model is a K011 compile error, reported before any scenario runs, exactly as in sqb compile, sqb plan, and sqb build.

Independent scenarios run concurrently, up to --concurrency or the project concurrency setting. Each scenario writes only its own prefixed relations, so concurrent scenarios never share an artifact. Results are reported in selection order with each scenario’s wall time, and the summary includes the total time. On DuckDB, concurrent scenarios use separate connections to the same database file; an in-memory database (:memory:) gives each connection its own database, so fixtures still isolate every scenario.

On Ctrl-C, SQLBuild prints one notice, cancels scenarios that have not started, cancels the statements running scenarios have in flight, drops their relations (unless --retain), and only then exits. A second Ctrl-C skips that cleanup and exits immediately; the next run of the same scenarios drops whatever they left behind. --local replay and sqb scenario capture run one scenario at a time.

Parallel CI runs

Give each concurrent CI job a unique scenario run namespace when jobs share a warehouse schema:

export SQLBUILD_SCENARIO_NAMESPACE="ci-${CI_RUN_ID}-${CI_JOB_ID}"
sqb scenario test
# The same option applies to warehouse fixture capture:
sqb scenario capture --scenario-namespace "ci-${CI_RUN_ID}-${CI_JOB_ID}"

Use a unique job/run identifier, including an attempt or matrix identifier when necessary. A pull-request identifier is suitable only when there is at most one active run for that identifier. Concurrent runs with the same namespace are unsupported; namespaces do not add locking. Keep separate working directories for concurrent jobs: namespaces isolate warehouse relation names, not local snapshot files or target/run/scenarios/ output paths.

The precedence is --scenario-namespace > SQLBUILD_SCENARIO_NAMESPACE > sqlbuild_local.toml > sqlbuild_project.toml. Both config files accept:

[scenario]
run_namespace = "orders-job-123"

Values must contain 1–128 ASCII letters, digits, hyphens, underscores, or periods. Empty or invalid values fail before warehouse work; values are case-sensitive and are not trimmed. The effective namespace and its source appear in run output, including JSON output.

With a namespace, the artifact prefix is the first 12 hexadecimal characters of SHA-256 of project_name:namespace:scenario_name. Only the hash enters identifiers, so identifier length limits are unchanged. Without a namespace, SQLBuild keeps the existing hash of project_name:scenario_name and all existing physical names. Prefix collisions are checked among the project’s scenarios within the effective namespace.

Preparation, normal teardown, and failure cleanup affect only the current namespace’s relations. --retain keeps that namespace’s artifacts for inspection. The janitor still recognizes artifacts from every namespace, including crashed CI jobs, and applies the existing age/retention policy without needing their namespace values. Choose retention longer than active scenario jobs. For SQLBuild versions without namespace support, use a separate warehouse schema per CI job.

Inspecting with --retain

When a scenario fails or you want to inspect intermediate state:

sqb scenario test --select daily_revenue_minimal --retain

This keeps all scenario-owned relations in the warehouse and prints a relation map showing the logical-to-physical name mapping. You can then query the scenario tables directly to debug.

Runtime artifacts (fixture SQL, model lifecycle SQL, check SQL, cleanup SQL) are always written to target/run/scenarios/<scenario_name>/ regardless of --retain.

Local scenario testing

Scenarios can run locally against DuckDB using captured JSONL snapshots - no warehouse connection needed. This is useful for CI pipelines and fast developer iteration.

Capture

First, capture scenario inputs from the real warehouse:

sqb scenario capture --select daily_revenue_minimal

This:

1. Materializes scenario input fixtures in the warehouse
2. Runs a preflight COUNT(*) on each fixture to check row counts against safety limits
3. Inspects column types and maps them to DuckDB-compatible types
4. Downloads rows as JSONL files (enforcing byte limits during writing)
5. Writes a scenario.json manifest with column metadata, types, row counts, and an input fingerprint
6. Cleans up warehouse artifacts

Snapshots are written to tests/_scenario_snapshots/<scenario_name>/ and can be committed to version control. JSONL is human-readable and git-diffable.

Capture safety limits

Capture enforces row and byte limits to prevent accidentally saving large datasets. Limits can be set in sqlbuild_project.toml:

[scenario.snapshot_limits]
max_rows_per_relation = 10000
max_total_rows = 50000
max_bytes_per_relation = 10485760   # 10 MB
max_total_bytes = 52428800          # 50 MB

Or per-command via CLI flags:

sqb scenario capture --max-snapshot-rows 5000 --max-snapshot-total-rows 20000
sqb scenario capture --max-snapshot-bytes 5242880

CLI flags override TOML config. Use --force to bypass all limits:

sqb scenario capture --force

When a limit is exceeded, capture fails with a clear error and suggests narrowing the fixture query, raising the limit, or using --force.

Local replay

Run scenarios locally against DuckDB:

sqb scenario test --local

This:

1. Checks snapshot freshness via the input fingerprint (missing/stale snapshots are skipped by default)
2. Creates a temporary DuckDB database at target/run/scenarios/<scenario_name>/local.duckdb
3. Loads JSONL snapshots into typed DuckDB tables using column metadata from scenario.json
4. Transpiles model and check SQL from the project adapter dialect to DuckDB; SQL captured from a DuckDB project runs as authored
5. Builds functions, models, and runs expected/assertion checks in DuckDB
6. Keeps the local DuckDB file for inspection (it lives under target/, so it’s always retained)

Sync snapshots

Instead of running capture and test separately, sync snapshots in one command:

# Capture missing/stale snapshots, then run locally
sqb scenario test --local --sync-snapshots

# Recapture all snapshots, then run locally
sqb scenario test --local --refresh

With --sync-snapshots, fresh snapshots are reused. With --refresh, all selected snapshots are recaptured even if fresh.

Strict mode

By default, missing or stale snapshots are skipped with a warning. Use --strict to treat them as errors:

sqb scenario test --local --strict

Local type overrides

When the automatic warehouse-to-DuckDB type conversion produces an incompatible type, you can override it in sqlbuild_project.toml:

[scenario.local_type_overrides.snowflake]
"OBJECT" = "JSON"
"ARRAY" = "JSON"
"NUMBER(*,0)" = "BIGINT"

Override keys are structural type patterns. Values can reference matched type arguments with {1}, {2}, etc:

[scenario.local_type_overrides.bigquery]
"NUMERIC({1},{2})" = "DECIMAL({1},{2})"

These overrides are written into scenario.json during capture and used when loading snapshots into DuckDB.

Cleanup

By default, remote scenario artifacts are dropped after each run (pass or fail). The janitor command also recognizes scenario artifacts and can clean retained or orphaned scenario relations:

sqb janitor

See the CLI reference for full command documentation.

Limitations

• Custom materializations are not supported in scenarios yet. Scenario models using custom materializations will fail with a clear error.
• Local replay compares VARIANT and JSON output columns by their JSON value, not their type: key order, whitespace and number spelling do not matter, so a Snowflake capture’s pretty-printed JSON equals an expected PARSE_JSON(...). Values of non-JSON types with the same JSON text also compare equal, for example DATE '2024-01-01'::VARIANT and '2024-01-01'::VARIANT.
• Local replay transpiles SQL from the project adapter dialect to DuckDB. Adapter-specific SQL that cannot be translated will produce a clear error with the failing resource name and reason.
• Outside local replay, scenario fixture, expected and assertion SQL runs as authored: SQLBuild replaces __ref, __source, __seed and __dbt_ref calls with relation names and may wrap a partial fixture to add its missing columns, but never regenerates the query. Calls inside comments and quoted text are left unchanged. Quoted text and comments follow the project adapter’s rules, including with --local: backslash escapes on Snowflake, BigQuery and Databricks, E'...' strings on DuckDB and Postgres, nested block comments on DuckDB, Postgres, Databricks and SQL Server, # line comments on BigQuery, and // line comments on Snowflake. A call naming a relation the scenario does not provide, or SQL with an unclosed string or comment, fails planning with S511 naming the scenario file.