The Snowflake tables nobody owns: governing object lifecycle at scale with dbt

Every team running dbt at scale hits this problem eventually.

You rename a model. Delete one. Restructure a domain. The dbt project moves forward but the old table stays behind in Snowflake, unmanaged, quietly accumulating. Nobody is maintaining it. But it looks legitimate to anyone who stumbles across it or connects it to reporting.

Multiply that across multiple active dbt projects in UAT and PROD, and you have a governance problem which erodes the confidence in the Enterprise Data Plarform.

Why we didn't use a post-hook

The community's standard answer is a post-hook. Post-hook fires after any dbt run — CI jobs, daily scheduled builds, ad-hoc manual runs. In PROD, that means a 2am job could silently drop a table with no audit trail, no notification, no human in the loop. In an enterprise environment, that's not acceptable. We needed something different.

What we built

A centralised dbt macro package — no runnable models — installed across all projects. The core macro is identify_orphaned_tables, triggered on CD merge in three modes across UAT and Prod:

uat_drop — drops orphans immediately in UAT. Low risk, fast cleanup.
prod_rename — renames the table in PROD (e.g. TABLE_ORPHAN_20240601) and notifies the SPOC. No data lost.
prod_drop — only runs after 7 days in renamed state. Never automatic. Sends a second SPOC notification before dropping anything.

Every action writes to DBT_ADMIN.ORPHAN_AUDIT_LOG in Snowflake — project, mode, timestamp, object name.

How projects are configured

Each project declares its SPOC in dbt_project.yml:
yaml
vars:
orphan_spoc_email: "finance-team@company.com"

Each project also ships an orphan_exclusions.csv seed file. Objects listed there are skipped entirely — legacy tables, external tool outputs, intentionally retained objects. Teams manage their own exceptions without touching the centralised package.

The notification pipeline

When prod_rename or prod_drop fires, the SPOC receives an automated email alert via dbt Cloud's job notifications or through a lightweight pipeline that watches the audit log and sends an HTML summary. The notification is decoupled from the CD job — pipeline hiccups don't swallow alerts. The SPOC gets the table name, project, and timestamp, with 7 days to raise a concern before prod_drop runs.

Why CD merge — not post-hook

Timing is exact — a table becomes an orphan the moment its model is removed. That's exactly when the merge happens.

There's a human behind every trigger — a developer made a deliberate PR, reviewed and approved. Orphan detection is a direct consequence of an intentional code change, not a background sweep.

It fits the existing governance gate — PROD deployments already require sign-off. Orphan detection rides the same gate.

The two-phase PROD lifecycle

Detection and renaming happen automatically at merge. Tables drop only happens after 7 days, an explicit second trigger, and a second SPOC notification. That gap is what makes this governed rather than just automated.

What surprised us

Over 600 orphans on projects we considered well-maintained. Not carelessness — dbt moves fast and cleanup rarely keeps pace. The exclusions files also surfaced informal retention agreements that existed nowhere in documentation. The audit log became an unexpected project history — when did this table stop being managed, what replaced it.

The broader point

Post-hook solves a cleanup problem. What we built solves a governance problem. The distinction matters when multiple teams and stakeholders need to trust the platform is actively managed — not just periodically swept.

If your team is hitting orphan table sprawl, the question isn't just how do we delete these. It is how do we govern the full lifecycle of every object we create. Reach out to Altis and we will walk you through it.