The Parts of SQLMesh That Aren't Production Ready

SQLMesh was always meant to have a production path. Tobiko Cloud was it: the managed platform, built by the same people who built the framework, and the answer to how you run this at enterprise scale without assembling it yourself. That path is closed. Whatever its official status, it is not something you can buy today.

So what do you do instead? Typically you will hear options like...

GitHub Actions - Not viable at all. GitHub cronjobs are a best-effort service and routinely have delays of several minutes to hours. If you don't believe me, just google "github actions cronjob reliability".
Airflow - Legit option if you already run Airflow, but you have to decompose your SQLMesh tasks into Airflow DAGs which is a pain in the ass.
Build it yourself. Here be dragons. This blog post is going to tell you where they are lurking.

SQLMesh's transformation engine is production ready. Plan/apply, virtual environments, interval tracking: that part is excellent and isn't what this post is about. This post is about the three things around it that aren't, in the sense that matters. You cannot turn them on and go. Run capacity you can't partition. A promotion path with no guardrail forcing you onto it. Deploy access to production that isn't enforced anywhere at all. Each is a place where SQLMesh implements the primitive correctly and leaves the operating discipline to you to figure out.

Running independent models concurrently

Strip away the specifics and the problem is simple. You have 500 models, most of them independent of each other, and you want them to run at the same time. That is the entire operational ask. You can't just pop sqlmesh run prod into a cronjob somewhere and let it rip for a couple of reasons...

It isn't concurrency safe. Multiple sqlmesh run statements will reprocess missing intervals or in the worst case lead to data corruption.
The concurrency settings aren't sufficient to satisfy a project with hundreds of models.

SQLMesh's default answer to this problem is one thread pool.

One pool, one number

SQLMesh executes the whole run in a single ThreadPoolExecutor sized by max_workers, taken from concurrent_tasks on your gateway connection. One pool, for every model in the project.

There is no priority, no per-model timeout, and no way to give a class of models its own capacity. The only per-model knob that exists, batch_concurrency, lowers how many batches of a single model run at once. It can never grant a model capacity of its own. It is also unlikely that this concurrency is enough to saturate your compute warehouse. In other words, your models end up queueing because your scheduler isn't executing them fast enough. You want the bottleneck to be your compute warehouse, not your scheduler.

Selection is the only lever, and it doesn't isolate

Since you can't give the slow model its own capacity, the only remaining move is to stop running it alongside everything else. That means partitioning the project by hand:

# Fast lane: every 15 minutes
sqlmesh run --select-model "tag:fast"

# Slow lane: the heavy models, nightly, on their own schedule
sqlmesh run --select-model "tag:heavy"

Except those two commands don't do what they look like they do. --select-model on run expands the selection to include every upstream ancestor and runs those too if they have missing intervals. SQLMesh's help text is explicit: "Select specific models to run. Note: this always includes upstream dependencies."

That's the correct default, since it's what stops you reading a table whose parent never refreshed. It also means your fast lane is not isolated. If a heavy model sits upstream of anything tagged fast and it happens to be due, your 15-minute run inherits its full runtime, on precisely the days you built the partition to protect.

The flag that gives you real isolation is --no-auto-upstream, and SQLMesh tells you exactly what it costs, by name: "Do not automatically include upstream models... this may result in missing / invalid data for the selected models."

So the choice is between two bad options:

# Correct, but not isolated: inherits any due upstream heavy model's runtime
sqlmesh run --select-model "tag:fast"

# Isolated, but correctness is now yours: may read parents that haven't refreshed
sqlmesh run --select-model "tag:fast" --no-auto-upstream

Take the default and your lanes aren't lanes. Take the flag and you've accepted responsibility for cross-lane ordering that nothing enforces, where the failure is wrong data rather than an error. SQLMesh warns you. It can't fix it, because the schedule that would need fixing lives in your CronJobs, not in SQLMesh.

Either way the partition is now yours to maintain, as tags in model definitions and selector strings spread across CronJob specs, with nothing validating that it's complete or still correct. A model in no lane never runs, and nothing warns you, because "no missing intervals" and "nobody asked about this model" look identical from outside. A model that grows from 40 seconds to 40 minutes quietly eats its lane's budget. The partition was correct for the DAG you had six months ago, and nothing rechecks it.

A prod backfill takes production offline

Run a long backfill against prod and your scheduled prod runs stop. Not slow down. Stop. This is the most important operational fact about SQLMesh plans and it is not obvious from the docs.

Here is the mechanism. Before Context.run() schedules a single node, it calls _block_until_finalized(), which reads the target environment's finalized_ts. While a plan is being applied that field is unset, and the run refuses to proceed:

