Testing

smelt lets you test your SQL models by defining mock input data and expected output rows directly in SQL files, without needing a running database or executing your full pipeline.

How it works

A test declares an assertion query that references the model(s) under test, provides mock data for their dependencies via PASSING clauses, and states the expected output via an EXPECT clause. When you run smelt test, smelt compiles the assertion query into a standalone SQL query with mock data substituted for dependencies, executes it against an in-memory DuckDB instance, and compares the actual output to the expected rows.

Tests are discovered the same way as models — by the project-wide scan of every non-excluded directory (paths: in smelt.yml affects how addresses are derived, not what gets scanned). They can live in a dedicated tests/ directory or co-located in model files.

smelt.test declarations

The primary way to write tests is with a smelt.test declaration. This keeps the query, mock data, and expectations together in a single SQL-native form:

smelt.test daily_revenue_basic AS (
    SELECT order_date, total_revenue
    FROM smelt.daily_revenue
)
PASSING orders AS (
    {order_id: 1, amount: 100.0, order_date: '2024-01-15'},
    {order_id: 2, amount: 200.0, order_date: '2024-01-15'}
)
EXPECT (
    {order_date: '2024-01-15', total_revenue: 300.0}
)

The grammar is:

smelt.test <name> AS ( <select> )
  [ PASSING <dep> AS ( <rows> ) ]...
  EXPECT ( <rows> )

• <select> — the assertion query. It references the model(s) under test via smelt.<path>. There is no separate model: field; the model under test is determined by what the query references.
• PASSING <dep> AS ( <rows> ) — mock data for one dependency. <dep> is the bare address path of the dependency (e.g. orders or silver.orders) — the smelt.<path> reference minus the leading smelt.. <rows> is a comma-separated list of record literals {col: value, ...}. Zero or more PASSING clauses are allowed.
• EXPECT ( <rows> ) — required. The expected output rows as record literals.

Dependencies not named in any PASSING clause are replaced with empty CTEs (zero rows). A PASSING clause that names a dependency the query does not actually reach is reported as UnknownTestInput and fails the test — this catches typos that would otherwise silently produce a false-green result.

Record-literal value types

Each value in a record literal is automatically cast to the appropriate SQL type:

Literal	SQL type	Example
Integer	INTEGER	42
Float	DOUBLE	3.14
Decimal-shaped string (has ., no exponent)	DECIMAL	'300.00'
'YYYY-MM-DD' string	DATE	'2024-01-15'
'YYYY-MM-DD HH:MM:SS' string	TIMESTAMP	'2024-01-15 10:00:00'
Other string	VARCHAR	'completed'
Boolean	BOOLEAN	true, false
Null	NULL	null

Frontmatter knobs

A YAML frontmatter block can precede the smelt.test declaration to configure test behaviour:

---
test:
  check_order: true
  cases: 20
---
smelt.test check_revenue_rank AS (
    SELECT rank, user_id FROM smelt.revenue_report ORDER BY rank
)
PASSING ... 
EXPECT ...

Key	Type	Default	Description
check_order	bool	false	If true, compare rows positionally (order matters). If false, compare as sets.
cases	integer	10	Number of iterations for property-based tests (see below).

Full-query tests

A full-query test inlines the referenced model's SQL and substitutes mock data for every smelt.<path> dependency named in a PASSING clause:

smelt.test check_user_activity AS (
    SELECT user_id, total_events
    FROM smelt.user_activity
)
PASSING users AS (
    {user_id: 1, user_name: 'Alice', signup_date: '2024-01-01'},
    {user_id: 2, user_name: 'Bob', signup_date: '2024-02-01'}
)
PASSING events AS (
    {event_id: 1, user_id: 1, event_type: 'page_view'},
    {event_id: 2, user_id: 1, event_type: 'click'},
    {event_id: 3, user_id: 2, event_type: 'page_view'}
)
EXPECT (
    {user_id: 1, total_events: 2},
    {user_id: 2, total_events: 1}
)

CTE-level tests with the # operator

Within a smelt.test body, you can target a specific CTE inside a model using the smelt.<model>#<cte> syntax:

