SQLMesh Plans and Virtual Environments: What dbt State Cannot Isolate, and When to Stay

A PR passed slim CI. state:modified+ and --defer built three models against prod’s upstream tables. Merge. Prod’s nightly job ran the same three models, plus a macro that CI had not marked modified because the comparison manifest was from Tuesday. fct_orders doubled Monday. The CI story was correct for what it compared. It never promised “this is the plan prod will apply.”

That is the gap SQLMesh is selling: a plan is an explicit diff of snapshots, reviewed, then applied. dbt’s state flags are a select. I like both. I will not pretend they are the same object. I also will not burn a year rewriting 800 dbt models to get a plan.

flowchart TD
  dev[PR / dev environment] --> plan[sqlmesh plan]
  plan --> diff[Snapshot diff]
  diff -->|reject| stop[Nothing applied]
  diff -->|apply| views[Virtual env views]
  views --> phys[Physical snapshot tables]
  dbt[dbt state:modified + defer] --> ci[CI schema]
  ci --> merge[Merge]
  merge --> night[Prod job selects models]
  night --> maybe[Maybe the same set]

A plan applies a known diff. dbt defer makes CI cheap. Only one of those is a prod apply preview.

Plans versus dbt state

sqlmesh plan prod computes which models changed, which downstream snapshots must move, and what SQL will run. You read it. Then you apply it. That is closer to Terraform than to dbt run --select.

dbt --select state:modified+ --defer builds a subset in CI and reads prod for the rest. It is the right way to keep CI under ten minutes. It is not a lock on what Monday’s job will do after two more merges. The manifest you compared against is a file someone has to upload. When it is stale, CI is a rumor.

If your pain is CI time, fix slim CI. If your pain is “we do not know what prod will run,” that is a plan — or a promotion job that runs the exact select CI ran, from the same SHA.

from sqlmesh.core.context import Context

ctx = Context(paths=["."])
# Preview only. Nothing moves until apply.
plan = ctx.plan("prod", auto_apply=False, skip_tests=False)

changed = [s.name for s in plan.new_snapshots]
print("snapshots that would move:", changed)
if "marts.fct_orders" in {str(n) for n in changed}:
    if not plan.directly_modified:
        raise SystemExit("fct_orders moved only as a downstream; inspect the plan")

# CI or a human applies after review.
# ctx.apply(plan)

I print the plan in the PR and I refuse auto-apply to prod from a laptop. Auto-apply to a personal virtual environment is fine. Prod is a reviewed apply from CI, same as a Terraform apply.

Virtual environments are views

A SQLMesh environment is not a cloned warehouse. Unchanged models keep pointing at the existing physical snapshot. Changed models get new snapshot tables. The environment is a layer of views (or table names) that make “dev” see the new stg_orders and the old dim_customer without rebuilding the dimension.

That is the feature dbt does not have natively. dbt schemas plus Snowflake clones can approximate it. They are extra machinery. SQLMesh makes the pointer swap the product.

MODEL (
  name marts.fct_orders,
  kind INCREMENTAL_BY_TIME_RANGE (
    time_column ordered_at
  ),
  start '2025-01-01',
  cron '@daily',
  grain (order_id),
  audits (
    unique_values(columns := (order_id)),
    not_null(columns := (order_id, customer_id))
  )
);

select
  order_id,
  customer_id,
  amount_usd,
  ordered_at
from staging.orders
where ordered_at between @start_ds and @end_ds;

INCREMENTAL_BY_TIME_RANGE is a write contract. You still need a lookback for late arrivals — the same class of bug as a dbt incremental missing its filter, documented in dbt incremental models. SQLMesh will not invent the late window you did not write.

When not to migrate off dbt

Stay on dbt when the project is healthy, the team thinks in ref(), you already have slim CI, and the warehouse adapter ecosystem is why you picked dbt. A plan is not worth 800 models of rewrite and a year of “how do I do this macro.”

Migrate a new domain, or a greenfield, if the team wants virtual environments and is willing to own SQLMesh’s scheduler semantics. Do not migrate because a vendor deck said dbt state is obsolete. State is a select. You can make the select honest.

Hybrid is allowed: dbt for the marts everyone knows, SQLMesh for a new pipeline that needs environment isolation. Two tools is a cost. A rewrite is a larger one.

You still need an orchestrator

sqlmesh run has a cron per model. That is not retries across a warehouse load, a file drop, and a dbt project in the same account. Something still kicks the run, pages, and backfills: Airflow, Dagster, or Prefect. The pick is in Airflow vs Dagster vs Prefect. SQLMesh does not replace that box. Treating it as Airflow-plus-dbt is how you discover there is no sensor for the S3 drop.

Failure modes

Auto-apply to prod. A plan you did not read.

Stale dbt manifest blamed on SQLMesh. Different tools. Fix the artifact.

Rewrite the estate for environments. Clones or a dev warehouse may have been enough.

No orchestrator. Nightly is a cron on a laptop.

Incremental without a late window. Same hole as dbt.

What to do Monday

If dbt CI is the fire, fix the manifest and defer. If prod applies are the fire, introduce a promotion job that runs a known select from a SHA — or adopt SQLMesh on the next new domain, with plans applied from CI and an orchestrator that still owns the clock. Do not schedule a conversion of the working marts.