Add scoped snapshot cleanup to invalidate command #5844

Problem

Currently, when invalidating an environment with sqlmesh invalidate <env> -s, the command only removes the environment record and virtual layer (views/schemas), but does not clean up the physical snapshot tables.

To clean up snapshots, users must run sqlmesh janitor --ignore-ttl separately. However, this operates globally and will delete all unreferenced snapshots across the project, including those from other previously invalidated environments that haven't been cleaned up yet.

This creates a problem when you want to clean up snapshots for a specific environment without affecting other environments' snapshots.

Proposed Solution

Add a --cleanup-snapshots flag to the invalidate command that triggers scoped snapshot cleanup for only the target environment. This would:

1. Capture the snapshot IDs from the environment being invalidated (before deletion)
2. Identify which of those snapshots are not referenced by any other environment
3. Delete only those exclusively-owned snapshots

Implementation Approach

The feature requires changes across multiple layers:

1. CLI Layer (sqlmesh/cli/main.py)

Add a new flag to the invalidate command:

@click.option(
    "--cleanup-snapshots",
    is_flag=True,
    help="Clean up snapshots exclusively used by this environment after invalidation",
)

2. Context Layer (sqlmesh/core/context.py)

Modify invalidate_environment to capture snapshot IDs before deletion and pass them to a scoped cleanup function when the flag is set. 1

3. State Sync Layer (sqlmesh/core/state_sync/db/facade.py)

Add a method to get expired snapshots scoped to a specific set of snapshot IDs, while still respecting the protection of snapshots referenced by other environments.

4. Snapshot State Layer (sqlmesh/core/state_sync/db/snapshot.py)

Modify get_expired_snapshots to accept an optional target_snapshot_ids parameter that restricts the query to only those IDs, while still excluding any that appear in other environments' promoted_snapshot_ids.

5. Janitor Layer (sqlmesh/core/janitor.py)

Add support for scoped snapshot deletion in delete_expired_snapshots and iter_expired_snapshot_batches.

Example Usage

# Invalidate environment and clean up its exclusively-owned snapshots
sqlmesh invalidate dev_mday_picnic_ibuk_test -s --cleanup-snapshots

This would be equivalent to running:

sqlmesh invalidate dev_mday_picnic_ibuk_test -s
sqlmesh janitor --ignore-ttl  # but scoped to only this environment's snapshots

Notes

• The implementation must preserve the existing protection mechanism: snapshots shared with other environments (e.g., prod) must never be deleted
• The --cleanup-snapshots flag should work with or without the -s flag
• This feature would be particularly useful for CI/CD workflows where PR environments are frequently created and invalidated

Wiki pages you might want to explore:

• Overview (SQLMesh/sqlmesh)

Citations

File: docs/integrations/github.md (L289-292)

| Option                                | Description                                                                                                                                                                                                                                                                                                                                                                                               |  Type  | Required |
|---------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|:------:|:--------:|
| `invalidate_environment_after_deploy` | Indicates if the PR environment created should be automatically invalidated after changes are deployed. Invalidated environments are cleaned up automatically by the Janitor. Default: `True`                                                                                                                                                                                                             |  bool  |    N     |
| `merge_method`                        | The merge method to use when automatically merging a PR after deploying to prod. Defaults to `None` meaning automatic merge is not done. Options: `merge`, `squash`, `rebase`                                                                                                                                                                                                                             | string |    N     |