Local development

mz-deploy provides a local development workflow for validating SQL before deploying: type-check with compile, test with test, and inspect query plans with explain.

External dependencies

Objects your project references but doesn’t own must be declared in project.toml:

dependencies = [
    "other_project.public.customers",
]

Tables and sources that your project manages (created via apply) are auto-discovered and do not need to be declared.

Lock types

types.lock captures column schemas (names, types, nullability) for tables, sources, and external dependencies. It enables offline type-checking during compile and schema validation during test.

Two categories of objects are tracked:

1. Project-managed tables and sources — auto-discovered. Running apply tables regenerates the lock file automatically.
2. External dependencies — declared in project.toml.

mz-deploy lock

This connects to your Materialize instance, fetches schemas, and writes types.lock.

• Commit types.lock to version control.
• Re-run mz-deploy lock when external schemas change.
• Without types.lock, compile cannot verify column names and types.

Compile and validate

mz-deploy compile

compile runs entirely locally with no database connection and no Docker. It:

• Parses all SQL files.
• Resolves inter-object dependencies (topological sort).
• Type-checks every statement using types.lock.
• Skips unchanged objects via incremental caching.

What it catches: parse errors, circular dependencies, type mismatches, and missing dependencies.

mz-deploy compile -v

Verbose mode shows the dependency graph, deployment order, and full SQL plan.

Use compile as your inner development loop: edit, compile, fix, repeat. Feedback is instant.

Write and run unit tests

Tests use the EXECUTE UNIT TEST syntax and live inline in the same .sql file as the view they test.

Here is a full example appended to the stalled_orders model file:

-- models/materialize/public/stalled_orders.sql
EXECUTE UNIT TEST test_stalled_order_detected
FOR materialize.public.stalled_orders
AT TIME '2024-06-15T12:00:00Z'
MOCK materialize.public.orders(
    id bigint, customer text, status text,
    amount numeric, created_at timestamptz, updated_at timestamptz
) AS (
  SELECT * FROM VALUES
    (1, 'acme', 'pending', 99.99,
     '2024-06-15T10:00:00Z'::timestamptz,
     '2024-06-15T11:00:00Z'::timestamptz)
)
EXPECTED(
    id bigint, customer text, amount numeric,
    created_at timestamptz, updated_at timestamptz, stalled_for interval
) AS (
  SELECT * FROM VALUES
    (1, 'acme', 99.99,
     '2024-06-15T10:00:00Z'::timestamptz,
     '2024-06-15T11:00:00Z'::timestamptz,
     INTERVAL '1 hour')
);

Key concepts:

• Every dependency needs a MOCK clause with typed columns and sample data.
• EXPECTED defines the rows the view should produce.
• AT TIME sets mz_now() for deterministic testing of temporal filters.
• Tests run in an isolated local Docker container.

Run all tests:

mz-deploy test

Run a filtered subset:

mz-deploy test 'materialize.public.*'

Export results for CI:

mz-deploy test --junit-xml results.xml

Explain query plans

mz-deploy explain materialize.public.stalled_orders

explain compiles the project, stages the target and its dependencies in a temporary schema, runs EXPLAIN, and then cleans up.

To explain an index, use the # separator:

mz-deploy explain materialize.public.stalled_orders#stalled_orders_customer_idx

All objects are created on the quickstart cluster regardless of your project’s cluster configuration.

Next step: iterate against production data

Once your changes compile and pass tests locally, use dev to validate behavior against real production data. dev creates a per-developer overlay database containing only your dirty views and requires the materialize_developer role — no deployer permissions needed.