mz-deploy

mz-deploy is declarative SQL project tooling for Materialize. A project is a git-tracked directory of .sql files describing the desired state of a Materialize environment; mz-deploy compiles it, type-checks it offline, tests it in a local container, and deploys changes through an atomic stage-then-promote lifecycle.

brew install materializeinc/materialize/mz-deploy

Discover Before You Run

The CLI is self-documenting, and its help text is the source of truth. Do not guess command names, flags, or argument order.

1. mz-deploy help — all commands, grouped by purpose.
2. mz-deploy help <command> — the detailed usage guide for one command: behavior, every flag, examples, error recovery, and exit codes.
3. mz-deploy help profiles — the configuration topic guide (profiles, variables, suffixes, file overrides, TLS).

This skill covers the concepts and the shape of the workflow so you know which command to reach for. Read that command's help for the flags before running it.

Run commands from the project root, or pass -d <path>. Global options worth knowing: -p/--profile selects the connection profile, --output json for machine-readable output (supported by most commands, useful in CI), -v for verbose debugging, -q to suppress informational output.

Project Layout

project.toml              # project config: mz_version, dependencies, per-profile settings
profiles.toml             # connection profiles (also resolved from ~/.mz)
.mzprofile                # gitignored per-checkout default profile
types.lock                # cached schemas of external dependencies, for offline type checking
target/                   # gitignored local build cache (safe to delete; see `clean`)
models/
  <database>/
    <schema>.sql          # schema mod file — schema-level statements (see below)
    <schema>/
      <object>.sql        # one view, materialized view, table, source, sink, connection, or secret
clusters/<name>.sql       # cluster definitions (+ their GRANTs)
roles/<name>.sql          # role definitions
network-policies/<name>.sql

The path determines the object's fully qualified name: an MV in models/materialize/catalog/clusters.sql is materialize.catalog.clusters. A schema mod file — <schema>.sql sitting beside the <schema>/ directory — holds statements that apply to the schema as a whole, such as SET api = stable.

mz-deploy new <name> scaffolds this structure in a new directory; init does the same in the current one.

Command Map

One-time bootstrap: mz-deploy setup creates the _mz_deploy tracking database, its tables, the _mz_deploy_server cluster, and three roles. It must be run by a superuser when RBAC is enabled, because it grants system privileges. Everything after that runs as an ordinary user.

Workflow

mz-deploy compile              # parse, resolve dependencies, type-check offline
mz-deploy test                 # run unit tests in a local Materialize container
mz-deploy apply                # converge infrastructure (see below)
mz-deploy stage                # deploy changed views/MVs to suffixed staging schemas
mz-deploy wait <DEPLOY_ID>     # watch clusters hydrate until ready
mz-deploy promote <DEPLOY_ID>  # atomic swap into production

compile needs no database connection, so it belongs in CI on every commit. A passing compile guarantees stage and apply will not fail at the SQL parsing stage. Note that every profile variant is validated regardless of --profile, so a syntax error in foo#staging.sql still fails compile --profile production.

Alongside: list shows unpromoted deployments (like git branch), describe <ID> details one, log shows promotion history (like git log), and abort <ID> destroys a staging deployment without promoting it.

Key Concepts

apply owns infrastructure; stage owns views. apply is declarative, diff-based, and idempotent, converging clusters, roles, network policies, secrets, connections, sources, and tables in dependency order. stage handles views and materialized views. Tables and sources are never created by stage — they must already exist, so apply runs first. delete <type> <name> is the inverse of apply: it drops one object without CASCADE and removes its project file.

Deploy IDs and staging suffixes. Each deployment gets an ID — by default the first 7 characters of the current commit SHA — used to suffix its schemas and clusters (public → public_abc123). Staging clusters are cloned from the corresponding production cluster's configuration, including any auto-scaling strategy, so staged objects hydrate the way production will. Staging runs in isolation alongside production.

Change detection is hash-based. stage compares each object's SQL hash against the last promoted snapshot and deploys only what changed, plus anything downstream of a change. Unchanged objects are not recreated. Override with --redeploy-schema <db.schema> or --redeploy-all.

Promotion is atomic and resumable. promote executes ALTER … SWAP on schemas and clusters inside a single transaction, then does post-swap work: creating deferred sinks, applying replacement MVs, repointing sinks at the new production objects, and dropping the old resources. If it dies mid-flight, re-running the same command detects the post-swap state and resumes cleanup.

