在dbt中使用temp db（#临时表）替代CTEs的方案咨询

当然可以在dbt中实现临时表复用的方案，针对你提到的动态层级、无法全物化的场景，以下是具体实现方式：

1. 手动创建会话级临时表 #

利用dbt-sqlserver适配器提供的{{ create_temp_table() }}宏，在单个模型内手动创建临时表，替代多层嵌套CTE。每个临时表仅执行一次，避免底层逻辑重复计算。

示例模型代码：

-- 创建最底层临时表L3
{{ create_temp_table('#tbl_L3') }}
INSERT INTO #tbl_L3
SELECT 
    id, 
    category, 
    amount 
FROM raw_data.sales 
WHERE sale_date >= DATEADD(month, -6, GETDATE());

-- 创建L2临时表，基于L3
{{ create_temp_table('#tbl_L2') }}
INSERT INTO #tbl_L2
SELECT 
    id, 
    category, 
    SUM(amount) AS monthly_amount 
FROM #tbl_L3
GROUP BY id, category, DATEPART(month, sale_date);

-- 创建L1临时表，基于L2
{{ create_temp_table('#tbl_L1') }}
INSERT INTO #tbl_L1
SELECT 
    category, 
    SUM(monthly_amount) AS total_category_amount 
FROM #tbl_L2
GROUP BY category;

-- 最终输出结果
SELECT * FROM #tbl_L1;

2. 动态层级的自动化处理 #

针对层级深度动态的场景，可以编写自定义Jinja宏来循环生成临时表，减少重复代码：

步骤1：编写自定义宏 #

在macros/目录下创建temp_table_generator.sql：

{% macro generate_temp_tables(level_configs) %}
    {% for config in level_configs %}
        -- 创建临时表
        {{ create_temp_table(config.table_name) }}
        -- 插入数据
        INSERT INTO {{ config.table_name }}
        {{ config.query_sql }};
    {% endfor %}
{% endmacro %}

步骤2：在模型中调用宏 #

{% set dynamic_levels = [
    {
        "table_name": "#tbl_L3",
        "query_sql": "SELECT id, category, amount FROM raw_data.sales WHERE sale_date >= DATEADD(month, -6, GETDATE())"
    },
    {
        "table_name": "#tbl_L2",
        "query_sql": "SELECT id, category, SUM(amount) AS monthly_amount FROM #tbl_L3 GROUP BY id, category, DATEPART(month, sale_date)"
    },
    {
        "table_name": "#tbl_L1",
        "query_sql": "SELECT category, SUM(monthly_amount) AS total_category_amount FROM #tbl_L2 GROUP BY category"
    }
] %}

-- 生成所有层级临时表
{{ generate_temp_tables(dynamic_levels) }}

-- 输出最终结果
SELECT * FROM #tbl_L1;

关键注意事项 #

• 临时表是会话级隔离的，dbt每个模型的执行都在独立会话中，不会出现跨模型的命名冲突
• 临时表在模型执行完成后会自动销毁，无需手动清理
• 避免在多个模型间共享临时表（会话隔离特性导致无法跨模型访问），如果需要跨模型复用，可考虑使用全局临时表（##tbl_xxx），但需注意并发冲突风险

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