[Feature]: sst drop — Remove Orphaned Semantic Views from Snowflake

[sophiascaglioni-whoop]

Problem Statement

When a semantic model is renamed in the dbt project, running sst generate creates the new semantic view via CREATE OR REPLACE SEMANTIC VIEW — but the old view persists in Snowflake because nothing ever drops it. Over time, orphaned semantic views accumulate in the target schema, creating confusion about which views are authoritative.

Proposed Solution

Add an sst drop command with two modes:

Mode 1: Drop a specific view

sst drop <view_name> [--target TARGET] [--db DB] [--schema SCHEMA]

Drops a single named semantic view from the target schema. Useful for ad-hoc cleanup.

Mode 2: Prune orphaned views (--prune)

sst drop --prune [--target TARGET] [--db DB] [--schema SCHEMA] [--dry-run]

Cross-references the SM_SEMANTIC_VIEWS tracking table against actual semantic views in the target schema (via SHOW SEMANTIC VIEWS IN <db>.<schema>). Any semantic view that exists in Snowflake but is not in SM_SEMANTIC_VIEWS is considered orphaned and dropped.

Safety features:

• Dry-run by default when --prune is used — shows what would be dropped without executing. Require --execute or --confirm to actually drop.
• Confirmation prompt — before dropping, list orphaned views and ask for explicit y/N confirmation (skippable with --yes for CI).
• Only targets SST-managed views — the cross-reference against SM_SEMANTIC_VIEWS ensures we never touch views that SST didn't create.

Example output (dry-run):

$ sst drop --prune --dry-run

  Reading metadata from: ANALYTICS.SEMANTIC_VIEWS
  Scanning semantic views in: ANALYTICS.SEMANTIC_VIEWS

  Found 72 semantic views in schema
  Found 66 views in SM_SEMANTIC_VIEWS tracking table
  
  6 orphaned semantic views detected:

    TABLE_NAME_1 (created 2026-01-29)
    TABLE_NAME_2 (created 2026-01-29)

  DRY RUN — no views were dropped. Re-run with --execute to drop.

CI integration:

# In the weekly CircleCI pipeline, after generate:
sst generate --all --defer-target prod
sst drop --prune --execute --yes

Alternatives Considered

• Manual DROP SEMANTIC VIEW SQL — Current workaround. Error-prone, requires remembering which views are stale, and requires elevated Snowflake permissions each time.
• Add --prune flag to sst generate — Bundles cleanup with generation in a single command. Considered too risky — users running sst generate may not expect views to be dropped. A separate command makes the destructive action explicit.
• Add --prune flag to sst deploy — Same concern as above; deploy already orchestrates validate → extract → generate, and adding implicit drops to that pipeline is surprising behavior.

Priority

Medium — Operational hygiene. Orphaned semantic views cause confusion when users browse the schema or query the wrong (stale) view.

Impact

• analytics-dbt maintainers — Primary users who rename/consolidate semantic models
• CI/CD pipeline — Can automate cleanup as a post-generate step
• Consumers of semantic views — Fewer stale views means less confusion when discovering views via Snowflake UI or SHOW SEMANTIC VIEWS

Technical Considerations

• Permissions: DROP SEMANTIC VIEW requires ownership or DROP privilege on the schema. The CIRCLECI_SVC_ROLE (which owns all current views) should have this. Individual developers may not — the command should surface a clear permissions error.
• SQL pattern: DROP SEMANTIC VIEW IF EXISTS <db>.<schema>.<view_name>; — use IF EXISTS to make the command idempotent.
• Discovery query: SHOW SEMANTIC VIEWS IN <db>.<schema> returns all views; anti-join against SM_SEMANTIC_VIEWS.NAME to find orphans.
• Existing CLI patterns: The command should follow the same option conventions as generate — --target, --db, --schema, --verbose, --dry-run. It should use the same SnowflakeConfig and ConnectionManager infrastructure.
• Lazy loading: Register in LAZY_COMMANDS in main.py like all other commands.
• Events: Fire events like ViewDropped, ViewDropFailed for consistent CLI output via the event system.

Example Usage

# Drop a single orphaned view
sst drop TABLE1 --target prod

# Preview what --prune would drop
sst drop --prune --dry-run

# Actually prune orphaned views (interactive confirmation)
sst drop --prune --execute

# Prune in CI (skip confirmation prompt)
sst drop --prune --execute --yes

# Verbose output for debugging
sst drop --prune --dry-run --verbose

Additional Context

• NA at this time

Pre-submission Checklist

• I have searched existing issues to avoid duplicates
• I have included all required environment details
• I have provided a clear problem statement and solution

[williamtsu]

Design wise, for semantic view discovery I would query the information_schema directly and use an anti join akin to the following

SELECT 
    'DROP SEMANTIC VIEW IF EXISTS ' || sv.catalog || '.' || sv.schema || '.' ||sv.name ||';' as drop_command
FROM <db>.<schema>.semantic_views AS sv --the literal semantic view that exist
full join <db.<schema>.sm_semantic_views ssv --the canonical list of semantic views that should be made
    ON lower(sv.name) = ssv.name
WHERE ssv.name is null;

SHOW SEMANTICS VIEWS would require an additional caching of the query results.

But other than that nit! yes this was the concept I had in mind.

It mirrors the following dbt macro that is commonly shared for cleaning up tables and views
https://discourse.getdbt.com/t/clean-your-warehouse-of-old-and-deprecated-models/1547