Shared multi-project environments cannot preserve gateway-specific published catalogs

[josephfinlayson]

Problem

SQLMesh can route model execution through each model's effective gateway, including when multiple projects are composed into one shared environment. However, the gateway connection's default catalog also qualifies the model, so it normally determines both:

• where versioned physical snapshots are stored; and
• where stable environment views are published.

environment_catalog_mapping can move the published layer, but it is one environment-wide override. It cannot choose a different published catalog for each gateway.

As a result, a shared environment cannot preserve gateway-specific physical catalogs while also publishing each gateway's models into a separate stable catalog. Users must currently choose between collapsing the published layer into one catalog, publishing views beside physical snapshots, or keeping separate SQLMesh environments/state.

Minimal example

Consider two projects loaded into the same prod environment:

gateways:
  project_a:
    connection:
      type: databricks
      catalog: project_a_physical
      # connection details omitted

  project_b:
    connection:
      type: databricks
      catalog: project_b_physical
      # connection details omitted

gateway_managed_virtual_layer: true

Models in each project select their gateway through the normal effective-gateway rules (an explicit model gateway, project model defaults, or the selected gateway).

The desired layout for the shared prod environment is:

Effective gateway	Physical snapshots	Stable prod views
project_a	project_a_physical	project_a_published
project_b	project_b_physical	project_b_published

Today, leaving catalog mapping unset publishes each project's views in its physical catalog. Setting environment_catalog_mapping can move the views, but moves the environment to one common catalog rather than preserving a gateway-specific published catalog.

Desired behavior

SQLMesh should support a gateway-level published/virtual-layer catalog that is independent of the connection's physical catalog. The route should be resolved from the model's effective gateway so a shared environment can retain project-specific physical and published boundaries without repeating catalog policy on every model.

Any implementation needs to preserve SQLMesh's full environment lifecycle:

• Persist the resolved published route with model/snapshot metadata so partial-project plans still know where remote snapshots are published when the originating project configuration is not loaded.
• Use that persisted route for promotion, old-view demotion, environment invalidation, and janitor cleanup.
• Treat a published-route-only change as metadata-only: publish the existing physical snapshot at the new stable location and remove the old view without rebuilding the physical table.
• Fail closed when composed projects define conflicting published routes for the same gateway name, rather than depending on project load order.
• Preserve the existing precedence and behavior of environment_catalog_mapping for backward compatibility.
• Leave external/source models unaffected; only SQLMesh-managed relations should inherit the route.
• Keep the new configuration and serialized metadata optional so existing projects, cached models, and stored snapshots retain their current behavior.

Alternatives considered

• One shared published catalog: works technically, but collapses gateway/project publication boundaries and can require broad grants and view/name migrations.
• Publish views beside physical snapshots: preserves gateway isolation but does not separate stable consumer-facing names from versioned physical storage.
• Separate state and environments per project: preserves isolation but prevents one shared environment from coordinating a cross-project DAG and lifecycle.
• Per-model catalog configuration: can express the route but duplicates an ownership policy across models and is prone to drift. The gateway is already SQLMesh's execution/ownership boundary.
• environment_catalog_mapping: remains useful for environment-wide overrides, but is not granular enough for a multi-gateway environment.

Related work

• Feat: Configure virtual layer catalogs per gateway #5963 is a draft prototype of a gateway-level virtual_layer_catalog and the required lifecycle persistence.
• Feat: Support gateway in model defaults #5962 added gateway selection in model defaults, which supplies part of the effective-gateway routing foundation.
• Supporting generic physical catalog/schema mapping #4684 concerns generic physical catalog/schema mapping. That is complementary but distinct: this issue is about separating gateway-specific stable published catalogs from gateway-specific physical snapshot catalogs inside one shared environment.
• Support environment specific catalogs #3251 discusses environment-specific catalogs, but does not provide per-gateway publication routing within one environment.