smelt.test daily_agg_rollup AS (
    SELECT day, revenue
    FROM smelt.daily_revenue#daily_agg
)
PASSING orders AS (
    {order_id: 1, amount: 100.0, order_date: '2024-01-01'}
)
EXPECT (
    {day: '2024-01-01', revenue: 100.0}
)

The #<cte> suffix selects one CTE within the referenced model. The CTE's upstream chain — every CTE it depends on, directly and transitively — runs as written. Only the model's external smelt.<path> dependencies are mockable via PASSING.

PASSING names in a CTE-level test are the model's external dependency paths (the smelt.<path> refs reachable from the target CTE's dependency chain), not internal CTE names. A #<cte> naming a CTE absent from the model is reported as UnknownTestCte.

Tip

CTE-level tests are ideal for complex models with long CTE chains. Instead of mocking all upstream dependencies for the entire model, you can test each transformation step in isolation — treating each CTE as a function with defined inputs and outputs.

Here's an example testing a window function CTE:

smelt.test customer_quantiles_check AS (
    SELECT customer_id, revenue_decile, frequency_decile
    FROM smelt.int_customer_segments#customer_quantiles
)
PASSING customer_metrics AS (
    {customer_id: 1, customer_segment: 'Premium', order_count: 10, total_revenue: 1000.0, total_net_revenue: 900.0},
    {customer_id: 2, customer_segment: 'Standard', order_count: 5, total_revenue: 500.0, total_net_revenue: 450.0},
    {customer_id: 3, customer_segment: 'Basic', order_count: 2, total_revenue: 100.0, total_net_revenue: 90.0},
    {customer_id: 4, customer_segment: 'Premium', order_count: 8, total_revenue: 800.0, total_net_revenue: 720.0}
)
EXPECT (
    {customer_id: 1, revenue_decile: 1, frequency_decile: 1},
    {customer_id: 4, revenue_decile: 2, frequency_decile: 2},
    {customer_id: 2, revenue_decile: 3, frequency_decile: 3},
    {customer_id: 3, revenue_decile: 4, frequency_decile: 4}
)

Property-based tests

When a PASSING row omits one or more columns that the CTE or model uses, smelt treats the test as property-based. For each of the cases iterations (default 10):

1. smelt infers the type of each omitted column from the model's type checker.
2. Generates a random value of the appropriate type.
3. Executes the test with the augmented input data.
4. Checks that specified EXPECT columns match (unspecified output columns are ignored).
5. Verifies the query does not crash.

---
test:
  cases: 20
---
smelt.test daily_agg_property AS (
    SELECT day, revenue
    FROM smelt.daily_revenue#daily_agg
)
PASSING cleaned AS (
    -- user_id is omitted: random values are generated each iteration
    {amount: 100.0, created_at: '2024-01-01'},
    {amount: 200.0, created_at: '2024-01-01'}
)
EXPECT (
    -- only `revenue` is checked; other columns are ignored
    {revenue: 300.0}
)

If any iteration fails, the framework reports the random seed for reproduction.

File placement

Each smelt.test declaration belongs in its own .sql file (or a file dedicated to tests). Any .sql file that contains a smelt.test declaration is classified as a test file by smelt — it will not be treated as a model, and other models cannot reference it via smelt.<name>.

Note

Convention: Place test files in a dedicated tests/ directory and add it to paths: in smelt.yml. This keeps model files clean and makes it clear which files contain tests.

# smelt.yml
paths:
  - models
  - tests

smelt.check declarations

smelt.check is a data-quality assertion that runs against your built pipeline data — not mocked data. Where a smelt.test supplies mock inputs and expected output rows, a check queries the tables that your pipeline has actually materialised and flags rows that violate a condition. If the query returns any rows, the check fails.

smelt.check no_negative_amounts AS (
    SELECT order_id, amount
    FROM smelt.revenue
    WHERE amount < 0
)

The grammar is:

smelt.check <name> AS ( <select> )

• <name> — identifier for the check, used in CLI output and selector expressions.
• <select> — a query that returns failing rows. The check passes when the result set is empty and fails when it contains at least one row.

Difference from smelt.test

	smelt.test	smelt.check
Input data	Mock rows supplied via PASSING clauses	Real tables built by the pipeline
Assertion	EXPECT clause lists expected rows	Any returned row is a failure
When it runs	smelt test (before or without a full build)	smelt check, against already-built data
PASSING / EXPECT	Valid	Error — not permitted on a check

