如何追踪dbt创建的所有表与视图，清理无对应模型的孤立表？

方案1：dbt内置变量+MSSQL系统表查询（无额外依赖，快速见效） #

不用维护额外的元数据表，直接通过两端数据比对就能拿到结果：

1. 先导出当前dbt项目所有有效对象列表，执行命令：
   dbt ls --output json --output-keys schema name resource_type
   过滤返回结果中resource_type为model/seed/snapshot的条目，整理成<schema_name>.<object_name>的格式存为列表。
2. 在MSSQL中执行查询，拿到所有dbt创建的表/视图列表：

SELECT 
  s.name AS schema_name,
  o.name AS object_name,
  o.type_desc AS object_type
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
-- 关联dbt默认生成的扩展属性，过滤非dbt创建的业务表
LEFT JOIN sys.extended_properties ep 
  ON ep.major_id = o.object_id 
  AND ep.name = 'dbt_version'
WHERE 
  -- U=用户表 V=视图
  o.type IN ('U', 'V')
  -- 替换为你项目中dbt用到的所有schema
  AND s.name IN ('dbt_prod', 'dbt_dev', '其他dbt schema')
  AND ep.value IS NOT NULL

3. 两个列表做差集，得到的就是当前无对应dbt模型的孤立表/视图。

方案2：on-run-end hook维护元数据表（适合长期自动化追踪） #

就是你提到的第一个思路，可实现自动追踪不用每次手动导出列表：

1. 先在MSSQL中创建专门的管理表，示例DDL：

CREATE TABLE dbt_meta.managed_objects (
  schema_name VARCHAR(100) NOT NULL,
  object_name VARCHAR(100) NOT NULL,
  object_type VARCHAR(50) NOT NULL,
  first_seen_at DATETIME NOT NULL DEFAULT GETDATE(),
  last_seen_at DATETIME NOT NULL DEFAULT GETDATE(),
  PRIMARY KEY (schema_name, object_name)
)

2. 新建dbt宏实现元数据自动upsert：

{% macro upsert_managed_objects() %}
{% if execute %}
{% set managed_objects = [] %}
{% for node in graph.nodes.values() if node.resource_type in ['model', 'seed', 'snapshot'] %}
  {% do managed_objects.append(
    "('" ~ node.schema ~ "', '" ~ node.name ~ "', '" ~ node.config.materialized ~ "', GETDATE())"
  ) %}
{% endfor %}

{% if managed_objects | length > 0 %}
MERGE dbt_meta.managed_objects AS target
USING (VALUES {{ managed_objects | join(', ') }}) 
  AS source (schema_name, object_name, object_type, update_time)
ON target.schema_name = source.schema_name 
  AND target.object_name = source.object_name
WHEN MATCHED THEN
  UPDATE SET last_seen_at = source.update_time, object_type = source.object_type
WHEN NOT MATCHED THEN
  INSERT (schema_name, object_name, object_type, first_seen_at, last_seen_at)
  VALUES (source.schema_name, source.object_name, source.object_type, source.update_time, source.update_time);
{% endif %}
{% endif %}
{% endmacro %}

3. 在dbt_project.yml中配置hook，每次dbt执行完自动更新元数据：

on-run-end:
  - "{{ upsert_managed_objects() }}"

后续要查孤立表直接用方案1的系统表查询结果，和dbt_meta.managed_objects中last_seen_at在最近1个月内的对象做差集即可。

方案3：Schema隔离（适合非生产环境快速清理） #

你提到的第二个思路，实现简单几乎不用额外开发：

• 不同环境、不同开发分支完全隔离schema，比如开发人员个人环境用dbt_{人名}，测试分支用dbt_test_{分支号}，生产单独用dbt_prod
• 废弃的分支、下线的环境直接删除对应整个schema，不需要单独排查孤立表，一次性清理所有冗余对象

清理注意事项 #

• 清理前先给所有孤立表加_to_delete后缀，观察1-2周无业务报错再正式删除
• 比对列表时注意排除dbt source、外部表等非dbt创建的对象，避免误删
• 生产环境的清理操作必须走审批流程，建议先备份待删对象数据

内容的提问来源于stack exchange，提问作者MYK