Sinks are deferred to promote. They must not start producing until the deployment is live, so stage records them and promote creates them.

Conflict detection works at schema and cluster granularity. Because a whole schema is swapped as a unit, two deployments touching any of the same schemas or clusters conflict — even when they modify different objects inside them. The first to promote wins; the second is rejected and must be re-staged against current production. --force skips the check, and doing so drops the other deployer's schemas, since schemas are swapped wholesale rather than merged. Only use it when clobbering that work is the intent.

Stable API schemas. By default a changed object is recreated in staging and its whole schema is swapped, which redeploys in-project dependents automatically but breaks consumers in other mz-deploy projects. Adding SET api = stable to a schema mod file marks that schema as an API boundary: changed MVs are updated in place via ALTER MATERIALIZED VIEW … APPLY REPLACEMENT, preserving object identity. Downstream consumers — in any project — need no redeployment. Constraints: stable schemas may contain only materialized views, and a changed replacement MV does not propagate dirtiness to its dependents.

Type checking is offline. compile and test read external dependency schemas from types.lock. Declare external objects in project.toml as dependencies = ["db.schema.table"] and run mz-deploy lock to refresh the file. Source tables created by CREATE TABLE FROM SOURCE are auto-discovered and need no declaration; apply tables regenerates the lock automatically.

Roles. setup creates materialize_deployer (stage, promote, abort), materialize_developer (read-only deployment state, plus dev overlays), and materialize_monitor. Each user must belong to exactly one — holding several is an error. Use separate profiles with distinct users for deploying, developing, and monitoring.

Profiles and Per-Profile Configuration

A profile is a named connection target in profiles.toml, resolved from --profiles-dir, then MZ_DEPLOY_PROFILES_DIR, then ~/.mz. The active profile resolves from --profile, then MZ_DEPLOY_PROFILE, then the gitignored .mzprofile in the project root (written by mz-deploy profile set). A built-in emulator profile always exists, so a local Materialize emulator works with zero configuration.

Passwords support ${VAR} substitution, overridable by MZ_PROFILE_<NAME>_PASSWORD. sslmode follows PostgreSQL's vocabulary and defaults to prefer for loopback hosts, require otherwise — use verify-full for Materialize Cloud.

The profile does more than pick a host. It also selects, at compile time:

• profile_suffix (in project.toml) — appended to every database and cluster name, including IN CLUSTER references. Write the delimiter yourself: "_staging", not "staging". Staging suffixes stack on top (foo → foo_staging → foo_staging_a).
• SQL variables — [<profile>.variables] in project.toml, referenced in any .sql file with psql syntax: :name (raw), :'name' (quoted string), :"name" (quoted identifier). Referencing an undefined variable fails compilation.
• File overrides — name#<profile>.sql replaces name.sql when that profile is active. All variants are validated at compile time regardless of which is active, and all must share the same primary statement type. Views and materialized views cannot have file overrides — use SQL variables instead.

Rollback

There is no rollback command. Reverse the change in the project and promote the result:

git revert <commit>
mz-deploy stage
mz-deploy promote <DEPLOY_ID>

Because promote swaps atomically, the rollback promotion is itself atomic — production switches back in a single transaction.

Unit Tests

mz-deploy test runs tests written inline in the same .sql file as the view they cover, using EXECUTE UNIT TEST with mocked dependencies. The syntax is specific to mz-deploy and documented nowhere else — see references/unit-tests.md for the full grammar, worked examples, and failure modes.

Gotchas

• stage requires a clean git tree. Commit, stash, or pass --allow-dirty.
• setup needs a superuser under RBAC, one time only.
• test and explain need Docker, and share one container named mz-deploy-sandbox across invocations on the host. Reuse is by name, not by image, so --docker-image has no effect until you docker rm -f mz-deploy-sandbox.
• delete never cascades. If dependents exist it fails and leaves the project file in place.
• dev will not target a production cluster. Provision a dedicated dev cluster.
• A profile's cluster option is ignored. mz-deploy pins every connection to its own internal _mz_deploy_server cluster; resize that with a standard ALTER CLUSTER if needed.
• stage failures roll back automatically. Pass --no-rollback to keep the partial deployment for debugging, then clean up with abort.
• A stale target/ cache can produce confusing compile or type errors. Run mz-deploy clean.