Environment 'prod' is being updated by plan '...'. Retrying in 30 seconds...

It sleeps and retries. environment_check_interval defaults to 30 seconds and environment_check_max_wait defaults to six hours, which works out to 720 attempts. Every scheduled prod run that fires during your backfill lands in that loop and sits there.

If the backfill outlasts the wait, the run does not quietly skip. It raises:

Exceeded the maximum wait time for environment 'prod' to be ready. This means that the environment either failed to update or the update is taking longer than expected.

So a backfill under six hours hangs your runs, and a backfill over six hours starts failing them. That is the "hangs or exits" behavior, and it falls directly out of the defaults.

It works in the other direction too. A run already in flight when a plan begins is not allowed to finish. Context.run() passes a circuit breaker into the scheduler, which is checked before every single node:

def run_node(node: SchedulingUnit) -> None:
    if circuit_breaker and circuit_breaker():
        raise CircuitBreakerError()

The breaker trips when the environment's plan_id changes or its finalized_ts goes unset. Start a plan and every in-flight prod run aborts mid-execution and restarts, at which point it hits the block above and waits.

For the entire duration of a prod backfill, production is not running.

None of this machinery is prod-specific, incidentally. It keys off whatever environment you are running against, so a long plan on staging blocks staging runs the same way. Prod is just where it hurts.

What is and isn't affected

Your prod tables are fine. Every changed snapshot gets its own physical table named from its fingerprint, the backfill writes there, and the virtual layer swaps the views only at the end. Queries against prod keep returning the old data, uninterrupted, until the swap. Nothing is truncated and no dashboard goes blank.

What stops is execution. No new intervals are processed, and none will be for as long as the plan runs. Your tables keep serving data that gets staler by the hour, which is a different failure from an outage and considerably harder to notice.

The sanctioned path: build promotable objects outside prod

SQLMesh has a real answer to this, and it is better than "keep your backfills short." The work can be done somewhere else and then adopted by prod without re-running.

The concept is deployability. SQLMesh tracks, per snapshot, whether "the output produced by the given snapshot in a development environment can be reused in (deployed to) production." That is what DeployabilityIndex exists for, and it is the thing that makes the whole pattern work.

Physical tables are named from the snapshot fingerprint, not from the environment. So when you plan a breaking or non-breaking change into a dev environment, the backfill writes into the same physical table prod will eventually point at. Nothing about that table is dev-specific. The dev environment is a set of views over it.

Which means the promotion has no work left to do:

# 1. Build in a dev environment. Takes six hours. Prod is untouched and keeps running.
sqlmesh plan dev --auto-apply

# 2. Promote. The snapshots already exist and their intervals are already filled,
#    so there is nothing to backfill. SQLMesh reports this as a Virtual Update.
sqlmesh plan prod

Step 2 swaps views. It is measured in seconds, so the window in which prod runs block is measured in seconds. You have converted a six-hour production outage into a few seconds of one, and the only thing you changed was where the work happened.

That is the real argument for pre-prod backfills. Not that prod plans are risky, but that a promotable object built elsewhere turns your outage into a view swap.

The exception: forward-only changes have nothing to promote

The mechanism above depends on the dev backfill producing a table prod can adopt. That is not always true.

Forward-only and indirect-non-breaking snapshots are, in SQLMesh's own words, "not deployable by their nature." For these, a dev plan does not write into the shared table. It writes into a separate physical table built from the snapshot's dev_version and given a dev suffix, specifically so that experimenting in a dev environment cannot touch the table prod is serving from.

That isolation is correct, and it costs you the promotion. For a forward-only change there is no promotable object to build: the dev tables are throwaway, prod still has to do the work itself against the live table, and your prod runs still block while it does. Backfilling in dev first buys you nothing here.

So the rule has a shape. For normal changes, build outside prod and promote, and prod blocks for seconds. For forward-only changes there is nothing to pre-build, so the only lever left is making the change small enough that what prod has to do is short.

Restricting who can deploy to prod

This is the one nobody thinks about until a SOC2 or SOX compliance inspection. There is not a building concept of RBAC to sqlmesh so out of the box anyone with warehouse credentials can modify your prod environment.

What `users` and `REQUIRED_APPROVER` actually do

SQLMesh's config has a users list, and UserRole includes REQUIRED_APPROVER. It reads like access control. It isn't.

User is documented as information used for notifications. The REQUIRED_APPROVER role is consumed in exactly one place: the optional GitHub CI/CD bot, where it checks whether the required approvers have approved the pull request before allowing a /deploy command. It is a gate on a GitHub PR workflow, matched against GitHub usernames.

