EXECUTE UNIT TEST

View as Markdown

PREVIEW EXECUTE UNIT TEST is in public preview. It is under active development and may have stability or performance issues. It isn't subject to our backwards compatibility guarantees.

EXECUTE UNIT TEST defines a unit test for a view or materialized view. A test substitutes literal rows for the view’s dependencies, runs the view against that fixed input, and compares the result to a set of expected rows. Tests are written inline in the same .sql file as the view they exercise.

WARNING! EXECUTE UNIT TEST is executed only by mz-deploy, not by Materialize itself. The Materialize SQL layer parses the statement but rejects it during planning, so running it through a SQL client such as psql returns an EXECUTE UNIT TEST statement not yet supported error. Use mz-deploy test to discover and run these tests.

Syntax

EXECUTE UNIT TEST <test_name>
FOR <target_view>
[AT TIME <timestamp_expr>]
[MOCK <dependency>(<col_name> <col_type>, ...) AS (<query>)[, ...]]
EXPECTED(<col_name> <col_type>, ...) AS (<query>);

Syntax element	Description
`<test_name>`	A name for the test. It identifies the test in `mz-deploy test` output and in test filters.
`FOR <target_view>`	The view or materialized view under test, given as a fully-qualified name (`<database>.<schema>.<view>`).
`AT TIME <timestamp_expr>`	Optional. Sets the value `mz_now()` returns while the test runs, so views with temporal filters produce deterministic results. If omitted, `mz_now()` is not pinned.
`MOCK <dependency>(...) AS (<query>)`	Replaces a dependency of the target view with literal rows. Specify zero or more `MOCK` clauses, separated by commas; there is no comma before the trailing `EXPECTED` clause. Every object the target view depends on must have a corresponding `MOCK`. The dependency name may be unqualified (`orders`), schema-qualified (`public.orders`), or fully-qualified (`materialize.public.orders`), and is resolved relative to the target view. The column list declares the names and types of the mock; `<query>` supplies its rows, typically with `VALUES`.
`EXPECTED(...) AS (<query>)`	Required. The rows the target view should produce. The column list must match the names and types of the target view’s output columns, and `<query>` supplies the expected rows. The test passes when the view’s output and the expected rows are equal as sets.

Details

Where tests run

mz-deploy test discovers every EXECUTE UNIT TEST statement in a project, then runs each one in a local Materialize Docker container — your remote database is never touched. For each test, mz-deploy creates temporary views for the mocks and the expected rows, rewrites the target view to read from the mocks instead of its real dependencies, and computes the symmetric difference between the view’s output and the expected rows. The test passes when that difference is empty.

Mocking dependencies

Every object the target view depends on must have a MOCK clause; an unmocked dependency is a validation error. A mock’s column names and types must match the real object’s schema, and the target view’s output columns must match the EXPECTED column list. Run mz-deploy lock to refresh the schema information used for this validation when an external dependency changes.

Type normalization

Column types in MOCK and EXPECTED are normalized before comparison, so common aliases are interchangeable — for example int/int4/integer, bigint/int8, text/varchar/string, float/float8/double precision, numeric/decimal, and json/jsonb.

Testing temporal logic

Views that filter on mz_now() depend on the current time, which would make their output non-deterministic in a test. Set AT TIME to pin the value mz_now() returns so the test result is stable.

Examples

Testing a join

CREATE VIEW user_order_summary AS
SELECT u.id AS user_id, u.name, count(*) AS total_orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

EXECUTE UNIT TEST test_single_user_single_order
FOR materialize.public.user_order_summary
MOCK materialize.public.users(id bigint, name text) AS (
  SELECT * FROM VALUES (1, 'alice')
),
MOCK materialize.public.orders(id bigint, user_id bigint) AS (
  SELECT * FROM VALUES (10, 1)
)
EXPECTED(user_id bigint, name text, total_orders bigint) AS (
  SELECT * FROM VALUES (1, 'alice', 1)
);

Pinning `mz_now()`

EXECUTE UNIT TEST test_recent_events
FOR materialize.public.recent_events
AT TIME '2024-01-15T12:00:00Z'
MOCK materialize.public.events(id bigint, ts timestamptz) AS (
  SELECT * FROM VALUES
    (1, '2024-01-15T11:59:00Z'::timestamptz),
    (2, '2024-01-14T12:00:00Z'::timestamptz)
)
EXPECTED(id bigint, ts timestamptz) AS (
  SELECT * FROM VALUES (1, '2024-01-15T11:59:00Z'::timestamptz)
);