如何自动捕获SQL中未使用的CTE？求SQL断言、dbt检查等方案

一、用dbt搞定的两种思路 #

• 靠dbt元数据查询
  dbt运行模型时会自动收集元数据，包括CTE的引用关系。你可以直接查询dbt生成的元数据表（比如dbt_ctes、dbt_cte_references，具体取决于你的dbt版本和数据库适配器），找出未被引用的CTE。比如针对你的示例SQL，模型运行完成后执行：

SELECT cte_name
FROM dbt_ctes
WHERE model_name = '你的模型名'
  AND cte_name NOT IN (
    SELECT referenced_cte_name
    FROM dbt_cte_references
    WHERE model_name = '你的模型名'
  )

• 写自定义dbt测试
  自己编写宏来解析模型的SQL代码：先提取所有CTE名称，再找出查询中实际用到的CTE，对比后就能揪出未使用的。大致步骤：

1. 获取模型的SQL文本
2. 用正则提取WITH子句里的CTE名称（比如匹配(\w+) as模式）
3. 提取主查询及其他CTE中引用的表/CTE名称
4. 对比两组名单，未出现在引用列表里的就是未使用的CTE
5. 若存在未使用的CTE，直接让测试失败并抛出提示

二、数据库原生+脚本方案 #

• 扒执行计划
  大部分数据库（PostgreSQL、BigQuery、Snowflake等）都支持EXPLAIN命令生成执行计划，计划里只会包含实际被使用的对象。针对你的示例SQL，执行：

EXPLAIN ANALYZE
WITH unused_cte as 
   (select 1 as one),
used_cte as
   (select 2 as two)
select * from used_cte

查看输出只会看到used_cte的相关节点，unused_cte不会出现在计划中。你可以编写脚本自动解析EXPLAIN的输出，识别未出现的CTE。

• 写脚本解析SQL
  用Python或JavaScript调用SQL解析库（比如Python的sqlglot、sqlparse）处理SQL文本。举个Python示例：

import sqlglot

sql = """
with unused_cte as 
   (select 1 as one),
used_cte as
   (select 2 as two)
select * from used_cte
"""
parsed = sqlglot.parse_one(sql)
# 提取所有CTE名称
ctes = [cte.alias_or_name for cte in parsed.args.get("with", [])]
# 提取查询中引用的表/CTE
references = [ref.name for ref in parsed.find_all(sqlglot.exp.Table)]
# 找出未使用的CTE
unused_ctes = [cte for cte in ctes if cte not in references]
print("未使用的CTE:", unused_ctes)

运行脚本会直接输出['unused_cte']，把这个逻辑集成到CI/CD流程中，就能自动检查所有SQL文件。

三、IDE和静态检查工具 #

• 像DataGrip、DBeaver这类SQL IDE自带静态代码分析功能，开启相关规则后，未使用的CTE会直接高亮提示，一眼就能发现问题。
• 使用sqlfluff这类SQL lint工具，配置好检测未使用CTE的规则，在代码提交前自动触发检查，直接把问题拦在提交阶段。

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