It has nothing to do with who can run sqlmesh plan prod from a terminal. There is no identity check anywhere in the core CLI: no --user flag, no current-user lookup, nothing that consults the users list for authorization. Anyone holding warehouse credentials and state database access can plan against prod from their laptop, and SQLMesh will not ask who they are.

If you are running the GitHub bot and your deploys genuinely happen through PR comments, REQUIRED_APPROVER is real and useful within that workflow. If your deploys happen any other way, it is doing nothing for you.

Python config, and the claim to avoid making

The advice you'll hear is "use config.py instead of config.yaml, because Python lets you enforce an approved list of prod users."

The first half is reasonable. The second half needs a correction and a caveat.

The correction: users is a plain config field and serializes fine from YAML. "YAML can't express an allowlist" is false, and someone will check. What Python config actually gives you is executable logic at config-load time. You can read an environment variable, check an identity, and raise before a Context is ever constructed. That's a real distinction, and it's the one worth making.

# config.py
import os
from sqlmesh.core.config import Config, GatewayConfig, SnowflakeConnectionConfig
from sqlmesh.utils.errors import ConfigError

# This is a guardrail against accident, not a security control.
# CI_DEPLOY_IDENTITY must be injected by the CI runner, somewhere the person
# being restricted cannot set it. If they can edit this file, they can delete
# this check, and they can also just pass --config and point somewhere else.
APPROVED_PROD_DEPLOYERS = {"ci-deploy-bot", "release-manager"}

def _prod_gateway() -> GatewayConfig:
    identity = os.environ.get("CI_DEPLOY_IDENTITY")
    if identity not in APPROVED_PROD_DEPLOYERS:
        raise ConfigError(
            f"'{identity}' is not authorized to deploy to prod. "
            f"Approved: {sorted(APPROVED_PROD_DEPLOYERS)}"
        )
    return GatewayConfig(
        connection=SnowflakeConnectionConfig(
            account=os.environ["SNOWFLAKE_ACCOUNT"],
            user=os.environ["SNOWFLAKE_USER"],
            password=os.environ["SNOWFLAKE_PASSWORD"],
            warehouse="PROD_WH",
            database="ANALYTICS",
        ),
    )

config = Config(
    gateways={
        "prod": _prod_gateway(),
        "dev": GatewayConfig(
            connection=SnowflakeConnectionConfig(
                account=os.environ["SNOWFLAKE_ACCOUNT"],
                user=os.environ["SNOWFLAKE_USER"],
                password=os.environ["SNOWFLAKE_PASSWORD"],
                warehouse="DEV_WH",
                database="ANALYTICS_DEV",
            ),
        ),
    },
    default_gateway="dev",
)

Now the caveat, stated plainly: this stops the mistake. It does not stop the decision.

The file lives in the repository. The person you are restricting can edit it, delete the check, pass --config pointing at their own file, or run against their own project directory. As a guardrail it's genuinely useful. It catches the engineer who typed prod when they meant their dev environment, which is the actual common failure. As a control against someone who intends to deploy to prod, it is decoration.

Where the control has to live

If you need real enforcement, it cannot live in a file that the person being restricted can edit. It has to live somewhere they don't control:

Prod warehouse credentials that don't exist on developer laptops. This is the only one that's actually airtight. If the credentials to write to prod are only ever injected into a CI runner or a service account, the question of who can run plan prod answers itself.
CI environment protection rules with required reviewers on the prod environment, so the deploy job cannot execute without approval.
A separate deploy identity that holds the state database connection for prod, so a developer's local SQLMesh cannot reach prod state at all.

Everything else is process. Process is worth having. Most incidents are accidents, and guardrails against accidents prevent most incidents. Just don't take the guardrail to a security review and call it a control.

The framework is not the system

Read together, these three have the same shape.

SQLMesh schedules a DAG correctly and gives you one global concurrency dial to feed it, then leaves workload isolation to you, as tags and selector strings you maintain by hand, with nothing to tell you when the partition has drifted out from under your DAG. It isolates snapshots and swaps views atomically, then lets you choose the two operations that bypass that safety net without ceremony. It gives you a config format expressive enough to write an authorization check, in a file the person being authorized can edit.

In all three cases the primitive is right and the operating envelope is left to you. That is a defensible framework design, since the alternative is a tool that imposes one team's operational opinions on everyone, but it means the work doesn't end when the models are correct. It means somebody owns workload partitioning, promotion discipline, and credential boundaries. And each of those three has the same failure signature: nothing errors, nothing alerts, and you find out from a stakeholder. If nobody has been named to own them, nobody owns them.

Have feedback or corrections? Reach out directly. We update these posts when we find things that are wrong or outdated.