Severity

By default a failing check is an error. Set severity: warn in frontmatter to emit a warning instead (the command exits 0 but reports the check as a warning):

---
severity: warn
---
smelt.check no_negative_amounts AS (
    SELECT order_id, amount
    FROM smelt.revenue
    WHERE amount < 0
)

Key	Values	Default	Description
severity	error, warn	error	Exit code and display treatment when the check fails.

File placement

Place check files in a directory listed in paths: in smelt.yml. A dedicated checks/ directory is conventional:

# smelt.yml
paths:
  - models
  - checks

Any .sql file that contains a smelt.check declaration is classified as a check file. Check files never materialise a DB object — they are not part of the execution graph and are not built by smelt run, smelt explain, or smelt build. They are evaluated in two places: on demand via smelt check, and automatically during smelt build after the models they reference materialise (see below).

Running checks

Run checks with smelt check. Each check's smelt.<path> references are compiled to the real materialized relations in the configured target, and the failing-rows query is executed against the data your pipeline has already built:

# Run all checks against the dev target
smelt check

# Run only checks whose name contains "revenue"
smelt check --select revenue

# Run against a different target
smelt check --target prod

Each check is reported as PASS (zero rows), FAIL (an error-severity violation), or WARN (a warn-severity violation). A violation shows the violating row count and a capped sample of the offending rows; the rows are shown inline only and are not written to the warehouse.

smelt check

  PASS  daily_revenue_non_negative
  FAIL  amount_must_exceed_500 — 3 violating row(s)
    {"order_id": "7", "amount": "120.00"}

  1 passed, 1 failed, 0 warned, 2 total

smelt check exits 0 when every error-severity check passes and 1 when any error-severity check has violations; warn-severity violations never change the exit code. A check that references a model which has not been built in the target fails loudly with CheckTargetNotBuilt rather than silently passing on a missing relation, so build the pipeline (smelt build) before running checks against it.

Checks during smelt build

smelt build runs each check automatically, immediately after the model it references materialises, against the freshly written data. This makes checks a guardrail on the pipeline rather than a separate after-the-fact step:

• An error-severity violation skips every model downstream of the checked model for the rest of the build, so bad data never propagates, and the build exits 1.
• A warn-severity violation is reported and the build continues — downstream models still build and the build exits 0.

Skipped models are listed in the build summary. Because the dependency edge is derived from the check's smelt.<path> references, a check guards exactly the models it reads (and everything downstream of them).

smelt run does not run checks — it only materialises models. Checks are a smelt build / smelt check concern, so use smelt build (or a smelt run followed by smelt check) when you want the data validated.

Declarative column tests

For the common case of asserting a fact about a single column — non-null, unique, one of a fixed set of values, or a foreign-key-style reference to another model — declare it directly on the column instead of hand-writing a smelt.check. Add a tests key under the column's entry in a model's columns: frontmatter:

---
name: orders
columns:
  order_id:
    tests:
      - not_null
      - unique
  status:
    tests:
      - accepted_values: ['pending', 'shipped', 'cancelled']
  customer_id:
    tests:
      - relationships:
          to: customers
          field: id
---
SELECT order_id, status, customer_id FROM raw_orders

Four kinds are recognized:

Kind	Form	Checks
not_null	bare string	the column is never NULL
unique	bare string	the column (or column set) has no duplicate values
accepted_values	{accepted_values: [...]}	every non-null value is one of the listed literals
relationships	{relationships: {to: <model>, field: <column>}}	every non-null value matches a row in to.field

A misspelled kind, or a tests entry on a column that doesn't exist in the model's output, is a hard compile error — unlike other columns: keys (a stale description is silently dropped), a stale or misspelled test would otherwise look like it's running when it isn't.

Proven tests cost nothing at run time

Before running anything, smelt checks whether the model's own SQL already proves the test true:

• not_null is proven when the type checker has already inferred the column non-nullable.
• unique is proven when the tested column (or column set) is exactly the model's declared unique_key:.

A proven test is reported as proven — no scan emitted and costs no query — it's re-verified on every compile, so a change that breaks the guarantee re-opens the scan rather than leaving a stale green result. accepted_values and relationships have no proof path yet and always run as a scan (see below).

