dbt增量加载生成临时表，如何实现自动删除？

针对你使用dbt-clickhouse 1.4.8搭配ClickHouse Server 23.3.8时，增量物化过程中生成的dim_table__dbt_new_data_xxxx这类临时表，以下是几种自动清理的可行方案：

1. 利用dbt-clickhouse内置TTL配置 #

dbt-clickhouse在delete+insert增量策略下本应自动清理临时表，若出现残留，可直接在模型配置中添加临时表生命周期参数，让ClickHouse自动过期清理：

{{
    config(
        materialized='incremental',
        on_schema_change='sync_all_columns',
        unique_key='id',
        incremental_strategy='delete+insert',
        tags = ['tag'],
        # 设置临时表1小时后自动过期
        temp_table_ttl = 'HOUR 1'
    )
}}
WITH src_table AS (
    select * from {{ref('src_table')}} )

SELECT
    * FROM
    (
        SELECT
            *,
            row_number() OVER (
                PARTITION BY
                    id
                ORDER BY
                    updated_at DESC
            ) AS r
        FROM
            src_table
    ) where
    r = 1

{% if is_incremental() %}

  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

2. 自定义dbt清理宏+运行钩子 #

如果内置配置不生效，可以编写自定义宏，在每次dbt任务结束后批量清理残留临时表：

第一步：创建清理宏 #

在项目的macros目录下新建cleanup_temp_tables.sql，内容如下：

{% macro cleanup_dbt_temp_tables(database=target.database, schema=target.schema) %}
    {% set temp_tables_query %}
        SELECT name FROM system.tables
        WHERE database = '{{ database }}'
          AND schema = '{{ schema }}'
          AND name LIKE '%__dbt_new_data_%'
    {% endset %}

    {% set temp_tables = run_query(temp_tables_query) %}

    {% for table in temp_tables.rows %}
        {% do run_query("DROP TABLE IF EXISTS " ~ database ~ "." ~ schema ~ "." ~ table[0]) %}
    {% endfor %}
{% endmacro %}

第二步：配置运行钩子 #

修改项目根目录的dbt_project.yml，添加on-run-end钩子：

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

这样每次dbt任务执行完毕后，会自动清理当前目标库和schema下的dbt临时表。

3. ClickHouse侧定时任务清理 #

直接在ClickHouse中创建定时任务，定期扫描并清理符合命名规则的临时表：

CREATE SCHEDULED TASK cleanup_dbt_temp_tables
EVERY 1 HOUR
AS
DROP TABLE IF EXISTS your_database.your_schema.*__dbt_new_data_* SYNC;

将your_database.your_schema替换为你的实际库表路径，该任务会每小时执行一次清理操作。

补充说明 #

你当前的模型配置逻辑本身无问题，临时表残留大概率是特定场景下dbt-clickhouse的清理逻辑遗漏，优先尝试第一种TTL配置方案，操作最简便且贴合dbt生态。

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