$ smelt check

smelt check — declarative column tests

  PROVEN  orders.order_id.not_null — no scan emitted
  PROVEN  orders.order_id.unique — no scan emitted

Unproven tests run as a scan

A test that isn't proven lowers to the same failing-rows scan machinery as a hand-written smelt.check, run by smelt check: zero rows returned means the test passes, one or more rows is a violation. accepted_values and relationships have no proof path today, so they always run as a scan. The generated failing-rows predicate per kind:

Kind	Failing-rows predicate
not_null	the column IS NULL
unique	the column's value appears more than once
accepted_values	the column's value is not NULL and not in the accepted list
relationships	the column's value is not NULL and has no matching row in to.field (a left-anti-join)

$ smelt check

smelt check — declarative column tests

  PROVEN  orders.order_id.not_null — no scan emitted
  PROVEN  orders.order_id.unique — no scan emitted

smelt check

  PASS  orders.status.accepted_values
  FAIL  orders.customer_id.relationships — 1 violating row(s)

Proven and scanned tests for a model are reported together, distinguished by verdict kind, so it's visible at a glance which of a model's tests cost a scan and which didn't. Declarative column tests are always error-severity; there is no warn-severity form today.

Comparison behavior

Set vs ordered comparison

By default, row order does not matter -- both actual and expected rows are compared as sets. Use check_order: true (in frontmatter) when row order is significant (e.g., testing window functions with specific ordering):

---
test:
  check_order: true
---
smelt.test check_rank AS (
    SELECT rank, user_id FROM smelt.revenue_report ORDER BY rank
)
PASSING revenue_report AS (
    {rank: 1, user_id: 42},
    {rank: 2, user_id: 17}
)
EXPECT (
    {rank: 1, user_id: 42},
    {rank: 2, user_id: 17}
)

Column filtering

Only columns listed in expect are compared. Extra columns in the actual output are ignored. This lets you assert on the columns you care about without listing every column the model produces.

Numeric tolerance

Floating-point values are compared with an epsilon of 1e-6. For example, an actual value of 300.0000001 matches an expected value of 300.0.

Type coercion

Literal values are automatically converted to SQL types:

Literal value	SQL type	Example
Integer	INTEGER	42
Float	DOUBLE	3.14
Decimal-shaped string (has ., no exponent)	DECIMAL	'300.00'
String	VARCHAR	hello or 'hello'
Boolean	BOOLEAN	true, false
Null	NULL	null
Date string	DATE	'2024-01-01' (YYYY-MM-DD pattern)
Timestamp string	TIMESTAMP	'2024-01-01 12:00:00' (YYYY-MM-DD HH:MM:SS or T-separator)

Note

Strings matching the YYYY-MM-DD pattern are automatically cast to DATE, and strings matching YYYY-MM-DD HH:MM:SS (space or T separator) are cast to TIMESTAMP. There is no escape mechanism if you need a date-shaped string as VARCHAR.

Running tests

Run all tests

smelt test

Filter by name

smelt test --select test_cohort_sizes
smelt test -s cohort -s user

Note

The --select flag uses substring matching on test names. Passing -s cohort runs any test whose name contains "cohort". This differs from the graph-aware selector syntax used by smelt run.

Show compiled SQL

smelt test --verbose

Use --verbose to see the SQL that smelt generates for each test. Helpful for debugging unexpected results.

Show passing tests

smelt test --show-all

By default, only failing tests appear in the output. Use --show-all to also see passing tests.

Output

smelt test

  PASS test_cohort_sizes (mart_cohort_retention::cohort_sizes)     0.02s
  FAIL test_user_activity (user_activity)                          0.03s

  1 passed, 1 failed, 2 total (0.05s)

For CTE tests, the output shows (model::cte_name). For whole-model tests, it shows (model_name).

The command exits with code 0 if all tests pass, or code 1 if any test fails.

Tip

Use smelt test in CI to catch regressions. The non-zero exit code integrates naturally with CI systems.

Further reading

• SQL Models -- model syntax and YAML frontmatter
• Materializations -- all materialization types
• CLI Commands -- full smelt test flag reference